« MySQL Performance: Table Open Cache in 5.6 | Main | MySQL Performance: Speaking during MySQL Connect 2012 »

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 ;-)

If you're agree to collect MySQL and system stats during such a workload, the following are the instructions about how to install a small kit with my tools on your server, collect the stats in the way I'll be able to analyze them more efficiently, and then archive the data and send it to me.

The tool kit is part of standard dim_STAT distribution, but I've made a simplified version with all the stuff already pre-configured for MySQL stats, so the only things you'll need:
  • 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
Easy, no? ;-))

NOTE: there is no any traces collected about your data or any other kind of sensible information -- all the info is collected within flat files, and you may verify them one by one to be sure about their content..

Let's try?..

Setup STAT-service on your Linux host

0. download STAT-service tarball preconfigured for MySQL from http://dimitrik.free.fr/STATsrv-Linux-x86-mysql.tgz

1. Install STAT-service

should be logged as a "root" user.

# cd /etc
# tar xzf /path/to/STATsrv-Linux-x86-mysql.tgz

Check 32-bit libraries dependency within /etc/STATsrv/bin directory (most of binaries are compiled in 32bit to be able to run on any Linux system, however some of Linux distros today are not installing 32bit libraries on 64bit systems by default) - mainly you have to check WebX and WebX.mySQL:

ex. on OEL6.2:

# ldd /etc/STATsrv/bin/WebX
linux-gate.so.1 => (0xf77c1000)
libc.so.6 => /lib/libc.so.6 (0xf761f000)
/lib/ld-linux.so.2 (0xf77c2000)

# ldd /etc/STATsrv/bin/WebX.mySQL
linux-gate.so.1 => (0xf773e000)
libm.so.6 => /lib/libm.so.6 (0xf7702000)
libz.so.1 => not found <================= missing..
libc.so.6 => /lib/libc.so.6 (0xf7572000)
/lib/ld-linux.so.2 (0xf773f000)

Add missing packages:

# yum install libzip.i686

2. Edit MySQL connection parameters:

# vi /etc/STATsrv/bin/.env-mysql

Change user/password/host/port to yours:

# Set connection params:

export User Passwd Host

3. Check STAT commands are ready to collect from your MySQL instance:

try to execute mysqlLOAD script to be sure to see some real numbers from your MySQL server:

# /etc/STATsrv/bin/mysqlLOAD.sh 5

On Sessions InnDirty InnoFree KeyDirty OpFiles OpTables ByteRx/s ByteTx/s Commit/s Delete/s Insert/s Select/s Update/s InnDsy/s InnDrd/s InnDwr/s InnLwr/s InnLsy/s Key_Rd/s Key_Wr/s Query/s AbrtClnt AbrtConn Connects SlowReqs TabLckWt Rollback
1 38.00 1.00 3958924.00 0.00 19.00 1147.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
1 38.00 1.00 3958924.00 0.00 19.00 1147.00 4140059.75 18216136.00 0.00 0.00 0.00 206989.00 0.00 3.20 0.00 3.20 2.00 2.20 0.00 0.00 206972.80 0.00 0.00 0.00 0.00 0.00 0.00

if you don't see any data coming from MySQL:
  • 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 ;-)

4. Once everything is working..

Be sure InnoDB monitoring via METRICS table is enabled in your MySQL 5.6 config setting:
  • innodb_monitor_enable = '%'

If you're expecting to monitor the most hot wait event via MySQL Performance Schema (PFS), be sure PFS is turned on MySQL start, for 5.6:
  • performance_schema=ON
  • performance_schema_instrument='%=on'

  • and once MySQL is started, execute the following SQL statements to enable mutexes and I/O instrumentation only (if you don't need any other):
    • 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%';

Collecting your system and MySQL server stats

5. Start EasySTAT to collect stats from your workload (for ex. every 10sec during 24 hours):

# mkdir /var/tmp/stats <--- create output directory
# /etc/STATsrv/bin/EasySTAT.sh /var/tmp/stats 10 24

NOTE: you may always interrupt its execution by Ctrl+C.

Once collected, just make a tarball from /var/tmp/stats and send it to me (if it's small, or place it somewhere and give me a way to get it via Internet)..

More information about EasySTAT can be found here: http://dimitrik.free.fr/UserGuide/MultiPage/Report_1_page8.html#note_4979

EasySTAT is a part of STAT-service (client agent for dim_STAT tool)
Posted by Dimitri at 23:46
Categories: dim_STAT, MySQL
blog comments powered by Disqus
Note: 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..