Web Development

One of the reasons MySQL is popping up in top or is eating up to much processor time... or nowdays is eating up too many processors are ORDER BY clauses. Many ORDER BY clauses will translate in temporary tables stored to disk. If you are using GROUP BY colA ORDER BY colB then for sure you will get a temporary table.

Just to give you time to fix the problem you can perform a small hack. Although MySQL optimization usually involves OS optimization also I still prefer to call it a hack because you are not rewriting your queries to boost the performance you're tweaking the OS.

Follow up:

But if the server load is getting too high this may do well.

In Fedora I mounted a folder in RAM. So I've made a folder: /mnt/mysql_tmp. In /etc/fstab I added the following line:

none /mnt/mysql_tmp tmpfs mode=1777,size=536870912

Then the changes in MySQL configuration folder that is usually /etc/my.cnf. Locate the option tmpdir if it's there change it, if it's not there add it... the final result should be something like:

tmpdir=/mnt/mysql_tmp

Now if you can afford a system reboot it would be fine. If not you should run
# mount -a
to mount the new folder in memory and then restart the MySQL server.

RAM is now cheap... so you can add as much as you can afford.
This allows the folder to accept up to 512 MB. This memory will be used only when needed and from experience I saw that MySQL is pretty honest at deleting temporary files. If the system has no available memory but the folder is growing the OS will start swapping... so be sure you computed correctly the size... otherwise the machine will spiral down to death. If the limit is reached MySQL will start logging that it's running out of disk space in the given directory, /mnt/mysql_tmp in our example.

From time to time this got me out of trouble generated by not that good queries. But I believe that this is just a temporary solution. The real solution is to change your queries.