MySQL memory and virtual memory optimization parameters and mysql optimization settings

MySQL memory and virtual memory optimization parameters and mysql optimization settings

Mysql optimization and Debugging commands
 
1. mysqld -- verbose -- help
This command generates a list Of all mysqld options and configurable Variables
2. connect to it and execute this command to see the value of the actually used variable:
Mysql> show variables;
You can also see the statistics and status indicators of the running server through the following statement:
Mysql> show status;
You can also obtain system variables and status information using mysqladmin:
Shell> mysqladmin variables
Shell> mysqladmin extended-status
Shell> mysqladmin flush-table command can immediately close all unused tables and mark all tables in use as disabled, which can effectively release the memory in most use cases. Flush table does not return results until all tables are closed.
 
Swap-s check available swap Zone
 
Mysql Memory Calculation Formula
 
Mysql used mem = key_buffer_size + query_cache_size + tmp_table_size
+ Innodb_buffer_pool_size + innodb_additional_mem_pool_size
+ Innodb_log_buffer_size
+ Max_connections *(
Read_buffer_size + read_rnd_buffer_size
+ Sort_buffer_size + join_buffer_size
+ Binlog_cache_size + thread_stack
)
 
Enter the following command in mysql to automatically calculate the maximum memory consumption of your current configuration.
 
Show variables like 'innodb _ buffer_pool_size ';
Show variables like 'innodb _ additional_mem_pool_size ';
Show variables like 'innodb _ log_buffer_size ';
Show variables like 'thread _ stack ';
SET @ kilo_bytes = 1024;
SET @ mega_bytes = @ kilo_bytes * 1024;
SET @ giga_bytes = @ mega_bytes * 1024;
SET @ innodb_buffer_pool_size = 2 * @ giga_bytes;
SET @ innodb_additional_mem_pool_size = 16 * @ mega_bytes;
SET @ innodb_log_buffer_size = 8 * @ mega_bytes;
SET @ thread_stack = 192 * @ kilo_bytes;
SELECT
(@ Key_buffer_size + @ query_cache_size + @ tmp_table_size
+ @ Innodb_buffer_pool_size + @ innodb_additional_mem_pool_size
+ @ Innodb_log_buffer_size
+ @ Max_connections *(
@ Read_buffer_size + @ read_rnd_buffer_size + @ sort_buffer_size
+ @ Join_buffer_size + @ binlog_cache_size + @ thread_stack
)/@ Giga_bytes AS MAX_MEMORY_GB;
 
 
Mysql key parameter settings
There are two types of parameter settings for the Mysqld database,
 
Global parameters affect global operations on the server;
The other is session-level parameters, which only affect the operations related to the current client connection.
 
When the server is started, all global parameters are initialized to the default value. You can change these default values by specifying options in the initialization file or command line. After the server is started, you can change the dynamic GLOBAL parameters by connecting to the server and executing the set global var_name statement. To change global parameters, you must have the SUPER permission. Modifications to global parameters only take effect for new connections, and existing client connections do not.
 
The server can also maintain session-level parameters for each client connection. During client connection, the current value of the corresponding global parameter is used to initialize the client session parameters. You can use the set session var_name statement to change the dynamic SESSION parameters. You do not need special permissions to set session-level parameters. However, each client can only change its own session-level parameters.
If the parameter type is not specified, the session-level parameter is set by default.
 

(1), max_connections:
Number of customers allowed simultaneously. Increase the number of file descriptors required by mysqld. This number should be added. Otherwise, you will often see the too connector connections error. The default value is 100. I will change it to 1024.

(2) 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). I changed it to 16773120 (16 m)

(3), key_buffer_size:
To minimize disk I/O, MyISAM storage engine uses the key cache to cache indexes. The key cache size is set by the key-buffer-size parameter. If the tables used in the application system are dominated by the MyISAM storage engine, you should increase the value of this parameter to cache the index as much as possible and increase the access speed.

