Disk: 2X 500 SSD RAID 1, 3 TB HDD RAM: 256 GB DDR4 CPU: Intel Xeon 10 core
Percona for MySQL 5.7.23
Average number of active connections:
I have 4 schemas, and in every schema there are 3 tables that are under a lot of actions (read, write and update) every second from those connected users.Two schemas have one procedure with transaction that add row to table after some checks. For three months database grows to 44 GB with deleting and it's not even on its load peak. In the last few days from time to time, bottleneck appears and causes me a lot of problems. Slow log file write: - COMMIT - INSERT INTO - SELECT Select is ok, because it's going through a lot of rows, but insert and commits are problems.
Parameters that I set at the beginning: innodb_io_capacity = 3000; innodb_io_capacity_max = 6000; innodb_buffer_pool_size = 212 GB; innodb_buffer_pool_instances = 32; innodb_read_io_threads = 8; innodb_write_io_threads = 8; innodb_flush_log_at_trx_commit = 0;
I'm afraid that I misconfigured something, but I don't know what. Everything I changed is from suggestions from the web. Also, I left the option that there is something wrong with SSD drives, and they are not capable to handle the load at some point. Just to mention there isn't replication.
I have a great machine, and I would like to configure it for maximum performance, so any experienced and reliable advice would be great.
UPDATE: Sorry for my late response, but we found that some processes of other applications cause problems to MySQL, even our CPU usage is miserably low, same as our RAM consumption. We started monitoring our thread usage, and found that some applications use full threads(max 5 out of 20 which we have). Adding a new pair of SSD disks(Hardware RAID 1), and switch database on it resolved our problems. <