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

This article is mainly about optimizing the solution of mysql, if you are interested, let's take a look at this article. I believe it is of some reference value to you after reading the solution of optimizing mysql.

# mysql Optimization solution

# default values of common parameters:

Max_connections = 151,

# colleagues deal with multiple Dalian connections. It is recommended that the maximum number of connections is about 80% of the upper limit.

Sort_buffer_size = 2m

# buffer size when sorting queries, which only works on order by and group by, and can be increased to 16m.

Open_files_limit = 1024

# Open the file count limit, if the value viewed by show global status like 'open_files' is equal to or greater than the open_files_limit value

# programs will not be able to connect to the database or get stuck

Default value of MyISAM parameter:

Key_buffer_size = 16m

# Index cache size, generally set to 30-40% of physical memory

Read_buffer_size = 128k

# cache size for read operations. It is recommended to set 16m or 32m

Query_cache_type = ON

# enable query caching

Query_cache_limit = 1m

# query cache limit. Only query results below 1m will be cached to avoid overwriting the cache pool due to large result data.

Query_cache_size = 16m

# View the cache size, which is used to cache SELECT query results. Next time, the same SELECT query will return the result directly from the cache pool. This value can be multiplied appropriately.

Default value of InnoDB parameter:

Innodb_buffer_pool_size = 128m

# Index and data buffer size, which is generally set to 60% and 70% of physical memory.

Innodb_buffer_pool_instances = 1

# number of buffer pool instances. It is recommended to set 4 or 8 instances

Innodb_flush_log_at_trx_commit = 1

# key parameter. 0 means that the log is written to the log and synchronized to disk every second. If the database fails, transaction data will be lost for about 1 second. 1 for each SQL executed, it is written to the log and synchronized to disk, which costs a lot of money, and it is inefficient to wait for the log to read and write after executing the SQL. 2 means that the log is only written to the system cache and then synchronized to disk every second, which is very efficient. If the CVM fails, the transaction data will be lost. The recommended setting for data security is not very high 2, the performance is high, and the effect after modification is obvious.

Innodb_file_per_table = OFF

# shared tablespaces are the default, and the number of idbdata files in shared tablespaces is increasing, which affects the performance of iUnix. It is recommended to enable independent tablespace mode. The indexes and data of each table exist in its own independent tablespace, so that a single table can be moved in different databases.

Innodb_log_buffer_size = 8m

# Log buffer size. Since the log is refreshed at most once a second, it is generally not required to exceed 16m.

# system kernel optimization

Net.ipv4.tcp_fin_timeout = 30

# TIME_WAIT timeout. Default is 60s.

Net.ipv4.tcp_tw_reuse = 1

# 1 means multiplexing is enabled, TIME_WAIT socket is allowed to be reused for new TCP connections, and 0 means closed

Net.ipv4.tcp_tw_recycle = 1

# 1: enable TIME_WAIT socket fast recycling; 0: disable

Net.ipv4.tcp_max_tw_buckets = 4096

# the system maintains the maximum number of TIME_WAIT socket. If this number is exceeded, some TIME_WAIT will be randomly cleared and warnings will be printed.

Net.ipv4.tcp_max_syn_backlog = 4096

# enter the maximum length of the SYN queue, and increase the queue length to accommodate more waiting connections

# in linux systems, if the number of file handles opened by a process exceeds the system default value of 1024, it will prompt the "too many files open" message, so adjust the limit of open file handles.

# vi / etc/security/limits.conf # add the following configuration, * for all users, you can also specify users. Restart the system takes effect.

* soft nofile 65535

* hard nofile 65535

# ulimit-SHn 65535 # effective immediately

Are the details of the above solutions for optimizing mysql helpful to you? If you want to know more about it, you can continue to follow our industry information section.

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.