The index block is buffered and shared by all threads. Key_buffer_size is the buffer size used for index blocks. You can increase the size of indexes that can be better processed (for all reads and multi-Rewrite) so that you can afford that much. If you make it too large, the system will begin to change pages and it will really slow down. The default value is 8388600 (8 m). My mysql host has 2 GB of memory, so I changed it to 402649088 (400 mb ).
By default, all indexes are cached with the same key. When the accessed index is not cached, LRU (Least Recently Used is Used at Least Recently) is Used) algorithm to replace the minimum recently used index blocks in the cache. To avoid competition for key-to-Express cache, you can set multiple key-to-Express caches starting with MySQL5.1 and specify the key-to-Express cache used for different index keys. The following example shows how to modify the value of the cache, how to set multiple cache keys, and how to specify different caches for different indexes:
Display the current parameter size, 16 M:
Mysql> show variables like 'key _ buffer_size ';
+ ----------------- + ------- +
| Variable_name | Value |
+ ----------------- + ------- +
| Key_buffer_size | 16384 |
+ ----------------- + ------- +
1 row in set (0.00 sec)
Change the parameter value to 200 M:
Mysql> set global key_buffer_size = 204800;
Query OK, 0 rows affected (0.00 sec)
Mysql> show variables like 'key _ buffer_size ';
+ ----------------- + -------- +
| Variable_name | Value |
+ ----------------- + -------- +
| Key_buffer_size | 204800 |
+ ----------------- + -------- +
1 row in set (0.00 sec)
This section describes the default key cache. The following describes how to set multiple key caches:
Set the hot_cache key cache to 100 MB, The cold_cache key cache to 100 MB, and the default 200 MB key cache. If the index does not specify the key cache, It will be placed in the default key cache.
Mysql> set global hot_cache.key_buffer_size = 102400;
Query OK, 0 rows affected (0.00 sec)
Mysql> set global cold_cache.key_buffer_size = 1024 00;
Query OK, 0 rows affected (0.01 sec)
Mysql> show variables like 'key _ buffer_size ';
+ ----------------- + -------- +
| Variable_name | Value |
+ ----------------- + -------- +
| Key_buffer_size | 204800 |
+ ----------------- + -------- +
1 row in set (0.00 sec)
To display the value of the set multi-key cache, you can use:
Mysql> SELECT @ global. hot_cache.key_buffer_size;
+ ------------------------------------ +
| @ Global. hot_cache.key_buffer_size |
+ ------------------------------------ +
| 1, 102400 |
+ ------------------------------------ +
1 row in set (0.03 sec)
Mysql> SELECT @ global. cold_cache.key_buffer_size;
+ ------------------------------------- +
| @ Global. cold_cache.key_buffer_size |
+ ------------------------------------- +
| 1, 102400 |
+ ------------------------------------- +
1 row in set (0.00 sec)
Specify different indexes to use different key caches:
Mysql> cache index test1 in hot_cache;
+ ------------ + -------------------- + ---------- +
| Table | Op | Msg_type | Msg_text |
+ ------------ + -------------------- + ---------- +
| Test. test1 | assign_to_keycache | status | OK |
+ ------------ + -------------------- + ---------- +
1 row in set (0.00 sec)
Mysql> cache index test2 in hot_cache;
+ ------------ + -------------------- + ---------- +
| Table | Op | Msg_type | Msg_text |
+ ------------ + -------------------- + ---------- +
| Test. test2 | assign_to_keycache | status | OK |
+ ------------ + -------------------- + ---------- +
1 row in set (0.00 sec)
When the database is just started, you need to wait for the database to heat up, that is, waiting for the data to be cached in the cache area, during this time, the database will cause low application access efficiency due to the low buffer hit rate. When using the key-based high-speed cache, you can use commands to pre-load the index into the cache area, which greatly shortens the database push time. The specific operation method is as follows:
Mysql> load index into cache test1, test2 ignore leaves;
+ ------------ + -------------- + ---------- +
| Table | Op | Msg_type | Msg_text |
+ ------------ + -------------- + ---------- +
| Test. test1 | preload_keys | status | OK |
| Test. test2 | preload_keys | status | OK |
+ ------------ + -------------- + ---------- +
2 rows in set (3.89 sec)
If you have used the cache index statement to allocate a key high-speed buffer to the INDEX, You can pre-load the INDEX block into the CACHE. Otherwise, the INDEX block will be loaded to the default key high-speed buffer.
 

4) back_log:
The number of connections that mysql can have. When the main mysql thread receives a lot of connection requests in a very short period of time, this works, and then the main thread takes some time (although very short) to check the connection and start a new thread.
The back_log value indicates how many requests can be stored in the stack within a short time before mysql temporarily stops answering new requests. Only if you want to have many connections in a short period of time, you need to increase it. In other words, this value is the size of the listener queue for the incoming TCP/IP connection. Your operating system has its own limit on the queue size. Trying to set back_log to be higher than your operating system limit will be invalid.
When you observe the process list of your host and find a large number of 264084 | unauthenticated user | xxx. xxx. xxx. xxx | null | connect | null | login | when a null process is to be connected, increase the value of back_log. The default value is 50. I will change it to 500.

(5) 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. I will change it to 7200.

(6) sort_buffer:
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. The default value is 2097144 (2 m). I changed it to 16777208 (16 m ).

(7), table_cache:
Number of tables opened for all threads. Increase this value to increase the number of file descriptors required by mysqld. Mysql requires two file descriptors for each unique opened table. The default value is 64. I changed it to 512.

