0

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!

3
  • And the network link between DB1 and DB2? – Rick James Sep 13 '19 at 23:49
  • Additional information request for DB1 and DB2 server for comparison. Post on pastebin.com and share the links. Text results of: B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; D) SHOW FULL PROCESSLIST; E) complete MySQLTuner report AND Optional very helpful information, if available includes - htop OR top OR mytop for most active apps, ulimit -a for a linux/unix list of limits, iostat -xm 5 3 for IOPS by device and core/cpu count, for server workload tuning analysis between DB1 and DB2 for CAUSE(s) of your timing differences. – Wilson Hauck Sep 15 '19 at 1:05
  • @jsgarcia Any chance of you posting requested information for analysis? – Wilson Hauck Oct 9 '19 at 21:11
0

A large query_cache_size can hurt performance.

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

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.