MySQL 5.1 -> Percona 5.5 == performance degredation despite my.cnf tuning.

MySQL & MariaDB Other MySQL® Questions
1

The upgrade was a bit more than the DB software. I went from 1) to 2)

  1. Old : (4 core w/ HT - 8 logical, 16 GB mem, raid1 Velociraptors, MySQL 5.1)
  2. New : (12 core w/ HT - 24 logical, 32 GB mem, raid1 SSDs 840 evo, Percona 5.5)

Obviously every layer has changed except the data set. This is the first time I’ve used MySQL|Percona 5.5.

The problem was… that a simple query that used to take 1 second, is now taking 4 seconds.
The query is of the form:

SELECT column FROM TABLE WHERE column1='i' AND column2='j' AND column3='k' ORDER BY column DESC LIMIT 1;

I’m not sure how many times that query was being run a minute.

I’m mostly concerned how my query time quadrupled on a more powerful machine. I’ve been scowering the internet… read endless forums, have read about 50 pages of O’Reily High Performance MySQL, been watching webinars… I just can’t figure out what I’m doing wrong. I’d appreciate any advice.

Does my my.cnf look adequate:


[mysql]

# CLIENT #
port = 3306
socket = /var/lib/mysql/mysql.sock

[mysqld]

# GENERAL #
user = mysql
default-storage-engine = InnoDB
socket = /var/lib/mysql/mysql.sock
pid-file = /var/lib/mysql/mysql.pid

# MyISAM #
key-buffer-size = 32M
myisam-recover = FORCE,BACKUP

# SAFETY #
max-allowed-packet = 16M
max-connect-errors = 1000000
skip-name-resolve
sysdate-is-now = 1

# DATA STORAGE #
datadir = /var/lib/mysql/

# BINARY LOGGING #
log-bin = /var/lib/mysql/mysql-bin
expire-logs-days = 14
sync-binlog = 1

# CACHES AND LIMITS #
tmp-table-size = 32M
max-heap-table-size = 32M
query-cache-type = 0
query-cache-size = 32M
max-connections = 500
thread-cache-size = 50
open-files-limit = 65535
table-definition-cache = 4096
table-open-cache = 4096

# INNODB #
innodb-flush-method = O_DIRECT
innodb-log-files-in-group = 2
innodb-log-file-size = 4G
innodb-flush-log-at-trx-commit = 2
innodb-file-per-table = 1
innodb-buffer-pool-size = 26G


# SSD tuning #
innodb_flush_neighbor_pages = 0
innodb_adaptive_flushing_method = keep_average
innodb_log_block_size = 4096
innodb_log_buffer_size = 8M
innodb_read_ahead = none
innodb_io_capacity = 500

# LOGGING #
log-error = /var/lib/mysql/mysql-error.log
log-queries-not-using-indexes = 0
slow-query-log = 1
slow-query-log-file = /var/lib/mysql/mysql-slow.log

​

2

I’ve been doing some tests with sysbench, and have been changing around the different threads. I have noticed that my new server does very well with 32 or 64 threads while my old server does best with about 8 threads.

So perhaps threading is my problem with the new 24 core system. I found two variables:


innodb_io_read_threads
innodb_io_write_threads

Could these potentially have been my weak link in the configuration? I see that they have defaulted to 4, with which, for my READ ONLY tests, the old server had the advantage. But the data set is entirely in memory, and it’s a select statement, so do these variables still apply?

Purpose: Analyze poor performance experienced today.
Details:
Test Methods: Sysbench mysql test




