MySQL is a popular relational database management system (RDBMS) that is used to store data for many websites. MySQL is free and open source software released under the GPL. MySQL has been used by millions of people around the world, including many large organizations, such as Google, Facebook, and Amazon. MySQL performance can be improved by using a tuning analyzer script. A tuning analyzer script can help you identify problems with your MySQL server and suggest ways to improve its performance. A tuning analyzer script can be used to analyze your MySQL server’s performance in several ways: -Throughput: This measure how quickly data is being processed by your MySQL server. -Memory usage: This measure how much memory your MySQL server is using. -Disk space usage: This measure how much space your MySQL server is using on disk. -CPU time: This measure how long it takes for your mysql server to process data. -File size: This measure how much data your mysql server is storing on disk. -Time taken to start up the mysqlserver: This measure how long it takes for mysqld to start up after being installed on the system.
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
