Percona-server SLOWER in my benchmarks?

MySQL & MariaDB Other MySQL® Questions
1

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.769pi()/180)) * sin((Latitudepi()/180)) + cos((52.769pi()/180)) * cos((Latitude*pi()/180)) * cos(((-2.385 - Longitude)*pi()/180))))180/pi())601.1515) as Distance FROM restaurants R group by R.sk_restaurantid HAVING (Distance1609.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_block_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_size=163840#ncreased querycache limit to 16M from 256Kquery_cache_limit= 6M# Try number of CPU’s2 for thread_concurrencythread_concurrency = 8wait_timeout=340interactive_timeout=240max_connections=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_file_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

2

Can anyone help me with why Percona is slower?

Ben

3

You need to paste the output of EXPLAIN from both servers. If the execution plan has changed (for example) then there’s nothing wrong with configuration.

While we are at it though, you do set some strange things:

#SPECIFIC TO PERCONA#innodb_io_capacity = 400 #default on MYSQL is 100, set to number of disks *100 (4 on RAID 6)

This is not specific to Percona. It appears in InnoDB plugin as well. It’s important to understand that it may make some queries slower (not the one you mentioned) because it does more background work earlier. It is worthwhile though - since you don’t want to have dips in performance.

#RECOMMENDED BY MYSQL ENTERPRISE MONITORinnodb_locks_unsafe_for_binlog=0key_cache_block_size=1024

In MySQL 5.1, the better option is to not use innodb_locks_unsafe_for_binlog, but Row-based-replication and READ-COMMITTED.

It really is an unsafe idea if the Enterprise monitor does promote setting this setting.

tmp_table_size = 512Mmax_heap_table_size = 512M

These just look massive. Do you really have that much memory, that you are willing to have each session consuming 512M for a temporary table?

Leave the defaults small. Tune up each session if you really need to.

#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=8M

These are all massive as well. I’d rarely recommend raising them from the defaults.

query_cache_size = 250M //was pruning more than hitting#query_cache_size= 250Mquery_alloc_block_size=32768query_prealloc_size=163840#ncreased querycache limit to 16M from 256Kquery_cache_limit= 6M

If you optimize queries, normally the query cache is useless. Many people are disabling it.

Try number of CPU’s*2 for thread_concurrencythread_concurrency = 8

This setting does nothing unless you use Solaris.

#lots of innodb log writes/sec (66/sec) this should be bigger. innodb_log_buffer_size = 128M

This looks large. A normal value is 8M. I would only increase it if you are getting a lot of log_waits (in SHOW GLOBAL STATUS).

innodb_thread_concurrency=8

Setting this setting was recommended in 5.0 releases, but it discouraged in 5.1 plugin/XtraDB and above.

#NEW BS 26/03/08innodb_flush_method=O_DIRECT

If you have a RAID Controller this setting is often better, but you should test it. The manual doesn’t help you here / and I’ve previously filed a bug on it:
[URL]MySQL Bugs: #54306: Docs issue - Advice on innodb_flush_method can be misinterpreted

4

Thanks Morgo

I haven’t changed any of these settings since 5.0 so am glad you pointed some of these out - I will benchmark the changes against my original config and see what results

I’ve reset the buffers to default and changed the settings you specified and tried again, but still seeing mysql outperform percona by about 5%

With regards to EXPLAIN:

On Percona Server 5.1:

±—±------------±------±------±--------------±-----±--------±-----±-----±----------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |±—±------------±------±------±--------------±-----±--------±-----±-----±----------------------------+| 1 | SIMPLE | R | range | geo2 | geo2 | 18 | NULL | 116 | Using where; Using filesort |±—±------------±------±------±--------------±-----±--------±-----±-----±----------------------------+1

On Mysql 5.1:

±—±------------±------±------±--------------±-----±--------±-----±-----±----------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |±—±------------±------±------±--------------±-----±--------±-----±-----±----------------------------+| 1 | SIMPLE | R | range | geo2 | geo2 | 18 | NULL | 116 | Using where; Using filesort |±—±------------±------±------±--------------±-----±--------±-----±-----±----------------------------+