Knowledgebase & Downloads

The knowledgebase provides documentation written by our team. Please select a category or search for answers.


Improving poor MySQL performance due to many temporary tables with RAM

Note: Use this solution only when you have enough free RAM for this.

When your CPU usage and load goes high and pages open slowly MySQL with slow I/O can be the culprit.
First check with 'top' if MySQL has high CPU usage.
Use mytop to see if queries are using temporary tables.
Then check for possible hints and solutions with mysqltuner.pl
Some of them may recommend limiting result set and optimizing queries and these are the primary solutions but for a fast resolution see below.

To correct the issue you may try to set higer values for 
in /etc/my.cnf and restart mysql to see if it helps.

If it does not help then also set
tmpdir = /dev/shm

This will create the temporary tables (for example for sorting operations) on /dev/shm (in RAM then swap). 
Check size of your /dev/shm with 'df -h'
After restarting MySQL check 
ls -lah /dev/shm
to see new temporary tables being created.

Powered by HostBill