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