MySQL server performance plummets

Question

Part of our software solution is responsible for processing files that get uploaded to us by our clients. We want to process them as quickly as we can.

Our system is architected so that there's a single redis job queue, and the processors live on separate processing servers. We can turn a processing server on/off individually. Ideally, the more processing servers we have turned on, the faster we are able to process uploaded files.

The entries in the redis job queue point to rows in MySQL. In order to actually do the processing on the uploaded files, we need to read from and write to MySQL.

The two graphs below show how our system performance changes as we add more processing servers. The green lines on both graphs show the number of processors running. Notice that as we add new processors, the rate at which we process files increases for a while, levels off and then decreases. The average select query time steadily grows, and then explodes. (Ignore the spike in select time around 11:04.)

I feel pretty confident that MySQL is reaching a performance maximum. However, I am not a MySQL admin, nor do I play one on TV.

Do you all have any suggestions about how to track down this performance issue? I can tell you that there are no queries in the slow log; that the machine has 24 cores, and each time I add a new processing server to the mix, the CPU % on mysql goes up by about 100%.

Answer 1

there are no queries in the slow log

Then, make sure they will be. One of the easiest ways to profile MySQL queries is through the slow log. if you find no slow queries is because you probably have a lot of them that take lower that 10 seconds to execute, or that are locked waiting (that time is not considered, "execution time"). Set long_query_time to a lower value, and if you want to profile fully the server, you can temporarily set it to 0 (Warning: may have a bin impact on your query performance), to log all your queries.

That, combined with aggregation tools like pt-query-digest is the 101 of server query optimization. There are other monitoring tools and techniques, but this is a good place to start. You may not identify 100% of the problems, but 90% in my experience.

I feel pretty confident that MySQL is reaching a performance maximum.

That is an assumption without data supporting it. Get evidence first (a.k.a. profile), then evaluate options.

each time [...]CPU % on mysql goes up by about 100%

That is not necessarily a bad thing. Resources are being used. The question is if they are doing it efficiently (that is the real question).