Troubleshooting performance issues within MySQL/MariaDB is never easy because in truth there can be 1001 reasons and avenues to go down in terms of investigation and in a lot of cases a deep in-depth knowledge of the specific database and also the code base interacting with the database is required in order to do meaningful troubleshooting.
This knowledge base article is intended to give you some tips, starting points and general advice in terms of general MySQL/MariaDB performance issue troubleshooting.
Server Resources #
You can have the best code base in the world with a sleek database design but if you are running an active, large database with many active connections on a spinning disk, not enough cores and very little ram, it will still run poorly, use basic tools within your operating system to check available resources such as ‘top’ or better still ‘atop’.
atop will show you a good overall representation of your server resource use, disk iops/r+w requests as well as cpu use and wait time, if any, all or some of these elements are peaking the chances are this will impact your database performance.
Remember caching to ram only works if you have ram available!
Backups #
It is incredibly common for the act of backing up the database to be the thing that kills the performance, while backups are great and highly recommended for database servers it can be more efficient to instead run the server in a master <> slave setup and then backup the slave instead, if that sounds too complicated for you then why not consider taking us up on our FREE Acronis Cyber Backup offer https://www.clouvider.co.uk/acronis-cyber-cloud-backup/ The Acronis agent is less likely to be as intrusive as your manual backup scripts.
Bad Queries #
Sometimes you just have a badly written query in the front end that causes everything else to hang You can enable the slow-query log in MySQL by simply adding the following line to the bottom of your /etc/my.cnf file (MySQL config file):
slow_query_log = 1log-slow-queries = /var/log/slow-queries.log
Give this at least 24 hours and then if you do have some very slow queries you can at least find out which ones they are so you can fix the front end code or report them to the code maintainer.
Defrag/Optimise #
Databases just like Windows 98 need to be defragmented, the more data you have been changed or written to the database the more you should do this, essentially with frequent updates and changes to data you can end up with holes where data once existed but is now no longer needed or it has been moved.
Usually, if you have a small database <500MB or one with infrequent changes this is not so much of an issue but if that is not the case, you should stay on top of this daily or weekly.
You can do this in a number of ways, if you have PHPMyAdmin installed, you simply go to the database, open it and choose to optimise tables, in some cases, you may need to select the individual tables on the left-hand side and optimise them 1 at a time. If you do not have any management front end for your database you can log in from the command-line shell on your server e.g. ‘mysql -u root -p’ or ‘mariadb -u root -p’ the enter the command ‘USE databasename;’ where databasename is the name of your database then optimise the tables manually with the following command:
optimize table my_table;
Drop Old Transactional Data #
It is common for developers to log certain transactions to the database for example sent emails, or login attempts, over a period of years this can grow out of control silently.
Imagine if every time you open a profile page for example on a website the stores all login dates and times and needs to be fetched and the last 10 times are displayed, this may have been silently growing unpruned for 6 years on a busy site with 100,000 active users, you could be looking at many millions of table entries which will certainly make things slow down and make the database itself unnecessarily large.
Obviously, this is just an example to get you thinking in the right direction but if you do find something like this do not be afraid to backup and then drop this table from the database and recreate it fresh.
