How do I optimize MySQL?


Sites or applications which use MySQL can perform slowly for several reasons, but often enough, MySQL queries can be sped up by simply optimizing the configuration file (usually located at /etc/my.cnf or /etc/mysql/my.cnf on Linux systems; MySQL’s ‘Data’ or root directory on Windows).

MySQL, and databasing at large, is primarily meant for the storage and retrieval of large amounts of data, yet the default configuration file allocates very little memory to the MySQL service. When MySQL is afforded enough memory, much of your data can be stored in memory. 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).

Is MySQL An Issue?

If you’re unsure if CPU or I/O is the bottleneck, or if MySQL is causing the most CPU usage, you can confirm using the top command via SSH.

You should see results similar to the following:

 

 

 

  1. If you see the process mysqld near the top of the list, then you can safely assume that MySQL needs help, or is related to the issue.
  2. Check any currently running queries. If you are using Plesk, you can use the command mysql –uadmin –p´cat /etc/psa/.psa.shadow´ –e “show full processlist” .If using cPanel, or if MySQL requires no password, you can use via SSH: mysql –e “show full processlist” . MySQL should rarely have tens or hundreds of queries listed, and if there are hundreds shown, it may be worth diagnosing further. Also, “show full processlist” will display the longest-running queries, indicating which queries may be causing overall slowness.
  3. Enable the slow query log. MySQL’s slow query log records the execution time for every query, logging any SQL query which executes longer than the long_query_time. The variable long_query_time, which controls the queries that are logged, can be set using this command:

[root@localhost]# mysql –e “set global long_query_time = 1.00”;

[root@localhost]# mysql –e “show variables like ‘slow_query_log*’”;

You can use programs like less, vi, or nano to read the slow_query_log_file. This file can help drill down which queries are the slowest, whether any specific SQL query can be improved, or if any configuration values in /etc/my.cnf can be optimized.

Editing the my.cnf File

When the MySQL service starts, it reads the my.cnf file, loading any specified configuration values. Depending on how MySQL is installed, the my.cnf file can be relatively sparse (utilizing little memory). If you open /etc/my.cnf (or the MySQL configuration file), you will see something similar to the following screen shot:

Firstly, it’s always respectable to back up a configuration file before any major edit.

[root@localhost]# cp /etc/my.cnf{,.bak}

Also, it’s worth noting that not all values should be adjusted, and even if a variable isn’t listed in my.cnf, a default value is used by MySQL—these values are often appropriate. Analyzing the workload of the queries being executed is necessary to make any fair judgment, but by default, a few variables in MySQL or in /etc/my.cnf are relatively low. A few are:

-      key_buffer_size, or key_buffer: Databases using the MyISAM storage engine store indexes in .MYI files, which can be cached by the server for quicker retrieval. To find if you have many indexes, or any of large size, you can run:

[root@localhost]# find /var/lib/mysql -name "*.MYI" -exec ls -lAsh {} \;

 

It’s safe to set the key_buffer_size to a value larger than the size of all indexes (e.g. “key_buffer_size = 32M”).

 

-      innodb_buffer_pool_size: MyISAM tables are stored in individual files, but by default InnoDB tables are stored in a single file, ibdata1. The InnoDB storage engine performs dramatically quicker when at least ibdata1 can be stored in memory; setting innodb_buffer_pool_size to a size larger than shown in, e.g. ls –lAsh /var/lib/mysql/ibdata1, is recommended. Also, the InnoDB buffer pool only allocates the memory it requires, meaning you can set innodb_buffer_pool_size much higher than necessary. Unless InnoDB tables are not being used, more than 8M of memory should be afforded.

 

-      tmp_table_size, max_heap_table_size: By default, tmp_table_size and max_heap_table_size are set to “16M”. Unless the application using MySQL has optimized indexing and query design, the temporary tables which MySQL generates to complete a query will likely be larger than 16M in size. As a default, 128M or 256M of memory will meet most single or multiple server workloads; on the other hand, if a query causing temporary tables requires multiple gigabytes of memory to complete quickly, the query itself may require configuration.

 

-      query_cache_type, query_cache_type: The MySQL query cache deserves a fair amount of detail, but disabling or enabling the query cache can be dramatic. With the query cache enabled, a query and its response are cached so that if the exact same query is received, MySQL processes it significantly more quickly. But on a UPDATE or INSERT statement, if any cached query references an updated table, it is invalidated. Thus a MySQL server with more updates than retrievals may perform better without the query cache.

 

If you update tables often, consider disabling the query cache. If you aren’t using the query cache but have frequently referenced tables, it may improve performance. Because data can be and is often invalidated, the query_cache_size may operate most quickly at smaller than expected values, from “20M” to “400M”. More information is available under the MySQL documentation: http://dev.mysql.com/doc/refman/5.5/en/query-cache.html

 

  1. To solve this type of issue, you need to make sure that you have at least a true Pentium of at least 2.4Ghz or greater.  The reason is because Celeron processors have trouble processing mySQL.  If you are having trouble with mySQl, you may consider upgrading to a server with a better processor.
  1. If you do have a server with a Pentium processor or higher, and you're still having trouble with your server running slow, you'll want to change the my.cnf file on your machine.  You can do this with the following command:

    The following command will backup the original my.cnf file to the /root directory:
 

[root@localhost]# mv /etc/my.cnf /root

  1. The next command copies a pre-optimized my.cnf file to where the my.cnf file is read from:
 

[root@localhost]# /cp /usr/share/doc/mysql-server-5.0.27/my-huge.cnf /etc/my.cnf

  1. Finally, all that needs to be done is to restart the mySQL server using this command:
 

[root@localhost]# service mysqld restart

 


This article has been provided by Codero Hosting, the leading provider of reliable dedicated, managed and cloud hosting services. Need more information on this topic or to learn more about Codero’s hosting services please visit www.codero.comchat with us online or give us a call at 866-2-CODERO.

 

Posted in
Last update:
2016-05-03 12:42
Average rating:0 (0 Votes)