MySQL's my. cnf Parameters

Go to the front: some time ago, the server was set up, and database optimization was actually very important. I searched an article and put it here for backup. Original article address: blogold.chinaunix.netu11263showart_1011942.html adjust MySQL operation parameters, modify the etcmy. cnf file, adjust mysql operation parameters, and restart MySQL. After MySQL 4, it will take effect in part

Go to the front: some time ago, the server was set up, and database optimization was actually very important. I searched an article and put it here for backup. Original address: http://blogold.chinaunix.net/u/11263/showart_1011942.html to adjust the MySQL running parameters, modify the/etc/my. cnf file to adjust the mysql running parameters after restarting MySQL take effect, after MySQL 4 version, part of

Go to the front: some time ago, the server was set up, and database optimization was actually very important. I searched an article and put it here for backup.
Address: http://blogold.chinaunix.net/u/11263/showart_1011942.html

Modify MySQL running parameters and/etc/my. the cnf file adjusts the mysql running parameters and takes effect after MySQL is restarted. After MySQL 4, some internal variables can be set during MySQL running, but the restart of mysql becomes invalid.
Mysqld Program-directory and file
[Text]
Basedir = path # Use the given directory as the root directory (installation directory ).
Datadir = path # Read database files from a given directory.
Pid-file = filename # specify a file for the mysqld program to store the process ID (only applicable to UNIX/Linux systems );

[Mysqld]
Socket =/tmp/mysql. sock # specifies a socket file for local communication between the MySQL client program and the server (/var/lib/mysql. sock file by default in Linux)
Port = 3306 # specify the port on which MsSQL listens
Key_buffer = 384 M # key_buffer is the buffer size used for index blocks. You can add indexes that can be better processed (for all reads and multiple writes ).
The index block is buffered and shared by all threads. The size of key_buffer depends on the memory size.
Table_cache = 512 # number of tables opened for all threads. Increase this value to increase the number of file descriptors required by mysqld. Avoid overhead caused by frequent data table opening.
Sort_buffer_size = 2 M # Each thread that needs to be sorted allocates a buffer of this size. Add this value to accelerate the order by or group by operation.
Note: The allocated memory corresponding to this parameter is exclusive to each connection! If there are 100 connections, the total size of the actually allocated sort buffer is 100 × 6 = 600 MB.
Read_buffer_size = 2 M # buffer size available for read query operations. Like sort_buffer_size, the allocated memory corresponding to this parameter is also exclusive to each connection.
Query_cache_size = 32 M # specify the buffer size of MySQL query results
Read_rnd_buffer_size = 8 M # this parameter is used for random read after the row pointer is used for sorting.
Myisam_sort_buffer_size = 64 M # buffer required for sorting the MyISAM table again when it changes
Thread_concurrency = 8 # maximum number of concurrent threads. The value is the number of logical CPUs on the server × 2. If the CPU supports H.T hyper-threading, then × 2
Thread_cache = 8 # Number of reusable cache threads
Skip-locking # Avoid MySQL external locks, reduce the chance of errors, and enhance stability.
[Mysqldump]
Max_allowed_packet = 16 M # maximum possible information packages that can be sent between the server and the client

[Myisamchk]
Key_buffer = 256 M
Sort_buffer = 256 M
Read_buffer = 2 M
Write_buffer = 2 M
[/Text]

Other optional parameters:
Back_log = 384
Specify the number of possible MySQL connections.
When the MySQL main thread receives many connection requests in a short time, this parameter takes effect. The main thread takes a short time to check the connection and start a new thread.
The value of the back_log parameter indicates how many requests can be stored in the stack within a short time before MySQL temporarily stops responding to a new request.
If the system has many connections in a short period of time, you need to increase the value of this parameter, which specifies the size of the listener queue for the incoming TCP/IP connection.
Trying to set back_log to be higher than your operating system limit will be invalid. The default value is 50. We recommend that you set the value to an integer smaller than 512 in Linux.
Max_connections = n
The maximum number of database connections that the MySQL server processes simultaneously (100 by default ). When the limit is exceeded, the Too worker connections error is reported.
Key_buffer_size = n
Used to store the RMA value of the index block (the default value is 8 Mb). You can add an index that can be better processed (for all reads and multiple writes)
Record_buffer:
Each thread that performs an ordered scan allocates a buffer of this size to each table it scans.
If you perform many sequential scans, you may want to increase the value. The default value is 131072 (128 K)
Wait_timeout:
The number of seconds that the server waits for action on a connection before closing it.
Interactive_timeout:
The number of seconds that the server waits for action on an interactive connection before closing it.
An interactive customer is defined as a customer who uses the CLIENT_INTERACTIVE option for mysql_real_connect.
The default value is 28800. You can change it to 3600.
Skip-name-resolve
Prohibit MySQL from performing DNS resolution on external connections. This option can eliminate the time for MySQL to perform DNS resolution.
However, if this option is enabled, IP addresses are required for all remote host connection authorizations. Otherwise, MySQL cannot process connection requests normally!
Log-slow-queries = slow. log
Record slow queries, and then optimize slow queries one by one
Skip-innodb
Skip-bdb
Disable unnecessary table types. Do not add this type if necessary.

