Improving poor MySQL performance due to many temporary tables with RAM

Knowledgebase & Downloads

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

Articles

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

max_heap_table_size

and

tmp_table_size

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.