Table of Transactions/sec with different thread counts. (Read/Write Test)
[TABLE]
	 		[TR]
			[TD="width: 33%"]# of Threads[/TD]
			[TD="width: 33%"]PredictiveDB[/TD]
			[TD="width: 33%"]Replication Server DB[/TD]
		[/TR]
		[TR]
			[TD="width: 33%"]4[/TD]
			[TD="width: 33%"]1446[/TD]
			[TD="width: 33%"]109[/TD]
		[/TR]
		[TR]
			[TD="width: 33%"]8[/TD]
			[TD="width: 33%"]2567[/TD]
			[TD="width: 33%"]109[/TD]
		[/TR]
		[TR]
			[TD="width: 33%"]12[/TD]
			[TD="width: 33%"]3630[/TD]
			[TD="width: 33%"]108[/TD]
		[/TR]
		[TR]
			[TD="width: 33%"]24[/TD]
			[TD="width: 33%"]5848[/TD]
			[TD="width: 33%"]107[/TD]
		[/TR]
		[TR]
			[TD="width: 33%"]32[/TD]
			[TD="width: 33%"]6100[/TD]
			[TD="width: 33%"]108[/TD]
		[/TR]
		[TR]
			[TD="width: 33%"]64[/TD]
			[TD="width: 33%"]6139[/TD]
			[TD="width: 33%"]109[/TD]
		[/TR]
	 [/TABLE]
Table of Transactions/sec with different thread counts.( Read ONLY Test)
[TABLE]
	 		[TR]
			[TD="width: 33%"]# of Threads[/TD]
			[TD="width: 33%"]PredictiveDB[/TD]
			[TD="width: 33%"]Replication Server DB[/TD]
		[/TR]
		[TR]
			[TD="width: 33%"]4[/TD]
			[TD="width: 33%"]2677[/TD]
			[TD="width: 33%"]4092[/TD]
		[/TR]
		[TR]
			[TD="width: 33%"]8[/TD]
			[TD="width: 33%"]4758[/TD]
			[TD="width: 33%"]5933[/TD]
		[/TR]
		[TR]
			[TD="width: 33%"]16[/TD]
			[TD="width: 33%"]7950[/TD]
			[TD="width: 33%"]5695[/TD]
		[/TR]
		[TR]
			[TD="width: 33%"]24[/TD]
			[TD="width: 33%"]9619[/TD]
			[TD="width: 33%"]5695[/TD]
		[/TR]
		[TR]
			[TD="width: 33%"]32[/TD]
			[TD="width: 33%"]10104[/TD]
			[TD="width: 33%"]5685[/TD]
		[/TR]
		[TR]
			[TD="width: 33%"]64[/TD]
			[TD="width: 33%"]10012[/TD]
			[TD="width: 33%"]5497[/TD]
		[/TR]
	 [/TABLE]

Tests Below:

[B]PredictionDB Server, 4 threads[/B]

[root@PredictiveDB bowenit]# sysbench --db-driver=mysql --test=oltp --oltp-table-size=1000000 --mysql-db=test --mysql-user=root --mysql-password= --max-time=60 --max-requests=0 --num-threads=4 run
sysbench 0.4.12: multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 4

Doing OLTP test.
Running mixed OLTP test
Using Special distribution (12 iterations, 1 pct of values are returned in 75 pct cases)
Using "BEGIN" for starting transactions
Using auto_inc on the id column
Threads started!
Time limit exceeded, exiting...
(last message repeated 3 times)
Done. [root@PredictiveDB bowenit]# sysbench --db-driver=mysql --test=oltp --oltp-table-size=1000000 --mysql-db=test --mysql-user=root --mysql-password= --max-time=60 --max-requests=0 --num-threads=32 run
sysbench 0.4.12: multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 32

Doing OLTP test.
Running mixed OLTP test
Using Special distribution (12 iterations, 1 pct of values are returned in 75 pct cases)
Using "BEGIN" for starting transactions
Using auto_inc on the id column
Threads started!
Time limit exceeded, exiting...
(last message repeated 31 times)
Done.

OLTP test statistics:
queries performed:
read: 5124938
write: 1830330
other: 732132
total: 7687400
transactions: 366065 (6100.57 per sec.)
deadlocks: 2 (0.03 per sec.)
read/write requests: 6955268 (115911.34 per sec.)
other operations: 732132 (12201.17 per sec.)

