In part 1 of this blog - Help! MySQL is spinning out of control - we wrote about performance challenges you may face when your database grows and which pitfalls you should take into account if you want to be well prepared. In short: what you need to think about beforehand to be able to grow flexibly along with your database. In this blog we want to give you the 6 most important parts you can pay attention to and which parameters are of influence.
1 What is measured, improves
Before you start turning the knobs at all, it is good to get an insight into how your database is functioning in the various areas. Monitoring is key! Monitoring is in fact nothing more than collecting data that gives you more in-depth information on for example queries, deadlocks, transactions, buffers etc.
Grafana and Prometheus are building blocks to set up monitoring. Scraping exporters in Prometheus and adding dashboards to Grafana creates a monitoring solution. Those exporters and dashboards can often be found on GitHub, as well as various plugins. There are also ready-made (open-source) monitoring solutions, such as Percona Management and Monitoring (PMM), available in the market for different types of database engines, including MySQL variants and MariaDB, as well as for DBaaS like AWS Aurora, RDS and Azure SQL database.
2 Query Analysis
One way to assess how your server is performing tasks is to monitor the load on the server. This allows you to identify the heaviest queries for further tuning. Here "time" is the most important factor, because when you run a query on the server, what matters most is how fast it completes. Load monitoring is easy to set up with tools such as MySQL Enterprise Monitor's query analyzer or the Percona Toolkit's pt-query-digest. These tools capture queries that the server executes, immediately bringing up the heaviest and most time-consuming tasks. This provides insight into what you should be focusing on.
If your database is not doing what you want, your primary reaction is to dive right in. However, it is advisable to first check your hardware and assess whether things are perhaps not working properly or are out of balance. Four basic ingredients are important: CPU, RAM, disks and network. If one of these four components is not functioning properly or cannot "give" enough, your database server will function poorly.
Make sure these components are well balanced. A fast processor is nice, especially since MySQL queries cannot be executed in parallel over multiple CPUs, but too little memory, for example, also causes the database to cache on disks and that is killing your performance. And not unimportantly, you will have to allocate this memory in the database settings so it will actually be used. And vice versa, if you allocate a lot of memory in the database settings, it must be available.
4 Operating system
Operating-system tuning is also very important. Think about swappiness, IO scheduler, NUMA technology, huge pages, etc. You can read more about that in this blog by Percona.
5 Inefficient data model
We've written a blog about it before: the influence of data modeling is often underestimated. Issues such as indexing strategy, data types and order of columns should properly reflect the current load of the database. Many databases are still often delivered without a solid indexing strategy or with an outdated index. It is advisable to implement an indexing strategy or to reorganize the current indexing. The choice of data types must be made carefully. For example, when house numbers need to be stored, a smallint or int usually suffices and a bigint wastes space.
The order of columns or fields can also have a substantial impact on the ultimate performance of the database. Often overlooked is the fact that the specifics of columns (mandatory/non-mandatory) and data type (fixed/variable width) affect the actual storage on the data pages.
Indexes help you with performance, but at the same time it can also slow you down tremendously. We still regularly encounter databases with 300+ tables and also 300+ indexes of which only ten are used. The other 290 require memory and also require maintenance because of mutations in the tables. We still encounter databases with an index on even every column. Look at where indexes can help your queries but don't overdo it. There will also need to be regular maintenance on the indexing. Refresh statistics, reorganize or even delete the indexes and recreate them where necessary. Tables that are frequently mutated will benefit greatly from this.
6 Database Settings
InnoDB has been the default storage engine since MySQL 5.5 and it is used far more frequently than any other storage engine. However, it must be carefully configured.
Note: There is a distinction between settings for the entire MySQL instance, such as innodb_buffer_pool_size, and settings per connection, such as tmp_table_size and max_heap_table_size. Because of the latter, a small change (16 MB more) can have big consequences (at 1000 connections potentially 16 Gb more memory usage).
Innodb_buffer_pool_size: The buffer pool is where data and indexes are cached: by making it as large as possible, you use RAM memory and not your disks for most read operations. Typical values are 5-6GB (8GB RAM), 20-25GB (32GB RAM), 100-120GB (128GB).
Innodb_log_file_size: The redo logs are used to ensure that write operations are fast and sustainable, even during crash-recovery. Since MySQL 5.5, crash-recovery performance has been greatly improved so you can now have good write performance and fast crash-recovery. Until MySQL 5.5, the total redo log size was limited to 4GB (the default is to have 2 log files). This has been lifted as of MySQL 5.6.
Max_connections: If you often get the error message 'Too many connections', then max_connections is too low. It often happens that, because the application does not close the connections to the database properly, you need much more than the default 151 connections. The main disadvantage of high values for max_connections (such as 1000 or more) is that the server will stop responding if it needs to perform 1000 or more active transactions for any reason. Using an application-level connection pool or a MySQL-level thread pool can help here.
Query_cache_size: Specifies the size of the cache of MySQL queries waiting to be executed. The recommendation is to start with small values around 10MB and then increase to no more than 100-200MB. With too many queries in the cache, there can be accumulation of queries 'Waiting for cache lock'. If you keep backing up queries, it is better to use EXPLAIN to assess queries and find ways to make them more efficient.
Other specific InnoDB settings that should not necessarily always be left at default:
The above is described in detail on the Percona website.
One button at a time
As you can imagine from all these configurations, options and connections, your database is not static but a dynamic interplay of parameters. Our advice if you want to do it yourself: push one button at a time. Don't change multiple settings at once. Also make sure you use the right configuration file and don't forget to do some version control! You won't be the first one who can't go back...
Rather call in an expert?
Do you find this interesting and is it in line with your request for help but would you rather have an expert look at it? You can always call or email us, we are happy to help!