Shulou(Shulou.com)06/01 Report--

Performance Optimization of mysql (2)

Limitations of query cache

External query sql statements in all subqueries will not be cache (cached)

Query in procedure,function and trigger is not cache

Contains a lot of other functions that may get different results each time you execute query cannot be cache

In the / etc/my.cnf file, add query_cache_size=256M query_cache_type=1 (enabled) under "mysqld" to take effect after restart

6.max_connect_error is a security-related counter value in mysql that blocks too many failed clients to prevent brute force cracking. Modify the number of times max_connect_errors= is added under the configuration file / etc/my.cnf [mysqld]

Restart service takes effect

7.sort_buffer_size allocates a size buffer for each thread, increasing this value to speed up order by or group by operations

Sort_Buffer_Size is a connection-level parameter that allocates the set memory at once when each connection (session) needs to use this buffer for the first time.

Sort_Buffer_Size is not the bigger the better, because it is a connection-level parameter, too large settings + high concurrency may deplete system memory resources. For example, 500 connections will consume 500*sort_buffer_size (2m) = 1GB of memory

For example, setting sort_buffer_size

Modify the / etc/my.cnf file and add the following under [mysqld]

Sort_buffer_size = 2m

After restarting MySQL Server, check that the settings have taken effect.

8.max_allowed_packet = 32m

MySQL limits the packet size accepted by Server based on the configuration file. Sometimes large inserts and updates are limited by the max_allowed_packet parameter, causing writes or updates to fail. The maximum value is 1GB, and a multiple of 1024 must be set.

9.join_buffer_size = 2m

The size of the cache used for inter-table association. Like sort_buffer_size, the allocated memory corresponding to this parameter is exclusive to each connection.

10.thread_cache_size = 300

Server thread cache, this value indicates that the number of threads saved in the cache can be reused. When disconnected, the client thread will be placed in the cache to respond to the next client rather than destroy (provided that the cache number does not reach the limit), and if the thread is re-requested, the request will be read from the cache if the cache is empty or a new request Then the thread will be recreated, and if there are many new threads, increasing this value can improve system performance. By comparing the variables of the Connections and Threads_created states, you can see the effect of this variable.

1GB memory configuration is 8je 2GB, 16je 3GB, 3GB and 32je 4GB and above, but it can be configured larger.

Threads_cached: represents how many threads are free in the thread cache at the moment.

Threads_connected: represents the number of connections currently established, because a connection requires one thread, so it can also be regarded as the number of threads currently in use.

Threads_created: represents the number of threads created since the last time the service was started. If the Threads_created value is found to be too high, it indicates that the MySQL server has been creating threads, which is also resource-consuming. You can increase the thread_cache_ size value in the configuration file appropriately.

Threads_running: represents the number of currently active (non-sleeping) threads. It does not represent the number of threads in use, sometimes the connection is established, but the connection is in the sleep state.

3) configure several variables of InnoDB

11.innodb_buffer_pool_size

For InnoDB tables, innodb_buffer_pool_size is the same as key_buffer_size is for MyISAM tables. InnoDB uses this parameter to specify the size of memory to buffer data and indexes. For individual MySQL servers, you can set this value to up to 80% of physical memory. According to the MySQL manual, the recommended value for machines with 2G memory is 1G (50%). If you don't have a lot of data and don't burst, you don't need to set the innodb_buffer_pool_size too big.

Mysql > show variables like 'innodb_buffer_pool_size'

Set up innodb_buffer_pool_size

Modify the / etc/my.cnf file and add the following under [mysqld]

Innodb_buffer_pool_size = 2048m

After restarting MySQL Server, check that the settings have taken effect.

12.innodb_flush_log_at_trx_commit

It mainly controls the time when innodb writes the data in log buffer to the log file and flush disk, with values of 0, 1 and 2, respectively. 0, which means that when the transaction commits, the log write operation is not done, but the data in log buffer is written to the log file and flush disk once every second; 1, then every second or every transaction commit will cause log file writing and flush disk operation, ensuring that the ACID; of the transaction is set to 2, and each transaction commit causes the action of writing to the log file, but the flush disk operation is completed every second.