#> Show variables like '% query_cache % ';
#> Show status like 'qcache % ';
If the Qcache_lowmem_prunes value is very large, it indicates that there is often insufficient buffer;
If the Qcache_hits value is very large, it indicates that the query buffer is used very frequently. If the value is small, it will affect the efficiency, so you can consider not to use the Query Buffer;
If the value of Qcache_free_blocks is very large, it indicates that there are many fragments in the buffer.

######################################## ##
###### Max_allowed_packet ######
######################################## ##
The communication information package is a single SQL statement sent to the MySQL server or a single row sent to the client.
The maximum possible information package that can be sent between the MySQL 5.1 server and the client is 1 GB.
When the MySQL client or mysqld server receives an information packet greater than the value of max_allowed_packet, the "information packet is too large" error is sent and the connection is closed. For some clients, if the communication information package is too large, the "lost connection to MySQL Server" error can be returned during query.
Both the client and server have their own max_allowed_packet variable. Therefore, if you want to process a large information package, you must add the variable on the client and server.
If you are using a mysql client, the default value of the max_allowed_packet variable is 16 MB. To set a large value, you can start mysql in the following ways:
Mysql> mysql-max_allowed_packet = 32 M
It sets the size of the information package to 32 MB.
The default max_allowed_packet value of the server is 1 MB. If the server needs to process a large query, you can add this value (for example, if you want to process a large BLOB column ). For example, to set this parameter to 16 MB, start the server in the following ways:
Mysql> mysqld-max_allowed_packet = 16 M

You can also use the option file to set max_allowed_packet. To set this variable of the server to 16 MB, add the following content to the option file:
[Mysqld]
Max_allowed_packet = 16 M
It is safe to increase the value of this variable because additional memory is allocated only when necessary. For example, if you issue a long query or mysqld must return a large result, mysqld will allocate more memory. The small default value of this variable is a preventive measure to capture error packets between the client and the server, and ensure that memory overflow is not caused by accidental use of large information packets.
If you use a large BLOB value and do not grant mysqld the permission to access enough memory for query processing, you may also encounter a strange problem related to the big information package. If this is suspected, add ulimit-d 256000 in the mysqld_safe script and restart mysqld.
######################################## ##
##### How to enable and disable a database table in MySQL #####
######################################## ##
Table_cache, max_connections, and max_tmp_tables affect the maximum number of files that the server keeps open. If you add one or two of these values, you can encounter a strong limit on the number of file descriptors opened by each process in your operating system. However, you can add this restriction to many systems. Ask your OS document to find out how to do this, because the methods for changing the limits vary greatly from system to system.
Table_cache is related to max_connections. For example, for 200 open connections, you should set a table buffer to at least 200 * n. Here n is the maximum number of tables in a join.
To open the table cache, you can add it to the maximum value of a table_cache (64 by default; this can be changed using the-O table_cache = # option of mysqld ). A table is never closed unless the cache is full and another thread tries to open a table or if you use mysqladmin refresh or mysqladmin flush-tables.
When the table cache is full, the server uses the following process to find a cache entry:
Tables that are not currently in use are released in the least recently used (LRU) Order.
If the cache is full and no table can be released, but a new table needs to be opened, the cache must be expanded temporarily.
If the cache is in a temporary extended state and a table changes from in use to out of use, it is disabled and released from the cache.
Open a table for each concurrent access. This means that if you allow two threads to access the same table or access the table twice in the same query (AS), the table needs to be opened twice. The first open of any table occupies two file descriptors; each additional use of the table occupies only one file descriptor. The extra descriptor opened for the first time is used to index the file; this descriptor is shared among all threads

Statement:In this paper, the MySQL my. cnf parameters are described in detail "using BY-NC-SA protocol for authorization.

For more information, see link:Http://blog.ueder.info/71.html