Test execution summary:
total time: 60.0051s
total number of events: 366065
total time taken by event execution: 1917.0571
per-request statistics:
min: 2.39ms
avg: 5.24ms
max: 47.66ms
approx. 95 percentile: 7.92ms

Threads fairness:
events (avg/stddev): 11439.5312/46.26
execution time (avg/stddev): 59.9080/0.00


OLTP test statistics:
queries performed:
read: 1214738
write: 433835
other: 173534
total: 1822107
transactions: 86767 ([B]1446.05 per sec.[/B])
deadlocks: 0 (0.00 per sec.)
read/write requests: 1648573 (27474.90 per sec.)
other operations: 173534 (2892.09 per sec.)

Test execution summary:
total time: 60.0029s
total number of events: 86767
total time taken by event execution: 239.5165
per-request statistics:
min: 1.81ms
avg: 2.76ms
max: 39.46ms
approx. 95 percentile: 3.32ms

Threads fairness:
events (avg/stddev): 21691.7500/21.71
execution time (avg/stddev): 59.8791/0.00


[B]Replicatiion Server, 4 threads[/B]

[root@mysql01-node02 tmp]# sysbench --db-driver=mysql --test=oltp --oltp-table-size=1000000 --mysql-db=test --mysql-user=root --mysql-password= --max-time=60 --max-requests=0 --num-threads=4 run
sysbench 0.4.12: multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 4

Doing OLTP test.
Running mixed OLTP test
Using Special distribution (12 iterations, 1 pct of values are returned in 75 pct cases)
Using "BEGIN" for starting transactions
Using auto_inc on the id column
Threads started!
Time limit exceeded, exiting...
(last message repeated 3 times)
Done.

OLTP test statistics:
queries performed:
read: 92232
write: 32940
other: 13176
total: 138348
transactions: 6588 ([B]109.74 per sec[/B].)
deadlocks: 0 (0.00 per sec.)
read/write requests: 125172 (2085.08 per sec.)
other operations: 13176 (219.48 per sec.)

Test execution summary:
total time: 60.0321s
total number of events: 6588
total time taken by event execution: 240.0464
per-request statistics:
min: 12.05ms
avg: 36.44ms
max: 177.84ms
approx. 95 percentile: 53.99ms

Threads fairness:
events (avg/stddev): 1647.0000/1.87
execution time (avg/stddev): 60.0116/0.01





[B]PredictionDB Server, 8 threads[/B]
[root@PredictiveDB bowenit]# sysbench --db-driver=mysql --test=oltp --oltp-table-size=1000000 --mysql-db=test --mysql-user=root --mysql-password= --max-time=60 --max-requests=0 --num-threads=8 run
sysbench 0.4.12: multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 8

Doing OLTP test.
Running mixed OLTP test
Using Special distribution (12 iterations, 1 pct of values are returned in 75 pct cases)
Using "BEGIN" for starting transactions
Using auto_inc on the id column
Threads started!
Time limit exceeded, exiting...
(last message repeated 7 times)
Done.

OLTP test statistics:
queries performed:
read: 2156658
write: 770232
other: 308093
total: 3234983
transactions: 154046 ([B]2567.32 per sec.)[/B]
deadlocks: 1 (0.02 per sec.)
read/write requests: 2926890 (48779.26 per sec.)
other operations: 308093 (5134.65 per sec.)

Test execution summary:
total time: 60.0028s
total number of events: 154046
total time taken by event execution: 479.0563
per-request statistics:
min: 1.91ms
avg: 3.11ms
max: 49.57ms
approx. 95 percentile: 3.73ms

Threads fairness:
events (avg/stddev): 19255.7500/55.51
execution time (avg/stddev): 59.8820/0.00



TRUNCATED