There’s a number of performance tuning scripts that will analyze your server settings and current status and give you information on recommended changes that you should make. You shouldn’t necessarily follow all of the suggestions, but it’s worthwhile to take a look at anyway.
The script I’ve been using gives you recommendations for the following:
Once you download the script, you’ll need to make it executable with the following command:
If you run this script as a regular user, it will prompt you for your password, so you’ll have to make sure to set access accordingly. If you run it as root it’ll pick up the mysql password from Plesk if you have that installed.
I’ve cut out a lot of the output, which had a lot more recommendations, but was just too long to fit on the page.
Looks like I need to increase my query cache… I set it to only 8MB but it’s cleaning out the cache far too often.
— MYSQL PERFORMANCE TUNING PRIMER — – By: Matthew Montgomery –
MySQL Version 4.1.20 i686
Uptime = 5 days 10 hrs 46 min 5 secAvg. qps = 4Total Questions = 2020809Threads Connected = 1
Server has been running for over 48hrs.It should be safe to follow these recommendations
———– snipped ————–
QUERY CACHEQuery cache is enabledCurrent query_cache_size = 8 MCurrent query_cache_used = 7 MCurrent query_cach_limit = 1 MCurrent Query cache fill ratio = 89.38 %However, 254246 queries have been removed from the query cache due to lack of memoryPerhaps you should raise query_cache_sizeMySQL won’t cache query results that are larger than query_cache_limit in size
———– snipped ————–
This type of information is just invaluable when you are trying to tune the performance of your website.
TEMP TABLESCurrent max_heap_table_size = 16 MCurrent tmp_table_size = 32 MOf 35170 temp tables, 74% were created on diskEffective in-memory tmp_table_size is limited to max_heap_table_size.Perhaps you should increase your tmp_table_size and/or max_heap_table_sizeto reduce the number of disk-based temporary tablesNote! BLOB and TEXT columns are not allow in memory tables.If you are using these columns raising these values might not impact your ratio of on disk temp tables.
———– snipped ————–
Download MySQL Performance Tuning Primer Script
