MariaDB / MySQL Performance Tuning

Introduction

MySQL is one of the world’s most popular open-source relational database management systems and is widely used in various applications, such as websites. It’s a powerful tool, storing and structuring data in a way that is meaningful and readily accessible. However, with large applications, or as data volumes grow and technology becomes increasingly complex, the sheer amount of data can lead to performance problems. It’s becoming more important to optimize MySQL databases properly to deliver the best end-user experience and to lower infrastructure costs.

MySQL performance tuning is a finely balanced process. It involves refining the configuration, structure, and design of a MySQL database to boost the speed and efficiency of data retrieval and manipulation. In summary this process involves analyzing queries, indexes, tables, and other database objects to identify and resolve bottlenecks. Additionally, MySQL performance tuning may involve using caching techniques to reduce the need for expensive queries. Optimizing the use of memory, storage, and other resources can also help enhance performance. This guide provides several tips on how to tune up your database systems.

Prerequisites

A Linux system with MySQL installed and running, Centos or Ubuntu

An existing database

Administrator credentials for the operating system and the database

System MySQL Performance Tuning

At the system level, you can adjust both hardware and software options to improve MySQL operations.

Balance the four main hardware resources

Storage

Take a moment to evaluate your storage. If you’re using traditional hard disk drives (HDD), consider upgrading to solid-state drives (SSD) for a performance improvement. Use a tool like iotop or sar from the sysstat package to monitor your disk input/output rates. If disk usage is much higher than usage of other resources, consider adding more storage or upgrading to faster storage.

Processor

Processors are usually considered the measure of how fast your system is. Use the Linux top command for a breakdown of how your resources are used. Pay attention to the MySQL processes and the percentage of processor usage they require. Processors are more expensive to upgrade, but if your CPU is a bottleneck, an upgrade might be necessary.

Memory

Memory represents the total amount of RAM in your MySQL database storage server. If you don’t have enough memory, or if the existing memory is inappropriately configured, it can damage your performance. Like other bottlenecks, if your server is constantly running out of memory, you should upgrade by adding more.  Configure MySQL memory usage to ensure it is not consuming too much memory and that it is configured to use available memory efficiently. If you run short of memory, your server will swap to data storage (like a hard drive) to act as memory, which will result in very poor performance.

Network

It’s important to monitor network traffic to make sure you have sufficient network bandwidth to manage the load. Overloading your network can lead to high latency, dropped packets, and even server outages. Make sure you have enough network bandwidth to accommodate your normal levels of database traffic.

Use InnoDB, not MyISAM

MyISAM is a database engine used for some very old MySQL databases. Its main weaknesses are lack of crash-safety and lack of write concurrency. The newer InnoDB addresses those issues and delivers greatly improved performance on most workloads. InnoDB data is clustered on the primary key and keeps data in pages. If a value is too large for a page, InnoDB creates overflow pages. This feature helps keep relevant data in the same place on the storage device, which minimizes the amount of storage I/O required.

Use the latest point release of MySQL

Using the latest version is not always feasible for older and legacy databases. But whenever possible, you should check the version of MySQL in use and upgrade to the latest point release. A part of the ongoing development includes enhancements and bug fixes, and some common adjustments may be rendered obsolete by newer versions of MySQL. In general, it’s usually better to use native MySQL capabilities over scripting and configuration files.

Monitor your metrics

Monitoring your MySQL metrics is an essential part of MySQL performance tuning. You can identify operational bottlenecks and take steps to address them, troubleshooting any concerns as and when they arise. Focus on your key performance indicators (KPIs) such as CPU utilization, disk I/O utilization, query latency, the buffer pool hit rate, and number of connections.

Consider looking is monitoring and tuning I/O. This includes monitoring disk I/O, network I/O, and other I/O-related metrics. It’s important to optimize these I/O configurations, such as tweaking the innodb_io_capacity parameter and the I/O scheduler.

It’s also vital to monitor the performance of queries, and watch out for issues such as slow queries and lock contention.

Optimize your queries

