Mysql optimization variable

 

[Mysqld]

Port = 3306

Server-id = 1

Socket =/tmp/mysql. sock

 

# Avoid external locks of MySQL to reduce the chance of errors and enhance stability.

Skip-locking

 

# Prohibit MySQL from performing DNS resolution on external connections

Skip-name-resolve

 

# Specify the number of possible MySQL connections

Back_log = 256

 

# Use Query Buffer

Query_cache_size = 32 M

Query_cache_type = 1

 

# Specify the buffer size used for indexing. For servers with around 4 GB of memory, this parameter can be set to 256M or 384Mkey_reads/key_read_requests, which must be at least and.

Key_buffer_size = 256 M

 

# The size of the allowed request package when a query is being executed, a copy of the current query statement also needs to allocate memory for it.

Max_allowed_packet = 4 M

 

# The cache size of a specified table is recommended for machines with 1 GB of memory. The value range is 128-256.

Table_cache = 256 K

 

# The buffer size that can be used for sorting is exclusive to each connection

Sort_buffer_size = 6 M

 

# The buffer size available for read query operations is exclusive to each connection

Read_buffer_size = 4 M

 

# The buffer size used by the Joint query operation is exclusive to each connection

Join_buffer_size = 8 M

 

# Cache required for re-sorting when the MyISAM table changes

Myisam_sort_buffer_size = 64 M

 

# Specify the size of the MySQL Query Buffer

Query_cache_size = 64 M

 

Tmp_table_size = 256 M

 

# Specify the maximum number of connection processes allowed by MySQL

Max_connections = 768

 

Max_connect_errorrs = 10000000

 

# Specify the maximum connection time of a request

Wait_timeout = 10

Interactive_timeout = 10

 

# Set this parameter to the number of server logical CPUs × 2

Thread_concurrency = 8

 

# Number of cache reusable threads

Thread_caching = 256

Thread_cache_size = 64

 

# Stack size of each thread

Thread_stack = 256 K

 

# Record slow queries, and then optimize slow queries one by one

Log-slow-queries = slow. log

Long_query_time = 2

Log-queries-not-using-indexes

 

# Disable unnecessary table types. Do not add this type if necessary.

Skip-innodb

Skip-bdb

 

# Set the default table to InnoDB.

Default-table-type = innodb

 

# Innodb main directory

Innodb_data_home_dir =/mysql_data

 

# Control the buffer allocated to sort Innodb internal data dictionaries.

Innodb_additional_mem_pool_size = 50 M

 

# Innodb specifies the data file name and size

Innodb_data_file_path = ibdata1: 20G; ibdata2: 200 M: autoextend

 

# Set the buffer pool size to 50-80% of your primary memory size

Innodb_buffer_pool_size = 70 M

 

# Set the log file size to about 25% of the buffer pool size

Innodb_log_file_size = 20 M

 

# InnoDB buffer size before writing logs to log Disk Files

Innodb_log_buffer_size = 4 M

 

# It means that the log has been written to the disk before the transaction is committed, the transaction can run longer, and the repair capability after the service crash

Innodb_flush_log_at_trx_commit = 1