I am running an application that is using a 3 node m/cluster database with 2 load balanced web servers as the front end.

The web servers are standard Linux/Apache/MySQL/PHP application which runs fine, except that every now and then a very simple database query takes an inordinate amount of time. For example, a simple SELECT query that typically takes 0.03 sec to execute takes 7 seconds to complete. I trace this to the time it takes to execute PHP mysql_query() command.

None of the servers are busy at all. m/cluster cmc reports less than 5% load in all 3 nodes with a total of 50 connections. The web servers have a load average of less than 0.2.

I was running the same application before against a MySQL database with the same load, and never observed such an issue. I am not sure if you can help me with this problem, or point me to someone who can, as this seems to be an m/cluster specific problem.

Many thanks,
Jaime

Take a look at what Huck says in http://forums.ev1servers.net/showthread.php?t=60736 we have worked on a few M/clusters and experienced the same sort of problems. Honestly a MySQL 5 cluster is, for many people, a better solution.

Why the actual queries takes so long is a mystery. Have you tried to run the query directly on an m/cluster machine to see if it takes a long time or if it is quick? Regardless 7 seconds is really long and hard to explain as long as it is not an overly complex query.

The queries are very simple SELECTs with index on tables with a few hundred entires. It should take no time, but it is taking long time every now and then.

I added some debuging code to try to quantify the problem. It seems to happen about once every 10-15 seconds. According to cmc the request rate is about 200 req/sec. Different table, and database every time.

mysql 5.0 is not an option since my databses (there are 80 of them, one of each of our customers) are currently about 10Gb in size total. Maybe mysql 5.1 when its available with disk data.