Tuesday, 25 September, 2012
MySQL Performance: Collecting stats from your workload
Pretty often I was asked by many people about solutions I'm using while analyzing MySQL performance issues and generating various graphs presented in my reports and blog posts.. - Well, I've always used and continue to use dim_STAT - the tool I'm developing myself since 15 years now, and which is freely available on this site :-) The tool is mainly collecting any kind of stats from the system and applications, and then giving you a way to analyze them live or later via web interface - nothing new now days, but well, I have my habits, like to play with collected data and want to know exactly what I'm analyzing ;-)) (while many of you are using other tools, including a really excellent MySQL Enterprise Monitor, and it's all ok for me too - as in my mind changes are coming only since people become unhappy with what they have, or hitting some limitations, etc.) -- personally, I'm pretty happy, and wish you the same ;-))
But I have a small problem -- I'm very curious by problematic workloads (and already started a call for workload scenarios this year, and many thanks to all who answered) -- but now I'm also very interested by workload stats, specially if you're trying MySQL 5.6 and hitting some unexpected performance issues on your system ;-)
- provide a connect info for your MySQL instance (user/password/host:port)
- check some system libs are not missed on your system to run my binaries (and install few missed if any)
- collect stats with 10sec interval during your workload
- send me the result
- be sure your database is running ;-)
- be sure MySQL access via IP is not protected by your firewall
- be sure MySQL is listening on the IP port you've configured
- be sure user/password pair is correct..
- goto 1. and debug ;-)
- innodb_monitor_enable = '%'
and once MySQL is started, execute the following SQL statements to
enable mutexes and I/O instrumentation only (if you don't need any
- use performance_schema;
- update setup_consumers set enabled = 'no';
- update setup_consumers set enabled = 'yes' where name like '%instr%';
- update setup_instruments set enabled = 'no', timed = 'no';
- update setup_instruments set enabled = 'yes', timed = 'yes' where name like '%wait/synch%';
- update setup_instruments set enabled = 'yes', timed = 'yes' where name like '%wait/io/file%';
blog comments powered by DisqusNote: if you don't see any "comment" dialog above, try to access this page with another web browser, or google for known issues on your browser and DISQUS..