02.10.2023

MySQL Performance Tuning and Optimization Tips

How to up performance?

A sluggish database primarily results from inefficient query performance, a multifaceted issue driven by several contributing factors. These include:

Mitigating these performance hindering factors necessitates astute identification and targeted intervention through meticulous monitoring, profiling, and optimization methodologies. Such endeavors can significantly enhance database performance, forestalling any potential slowdowns.

Hardware issue

Select appropriate hardware. MySQL's optimal performance depends on the availability of specific hardware resources. Ensure that your server possesses adequate CPU processing power, memory capacity, and disk storage space to effectively cater to your database's requirements. For beginner setup minimal requirements include a modern multi core CPU, at least 1 GB of RAM (4 GB recommended), and 1 GB of free disk space. Ensure compatibility with your operating system, install necessary dependencies, and use a supported MySQL version tailored to your specific needs.

In the Serverspace you can scale up configuration for your VPS/VDS server by the several clicks! For that  go to the main page and choose your cloud platform:

Screenshot №1 — Main page

Next choose your server and click on the board with label, then you will see panel:

Screenshot №2 — Config

At the left of screenshot you can see button for change configuration +Add or Change configuration, after click on the button on your screen will appear pop up window:

Screenshot №3 — Change config

The cloud platform allowed to change hardware by the several click, after make changes you need to wait restart server and try to use database.

Choose right engine

The choice of the optimal MySQL storage engine hinges on your precise requirements. Nevertheless, in a general context, InnoDB emerges as the superior option for most use cases. It serves as the default storage engine in MySQL 8.0 onwards, boasting several advantages over its counterparts, which include:

Ultimately, the choice of storage engine should align closely with the specific demands and characteristics of your application. While InnoDB stands as a robust default choice, your unique requirements may lead to different considerations, such as the utilization of MyISAM for specific read intensive workloads or MEMORY for in memory data storage. Tailor your choice based on the intricacies of your project and performance priorities. For switching engine you can use command, but remind, you need to change name table and database for yours:

USE gg;
SHOW CREATE TABLE t1;

Screenshot №4 — Show engine

After that you can choose desirable example of engine, which will fit for your case:

ALTER TABLE t1 ENGINE=MyISAM;
SHOW CREATE TABLE t1;

Screenshot №5 — Switch engine

There are engines, which don't support several functions. The MyISAM engine absolutely don't work with parameters foreign keys. Switching engines can be a time consuming operation, especially for large tables.If you are switching engines to a transactional engine, such as InnoDB, you will need to commit the transaction before you can start using the table.

Disabled caching function

Cache function help to reduce load at the DB server, but take space for recently quries. Enabling caching in MySQL involves an initial step of activating the query cache. This action can be accomplished by configuring the query_cache_type variable to a value of 1 within the MySQL configuration file.

Once the query cache has been activated, MySQL assumes the responsibility of storing frequently accessed queries in memory. This caching mechanism contributes to enhanced performance by diminishing the frequency with which MySQL must access the physical database disk. Check status of caching, if that equal one, then all work properly and you need to check another point of problem:

SHOW VARIABLES LIKE 'query_cache_type';

Screenshot №6 — Cache

In the configuration file change parameters to value below:

Then enter command to restart MySQL server:

service mysql restart

Also you can use utility systemctl:

systemctl restart mysql

To clear the query cache, you can run the following query:

FLUSH QUERY CACHE;

Some tips

Here are some strategies to enhance the efficiency of your MySQL database:

Conclusion

A well tuned MySQL database involves hardware considerations, storage engine selection, cache optimization, and query tuning. By implementing these best practices, you can significantly enhance database performance and responsiveness.