Percona 5.5 performance regression FreeBSD

MySQL & MariaDB Other MySQL® Questions
1

Percona seems to be slower on FreeBSD as we tested on a clean install server:

Hardware: Supermicro PDSMU Xeon 3220 quad core 2.4Ghz, 4 GB ECC ram, 160 GB Seagate Barracuda 3.0 Gb/s SATA drive

OS: FreeBSD 8.4-RELEASE-p1 AMD64

  • all software were installed using FreeBSD ports and default compile configurations
  • test were conducted using sysbench 0.4.12
  • MySQL data dir was cleared (removed all files) prior to each install of MySQL and Percona
  • each test ran twice per number of threads

Configuration file generated from Percona Configuration Wizard:

my.cnf:


[mysql]

# CLIENT #
port = 3306
socket = /tmp/mysql.sock

[mysqld]

# GENERAL #
user = mysql
default_storage_engine = InnoDB
socket = /tmp/mysql.sock
#pid_file = /var/db/mysql/mysql.pid

# MyISAM #
key_buffer_size = 32M
myisam_recover = FORCE,BACKUP

# SAFETY #
max_allowed_packet = 16M
max_connect_errors = 1000000
skip_name_resolve
sql_mode = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_S UBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ONLY_FULL _GROUP_BY
sysdate_is_now = 1
innodb = FORCE
innodb_strict_mode = 1

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

# BINARY LOGGING #
#log_bin = /var/db/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 = 0
max_connections = 500
thread_cache_size = 50
open_files_limit = 65535
table_definition_cache = 1024
table_open_cache = 128

# INNODB #
innodb_flush_method = O_DIRECT
innodb_log_files_in_group = 2
innodb_log_file_size = 128M
innodb_flush_log_at_trx_commit = 1
innodb_file_per_table = 1
innodb_buffer_pool_size = 2G

# LOGGING #
log_error = /var/db/mysql/mysql-error.log
log_queries_not_using_indexes = 1
slow_query_log = 1
slow_query_log_file = /var/db/mysql/mysql-slow.log

TESTING:

test command:
#sysbench --test=oltp --num-threads={i} --mysql-user=root --max-requests=0 --max-time=120 --mysql-password=mypasswd {prepare | run}

Results:

percona 5.5

4 thread:
transactions: 132707 (1105.86 per sec.)
deadlocks: 0 (0.00 per sec.)

8 thread:
transactions: 155294 (1294.04 per sec.)
deadlocks: 15 (0.12 per sec.)

12 thread:
transactions: 158591 (1321.55 per sec.)
deadlocks: 24 (0.20 per sec.)

16 thread:
transactions: 155354 (1294.54 per sec.)
deadlocks: 26 (0.22 per sec.)

++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++++++++++++++++++++++

mysql-community 5.5

4 thread:
transactions: 134547 (1121.20 per sec.)
deadlocks: 0 (0.00 per sec.)

8 thread:
transactions: 158412 (1320.07 per sec.)
deadlocks: 15 (0.12 per sec.)

12 thread:
transactions: 161310 (1344.19 per sec.)
deadlocks: 19 (0.16 per sec.)

16 thread:
transactions: 158446 (1320.34 per sec.)
deadlocks: 25 (0.21 per sec.)

2

This could be related to many things -the additions of some Percona-only features or the lack of optimization for the that Operating System. In order to profile the issue, it would be really nice to have the output of oprofile/perf and/or mutexes hit by the servers to understand what is the bottleneck on each case. There are several things that are changed in PS, from the flushing algorithms, to the drop table process, and the partitioning of several InnoDB internal structures. Also, please make sure that you restart both servers between tests and execute them several times, as we have seen some strange cases of performance suddenly dropping without known cause (in any flavor of MySQL), even more than that 1% that you are suffering.

Also, 120 seconds per test may be not enough to show some of the improvements in Percona, as some of them, for example, are aimed to have a better stability on the flushing algorithm on the long term. Try 1 hour of activity on your peak throughput.
Additionally, please note that if you are only interested in raw performance, there are some parameters that probably should be tuned for both servers (and of course, there are no Percona-only features activated by default).

3

I’m sure as with any OS there are tuning parameters that can be done to improve performance. The point of this benchmark is just a apple to apple comparison of a generic / out-of-the-box configuration of both software. I know that a long time ago Percona did a FreeBSD benchmark that showed unfavorable results. It would be nice if they revisited FreeBSD since there is a stable port of Percona for it and I am sure a lot of FreeBSD admins would like to implement Percona in their production servers.

4

just additional testing with longer duration:

  • same process and configuration as stated above (with additional server restart after each install of Percona and MySQL
  • increased table size from default 10000 to 1000000 rows
  • tested 8 threads only
  • database is warmed up using 120 secs of OLTP test via sysbench prior to the longer test

test:
#sysbench --test=oltp --oltp-table-size=1000000 --num-threads=8 --mysql-user=root --max-requests=0 --max-time=21600 --mysql-password=mypasswd {prepare|run}

Percona 5.5 server | Percona 5.5 client

OLTP test statistics:
queries performed:
read: 352106286
write: 125752232
other: 50300893
total: 528159411
transactions: 25150444 (1164.37 per sec.)
deadlocks: 5 (0.00 per sec.)
read/write requests: 477858518 (22123.07 per sec.)
other operations: 50300893 (2328.74 per sec.)

Test execution summary:
total time: 21600.0069s
total number of events: 25150444
total time taken by event execution: 172521.0576
per-request statistics:
min: 2.91ms
avg: 6.86ms
max: 423.15ms
approx. 95 percentile: 9.47ms

Threads fairness:
events (avg/stddev): 3143805.5000/2675.13
execution time (avg/stddev): 21565.1322/0.20

Innodb mutexes:


----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 2878508, signal count 11155593
Mutex spin waits 38129568, rounds 157730134, OS waits 1895815
RW-shared spins 3926004, rounds 30666043, OS waits 524392
RW-excl spins 4186506, rounds 37184802, OS waits 229033
Spin rounds per wait: 4.14 mutex, 7.81 RW-shared, 8.88 RW-excl

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

MySQL 5.5 server | MySQL 5.5 client (community version)

OLTP test statistics:
queries performed:
read: 362446336
write: 129445102
other: 51778041
total: 543669479
transactions: 25889017 (1198.57 per sec.)
deadlocks: 7 (0.00 per sec.)
read/write requests: 491891438 (22772.75 per sec.)
other operations: 51778041 (2397.13 per sec.)

Test execution summary:
total time: 21600.0052s
total number of events: 25889017
total time taken by event execution: 172516.9098
per-request statistics:
min: 2.63ms
avg: 6.66ms
max: 2659.06ms
approx. 95 percentile: 9.15ms

Threads fairness:
events (avg/stddev): 3236127.1250/3392.91
execution time (avg/stddev): 21564.6137/0.31

InnoDB mutexes:


----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 3296925, signal count 19525633
Mutex spin waits 72244656, rounds 200727598, OS waits 1964663
RW-shared spins 4415105, rounds 40987989, OS waits 727326
RW-excl spins 3422351, rounds 106873545, OS waits 335181
Spin rounds per wait: 2.78 mutex, 9.28 RW-shared, 31.23 RW-excl