Hello all!
I hope you chaps we’ll be able to help me on this most bizarre issue i have.
We’ve been trialling percona server 5.5.23 to replace our mysql 5.1.44 enterpise servers.
We set up 2 identical VM’s on an ESXi host running debian lenny amd-64.
After dropping the binaries in and tweaking the cnf accordingly percona started running fine (see cnf here:)
[mysqld]
#federated
socket=/var/run/mysqld/mysqld1/mysqld.sock
general-log-file=/var/log/mysql/mysqld.log
log-error=/var/log/mysql/mysqld.err
log_warnings=2
innodb_buffer_pool_size=12G
innodb_log_file_size=750M
innodb_log_buffer_size=8M
innodb_flush_log_at_trx_commit=2
innodb_flush_method=O_DIRECT
#innodb_file_per_table
transaction-isolation=READ-COMMITTED
max_connections=1000
#log-bin=mysql.bin
#binlog-format=MIXED
server-id=1
#ignore_builtin_innodb
#plugin-load=innodb=ha_innodb_plugin.so
default-storage-engine=InnoDB
thread_cache_size=24
tmp_table_size=128M
max_heap_table_size=128M
#slow_query_log_file=/storage/logs/mysql/slow-query/sql1.log
#slow_query_log=1
long_query_time=3
#log-slow-admin-statements
#log-queries-not-using-indexes
expire_logs_days=7
net_read_timeout=3000
net_write_timeout=3000
max_allowed_packet=1G
group_concat_max_len=32M
#binlog_cache_size=1M
table_open_cache=2048
table_definition_cache=1024
join_buffer_size=1M
innodb_lock_wait_timeout=2400
skip-name-resolve
query_cache_size=48M
bind-address=0.0.0.0
wait_timeout=86400
skip-external-locking
key_buffer=16M
thread_stack=192K
query_cache_limit=1M
connect_timeout=120
I ran a few mysqlslap tests just to get a taste for how much faster the percona server would be and as expected it came out faster than the 5.1.44 server:
percona 5.5.23: mysqlslap --user=root -p***** --auto-generate-sql --concurrency=300 --engine=innodb Benchmark Running for engine innodb Average number of seconds to run all queries: 1.069 seconds Minimum number of seconds to run all queries: 1.069 seconds Maximum number of seconds to run all queries: 1.069 seconds Number of clients running queries: 300 Average number of queries per client: 0
MySQL 5.1.44: mysqlslap --user=root -p***** --auto-generate-sql --concurrency=300 --engine=innodb Benchmark Running for engine innodb Average number of seconds to run all queries: 1.552 seconds Minimum number of seconds to run all queries: 1.552 seconds Maximum number of seconds to run all queries: 1.552 seconds Number of clients running queries: 300 Average number of queries per client: 0
Then i yanked some of the more larger SELECTS from our production db to see how well it handled a more bespoke query, and oddly the percona server was consistently slower than the mysql server. Putting this down to the possibility that the query itself was just badly written i ran the following commands on each server:
percona server:
mysql> SELECT BENCHMARK(10000000000,1+1);
±---------------------------+|
BENCHMARK(10000000000,1+1)
|±---------------------------+|
0 |±---------------------------+1
row in set (3 min 32.06 sec)
mysql server:
mysql> SELECT BENCHMARK(10000000000,1+1);
±---------------------------+
| BENCHMARK(10000000000,1+1) |
±---------------------------+
| 0 |
±---------------------------+
1 row in set (2 min 32.35 sec)
Thinking i had a botched install i re ran the install script set the server back up again it came out the same?
Now i’m almost certain i have an iffy setting in my cnf somewhere which is causing this and i’m not overly confident about throwing a full test suite at it until i’m happy thats correct.
Could someone maybe point me in the right direction re my cnf or are my intial tests just a bit naff?