Mysql performance management


mysql performance managementWhen fixing or improving the performance of MySQL, you will notice that administrators often use the basic yum install mysql performance management command during implementation and forget about the issue. With this approach, it is not surprising that the performance of the database is not at its highest level.

The easiest way to increase this mysql performance management is to order new machines with more RAM and processors. Unfortunately, due to the default operating parameters of the MySQL database resulting from a simple installation after the purchase and implementation of the purchased hardware, no significant improvement is made.

We have looked at what configurations are used by vendors such as Amazon and Microsoft, and suggest what parameters are worth paying attention to when optimizing the operation of supervised databases. Often, even without analyzing the queries or application of the database, it is enough to change the options shown in Table 1 to observe a significant improvement in efficiency. Below we present what the individual parameters mean:

  innodb_buffer_pool_size - memory for the data itself and the index. The expected value is usually between 70 and 80 percent of physical memory.

  innodb_log_file_size - size of the repetition log. Repeat logs ensure that writing operations are fast, reliable and most importantly, that in case of unexpected failure data will not be lost. If the set value is too small, then the data from the repetition log must go to the data files, which in turn is more laborious and takes more time. 512 MB usually gives enough space for logging the write operation.

   max_connections - sometimes applications do not close connections correctly. A higher value will give the server more time to close unused connections and the possibility to accept new connections. The maximum number of connections is 10 000, but the recommended maximum is 5000.

mysql performance management   innodb_file_per_table - this setting enables or disables the InnoDB engine function responsible for storing tables in separate files. From a performance point of view, splitting tables into separate files increases performance. This option should also be enabled to ensure the efficient use of a number of advanced administrative operations. Enabling this function can speed up the transmission of the data area from the tables. Starting with MySQL 5.6, the default setting is 1 (enabled). In older versions, the default setting was 0 (disabled). This value should be changed before creating tables, as this only applies to newly created tables.

    innodb_flush_log_at_trx_commit - The default value is 1, with a value range from 0 to 2. The default value is the most suitable option for a standalone MySQL server. Setting 2 provides the most consistent data and is suitable for multi-server (MySQL Cluster) configurations. Setting 0 allows for data loss, which can affect reliability (in some cases we can achieve much better performance). This is a suitable setting for Slave in MySQL multi-server solutions.

   innodb_log_buffer_size - the amount of RAM allocated to the log buffer. It allows you to run the transaction without having to save the log file to disk before approving the transaction. However, if there is a large binary object or text field, the cache will be quickly consumed and frequent disk I/O operations will be started. Therefore, it is worth increasing the buffer size if the state variable Innodb_log_waits is not 0.

mysql performance management    query_cache_size - the best option is to set this value to zero (default setting in MySQL 5.6) and disable this mechanism at the beginning of the configuration. Use other methods to speed up queries. This is one of the more controversial settings, as a value different from zero can speed up certain queries. If you decide to modify it, remember the following rules. We start with a value of 10 MB and increase it by 10 MB. As a rule, we should not exceed 200 MB, although some sources give a value even half that - 100 MB. The large size of the query buffer leads to a significant decrease in performance, which results from excessive and blocked cache. Queries in the cache contain exclusive locks. In addition, any insertion, update, deletion, or other modification of the table clears all important entries in the query cache. This happens even when free memory is available in query_cache_size. As a result, the larger the query cache, the more system time is used for blocking, emptying, and markup. These recommendations may be different for a specific database - e.g. read-only.

Knowledge of these parameters is the basis for managing and implementing MySQL. Cloud providers, on the other hand, can help us to set the remaining MySQL parameters. We have checked which database parameters are used by default by the largest companies, whether and to what extent they deviate from the default values and what impact these changes may have on performance.