« October 2012 | Main | July 2012 »

Saturday, 29 September, 2012

MySQL Performance: Welcome 5.6 RC, the best MySQL ever

Today we're announcing MySQL 5.6 RC availability, which is placing MySQL 5.6 to the "best ever MySQL" position, kept until now by MySQL 5.5 ;-))

In one word: "Champagne !!!" :-)

While few graphs with benchmark results will say you it better than any words...

Sysbench OLTP_RO






Sysbench RO Simple-Ranges




Sysbench OLTP_RW

More details about during my presentation @MySQL Connect at 17:30 today..
Then my presentation slides will be posted here: MySQL Perf @Connect 2012 

NOTE: the results are presented in QPS (query per second, counting only Read + Write requests/sec reported by Sysbench), and representing the most higher QPS results obtained on each MySQL version for each test case with a best tunning possible in each case, and on the same Linux 32cores server...

Posted by Dimitri at 18:39 - Comments...
Categories: MySQL

Wednesday, 26 September, 2012

MySQL Performance: Speaking during MySQL Connect 2012

Seems I did not say it before, but I'm presenting "Demystified MySQL/InnoDB Performance Tuning" during MySQL Connect, this Saturday, 5.30 pm, Room Golden Gate 4.. And looking on my slides, I'm realizing that it'll be hard to tell within one hour all the stuff I want :-))

So, if you want me absolutely to cover some particular topic during presentation, please, leave a comment here or just drop me an e-mail, I'll then keep it in mind and not miss it for sure ;-)

MySQL Performance is a so passionating topic that it's quite easy to forget about time while speaking about.. And I'm very happy to see so many sessions about performance.. Seems it'll be hard for me too to manage my time optimally and attend all the presentations and speakers I want.. - but I very hope so :-)

Looking very promising so long awaited conference..

See you there! ;-)

Posted by Dimitri at 21:46 - Comments...
Categories: MySQL

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:
User=dim
Passwd=dim
Host=127.0.0.1:3306

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
^C

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 - Comments...
Categories: dim_STAT, MySQL

Tuesday, 04 September, 2012

MySQL Performance: Table Open Cache in 5.6

Over this summer I've observed several very curious MySQL performance issues, but time was short to blog about (and vacation period is not motivating to it either ;-))

But here is a one of them I'd like to share.

The following graphs are representing a MySQL test case running on 12cores Linux box from the same workload: simple Sysbench OLTP_RO benchmark with a growing number of concurrent users (150, 200, 250, 300, 350):
the only difference here that in the fist case (the left part of the graph) MySQL performance is dropping down since 300 concurrent users, while in the second case it's fixed and remaining stable as expected (well, in reality the issue came up since 1024 concurrent users, but I've forced the config params to bring it earlier and to simplify explanation ;-)

Once again, MySQL Performance Schema (PFS) is helping here to fire the hottest contention in this workload:

In the "broken" case the top contention is firing on the LOCK_table_cache mutex!..

Why?..

The exact answer is coming from the new status variables introduced since MySQL 5.6.6 as a part of solution fixing the old and so painful for many years LOCK_open mutex contention:

there are now table_open_cache related status counters reporting the levels of cache hits/misses and overflows (see: http://dev.mysql.com/doc/refman/5.6/en/server-status-variables.html#statvar_Table_open_cache_hits). And as you can see from the graphs, in the "broken" case the hits were out-passed by overflows, which is clearly indicating that the table_open_cache variable was simply not set high enough!..

But which value is "high enough" ?.. - it depends :-) depends on your workload and your System/OS/HW capacities (and of course on the MySQL version you're using ;-)) -- currently to obtain the presented graphs I've set it to 100 - sure, pretty small, but it was still enough to keep the load correctly up to 250 concurrent users anyway ;-) While the current default value in MySQL 5.6 is 400, and in the real test case it was still good enough to keep up to 1024 users! - but was not enough for more.. However, personally, from a long date I'm setting "table_open_cache" to several thousands during my tests, and that's why I've never observed this issue before :-)

But well, since MySQL 5.6 you have now a good indicator from status variables (or I_S) to see if your setting is good enough or not. While some may still say me that it was always a good practice to set "table_open_cache" to something big enough, and I may only agree (as I'm always doing the same myself too).. - but let me now to present you another issue ;-))

Same workload scenario, Sysbench OLTP_RO, table_open_cache is set to 8000, but now I'm running the test on 32cores server:

Observations :

  • on the left side the LOCK_table_cache mutex wait time is on the second position after MDL_lock::rwlock
  • there is no more issues with cache hits, and table_open_cache=8000 setting is more than enough..
  • in this case the LOCK_table_cache mutex becomes really much more hot due a much higher concurrency involved on 32cores (vs what is possible on 12cores)
  • so, this contention can be fixed now by using a bigger table_open_cache_instances value (default is 1) -- setting it to 16 is completely removing the LOCK_table_cache mutex wait events (as you can see on the right side) and giving at once 10% performance improvement! ;-)
  • the table_open_cache_instances setting was introduced since MySQL 5.6.6 (http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_table_open_cache_instances)


On the same time in the presented test case this mutex contention was not the top one, but only on the second position. So, of course, the gain may be even bigger when LOCK_table_cache mutex time arrives to the #1 in your workload ;-)

For example, over 15-20% gain on the following POINT-SELECT test workload:

Observations :
  • LOCK_table_cache mutex contention is #1 here (left side)
  • and completely disappearing (right side) once I've set table_open_cache_instances=16 and bringing 15-20% performance gain!

Looks to me like we should adjust little bit the default settings within MySQL 5.6 to get all these features out-if-the-box. While I want also to note that PFS instrumentation is working better and better now! - during presented workloads I've used mutex instrumentation only within PFS, which lowered my base performance level by near 10%.. - however, keeping in mind that the instrumentation code path of the most hot mutexes was executed here over several tens of millions times per second(!) - 10% regression is looking in this case pretty acceptable for the amount of information PFS is providing for my analysis ;-)

Well, would be great now to remove contention on the MDL_lock::rwlock.. - but it's an another story ;-)

Rgds,
-Dimitri
Posted by Dimitri at 17:35 - Comments...
Categories: MySQL