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?