(8), thread_cache_size:
The number of threads that can be reused. If yes, the new thread is obtained from the cache. If there is space when the connection is disconnected, the customer's thread is placed in the cache. If there are many new threads, this variable value can be used to improve performance. By comparing variables in connections and threads_created states, you can see the role of this variable. I set it to 80.

(9) mysql search function
Mysql is used for search. It is case-insensitive and can be searched in Chinese.
You only need to specify -- default-character-set = gb2312 when starting mysqld

(10), wait_timeout:
The number of seconds that the server waits for action on a connection before closing it. The default value is 28800. I will change it to 7200.
 
(11), innodb_thread_concurrency:
Set the number of CPUs on your server to 8 by default.
 
(12), query_cache_size and query_cache_limit
Negative effects of QueryCache:
A) the hash operation of the Query statement and the consumption of hash search resources. After we use the Query Cache, each SELECT
After a Query arrives at MySQL, a hash operation is required to Query whether the Query exists.
Cache, although the hash algorithm may be very efficient, the hash search process has been optimized enough.
A Query consumes a very small amount of resources, but when we have thousands or even thousands
When querying, we cannot completely ignore the CPU consumption.
B) invalid Query Cache. If our tables are frequently changed, the Query Cache becomes very inefficient.
High. Table changes here refer not only to changes to table data, but also to any changes to the structure or index. That is to say, I
The Cache data cached in the Query Cache may be changed immediately after it is saved.
After the new same Query comes in, the previous Cache cannot be used.
C) The Result Set is cached in the Query Cache, rather than the data page. That is to say, the same record is frequently cached.
. This results in a transitional consumption of memory resources. Of course, some people may say that we can limit the Query
The Cache size. Yes, we can limit the size of the Query Cache, but in this way, the Query Cache is very
It is easy to be swapped out due to insufficient memory, resulting in a decrease in the hit rate.
 
The correct use of QueryCache:
Although the use of Query Cache may have some negative effects, we should also believe that it will certainly have some value. Me
You don't have to lose confidence in the Query Cache because of the above three negative effects of the Query Cache. As long as we understand
The implementation principle of Query Cache, so we can use some means to use the Query Cache to develop strengths and circumvent weaknesses
To give full play to its advantages and effectively avoid its disadvantages.
First, we need to determine which tables are suitable for Query and which are not suitable Based on the Query Cache failure mechanism. Because Query
The Cache is invalid mainly because the data in the Table on which the Query depends has changed, and the Result Set of the Query may have been
If the Query Cache becomes invalid due to some changes, we should avoid the Query of tables with frequent Query changes.
Instead, it should be used on the queries of tables with a small change frequency. MySQL has two specialized
SQL Hint used (prompt): SQL _NO_CACHE and SQL _CACHE, respectively, indicate that Query Cache is not used and Query Cache is used forcibly.
Query Cache. We can use these two SQL hints to let MySQL know which sqls we want to use Query Cache and
Do not use any SQL statements. This not only wastes the memory of the Query Cache for the Query with frequent Table changes, but also
Reduce Query Cache measurement.
Secondly, for those data with very small changes and mostly static data, we can add SQL Hint of SQL _CACHE,
Force MySQL to use Query Cache to improve the Query performance of the table.
Finally, some SQL statements have a large Result Set. If you use the Query Cache, the Cache memory may be insufficient.
Some old Cache files have been washed out before. There are two solutions for this type of Query. One is to use the SQL _NO_CACHE parameter.
Number to force him to do not use the Query Cache, and each time they are directly from the actual data to find, another way is to set
The "query_cache_limit" parameter value controls the maximum Result Set cached in the Query Cache. The default value is
1 M (1048576 ). When the Result Set of a Query is greater than the value Set by "query_cache_limit ",
The Cache does not Cache this Query.

(13), innodb_buffer_pool_size
Innodb_buffer_pool_size defines the maximum memory buffer size for table data and index data of the InnoDB Storage engine. Unlike the MyISAM storage engine, MyISAM's key_buffer_size can only cache index keys, while innodb_buffer_pool_size can cache data blocks and index keys. Appropriately increasing the size of this parameter can effectively reduce the disk I/O of InnoDB tables. On an InnoDB-based dedicated database server, you can consider setting this parameter to 60%-80% of the physical memory size.
 
In addition to innodb_buffer_pool_size, InnoDB memory is used to store page cache data. In addition, there is about 8% overhead under normal circumstances, which is mainly used for data structures such as description and adaptive hash of each cached page frame, if you do not want to disable it safely and restore it at startup, You need to enable about 12% of the memory for restoration. The sum of the two will have about 21% of the overhead.

In this way, the innodb_buffer_pool_size of 12 GB may occupy 14.5 GB (12g x 21%) of memory at most, plus several hundred MB of memory used by the operating system, nearly a thousand thread stacks, almost 16 GB.
 
