MySQL configuration file in Linux system detailed

In the daily development process of access to SQL Server and MySQL database operational issues, it may be that the previous contact is SQL Servers before beginning to contact MySQL, always feel that using MySQL is not using SQL Server so handy, some key system functions, For example, a series of problems, such as window opening, row and column, column changing, self-increment field, and so on, although finally found the alternative, but the solution to the performance problem is quite a bit of effort. Compared to SQL Server, MySQL in the Windows environment, MySQL in the Linux environment performance, the same stored procedure, in the stored procedure set up eight temporary tables, and loop to each temporary table inserted 100,000 data, the field about 12 or so, Finally, the data was queried for 6 seconds under SQL Server, 3 seconds for MySQL under Linux, and more than 60 seconds for MySQL in the Windows environment to 1000 data. Overall performance difference is still very large, the following to talk about the Linux MySQL configuration file.

Set the length of the slow query time and the location where the slow query log is stored:

Long_query_time=1
Slow_query_log_file =/var/log/mysql/mysql-slow.log

Avoid MySQL external locking, reduce the chance of error, enhance stability:
Skip-external-locking

Disable MySQL for DNS resolution of external connections and use this option to eliminate the time for NDS parsing by MySQL. But # #需要注意的是: If this option is turned on, all remote host connection authorizations will use IP address, otherwise MySQL will not be able to handle connection requests properly.
Skip-name-resolve

the value of the Back_log parameter indicates how many requests in a short time can be saved before MySQL temporarily stops responding to a new request, and in the stack, if the system has a lot of connections in a short period of time, you need to > increase the value of the parameter, specifying the arrival tcp/ The size of the listening queue for the IP connection. Different operating systems have their own limits on the size of this queue, and setting the Back_log above the operating system limit will be invalid, with a default value of 50, and for Linux systems, the recommended setting is an integer less than 512:

back_log=1200 binlog_cache_size=64m

index buffer size, increase it for better index processing performance, for the presence of around 4GB server, this parameter can be set to 256M or 384M. If this parameter value is set too large > The overall efficiency of the server is reduced:

key_buffer_size=512m

Set the maximum value of the message transmission in the network transmission, the system default value is 1MB, the maximum value is 1GB, must be set to a multiple of 1024, in bytes:

max_allowed_packet=64m

Set the stack size for each thread of MySQL, the default value is large enough to satisfy normal operation. Can be set to a range of 128KB to 4GB, the default 192K:

thread_stack=64m sort_buffer_size=64m

Max_connect_errors = 6000 Open_files_limit = 65535

table_open_cache=256 max_heap_table_size=16m

sets the maximum number of connection pool threads that can be cached in the thread cache pool, which can be set to 0-16384 and default to 0. 1GB memory we configured for 8,2GB memory we configured for 16,4GB or 4GB above the intrinsic we configured to:

thread_cache_size=128

Specifies the size of the MySQL query buffer, which can be observed in the MySQL console if the value of the qcache_lowmem_prunes is very large, indicating that there is often insufficient buffering, and that if the value of qcache_hits is very large, the query buffer is used very frequently:

query_cache_size=64m

query_cache_limit=64m

sets the intrinsic temporary table maximum value, and if this value is exceeded, the temporary table is written to disk with a range of 1KB to 4GB:

tmp_table_size=64m

Specify the maximum number of connection processes allowed by MySQL, and if you frequently see too many connections error prompts when accessing programs, you need to increase this parameter value:

max_connections=1500

max_user_connections=1500

Specifies the maximum connection time for a request, which can be set to 5-10 for the server within about 4GB:

wait_timeout=100

This parameter value is the number of server logical CPUs, for example, the server has two physical CPUs, each physical CPU supports HT Hyper-threading, so the actual value of 4*2=8, which is the current dual quad core server configuration:

Join_buffer_size = 64M

