How to measure mySQL bottlenecks? -


what mysql server variables should looking @ , thresholds significant following problem scenarios:

  1. cpu bound
  2. disk read bound
  3. disk write bound

and each scenario, solutions recommended improve them, short of getting better hardware or scaling database multiple servers?

this complicated area. "thresholds" affect each of 3 categories overlap quite bit.

  1. if having problems operations being cpu bound, need at: (a) structure of database - normalized. bad db structure leads complex queries hit processor. (b) indexes - needed queries sufficiently indexed. lack of indexes can hit both processor , memory hard. check indexes, "explain ...your query". row in resulting explanation says isn't using index, need @ closely , if possible, add index. (c) use prepared statements wherever possible. these can save cpu doing quite bit of crunching. (d) use better compiler optimizations appropriate cpu. 1 dedicated types, can glean odd percent here , there.

  2. if having problems operations being read bound (a) ensure caching possible. check configuration variables query_cache_limit , query_cache_size. isn't magic fix, raising these can help. (b) above, check indexes. indexes reduce amount of data needs read.

  3. if having problems operations being write bound (a) see if need indexes have. indexes good, trade-off them improving query time, maintaining indexes can impact time spent writing data , keeping them date. want indexes if in doubt, you're more interested in rapidly writing table in reading it. (b) make possible use of insert delayed "queue" writes database. note, not magic fix , inappropriate, in right circumstances can of help. (c) check tables heavily read , written @ same time, e.g. access list update's visitor's session data , read much. it's easy optimize table reading from, , writing to, not possible design table @ both. if have such case , it's bottleneck, consider whether it's possible split functions or move complex operations using table temporary table can update block periodically.

note, stuff in above has major effect, query design / indexing. beyond that, want start considering @ better hardware. in particular, can lot of benefit out of raid-0 array doesn't lot writing bound problems, can wonders read-bound problems. , can pretty cheap solution big boost.

you missed 2 items off list.

  1. memory bound. if hitting memory problems must check can usefully indexed indexed. can @ greater connection pooling if reason you're using lot of discrete connections db.

  2. network bound. if hitting network bound problems... aren't, if are, need network card or better network.

note, convenient way analyze db performance turn on log_slow_queries option , set long_query_time either 0 everything, or 0.3 or similar catch might holding database up. can turn on log-queries-not-using-indexes see if interesting shows up. note, sort of logging can kill busy live server. try on development box start.

hope that's of help. i'd interested in anyone's comments on above.


Comments

Post a Comment

Popular posts from this blog

c# - SharpSVN - How to get the previous revision? -

i trying find efficient way previous revision of file text comparison using sharpsvn. using (svnclient c = new svnclient()) { c.authentication.defaultcredentials = new networkcredential( configurationmanager.appsettings.get("svnserviceusername") , configurationmanager.appsettings.get("svnservicepassword") , configurationmanager.appsettings.get("svnservicedomain") ); c.authentication.sslservertrusthandlers += new eventhandler<svnsslservertrusteventargs>(authentication_sslservertrusthandlers); collection<svnfileversioneventargs> fileversioncollection = new collection<svnfileversioneventargs>(); svnrevisionrange range = new svnrevisionrange(0, this.hooks.revision); svnfileversionsargs args = new svnfileversionsargs(); args.retrieveproperties = true; args.range = range; foreach (svnchangeitem item in log.changedpaths) { string path = this.repositorypath + it...
Read more

c++ - Is it possible to compile a VST on linux? -

for class project i'm attempting write vst plugin backed cuda. current cuda workflow on linux box, i'd prefer compile , link there. according wikipedia , should possible (i couldn't find steinberg documentation relevant linux) can't find makefile or instructions on how build if aren't using xcode or visual studio. i'm vst 3 sdk doesn't support linux. when try compile plugin under linux, error: ./base/source/fatomic.cpp:39:30: fatal error: libkern/osatomic.h: no such file or directory this issue caused following code in "vst3 sdk/base/source/fatomic.cpp" #if mac #include <libkern/osatomic.h> #if mac_os_x_version_min_required > mac_os_x_version_10_4 #define native_atomic_type (volatile int32_t*) #else #define native_atomic_type (int32_t*) #endif #elif windows #include <windows.h> #endif but hope compiling under linux work vst sdk 2.4. reading. jvstwrapper seems run on lin...
Read more

url - Querystring manipulation of email Address in PHP -

i using third party application (email2http) send me emailid, body , message. unable retrieve email id due name appended on , enclosed < , >. here querystring getting. test.php?from=ajay+reddy &subject=cx&body=somebody when print variable in php returns name , not email id. $from = $_get['from']; echo $from; //output ajay reddy please suggest me how can emailid also. i dont want use post method due testing , debugging. it's working fine. forgot use htmlentities() in debugging.
Read more