OK, So I've just set up a benchmark on our system using real data, comparing the latest Percona-Server with the latest Mysql 5.1 - I'll admit it's not the most scientific of tests but I wanted to test our real-world queries on the different servers.
The query I am running is set to SQL_NO_CACHE and is a tricky one for us as it compares lat/long so doesnt tend to work with indexes very well so a good one to benchmark as it reflects a commonly used query
Query:
SELECT SQL_NO_CACHE R.*, (((acos(sin((52.769*pi()/180)) * sin((Latitude*pi()/180)) + cos((52.769*pi()/180)) * cos((Latitude*pi()/180)) * cos(((-2.385 - Longitude)*pi()/180))))*180/pi())*60*1.1515) as Distance FROM restaurants R group by R.sk_restaurantid HAVING (Distance*1609.344) <=16090 order by hits desc LIMIT 15
This is an INNODB table.
Our server is :
Freebsd 8.1 AMD64
2.66Ghz Quad Core QPi (8 virtual cores)
16GB DDR3
4 x 73Gb raid 6
It's a new server, so no production usage. We waited until all CPU/IO stats were back to 0% before running each test
I've used the exact same /etc/my.cnf for both tests, except I specified innodb_io_capacity = 400 when using xtradb
Results (figures are Qieries per sec from SuperSmack):
Clients Percona Mysql 5.1100 6134.05 6467.55150 6071.11 6247.34200 5920.65 6182.7250 5974.97 6137.74300 5955.5 6154.72349 5913.35 6140.48400 5907.92 6014.77499 5938.17 6038.96
# The MySQL server[mysqld]port = 3306socket = /tmp/mysql.sock#tmpdir = /var/db/mysql/tmpdatadir =/var/db/mysql/log-error=/var/log/mysqld.log#SPECIFIC TO PERCONA#innodb_io_capacity = 400 #default on MYSQL is 100, set to number of disks *100 (4 on RAID 6)#RECOMMENDED BY MYSQL ENTERPRISE MONITORinnodb_locks_unsafe_for_binlog=0key_cache_b lock_size=1024#RECOMMENDED TO FIX BUG 32149 and/or 20358 on 10/12/08skip-innodb-adaptive-hash-indexsync_binlog=1ft_min_word_len=2skip-name-resolveskip-external-locking#CHANGED FROM 50 21/05/07back_log = 100myisam_recover = FORCE,BACKUPkey_buffer_size =200Mmax_allowed_packet = 100M#RAISED 200>5000#renamed from table_cache to table_open_cachetable_open_cache = 2500tmp_table_size = 512Mmax_heap_table_size = 512M#BELOW CHANGED FROM 1M to value recommended by Ent MOnitorbinlog_cache_size = 2097152slow_query_loglong_query_time = 1#log_long_formatlog-queries-not-using-indexes#PER THREAD BUFFERS#reduced from 16m to 8mread_buffer_size = 8Mread_rnd_buffer_size = 8M#raised sort buffer 1M => 4Msort_buffer_size = 16M#raised read bufferto 2Mmyisam_sort_buffer_size = 16Mjoin_buffer_size=8Mthread_cache_size = 180bulk_insert_buffer_size = 32Mmyisam_max_sort_file_size = 4G#mysiam_max_extra_sort_file_size = 4Gquery_cache_size = 250M //was pruning more than hitting#query_cache_size= 250Mquery_alloc_block_size=32768query_prealloc_siz e=163840#ncreased querycache limit to 16M from 256Kquery_cache_limit= 6M# Try number of CPU's*2 for thread_concurrencythread_concurrency = 8wait_timeout=340interactive_timeout=240max_connec tions=500#keep this on for incremental backups!log-bin=/var/db/mysql/master-bin.loglog-bin-index=/var/db/mysql/master-log-bin.index#for replication 260509sync_binlog=1# required unique id between 1 and 2^32 - 1# defaults to 1 if master-host is not set# but will not function as a master if omittedserver-id = 1max_binlog_size = 100Mexpire_logs_days=6#skip-bdb# Uncomment the following if you are using InnoDB tablesinnodb_data_home_dir = /var/db/mysql/#CHANGE THIS TO 1G:2Ginnodb_data_file_path = ibdata1:256M;ibdata2:500M:autoextend#innodb_data_f ile_path = ibdata1:256M:autoextend:max:2000Minnodb_autoextend _increment=200innodb_log_group_home_dir = /var/db/mysql/#WAS innodb_buffer_pool_size = 4G#CHANGED ON 16/Oct/10 due to 100% usageinnodb_buffer_pool_size = 8G innodb_additional_mem_pool_size = 80M#innodb_file_per_table = 1innodb_log_file_size = 128M#lots of innodb log writes/sec (66/sec) this should be bigger. innodb_log_buffer_size = 128M innodb_log_files_in_group = 2innodb_flush_log_at_trx_commit = 2innodb_lock_wait_timeout = 50innodb_status_file=1innodb_thread_concurrency=8# NEW BS 26/03/08innodb_flush_method=O_DIRECT
The query I am running is set to SQL_NO_CACHE and is a tricky one for us as it compares lat/long so doesnt tend to work with indexes very well so a good one to benchmark as it reflects a commonly used query
Query:
SELECT SQL_NO_CACHE R.*, (((acos(sin((52.769*pi()/180)) * sin((Latitude*pi()/180)) + cos((52.769*pi()/180)) * cos((Latitude*pi()/180)) * cos(((-2.385 - Longitude)*pi()/180))))*180/pi())*60*1.1515) as Distance FROM restaurants R group by R.sk_restaurantid HAVING (Distance*1609.344) <=16090 order by hits desc LIMIT 15
This is an INNODB table.
Our server is :
Freebsd 8.1 AMD64
2.66Ghz Quad Core QPi (8 virtual cores)
16GB DDR3
4 x 73Gb raid 6
It's a new server, so no production usage. We waited until all CPU/IO stats were back to 0% before running each test
I've used the exact same /etc/my.cnf for both tests, except I specified innodb_io_capacity = 400 when using xtradb
Results (figures are Qieries per sec from SuperSmack):
Clients Percona Mysql 5.1100 6134.05 6467.55150 6071.11 6247.34200 5920.65 6182.7250 5974.97 6137.74300 5955.5 6154.72349 5913.35 6140.48400 5907.92 6014.77499 5938.17 6038.96
# The MySQL server[mysqld]port = 3306socket = /tmp/mysql.sock#tmpdir = /var/db/mysql/tmpdatadir =/var/db/mysql/log-error=/var/log/mysqld.log#SPECIFIC TO PERCONA#innodb_io_capacity = 400 #default on MYSQL is 100, set to number of disks *100 (4 on RAID 6)#RECOMMENDED BY MYSQL ENTERPRISE MONITORinnodb_locks_unsafe_for_binlog=0key_cache_b lock_size=1024#RECOMMENDED TO FIX BUG 32149 and/or 20358 on 10/12/08skip-innodb-adaptive-hash-indexsync_binlog=1ft_min_word_len=2skip-name-resolveskip-external-locking#CHANGED FROM 50 21/05/07back_log = 100myisam_recover = FORCE,BACKUPkey_buffer_size =200Mmax_allowed_packet = 100M#RAISED 200>5000#renamed from table_cache to table_open_cachetable_open_cache = 2500tmp_table_size = 512Mmax_heap_table_size = 512M#BELOW CHANGED FROM 1M to value recommended by Ent MOnitorbinlog_cache_size = 2097152slow_query_loglong_query_time = 1#log_long_formatlog-queries-not-using-indexes#PER THREAD BUFFERS#reduced from 16m to 8mread_buffer_size = 8Mread_rnd_buffer_size = 8M#raised sort buffer 1M => 4Msort_buffer_size = 16M#raised read bufferto 2Mmyisam_sort_buffer_size = 16Mjoin_buffer_size=8Mthread_cache_size = 180bulk_insert_buffer_size = 32Mmyisam_max_sort_file_size = 4G#mysiam_max_extra_sort_file_size = 4Gquery_cache_size = 250M //was pruning more than hitting#query_cache_size= 250Mquery_alloc_block_size=32768query_prealloc_siz e=163840#ncreased querycache limit to 16M from 256Kquery_cache_limit= 6M# Try number of CPU's*2 for thread_concurrencythread_concurrency = 8wait_timeout=340interactive_timeout=240max_connec tions=500#keep this on for incremental backups!log-bin=/var/db/mysql/master-bin.loglog-bin-index=/var/db/mysql/master-log-bin.index#for replication 260509sync_binlog=1# required unique id between 1 and 2^32 - 1# defaults to 1 if master-host is not set# but will not function as a master if omittedserver-id = 1max_binlog_size = 100Mexpire_logs_days=6#skip-bdb# Uncomment the following if you are using InnoDB tablesinnodb_data_home_dir = /var/db/mysql/#CHANGE THIS TO 1G:2Ginnodb_data_file_path = ibdata1:256M;ibdata2:500M:autoextend#innodb_data_f ile_path = ibdata1:256M:autoextend:max:2000Minnodb_autoextend _increment=200innodb_log_group_home_dir = /var/db/mysql/#WAS innodb_buffer_pool_size = 4G#CHANGED ON 16/Oct/10 due to 100% usageinnodb_buffer_pool_size = 8G innodb_additional_mem_pool_size = 80M#innodb_file_per_table = 1innodb_log_file_size = 128M#lots of innodb log writes/sec (66/sec) this should be bigger. innodb_log_buffer_size = 128M innodb_log_files_in_group = 2innodb_flush_log_at_trx_commit = 2innodb_lock_wait_timeout = 50innodb_status_file=1innodb_thread_concurrency=8# NEW BS 26/03/08innodb_flush_method=O_DIRECT
Comment