turn on this option to completely turn off MySQL TCP/IP connection, if the Web server is a remote connection to the MySQL database server, do not turn on this option, otherwise > will not connect properly. Skip-networking complaining that InnoDB is 100 times times slower than MyISAM? Then you probably forgot to adjust the value. The default value of 1 means that every single transaction commit or out-of-transaction instruction requires the log to be written to (flush) the hard disk, which is time consuming. Especially when using the battery-powered cache (Battery backed up cache). Set to 2 for many applications, especially from the MyISAM table is possible, it means to write to the system cache instead of writing to the hard disk. The log will still flush to the hard drive every second, so you will generally not lose more than 1-2 seconds of updates. Set to 0 will be a bit faster, but the overall aspect of Ann > is poor, even if MySQL hangs may also lose the transaction data. A value of 2 will only lose data if the entire operating system is hung:

innodb_flush_log_at_trx_commit=2

This is the buffer used by the transaction log of the InnoDB storage engine. Similar to Binlog Buffer,innodb when writing the transaction log, in order to improve performance, the information is written > into the INNOFB log Buffer, when the Innodb_flush_log_trx_commit The log is written to the text > (or to the disk) after the corresponding condition (or the log buffer is full) is set by the parameter. You can set the maximum memory space that can be used by the Innodb_log_buffer_size parameter:

innodb_log_buffer_size=64m

innodb_buffer_pool_size=64m

innodb_log_file_size=1g

Innodb_file_per_table=1

innodb_read_io_threads=10

innodb_write_io_threads=10

Innodb_flush_method=o_direct

innodb_io_capacity=1000

innodb_io_capacity_max=1000

innodb_lru_scan_depth=500

Innodb_thread_concurrency=0

innodb_autoinc_lock_mode=2

Innodb_log_files_in_group=3

Innodb_max_dirty_pages_pct=90

innodb_lock_wait_timeout=100

Bulk_insert_buffer_size = 16M

Innodb_thread_concurrency = 8

Innodb_purge_threads = 1

The default size of Tmp_table_size is 32M. If a temporary table exceeds that size, MySQL produces an error in the form of the table tbl_name is, if you do a lot #高级 GROUP by query, increase the Tmp_table_size value:

tmp_table_size=512m

Random Read data buffers use memory (read_rnd_buffer_size): In contrast to sequential reads, when MySQL makes a non-sequential read (random read) block of data, it uses the > buffer to hold the data read. If you read the table data based on the index information, join the table based on the sorted result set, and so on. In general, MySQL needs to generate random reads to use the memory buffers set by the Read_rnd_buffer_size parameter when the data blocks are read in a certain order of >:

read_rnd_buffer_size=128m

Instead of using any uppercase letters, you might want to use a combination of lowercase letters and underscores when defining database naming rules:

Lower_case_table_names=1

To set the checksum mode:

Sql_mode=no_engine_substitution,strict_trans_tables

The default configuration does not open the query cache:

Explicit_defaults_for_timestamp

A database that requires logging into the log. If you have multiple databases that can be separated by commas, or use multiple binlog-do-db options:

Binlog-do-db=showeedb

You do not need to log the database into the log. If you have multiple databases that can be separated by commas, or use multiple binlog-do-db options:

Binlog-ignore-db=mysql

The database that needs to be synchronized. If you have multiple databases that can be separated by commas, or use multiple replicate-do-db options:

Replicate-do-db=showeedb

Databases that do not need to be synchronized. If you have multiple databases that can be separated by commas, or use multiple replicate-ignore-db options:

Replicate-ignore-db=mysql,test

Synchronization Parameters:

Server-id=1

Log_bin=/var/log/mysql/mysql-bin

Ensure that the slave hangs on any master and receives the write information of the other master:

Log-slave-updates

Sync_binlog=1

Auto_increment_offset=1

auto_increment_increment=2

Filter out some bugs that don't matter:

Slave-skip-errors=all

MySQL configuration file in Linux system detailed