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

The most common mysql optimization is my.cnf file configuration optimization.

Restrict threads, access, etc.

Max_connections = 151,

The limit of each version is different, the maximum number of connections is limited, and there are many concurrent visits, which can be increased appropriately.

Back_log = numeric

If the concurrency is large, many commands cannot be solved in time. Using this parameter, you can queue up subsequent commands and store them in the stack.

Show full processlist can use this command to see how many commands are waiting. If many of them need to increase the value of back or connections,

Wait_timeout = 60 is the number of seconds of non-interactive waiting, saving resources

Interactive_time = 200Interactive links, automatically disconnect if you don't operate for a long time to make way for other resources

Key_buffer_size = 256m

Index buffer size, caches indexes, saves disk reads, saves IO resources, but takes up memory space

Show status like 'key_read%'; looks at the index request, how much is in the buffer and how much is not.

Query_cache_size = 256m

SQL query statement buffering requires two statements exactly the same to match

Show status like 'Qcache%' to view various query_cache_size settings information

Qcache_free_blocks, if the value is too large, there are too many fragments that need to be sorted out.

Amount of remaining memory in Qcache_free_memory

How many times does Qcache_hits hit the cache

How many times did Qcache_inserts miss?

Qcache_lowmem_prunes statements that are not recorded because of insufficient memory

Show variables like 'query_cache%'

Queries exceeding query_cache_ limit are not cached, depending on the situation.

The size of the query_cache_ min_res_unit block increases speed and saves space.

Query_cache_ size query cache size

Query_cache_ type = 0Action1Cash 2 cache type, 0 equals to turn off do not cache 1 will cache all results 2 specify the query that needs to be cached through sql_cache

Max_connect_errors = 10

A limit on the number of password errors can prevent violent cracking. if you type the wrong number of times, you will never respond again unless you restart mysql or flush hosts empty.

Sort_buffer_size = 2m

Mysql deals with problems through threads. The main thread is assigned to a worker thread, and each thread will occupy certain memory resources. If this value is too high, if you happen to encounter high concurrency, it will run out of memory, depending on the situation.

Max_allowed_packet = 32m

Mysql limits the size of accepted packets based on this. If it is large, it fails.

Join_buffer_size=2M

The table association cache size, as with sort_buffer_size = 2 M, is allocated for each thread link.

Thread_cache_size = 300

The cached worker thread, when something happens, the main thread extracts the allocation directly from the cache thread, and if it is automatically generated, it will be automatically destroyed at the end.

Innodb configuration

Innodb_buffer_pool_size = 2048m

Similar to key_buffer_size lnnodb uses this to determine the size of buffered data and indexes

Innodb_flush_log_at_trx_commit = 0Action1Comp2

0 writes data from logbuffer to flush every second

1 write the data in log buffer to flush every time a transaction is submitted

2 complete the flush operation every second every time

This has a great impact on speed. If the mysql address is allowed to break down some things, it can be set to 0. 2.

Innodb_thread_concurrency = 0

Set the concurrency of innodb. 0 does not limit the number of general CPU cores to 2 times, usually 8.

Innodb_log_buffer_size = 32m

Cache log size, things are very large, need to increase the cache

Innodb_log_file_size = 50m

Determine the size of the data log file

Innodb_log_files_in_group = 3

Mysql can write log files to multiple files. The recommended value is 3.

Read_buffer_size = 1m

Allocating memory separately for each link increases performance but consumes memory

Read_rnd_buffer_size = 16m

If you need to sort, a random read buffer will be allocated to sort the query to avoid disk search occupying IO to improve the query speed. If the data is large, it can be improved appropriately.

Bulk_insert_buffer_size = 64m

Bulk insert cache size can effectively improve efficiency. Default is 8m.

Binary log

Log-bin=/*mysql-bin

Binlog_cache_size

Store binary log file cache

Max_binlog_cache_size = cache memory size used by 8m

Max_binlog_size = 512m log file size, if reached, a new binlog log will be created

Expire_logs_days = 7 periodically clean up expired logs

Mysqladmin flush-logs regenerates the new log file.

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.