Adjust MySQL running parameters, modify the /etc/my.cnf file Adjust MySQL running parameters to take effect after restarting MySQL, after MySQL 4 version, some internal variables can be set at MySQL runtime, but it will be invalid when MySQL is restarted.
mysqld program - directories and files
basedir = path # Use the given directory as the root directory (installation directory).
datadir = path # Read the database file from a given directory.
pid-file = filename # Specify a file for the mysqld program to store the process ID (only available for UNIX/Linux systems);
[mysqld]
socket = /tmp/mysql.sock # Specifies a socket file for local communication between the MySQL client program and the server (the default is /var/lib/mysql/mysql.sock file on Linux)
port=3306 # Specifies the port that MsSQL listens for
key_buffer = 384M # key_buffer is the size of the buffer used for indexing blocks, increasing the index it handles better (for all reads and multiwrites).
The index block is buffered and shared by all threads, and the size of the key_buffer depends on the size of the memory.
table_cache = 512 # The number of tables open for all threads. Increasing this value increases the number of file descriptors required by mysqld. It avoids the overhead of frequently opening data tables
sort_buffer_size = 2M # Allocate a buffer of that size for each thread that needs to be sorted. Increasing this value accelerates ORDER BY or GROUP BY operations.
Note: The allocated memory corresponding to this parameter is exclusive to each connection! If there are 100 connections, then the total sort buffer size actually allocated is 100×6 = 600MB
read_buffer_size = 2M # The size of the buffer that can be used for the read query operation. As with sort_buffer_size, the allocated memory corresponding to this parameter is also exclusive to each connection.
query_cache_size = 32M # Specifies the size of the MySQL query result buffer
read_rnd_buffer_size = 8M # Change the parameter to be read randomly after sorting using the row pointer.
myisam_sort_buffer_size =64M # Buffer required to reorder MyISAM tables when they change
thread_concurrency = 8 # The maximum number of concurrent threads is set as the number of server's logical CPUs ×2, and if the CPU supports H.T hyperthreading, × 2
thread_cache = 8 # #缓存可重用的线程数
skip-locking # avoids external locking of MySQL, reduces the chance of errors, and enhances stability.
[mysqldump]
max_allowed_packet =16M # The maximum possible packet of information that can be sent between the server and the client
[myisamchk]
key_buffer = 256M
sort_buffer = 256M
read_buffer = 2M
write_buffer = 2M
Other optional parameters:
back_log = 384
Specifies the number of possible connections for MySQL.
When the MySQL main thread receives a very large number of connection requests in a very short period of time, this parameter takes effect, and the main thread takes a short time to check the connection and start a new thread.
The value of back_log parameter indicates how many requests can be stored in the stack for a short period of time before MySQL temporarily stops responding to new requests.
If the system has many connections in a short period of time, it is necessary to increase the value of this parameter, which specifies the size of the listening queue for incoming TCP/IP connections.
Trying to set a limit that back_log higher than your operating system will be ineffective. The default value is 50. For Linux systems, it is recommended to set an integer less than 512.
max_connections = n
The maximum number of database connections that the MySQL server can handle at the same time (the default setting is 100). If the limit is exceeded, the Too many connections error will be reported
key_buffer_size = n
RMA value for index blocks (default setting is 8M), increasing the index for better handling (for all reads and multiwrites)
record_buffer：
Each thread that performs a sequential scan allocates a buffer of this size to each table it scans.
If you do a lot of sequential scans, you may want to increase that value. The default value is 131072 (128K)
wait_timeout：
The number of seconds the server waits for action on a connection before shutting it down.
interactive_timeout：
The number of seconds the server waits for action on an interactive connection before shutting it down.
An interacting 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
Disable MySQL from DNS resolution on external connections, using this option eliminates the time it takes for MySQL to resolve DNS.
However, it should be noted that if this option is enabled, all remote host connection authorization must use the IP address method, otherwise MySQL will not be able to process the connection request normally!
log-slow-queries = slow.log
Record slow queries and optimize them one by one
skip-innodb
skip-bdb
Close unwanted table types and don't add this if you need to
# > 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 buffering;
If the value of the Qcache_hits is very large, it indicates that the query buffer is used very frequently, and if the value is small but will affect the efficiency, then you can consider not using query buffering.
If the Qcache_free_blocks value is very large, it indicates that there is a lot of debris in the buffer.
##########################################
###### max_allowed_packet ######
##########################################
A communication packet is a single SQL statement sent to the MySQL server or a single line to the client.
The maximum possible packet that can be sent between the MySQL 5.1 server and the client is 1 GB.
When a MySQL client or mysqld server receives a packet larger than max_allowed_packet bytes, a "packet is too large" error is issued and the connection is closed. For some clients, if the communication packet is too large, you may encounter the error "Loss of connection to MySQL server" during the query.
Both the client and server have their own max_allowed_packet variables, so if you plan to handle large packets, you must add that variable on both the client and the server.
If you are using a mysql client program, its max_allowed_packet variable defaults to 16MB. To set a larger value, you can start mysql in the following way:
mysql> mysql --max_allowed_packet=32M
It sets the size of the information package to 32MB.
The default max_allowed_packet value for the server is 1MB. If the server needs to process large queries, you can increase this value (for example, if you are preparing to process large blob columns). For example, to set it to 16MB, you can start the server as follows:
mysql> mysqld --max_allowed_packet=16M
You can also use options files to set max_allowed_packet. To set this variable for the server to 16MB, add downlink content to the options file:
[mysqld]
max_allowed_packet=16M
It's 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 mysqld has to return large result lines. The small default value of this variable is a precautionary measure to catch error packets between the client and server and to ensure that memory overflows are not caused by accidental use of large packets.
If you are using large blob values and are not granting mysqld permission to access enough memory to process queries, you will also run into weird problems related to large packets. If you suspect this is the case, try starting to increase ulimit -d 256000 in mysqld_safe script and restart mysqld.
##########################################
##### How to open and close database tables in MySQL #####
##########################################
table_cache, max_connections, and max_tmp_tables affect the maximum number of files that the server keeps open. If you increase one or both of these values, you can run into the limit imposed by your operating system on the number of file descriptors that each process opens. However, you can increase the limit on many systems. Ask your OS documentation to figure out how to do this, as the methods of changing the limits vary greatly from system to system.
table_cache related to max_connections. For example, for 200 open joins, you should have a table buffer of at least 200 * n, where n is the maximum number of tables in a join.
The cache of open tables can be increased to a maximum of one table_cache (64 by default; This can be done with mysqld's -O table_cache=#选项来改变). A table is never closed except when 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 procedure to find a cache entry to use:
Tables that are not currently in use are released, in the order of least used (LRU).
If the cache is full and there are no tables to release, but a new table needs to be opened, the cache must be temporarily enlarged.
If the cache is in a temporary expanded state and a table changes from active to inactive, it is closed and released from the cache.
Open a table for each concurrent access. This means that if you have 2 threads accessing the same table or accessing the table twice in the same query (with AS), the table needs to be opened twice. The first opening of any table accounts for 2 file descriptors; Each additional use of the table accounts for only one file descriptor. For the first opening, an extra descriptor is used to index the file; This descriptor is shared among all threads
|
Posted on 11/13/2017 11:55:59 AM
|
|
|
