Re: Query Takes Longer Each Time Its Run

[anthony]

Enabled the slow query log and restarted the server. Ran the query 8 times.

Here is the slow query log from mysqldumpslow:

Reading mysql slow query log from dbn01-slow.log
Count: 8 Time=20.47s (163s) Lock=0.00s (0s) Rows=22.0 (176), root[root]@localhost
( SELECT
users.FirstName,
users.LastName,

The run time of the 8 queries was:

2.3
8.3
13.4
18.32
23.19
27.97
32.70
37.4

Each run the CPU pegs at 99.9:

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
23805 mysql 18 0 4442m 819m 5248 S 99.9 5.1 2:16.42 mysqld


We built two identical machines and both exhibit the exact same issue. I thought it may have been MySQL5.5 on CentOS 5.5 so I reinstalled the second server with 5.1. Its running the same OS and the same MySQL version now as our production db server, but this machine has twice the CPU power and twice the RAM. I used the same my.cnf as our production server. On this machine, on the same data with the same indexes the query runs in 1.5 seconds consistently, still not the .4 were getting off the production server.

So, I really don't know where to go from here. What's the feeling on this? MySQL? OS? Hardware? We were looking to increase performance with an updated server and MySQL not go backwards lol.