DML queries with different performance on similar servers

Question

I am measuring the performance between two servers that serve a database via MySQL 5.6. The DB1 server is a dedicated database server and the DB2 server shares resources with other services such as HTTP, Redis and AMQP (it has more workload).

The idea is separate all DB2 services on dedicated servers. The problem comes with the performance of DML commands between servers. Executing the same query, the DB2 (shared server) performs better than the dedicated server (between 400ms and 3500ms faster on average in all queries). They two servers mount the same type of hardware, operating system (CentOS 6) and the database is identical (including indexes).

DB1 has CPU with 8 threads and 32GB of RAM. The my.cnf configuration is as follows:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

symbolic-links=0

# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

# Fine Tuning
thread_stack = 512K
key_buffer = 1G
max_allowed_packet = 32M
sort_buffer_size = 12M
read_buffer_size = 12M
read_rnd_buffer_size = 6M
myisam_sort_buffer_size = 64M
query_cache_size = 512M
query_cache_type = 1
join_buffer_size = 16M
tmp_table_size = 512M
max_connections = 2048
max_heap_table_size = 64M
wait_timeout = 3000

skip-external-locking
skip-name-resolve

# InnoDB
innodb_buffer_pool_size = 24G
innodb_additional_mem_pool_size = 256M
innodb_file_per_table = ON
innodb_log_buffer_size = 64M
innodb_log_file_size = 64M
innodb_flush_log_at_trx_commit = 0

DB2 has CPU with 16 threads and 64GB of RAM. The my.cnf configuration is as follows:

[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
log-error       = /var/log/mysql/error.log

symbolic-links=0

# Fine Tuning
thread_stack = 512K
key_buffer = 384M
max_allowed_packet = 32M
sort_buffer_size = 4M
read_buffer_size = 4M
read_rnd_buffer_size = 2M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
thread_concurrency = 8
query_cache_size = 128M
query_cache_type = 1
join_buffer_size = 8M
tmp_table_size = 256M
max_connections = 800
max_heap_table_size = 32M
wait_timeout = 5000

skip-external-locking
skip-name-resolve

# InnoDB
innodb_lock_wait_timeout = 5000
innodb_thread_concurrency = 8
innodb_additional_mem_pool_size = 40M
innodb_file_per_table=1

innodb_buffer_pool_size = 4G
innodb_log_buffer_size = 256M
innodb_log_file_size = 1G
innodb_flush_log_at_trx_commit = 0

Anyone who knows what may be happening?

Thank you!

Answer 1

A large query_cache_size can hurt performance.

innodb_buffer_pool_size is quite different between the servers. How big is the dataset?

Answer 2

Suggestion for DB1 to consider for your my.cnf [mysqld] section

sort_buffer_size=2M
read_buffer_size=128K
read_rnd_buffer_size=256K
query_cache_size=64M
tmp_table_size=164M
max_heap_table_size=164M
innodb_buffer_pool_size=8G
innodb_buffer_pool_instances=8
innodb_lru_scan_depth=100
innodb_log_file_size=256M

Let us know how you are doing after implementation on DB1, please.