0

Server:

Disk: 2X 500 SSD RAID 1, 3 TB HDD RAM: 256 GB DDR4 CPU: Intel Xeon 10 core

Database:

Percona for MySQL 5.7.23

Average number of active connections:

1700

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. <

7
  • 2
    You might want to narrow down where you want to start. There are a lot of pieces to this puzzle and we don't even understand your data model, how you are updating records and when and how records are selected and when plus peak times, your index strategies, etc. For the slow log COMMIT - INSERT INTO - SELECT, I'd say start by trying to determined which stored procedure or whatever is running the exact query, and then perhaps look at tuning that up and see how updates are affecting indexes if applicable. Quick thoughts only. – IT Thug Ninja Jan 18 '19 at 2:34
  • SHOW GLOBAL STATUS information might provide some hints too. innodb_flush_log_at_trx_commit=0 is just asking for data loss. How are your SSDs configured for MySQL and is the HDD used? If so which file on which disk/raid? – danblack Jan 18 '19 at 3:24
  • And SHOW VARIABLES; – Rick James Jan 18 '19 at 4:58
  • COMMIT -- find out what transaction is doing a lot of stuff. Then let's look at all the SQL in that xaction. Also, please provide SHOW CREATE TABLE. – Rick James Jan 18 '19 at 5:01
  • As stated above there are many things and one came in my mind is if there are many DML operations and specially delete, please do run optimize table to de-fragment tables and see if performance gets better, do this in low/off production window – Nawaz Sohail Jan 18 '19 at 7:15

Your Answer

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

Browse other questions tagged or ask your own question.