MAX_QUERIES_PER_HOUR is used to limit the number of queries that a user runs per hour:
Mysql> grant all on dbname. * To db @ localhost identified by "123456" with max_connections_per_hour 5;
(The db user controls the number of new connections opened by the user per hour on the dbname database)
 
MAX_USER_CONNECTIONS:
Mysql> grant all on dbname. * To db @ localhost identified by "123456" with max_user_connections 2;
(The maximum number of connections that a db user can connect to at one time in the database account of dbname is 2)

MAX_UPDATES_PER_HOUR is used to limit the number of database data modifications per hour:
Mysql> grant all on dbname. * To db @ localhost identified by "123456" with max_updates_per_hour 5;
(The db user controls the number of times that the user modifies and updates the database to 5 times per hour on the dbname database)
MAX_USER_CONNECTIONS is used to limit the number of users who modify Database Data per hour:
Mysql> grant all on dbname. * To db @ localhost identified by "123456"
With MAX_QUERIES_PER_HOUR 20; the maximum number of connections of a single mysql user
(The db user controls the number of connections per hour on the dbname database to 20)
 
Optimization example
Optimize the my. cnf file:
[Mysqld]
Skip-locking (cancel the external lock of the file system)
Skip-name-resolve (do not perform domain name anti-resolution, pay attention to the resulting permission/authorization issues)
Key_buffer_size = 256 M (total memory allocated to MyISAM index cache) for servers with around 4 GB of memory, this parameter can be set to 384 M or M.
Note: If this parameter value is set too large, the overall efficiency of the server will be reduced!
Max_allowed_packet = 4 M (maximum package size allowed)
Thread_stack = 256 K (size of each thread)
Table_cache = 128 K (number of reusable cache threads)
Back_log = 384 (the number of requests that can be piled up in a short time before the temporary stop response to a new request. If you need to allow a large number of connections in a short time, you can increase this value)
Sort_buffer_size = 2 M (allocated to each thread for sorting)
Read_buffer_size = 2 M (read index buffer size)
Join_buffer_size = 2 M (memory allocated to each thread for processing scan table connections and indexes)
Myisam_sort_buffer_size = 64 M (size of the sort buffer of myisam engine)
Table_cache = 512 (number of cached data tables to avoid overhead of opening tables repeatedly)
Thread_cache_size = 64 (number of reusable cache threads, see the overhead of creating new threads)
Query_cache_size = 64 M (control the total memory allocated to the query cache)
Tmp_table_size = 256 M (specify the memory size of the mysql cache)
Max_connections = 768 (maximum number of connections) indicates the total maximum number of connections of mysql.
Max_connect_errors = 10000 (maximum connection Error Data)
Wait_timeout = 10 (timeout to avoid attacks)
Thread_concurrency = 8 (set based on the number of CPUs)
Skip-bdb disables unnecessary Engines
Skip-networking (disable mysql TCP/IP connection)
Log-slow-queries =/var/log/mysqlslowqueries. log
Long_query_time = 4 (set the slow query time)
Skip-host-cache (improves mysql speed)
Open_files_limit = 4096 (number of opened files)
Interactive_timeout = 10 (the number of seconds the server waits for action on an interactive connection before closing it)
Max_user_connections = 500 (maximum number of user connections)
 
The default value of key_buffer_size is 218 to 128.
Query_cache_size
Tmp_table_size: The default value is 16 MB and the value is adjusted to 64-256.

Innodb_thread_concurrency = 8 The number of CPUs on your server is set to 8 by default.
 
Table_cache = 1024 the larger the physical memory is, the larger the setting will be. The default value is 2402. The optimal value is adjusted to 512-1024.
Innodb_additional_mem_pool_size = 8 M; default value: 2 M
Innodb_flush_log_at_trx_commit = 0 wait until innodb_log_buffer_size is full and then store it in a unified manner. The default value is 1.
Innodb_log_buffer_size = 4 M; default value: 1 M

Read_buffer_size = 4 M. The default value is 64 K.
Read_rnd_buffer_size the random read cache is 256 kb by default.
Sort_buffer_size = 32 M. The default value is 256 K.
Max_connections = 1024 the default value is 1210.
Thread_cache_size = 120 The default value is 60.
 
Performance Testing
1. mysql Built-in Test Tool
Shell> perl-MCPAN-e shell
Cpan> install DBI
Cpan> install DBD: mysql
Shell> cd SQL-Example
Shell> perl run-all-tests -- server = server_name
Server_name is a supported server. To obtain all options and supported servers, call the following command:
Shell> perl run-all-tests -- help
2. mysqlreport
Http://hackmysql.com/mysqlreport
 
References
Http://dev.mysql.com/doc/refman/5.1/zh/optimization.html
Http://hackmysql.com/tools
Http://www.imysql.cn/