The actual test shows that this value has a great influence on the speed of inserting data. It takes only 2 seconds to insert 10000 records when set to 2, 1 second when set to 0, and 229 seconds when set to 1. Therefore, the MySQL manual also recommends that inserts be merged into a single transaction as much as possible, which can be significantly faster.

According to the MySQL manual, you can set this value to 0 or 2 if you allow the risk of losing some of the most recent transactions.

13.innodb_thread_concurrency = 0

This parameter is used to set the number of concurrent innodb threads. The default value is 0, which means there is no limit. If you want to set it, it will be the same as the CPU core of the server or 2 times the number of cpu cores. It is recommended to use the default setting, which is generally 8.

14.innodb_log_buffer_size

This parameter determines the amount of memory used by these log files, in M units. Larger buffers can improve performance, and for larger transactions, you can increase the cache size.

Innodb_log_buffer_size=32M

15. Innodb_log_file_size = 50m

This parameter determines the size of the data log file, in M units, larger settings can improve performance.

16. Innodb_log_files_in_group = 3

To improve performance, MySQL can write log files to multiple files in a circular manner. Recommended setting to 3

17.read_buffer_size = 1m

MySql read buffer size. A request for a sequential scan of the table allocates a read buffer and MySql allocates a memory buffer for it. If sequential scan requests for tables are very frequent, and you think frequent scans are too slow, you can improve its performance by increasing the value of the variable and the memory buffer size. Like sort_buffer_size, the allocated memory corresponding to this parameter is exclusive to each connection.

18.read_rnd_buffer_size = 16m

The random read (query operation) buffer size of the MySql. When rows are read in any order (for example, in sort order), a random read cache is allocated. When sorting a query, MySql scans the buffer first to avoid disk search and improve query speed. If you need to sort a large amount of data, you can increase the value appropriately. However, MySql allocates this buffer space for each customer connection, so you should try to set this value appropriately to avoid excessive memory overhead.

Note: sequential reading means that the required row data can be read sequentially according to the leaf node data of the index. Random reading means that it is generally necessary to find the actual row data according to the primary key in the secondary index leaf node, but the data segment of the secondary index and the primary key is different, so the access mode is random.

19.bulk_insert_buffer_size = 64m

Bulk insert data cache size, which can effectively improve insertion efficiency. The default is 8m.

20.binary log

Log-bin=/usr/local/mysql/data/mysql-bin

Binlog_cache_size = 2m / / the memory allocated for each session is used to store the cache of binary logs during the transaction, improving the efficiency of recording bin-log. There are no big transactions, and dml can be set a little smaller if the transaction is not very frequent, and if the transaction is large and many, and the dml operations are also frequent, you can scale it up appropriately. The former suggestion is-1m, while the latter suggestion is: 2Murray 4m

Max_binlog_cache_size = 8m / / indicates the maximum amount of cache memory that binlog can use

Max_binlog_size = 512m / / specifies the size of the binlog log file, and if the current log size reaches max_binlog_size, a new binary log is automatically created. You cannot set this variable to be greater than 1GB or less than 4096 bytes. The default value is 1GB. When importing large-capacity sql files, it is recommended to close sql_log_bin, otherwise the hard drive can not be carried, and it is recommended to delete it regularly.

Expire_logs_days = 7 / / defines when mysql clears the expired log.

The number of days that the binary log is automatically deleted. The default value is 0, which means "no automatic deletion".

Mysqladmin flush-logs can also start a new binary log.

Execute the mysqlslap tool for testing before optimization

[root@localhost] # mysqlslap-defaults-file=/etc/my.cnf-- concurrency=10-- iterations=1-- create-schema='test1'-- query='select * from test1.tb1'-- engine=innodb-- number-of-queries=2000-uroot-p123456-verbose

Display the results:

Benchmark

Running for engine innodb

Average number of seconds to run all queries: 13.837 seconds

Minimum number of seconds to run all queries: 13.837 seconds

Maximum number of seconds to run all queries: 13.837 seconds

Number of clients running queries: 10

Average number of queries per client: 200

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.