LAMP 3.0 MySQL Configuration explained

After MySQL was installed, we copied a template configuration file from the Support-files of the installation package, and the default
MySQL configuration file is under/ETC/MY.CNF, in fact, this path or file name we can be modified, in the Kai
Modified in the script. Below are some common configurations.

MySQL configuration file in

Vim/etc/my.cnf

The core configuration is mysqld

3306 specify the port on which MsSQL listens

Socket          =/tmp/mysql.sock Specifies a socket file for local communication between the MySQL client and the server (the default is /varunder Linux/lib/mysql/mysql.sock File

skip-Locking Skip Lock

Key_buffer_size = 256M The buffer size for the index block, increasing the index (for all read and multiple writes) that it can get better processing. The index is shared by all threads, and the size of the key_buffer depends on the size of the memory. 

Max_allowed_packet = 1M packet size accepted; It is safe to increase the value of this variable because additional memory is allocated only when needed. For example, MYSQLD will allocate more memory only if you issue a long query or if MySQLd must return a large result row. This variable takes a smaller default value as a precaution to capture the error message packets between the client and the server and to ensure that memory overflows are not caused by accidental use of large packets. 

*n (N is the number of file descriptors required for each connection); When the Table_open_cache is set to very large, If the system does not handle so many file descriptors, then the client fails and the connection is not possible.

Sort_buffer_size = 1MMySQL The buffer size used to perform the sort. Single thread ordering consumes 1 m of memory if you want to increase the speed of the ORDER by, first see if you can let MySQL use the index instead of the extra sort stage. If not, you can try increasing the size of the sort_buffer_size variable

Read_buffer_size = the size of the buffer that can be used by the 1M read query operation. As with Sort_buffer_size, the allocated memory for this parameter is exclusive to each connection. A request for sequential scanning of a table allocates a read-in buffer, which is allocated by MySQL to a 137-segment memory buffer. If the sequential scan requests for a table are frequent, and you think that frequent scans are going too slowly, you can improve their performance by increasing the value of the variable and the size of the memory buffer.

read_rnd_buffer_size = 4M Random read buffer size. When rows are read in any order (for example, in sort order), a random read buffer is allocated. When you sort a query, MySQL scans the buffer first to avoid disk searches, improve query speed, and, if you need to sort large amounts of data, raise the value appropriately. However, MySQL will issue this buffer space for each client connection, so you should set this value as appropriate to avoid excessive memory overhead

Myisam_sort_buffer_size = buffer required to reorder when the 64MMyISAM table changes

88

query_cache_size= 16M Specifies the size of the MySQL query result buffer

8 Maximum number of concurrent threads, value of server logical CPU x 2

Add to

88

Indicates the idle connection timeout, the default is 28800s, this two parameter is used together, that is, for wait_timeout to take effect, you must set Interactive_timeouT at the same time

1=/data/mysql/slow.log

Slow query more than 1 seconds build log

Path of the slow query log

LAMP 3.0 MySQL Configuration explained