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.
- © 2016 JVMHost.com All rights are reserved.