At my company, we have been deploying Percona 5.6.36-rel82.1 on Centos servers as the data backend for our Java application.
Oddly, on one of our sites the performance is much lower than the others, and we are at a loss for figuring out why.
The database server at this site is a Centos 7.6 Dell PowerEdge R740xd, with a H740P RAID controller and a 7 x 1.92TB SSD RAID5, with 1.5TB of 2666Mhz DDR4 , connected via 10Gbit ethernet to a server running the java application.
The my.cnf for this server is currently:
[mysql]
max_allowed_packet = 10M
[mysqld]
datadir = /export/ssdraid/mysql
socket = /export/ssdraid/mysql/mysql.sock
max_connections = 10000
key_buffer = 1M
max_allowed_packet = 10M
myisam_sort_buffer_size = 64M
query_cache_size = 16M
innodb_buffer_pool_instances = 16
innodb_buffer_pool_size = 640G
tokudb_cache_size = 640G
net_write_timeout = 120
net_read_timeout = 120
connect_timeout = 120
innodb_lock_wait_timeout = 120
thread_pool_idle_timeout = 120
innodb_additional_mem_pool_size = 20M
innodb_checksums = 0
innodb_log_file_size = 2000M
innodb_log_buffer_size = 1024M
innodb_flush_log_at_trx_commit = 0
performance_schema = OFF
innodb_read_ahead_threshold = 32
innodb_file_format = Barracuda
bulk_insert_buffer_size = 16M
innodb_file_per_table
transaction-isolation = READ-UNCOMMITTED
secure-file-priv = “”
tmpdir = /export/ssdraid/tmp
symbolic-links = 1
[mysqld_safe]
thp-setting=never
log-error = /var/log/mysqld.log
pid-file = /var/run/mysqld/mysqld.pid
When I examine the server during the slow jobs, I see that it is fairly busy. About 1.1GB/sec SSD reads/ 50MB/sec SSD writes, about 1000% CPU showing in ‘top’ for the mysqld process.
The database is a mixture of InnoDB and TokuDB tables.
Any advice on tuning this?
Also, we are willing to contract with Percona to provide paid optimization consulting for this problem. Who do we contact about this?