Overview

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. 

These instructions are for dedicated or Virutal Private Servers (VPS’s) only. If you have shared hosting and need better database performance, talk to the Conetix team about migrating to a VPS.

Instructions

Warning! Make sure your database has been backed up first before making any changes.

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.

It’s best to run this tool a few hours after the database is in use, not just after restarting.

  1. Download the MySQLTuner script:

    wget https://raw.github.com/major/MySQLTuner-perl/master/mysqltuner.pl

  2. Add execute permissions:

    chmod +x mysqltuner.pl

  3. Then, run the script: 

    ./mysqltuner.pl

Warning! While MySQLTuner is great at giving you an initial idea of what settings to change, please understand what you are changing before editing configurations. Some changes could result in degraded performance and at worst, data loss without proper care and attention.

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.

Further Reading

Was this article helpful?
Yes No

Related Articles