MySQL optimization problem for database

1 Overview

There are four ways to optimize a database:

Index policy, explain to determine the validity of indexes, removing redundant indexes, preserving valid indexes

Stand-alone cache, MySQL internal key cache

Side-hanging caches, such as Redis or memcached, are used by the program to cache the results at its own discretion

Improve MySQL server performance by modifying related parameters

This paper mainly introduces the single-machine cache and related parameters of MySQL.

2 Stand-alone cache

MySQL's internal key caching mechanism, query cache, the MySQL cache function is self-contained, but need to be enabled, in order to be efficient enough to hit, MySQL in memory to open up cache space, the data in the cache is the KV value, the KV value is also one of the reasons for the cache to improve efficiency.

The problem here is what to use as a key, the key is the hash value, so you need to do hash operation of the query results. MySQL cache is non-pre-allocated, may cause fragmentation, reduce efficiency

In order to improve the cache hit rate, to allow programmers to use the same kind of separation to write statements, such as all in uppercase, or some keywords are lowercase, because MySQL will be the query statement hash operation, the case is not the same, the hash value is different. If the hash value is different, it cannot be hit. Usually the size of the keyword.

The query cache for MySQL is stand-alone, and the cache is placed on the local host. Considering the cache hit rate, when the front-end scheduling, can be based on the statement routing, the same statement dispatched to the same host. However, Proxysql does not have this scheduling ability, Haproxy can implement seven protocol scheduling, but MySQL does not support the seven layer protocol, MySQL is a list statement, so it cannot be done by Haproxy to do this scheduling

Query cache:

Cache: k/v

Key: Hash value of query statement

Value: The execution result of the query statement

How to tell if a cache is hit:

Judging by the hash value of the query statement: The factors considered by the hash value include

Query itself, to query the database, the protocol version used by the client 、...

SELECT Name from students WHERE stuid=3;

Select Name from students where stuid=3;

Which queries might not be cached? Add an immutable cache as follows

1 Query statement contains UDF (user-defined Functions)

2 Storage functions

3 User-defined variables

4 Temporary tables

5 MySQL system tables or queries that contain column-level permissions

6 functions with indeterminate result values (now ());

7 SELECT statement, if on-demand caching is enabled, in the SELECT statement, [Sql_cache | Sql_no_cache] These two options clearly indicate that the statement needs to be cached or not cached, such as explicitly using Sql_no_cache does not cache

View Cache-related options

MariaDB [(None)]> show global variables like '%cache% ';

Description of the server variables related to query caching:

Query_cache_limit: Maximum query result that can be cached; (maximum of single statement result set size)

A statement with a large result set, explicitly using Sql_no_cache to avoid first caching and then moving out; default is 1M

Query_cache_min_res_unit: The smallest allocation unit of memory block; cache too small query result assembly wasted memory space;

Smaller values reduce space waste, but can result in more frequent memory allocations and recycling operations;

Larger values can lead to wasted space;

Query_cache_size: The total available size of the query cache space, the unit is byte, must be an integer multiple of 1024, and 0 means it is not enabled. This number is not recommended, because each time you adjust this value, the previous cache entry is deleted and the cache needs to be regenerated

If set to 64M, the command is as follows

MariaDB [(None)]> set @ @global. query_cache_size=67108864;

Query_cache_strip_comments

Query_cache_type: The caching function is enabled or not;

On: Enabled, cached data cache, processing of the above mentioned 7 types of queries can not be cached

OFF: Disabled;

DEMAND: Cache On Demand, cache only the query results with Sql_cache in the SELECT statement, if the result size is larger than the cache space, explicitly does not cache, that is, No_cache.

Query_cache_wlock_invalidate: If a table is locked by another connection, the query results can still be returned from the query cache; default is off, which means yes; on means no; the default is off (allowed, double negative invalidate Off so is enabled, read more write less environment, the cache is basically valid)

View status variables: counter, query name or number of insertions.

Mysql> SHOW GLOBAL STATUS like ' qcache% ';

+-------------------------+----------+

| variable_name | Value |

+-------------------------+----------+

| Qcache_free_blocks | 1 Number of free blocks |

| Qcache_free_memory | 16759688 | Query cache Yet remaining space

| Qcache_hits | 0 indicates the number of hit caches |

| Qcache_inserts | 0 indicates the number of times the query was inserted |

