MySQL has two primary storage engines: MyISAM and InnoDB.
Each has its own performance characteristics and considerations but generally MyISAM is good for read-intensive data and InnoDB is good for transactional data.
Use the following simple guidelines to tune your MySQL Server’s performance:
Run ‘OPTIMIZE TABLE’ and ‘REPAIR TABLE’ operations regularly to keep things running smoothly. You can use the MySQL ‘OPTIMIZE TABLE’ command to effectively defragment a mysql table and increase the loading speed of your database. This command is very useful for tables which are frequently updated.
mysql> OPTIMIZE TABLE demo; +-----------------------+----------+----------+----------+ | Table | Op | Msg_type | Msg_text | +-----------------------+----------+----------+----------+ | vps.demo | optimize | status | OK | +-----------------------+----------+----------+----------+ 1 row in set (1.57 sec)
This has the effect of defragmenting the table and reducing the size of the table on disk. It also has a very positive effect on query performance, reducing the select query response time.
If you have the DirectAdmin control panel installed on your server, you can optimize the MySQL databases using the following command:
mysqlcheck --defaults-extra-file=/usr/local/directadmin/conf/my.cnf --auto-repair --optimize --all-databases
Make sure you have optimized all your queries first, and ensure that your tables are indexed properly for MySQL to use.
Enable the slow query log. The slow query log can be used to find queries that take a long time to execute and are therefore candidates for optimization.
Edit the ‘/etc/my.cnf ‘ MySQL configuration file (or ‘/etc/mysql/my.cnf’ on Debian based distros) and add/uncomment the following lines:
slow_query_log=1 slow_query_log_file=/var/log/mysql-slow_query.log long_query_time=3
Note that in MySQL versions prior to 5.5 the syntax is slightly different, so add/uncomment the following lines in ‘my.cnf’ configuration file:
log_slow_queries=1 log_slow_queries=/var/log/mysql-slow_query.log long_query_time=3
Restart the MySQL server for the changes to take effect. This enables logging of slow queries. After enabling slow query log, MySQL will create, capture and log to the log file all the SQL statements that took more than 3 seconds to execute, which is by default set to 10 seconds. The overall MySQL and server performance will increase significantly by lowering the number of slow queries.
After these optimization steps, the MySQL service will take much less time to query your database which will result in better performance of your website scripts.
PS. If you liked this post please share it with your friends on the social networks using the buttons on the left or simply leave a reply below. Thanks.