MySQL is a database server used for large data storage and retrieval, yet the default configuration file very little memory to the MySQL service. When MySQL is afforded enough memory, much of your data can be stored in memory for faster access. The more data that is stored in MySQL’s memory, or RAM, the less to be read from a hard drive (which is magnitudes slower).
Your website and other applications using MySQL can work slow for various reasons. Most often, its query performance is enhanced by tweaking the configuration file (located at /etc/my.cnf or /etc/mysql/my.cnf on Linux systems and at MySQL’s ‘Data’ or root directory on Windows systems).
Below is the MySQL troubleshooting process:
- To find out if CPU or I/O is reason for most CPU usage, use top command via SSH to verify.
- If mysqld process is near the top of the list, then you can assume that MySQL has an issue and needs help.
- Check currently running queries. If you’re using Plesk, use command : [root@localhost]# mysql –uadmin –p`cat /etc/psa/.psa.shadow` –e “show full processlist”
and if you’re using cPanel or if MySQL doesn’t requires password, you can this command via SSH:
- [root@localhost]# mysql –e “show full processlist”
MySQL should have 10s or 100s listed queries, and if it has more, they should be diagnosed further. Plus, “show full processlist” displays longest-running queries indicating queries cause overall slowness.
Activate slow query log. MySQL’s slow query log keeps a record of execution time for every query, logging any SQL query which executes longer than long_query_time command which controls logged queries, can be set using command:
- [root@localhost]# mysql –e “set global long_query_time = 1.00”;
- [root@localhost]# mysql –e “show variables like ‘slow_query_log*’”;
To read slow_query_log_file, you may use programs like vi, nano or less.
You can use programs like less, vi, or nano to read the slow_query_log_file. It helps you know the information about slowest queries, if any specific SQL query can be enhanced, or if any of the configuration values in /etc/my.cnf are optimizable.
To edit ‘my.cnf’ file:
When MySQL starts, my.cnf file reads loading specified configuration values. The my.cnf file can be relatively scarce depending on the way MySQL is installed.
It’s always advised to backup a configuration file before any major edit- [root@localhost]# cp /etc/my.cnf{,.bak}
Plus, all values shouldn’t be adjusted and a default value is used by MySQL if a variable isn’t listed in my.cnf- these are often accurate values. Workload analysis of executed queries is essential to make a fair decision but by default, few variables in MySQL are relatively low and they are as below:
key_buffer_size, or key_buffer:
Databases that use MyISAM storage engine store, indexes in .MYI files that can be cached by server for faster retrieval. To find out if you have large filesize or many indexes, you may run:
[root@localhost]# find /var/lib/mysql -name "*.MYI" -exec ls -lAsh {} \;
It’s secure to set the key_buffer_size to a larger value than all indexes’ size.
innodb_buffer_pool_size:
MyISAM tables get stored in individual files and but InnoDB tables get stored in single file ibdata1 by default. InnoDB storage engine runs faster when at least ibdata1 can be stored in memory; setting innodb_buffer_pool_size to a larger size is recommended. e.g. ls –lAsh /var/lib/mysql/ibdata1. Plus, the InnoDB buffer pool only allocates the memory it needs, i.e. you can set innodb_buffer_pool_size higher than necessary.
tmp_table_size, max_heap_table_size:
These parameters must be set to ‘16M’. Temporary tables which MySQL generates to complete a query will be larger than 16M unless app using MySQL has optimized indexing and query design. By default, 128M or 256M memory meets multiple server workloads; and if a query causing temporary tables needs multiple GBs of memory to complete quickly, it may need configuration itself.
query cache, query_cache_size:
When query cache is enabled, a query with its response are cached so that if exactly same query gets fetched, MySQL processes it much faster. But on INSERT or UPDATE statement, in case a cached query references an updated table, it’s nullified. So, a MySQL server performs better with more updates than retrievals w/o the query cache.
It is preferred to keep the query cache disabled if tables are often updated. Not using query cache and having frequently referenced tables can boost your performance. As data is and can be nullified often, the query_cache_size operates much faster at values smaller than expected, less than “20M-400M”.
And, for resolving such issue, you need to have a true Pentium greater 2.4 GHz as Celeron processors find it hard to process MySQL. In case you have problem with MySQL, you should go for server with better processor.
If you already have a server with Pentium processor or higher, to accelerate speed of your slow-running server, you need to change my.cnf file on your machine. You can do it as follows:
- Backup the original ‘my.cnf’ file to the /root directory using command:
- [root@localhost]# mv /etc/my.cnf /root
- To copy a pre-optimized ‘my.cnf’ file from where it is read, use command:
- [root@localhost]# cp /usr/share/doc/mysql-server-5.0.27/my-huge.cnf /etc/my.cnf
- At last, you just have to restart the mySQL server using command:
- [root@localhost]# service mysqld restart
Please Note:
This is a brief article to help you identify several issues with your config file as full instructions of MySQL optimization is beyond the scope of this simple knowledge base.
All these commands have been performed by a Professional Server Administrator. Before testing commands or applying any changes, it is advised to take backup of all your data.