| Qcache_lowmem_prunes | 0 This value is large enough to indicate that the cache space is too small |

| qcache_not_cached | 0 number of buffers that would have been put into cache space but not actually cached |

| Qcache_queries_in_cache | 0 Number of queries in the cache system |

| Qcache_total_blocks | 1 queries Total block Count |

+-------------------------+----------+

Evaluate the Select cache hit rate formula as follows:

Qcache_hits/com_select

When read and write operations are similar, it is recommended to turn off the feature, but if it is a large number of queries, that is, in a read-write-less environment, it is recommended to enable caching

Note that the cache is generated on the first query and the next query hits the cache

See the total number of Com_select

MariaDB [sunny]> show global status like ' com%select% ';

3 Modifying related parameters

The InnoDB storage Engine related Parameters View command is as follows

MariaDB [sunny]> show global variables like ' innodb% ';

Default parameters are generally not recommended.

Optimization of parameters, the general following parameters do not use the default value to run, but to adapt to the actual situation to use the service, the relevant parameters are described below

Innodb_buffer_pool_size This parameter is explained as follows:

The size of the InnoDB cache pool, typically used to cache indexes, data, and buffers when data is inserted. Innodb_buffer_pool_size This parameter generally according to the actual situation, do not support dynamic modification, need to modify the configuration file, the default is 128M, this value generally to adjust, improve performance optimization, this is to take up memory space, the server is dedicated or combined use, To determine the size of this numeric adjustment.

If the InnoDB engine is the main host, it is recommended that Innodb_buffer_pool_size set the 40%~60% memory space.

If MSYQL is a dedicated host with a memory space of more than 32G, innodb_buffer_pool_size generally recommends setting the memory space to 70--80%. This value is useful for MySQL performance improvements. But cannot be set too large, such as the remaining memory space is not enough for the system to function properly, may cause more problems, so this value should be set appropriately.

If the dataset itself is small, a reasonable innodb_buffer_pool_size value can be set based on the amplitude of the data and the planned online duration, such as a slightly larger than the estimated target value.

Innodb_buffer_pool_size can be dynamically adjusted after version 5.7, however, it is recommended to write innodb_buffer_pool_size to the configuration file, and it is recommended not to adjust the value when the system is busy

Modify the configuration as follows

[[Email protected] ~] #vim/etc/my.cnf.d/server.cnf

[Server]

Innodb_buffer_pool_size = 512M

And then restart the MySQL service after it takes effect

The number of segments (instances) of the innodb_buffer_pool_instances:buffer_pool, which means that the size of the memory space is cut into several spatial fields, the locked units will be smaller, can improve the efficiency of MySQL operation, but the number of recommendations not too much

Innodb_log_files_in_group: Number of log files in a group, at least two

Innodb_log_file_size: Log file size, not too large, the default is 5M, according to the actual situation to adjust. It is recommended to increase the size, generally can be set to 50--100m

Innodb_flush_logs_at_trx_commit: Transaction Commit brush Write disk setting parameter, value 0 1 2

0:log_buffer (in memory) 1 synchronizations to Log_file per second, simultaneous log file to data file synchronization, 0 loss of up to 1s transactions

1: Log buffer is synchronized to log file for each commit, simultaneous log file to data file, 1 loss of 1 transactions

2: Log buffer is synchronized to log file on each commit, but does not synchronize the log file to data file at the same time, and the MySQL thread decides when to refresh the data. 2 performance is the highest, if transaction security is not very large, it is recommended to set to 2

Recommendation: Turn off autocommit, and then set this value Innodb_flush_logs_at_trx_commit to 1 or 2;

Many of the advanced features of INNODB_FILE_PER_TABLE:INNODB depend on this parameter, and it is recommended to turn on

Innodb_file_io_threads: The number of IO threads that the file reads and writes, and if the CPU core is large enough, it is recommended to scale up by 4. This value is to be adjusted according to the amount of concurrency and CPU cores

Innodb_open_files:innodb the maximum number of files that can be opened, adjust this value as needed

Innodb_flush_method:innodb How to brush and write disks

INNODB_THREAD_CONCURRENCY:INNODB number of threads concurrently, that is, the number of threads that can run concurrently at the kernel level, typically the number of CPU cores

Skip_name_resolve: Ignore host name resolution, network optimization related, prohibit calling external DNS for name resolution

Max_connections: Maximum number of concurrent connections

MySQL optimization problem for database