MySQL queries slowing down

[DVDGuy]

I've run into a strange situation. Recently moved servers, both were using the latest version of WHM/cPanel (and similar set up in terms of other software), and the same versions of php/MySQL. There was a processing script on the old server that required thousands of SELECT queries to be processed (just SELECT queries, no INSERTS), and it managed to do that pretty quickly, in about 19 seconds. The queries are being run in a 'for' loop.

When the same script was run on the new server, which is actually faster and has more RAM, it takes much much longer, about 3 or 4 minutes. Having analysed the queries, it seems they start out really quickly, and then instead of hundreds of queries being processed per second like at the start, it falls all the way down to about a dozen every second. On the old server, they're being processed at a steady, fast, rate.

Other than the obvious suggestion of optimizing the script (I already have, and have bypassed the problem), can anyone suggest what could be happening? Is there some kind of intentional throttling in play here?

I bypassed the problem by having a script that processes a few hundred queries at a time, and then a main script that calls that script via curl, and even though the number of SELECT's remain the same, the processing is done in about the same time as the old server (actually a bit faster, as you would expect).

One major difference between the old server and the new server is that the new server is running 64-bit RHEL 5, vs 32-bit RHEL 4 on the old server. Not sure if this could be the cause.

Thanks.

[LinuxTechie]

Hello,

I a pretty sure that the arch 64 bit is not at all causing the issue. Can you check whether mysql is optimized in your new server?

[cPanelTristan]

Based on prior tests I've performed for MySQL, 64-bit MySQL actually should perform faster than 32-bit MySQL. Do you have the same settings in /etc/my.cnf on both machines? Can you provide the contents of /etc/my.cnf from both of those machines for us to see what they have?

[garrettp]

Two major things that are going to make a huge difference for you, and it doesn't sound like they got moved over:

1) Table index(es)
2) MySQL Query Cache

Implementing one or both will most certainly bring you back to par with your previous execution times.

[DVDGuy]

Thanks for the replies.

The new server has been optimized for MySQL, and I've checked the relevant data and they're all fairly healthy (like the query cache, # of opened tables ...).

So I did further testing, to narrow down which function or query was actually causing the slow down, and actually found that it wasn't a database issue at all, but rather a php one, and one that's not hard to solve (with some better coding).

So it's all good now

[cPanelTristan]

Would you be willing to share what the exact PHP issue happens to be for future reference?

[DVDGuy]

It was the kind of silly php newb mistake, of using include() in a function, and then trying to call that function about 50,000 times. Still, it's strange that the old server managed to deal with this, while the new server would start to slow down after after the first 1,000 calls or so, and sometimes even with an out of memory error (the included file was nothing more than just a list of pre-set variables).

[LinuxTechie]

Hello,

Are you able to find anything in the error_log or mysql logs?

[garrettp]

Perhaps your old server had a better disk subsystem allowing better I/O throughput which would be paramount for quick reads on that many includes.