MySQL rand() High CPU Usage

MySQL has been eating my server alive for the past several months, and I never could determine the exact cause. I’m running MySQL in a VPS environment with 1GB of RAM with two dedicated AMD (Opteron) CPU cores. From a hardware standpoint, the system is a little bit dated, but has plenty of horsepower to run a fairly busy Apache/MySQL server.

So, after doing some analysis on my server setup and some digging around on various blogs and forums, I determined the problem. I had three separate php scripts that were pulling multiple random records out of a database. The MySQL queries looked something like this:

SELECT t1.field1, t1.field2, t2.field1, t2.field2
     FROM table1 t1, table2 t2 where t1.field1= '1' and t1.id = t2.id 
     ORDER BY RAND() LIMIT 10;

Now, the MySQL query above was selecting from two MySQL database tables with over 20,000 records each, then sorting and pulling out several random records. I knew at the time that this was a CPU-intensive way of going about it, but figured that I would optimize it someday. Unfortunately, someday never came until my server started having a difficult time serving all of the requests that it was getting.

I was seeing in the output of ‘top’ that CPU availability was bouncing against 0.0% and that some processes were even having to wait for CPU time with MySQL taking up to 70 or 75% of a single CPU. It looked something like this:

top - 20:36:59 up 492 days, 11:08,  1 user,  load average: 9.71, 8.47, 11.40
Tasks:  64 total,   1 running,  63 sleeping,   0 stopped,   0 zombie
Cpu(s):  3.0% us,  0.9% sy,  0.0% ni, 2.1% id,  1.2% wa,  0.0% hi,  0.0% si
Mem:   1048576k total,   487916k used,   360660k free,        0k buffers
Swap:        0k total,        0k used,        0k free,        0k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
 1851 mysql     15   0  333m 222m 5000 S 72.3 26.8 464:48.28 mysqld
 1834 nobody    15   0 30752  16m 4324 S  3.0  1.6   0:04.53 httpd
 5841 nobody    15   0 28340  16m 3940 S  1.3  1.6   0:00.17 httpd
 5624 nobody    15   0 28240  16m 3976 S  1.0  1.6   0:00.40 httpd
 3805 nobody    15   0 30884  16m 4280 S  0.7  1.6   0:00.95 httpd
 5793 nobody    15   0 28120  16m 3972 S  0.7  1.6   0:00.28 httpd
 5798 nobody    15   0 27984  15m 3556 S  0.7  1.5   0:00.04 httpd

The problem was very noticeable on the web services side of things as well. Visiting a website on the server would sometimes result in 10 seconds or more of waiting for the httpd to serve a page after the connection was made. Sometimes, it would even reach the point where the browser would time out the connection.

Clearly, this was not a sustainable situation! So, I did some searching and digging, and eventually remembered those mysql queries that I had created over the last several months that I knew were CPU-intensive.

One of the alternative approaches that I saw relied upon the server-side php code generating a random number, then executing against that number instead of letting the MySQL rand() function do it. Since I really don’t care if all three of my return values from the database are truly random, I was more than happy to use the php value to create an index point into my set of records, then take the next 9 records in sequence to fill my 10 value quota.

// Get a random value between 1 and 10000 to create our index
$start_row = mt_rand(0, 10000);
// execute our query using limit $start_row, number_of 
$query = "SELECT t1.field1, t1.field2, t2.field1, t2.field2
     FROM table1 t1, table2 t2 where t1.field1 = '1' and t1.id = t2.id 
     ORDER BY t1.field2 ASC LIMIT $start_row, 10";

We could take this a step further and execute a separate query to get the actual number of records in the table instead of using the number 10000, but since I’m not concerned with getting to every single possible record in the table, I’m not too worried about that aspect. Maybe I’ll think about fixing/optimizing that part of it sometime in the next few months.