MySQL Performance Problems

Posted on

Question :

Started having issues with our central production MySQL Database Server today.

We have 1 central production MySQL 5.7.11-log server which is bare metal with 32GB mem and 12 core CPU with 2 SSDs in RAID. This server houses 3 databases which are each used by 1 VM running Apache for Each of our custom Web applications (total of 3 VMs running Apache).

One of the Web servers started to slow down this morning and after a few minutes came to a halt entirely. The other 2 Web apps continue to function and perform normally.

We started digging in Apache, restarted the Apache daemon, no effect, restarted the VM with no effect. I restarted the MySQL daemon and the app starting running fine again. A couple hours later in happened again, I restart mysqld and we’re good for another couple of hours.

I’ve dug through Apache logs, PHP logs, MySQL logs. The only thing that stands out in the mysql error.log is Aborted connection 766409 to db: 'DB' user: 'USER' host: 'HOST' (Got an error reading communication packets) there is nothing in the other logs I’ve looked through and been monitoring today that looks out of place.

I’ve been staring at htop, iotop, iftop, and mytop all day and server load on MySQL and the Web VM remains well withing what is normal on any other day. One thing I do notice is in mytop as the slowdown is happening I see a lot of connections piling up from this Web app, almost as many sleeping connections (22) as there were connections waiting or creating (21).

mytop output at time of incident

This application has stopped responding a total of 3 times today, a couple hours apart. All three times the other applications which connect to this database server continue to function normally. The first time it happened I didn’t get a chance to look at mytop before restarting mysql, but the other 2 times I had mytop running on another monitor and watching it and connections piled up both times.

We’re a small company, and do not have a DBA. I’m a developer and have been able to manage and troubleshoot MySQL over the last couple years, but it seems as we grow we’re running into things that I just don’t have the experience to resolve, I’m talking to the owner of the company about hiring a DBA consultant to help fill the gap in our development team, but in the meantime I have no idea where to look next to continue to troubleshoot this and would really appreciate all the help and advice I can get at this point.

Answer :

Based on the information you have provided I would highly scrutinize the oldest queries running when the “slowdown” happens. It’s possible the select(green 21) or the update (green 20) are blocking the rest of your queries and causing a pile up.

The thread state seems to be truncated but this can give you more information as to what may be going on.

It also could be those very old sleeping connections (1 & 2 red) Even though they are sleeping they could be holding active transactions that are hiding. A ‘show engine innodb status’ will reveal potential active transactions holding row locks that are causing newer queries to wait.

Best of luck to you!

Leave a Reply

Your email address will not be published. Required fields are marked *