One of the most important aspects of MySQL performance tuning is optimizing your queries. This can be done by identifying slow-performing queries, reviewing the query execution plans, and making adjustments to the query. Identifying slow queries can be done using features like the MySQL slow query log, the performance schema, and third-party tools like pt-query-digest, mysqldumpslow, PMM or SSM. Analyzing query execution plans can be done using the EXPLAIN statement. This will avoid guessing and help you understand the particular characteristics of your database and its workload. Some best practices for writing queries include avoiding unnecessary functions in predicates and wildcards at the beginning of a predicate, using inner joins instead of outer joins, and using DISTINCT and UNION only when necessary.

Refining any subqueries is another important step in improving the performance of your MySQL database. This can be achieved by using the EXISTS or IN operators instead of the JOIN operator, and by using indexes on the columns used in the subquery.

Use proper indexing

Proper indexing is essential as it allows the database to quickly locate and retrieve the desired data, rather than having to scan through the entire table. When creating indexes, consider the columns used in WHERE, JOIN, ORDER BY, and GROUP BY clauses. Additionally, it’s important to avoid over-indexing as this can lead to increased storage costs, slow down table writes, and confuse the query optimizer.

Rows can be served from indexes

MySQL can find and serve rows directly from indexes where all elements from WHERE, GROUP BY, ORDER BY and SELECT clauses are in the index. Such an index is called a “covering index”. All secondary keys point to the primary key and the primary key contains the row itself. This is known as “clustering table on the primary key”. If the InnoDB Buffer Pool is big enough, it can hold most data in memory too. You can use composite keys, which are more effective for queries than individual per-column keys if the query filters on all of the elements in the index. MySQL can use one index per table access, so if you are running queries with a clause like WHERE x=1 and y=2 then having an index over x,y is better than having individual indexes over each column.

Secondary keys are not your enemy

Indexes add to the disk footprint and adding secondary keys will increase the storage requirements. It is important not to over-index your databases, as running many indexes may not provide the efficiency improvements you want to achieve. InnoDB also has to keep them up to date for every row modification. However – the performance benefit of an index that is actually used will vastly outweigh the costs.

Schema design is just as important as any other MySQL settings

Schema design determines the organization of data and how it is stored on the disk. If this is not optimal, it can negatively impact the performance of the database. Since MySQL moved to using InnoDB as the default storage engine in version 5.6, the schema design becomes even more important, as in InnoDB, everything is a primary key! The table is clustered on the primary key, and every secondary key contains a pointer to the primary key. InnoDB uses B-tree indexes, so inserting data in an ordered way (i.e. using quasi-sequential values) prevents primary key fragmentation and thus reduces I/O operations required to find leaf nodes.

Optimize your database structure

The structure of your database can also have a significant impact. so try looking normalizing data, partitioning large tables, and denormalizing data for specific use cases. Structure your database properly, and you can improve the efficiency of your queries and reduce the amount of data that needs to be scanned.

Adjust your configuration settings

The MySQL configuration plays a critical role in database operations and contains a number of settings that can be adjusted to improve performance. Some of the most important settings to consider include the buffer pool size, table_open_cache, and the innodb_log_file_size. It’s important to make small, incremental adjustments to these settings and observe the impact of each change using an advanced monitoring tool like SSM before making any further changes. Optimal settings always depend on the workload, and it is not possible to generically advise on what an optimal configuration is going to be without extensive monitoring to observe the effects via various performance counters.

Use caching

Last but not least, caching can be a powerful tool, although it depends on your system. By caching data in memory, you can reduce the number of disk I/O operations and therefore speed up data retrieval, or you can use third-party caching solutions such as Memcached to cache data, thus reducing the load on the MySQL server. The downside of caching is that the returned data will sometimes be stale.

Conclusion

In conclusion, MySQL performance tuning is an ongoing process that requires a combination of proper schema design, query optimization, indexing, database structure, configuration settings, monitoring, and caching. By following these tips, you can improve the day-to-day functioning of your MySQL database and deliver a better end-user experience. However, it’s important to remember that the specific requirements of your application and the environment in which it runs will determine the most appropriate approach. It’s also important to conduct regular testing and ongoing monitoring to ensure that any changes made to your MySQL environment are having the desired effect.