MySQL is a highly versatile and high performing relational database management system making, used by many web based packages such as WordPress, Joomla and Magento. While the default configuration offers reasonable performance, there's always ways to ensure you're getting the best speed possible from your database.
As a hosting provider, we're asked all the time for a "faster" server or more resources, whereas in many cases this isn't going to help the issue. Better optimisation and performance tuning of your existing setup should always be the first step.
One of the easiest tools to help diagnose issues is MySQLTuner. This is a Perl based script which will analyse the current performance of your database service.
Download the MySQLTuner script:
Add execute permissions:
chmod +x mysqltuner.pl
Then, run the script:
Here's a few basic hints:
- Look at your InnoDB memory allocation (innodb_buffer_pool_size) to ensure enough memory is allocated to MySQL.
- Check for warnings about joins performed without indexes. These have the potential to be very slow to run, especially if you have a large amount of rows.
- Check for fragmented tables.
- Use the EXPLAIN command in MySQL to help determine performance issues with your queries.
There are many other tips, tweaks and enhancements possible, use the suggestions from MySQLTuner to help point you in the right direction and run a few Google searches on the issue. If you're a Conetix customer, you can log a support ticket and we'll perform some basic analysis for you completely free of charge.
- InnoDB Performance Tuning: http://dev.mysql.com/doc/refman/5.0/en/innodb-tuning.html
- Oracle Top 10 Performance Tips: https://wikis.oracle.com/pages/viewpage.action?pageId=27263381
- How To Index For Joins With MySQL: hackmysql.com/case4
- MySQL EXPLAIN Explained: http://www.slideshare.net/phpcodemonkey/mysql-explain-explained