MySQL (innodb) performance tuning

Written by Gionatan Danti on . Posted in Linux & Unix

User Rating:  / 5
PoorBest 

Testbed and Methods

All tests were run on a Dell Latitude D620 machine, with the following hardware and software configuration:

  • Core2 T7200 CPU @ 2.0 GHz (dual core, 4 MB L2 cache)
  • 4 GB of DDR2-667 RAM (3.3 GB OS allocable memory)
  • Quadro NVS110 videocard (used in text-only mode)
  • a Seagate ST980825AS 7200 RPM 80 GB SATA hard disk drive (in IDE compatibility mode, as the D620's BIOS does not support AHCI operation)
  • O.S. CentOS 6.2 amd64 with kernel version 2.6.32-220.4.2.el6.x86_64
  • MySQL server version 5.1.61

To benchmark mysql server speed, I used these benchmarks:

  • DBT-2 ver. 0.40 (compiled from sources, see here for more informations): DBT-2TM is an OLTP transactional performance test. It simulates a wholesale parts supplier where several workers access a database, update customer information and check on parts inventories. DBT-2TM is a fair usage implementation of the the TPC's TPC-CTM Benchmark specification. The results of a test run include transactions per second, CPU utilization, I/O activity, and memory utilization
  • Sysbench ver. 0.4.12: an OLTP benchmark

In order to compare the performance (dis)vantage of changing some server-side options, I will start with a default, basic configuration and, form here, change one thing at a time, adding changes one-by-one. Default mysql configuration use the following settings:

  • innodb_buffer_pool_size = 8 MB;
  • query_cache_size = 0 MB (this effectively makes query cache inactive);
  • query_cache_type = 1 (while this value enable the query cache, remind that by default this cache as a value of 0 bytes, so it is inactive);
  • innodb_flush_method = fdatasync (please note that, to set the default value, you must remove every occurrence of this variable from the configuration file; trying to set it manually will end mis-configuring the InnoDB engine);
  • innodb_flush_log_at_trx_commit = 1 (by synchronizing each commit to disk, this value guarantee ACID compliance).

Comments   

 
#1 David van Enckevort 2012-06-09 00:25
Nice tests. Another good source of information you could mention is the MySQL Performance blog by the people from Percona: http://www.mysqlperformanceblog.com/
 
 
#2 Gionatan Danti 2012-06-11 15:54
Sure. It is a very renowned blog!

Thank you for posting the link here :)
 
 
#3 ykyuen 2012-07-26 11:23
Thanks for your blog post. it is very helpful and the content is easy to follow.

=D
 

You have no rights to post comments