Mysql memory and virtual memory optimization settings Parameters _mysql

MySQL optimized debug command

1, mysqld--verbose--help
This command generates a list of all mysqld options and configurable variables
2. By connecting to it and executing this command, you can see the value of the variable actually used:
Mysql> show VARIABLES;
You can also see statistics and status indicators for running the server through the following statements:
Mysql>show STATUS;
System variables and status information can also be obtained using mysqladmin:
Shell> mysqladmin variables
Shell> mysqladmin Extended-status
The shell> mysqladmin flush-table command closes all unused tables immediately and marks all used tables as closed, effectively freeing most of the memory in use. FLUSH table does not return results until all tables are closed.

Swap-s Check available swap areas

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 your current configured maximum memory consumption

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 = @mega_bytes;
SET @innodb_log_buffer_size = 8 * @mega_bytes;
SET @thread_stack = * @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.

One is the global parameter, which affects the global operation of the server;
The other is the session-level parameter, which affects only the current client connection actions.

When the server starts, all global parameters are initialized to the default values. You can change these defaults by specifying options in the initialization file or the command line. After the server starts, 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 SUPER permissions. Changes to global parameters take effect only for new connections, and existing client connections do not take effect.

The server can also maintain session-level parameters for each client connection, initializing client session parameters with the current value of the corresponding global parameter when the client connects. The client can change the dynamic session parameters through the SET var_name statement. Setting session-level parameters does not require special permissions, but each client can change only its own session-level parameters and cannot change the session-level parameters of other clients.
When you do not specify a parameter type for a setting, the default setting is the session-level parameter.

(1), Max_connections:
The number of simultaneous customers allowed. Increase this value to increase the number of file descriptors required by mysqld. This number should be increased, otherwise you will often see too many connections errors. The default value is 100, and I'll change it to 1024.

(2), Record_buffer:
Each thread that carries out a sequential scan assigns a buffer of this size to each table it scans. If you do a lot of sequential scans, you might want to add that value. The default value is 131072 (128k) and I change it to 16773120 (16m)

(3), Key_buffer_size:
To minimize disk I/O, the table of the MyISAM storage engine caches the index using the key cache, and the size of the key cache is set by the Key-buffer-size parameter. If the table used in the application system is based on the MyISAM storage engine, the value of the parameter should be appropriately increased so that the index can be cached as much as possible to increase the speed of the access.

The index block is buffered and is shared by all threads. Key_buffer_size is the size of the buffer used for the index block, adding that it can be better processed by indexing (for all read and multiple writes), to the extent that you can afford that much. If you make it too big, the system will start to change pages and it really slows down. The default value is 8388600 (8m), my MySQL host has 2GB memory, so I changed it to 402649088 (400MB).
By default, all indexes use the same key cache, and the LRU (least recently Used least recently used) algorithm is used to replace the least recently used index block in the cache when the accessed index is not in the cache. To further avoid contention for key caching, starting with MySQL5.1, you can set up multiple key caches and specify the key cache to use for different index keys. The following example shows how to modify the value of a high-speed key cache, how to set multiple key caches, and how to specify different caches for different indexes:
Displays the current parameter size, which is 16M:
Mysql> Show variables like ' key_buffer_size ';
+-----------------+-------+
| variable_name | Value |
+-----------------+-------+
| Key_buffer_size | 16384 |
+-----------------+-------+
1 row in Set (0.00 sec)
Modify parameter values to 200M:
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 is the default key cache, which describes how to set up multiple key caches:
Set Hot_cache key Cache 100M, Cold_cache key cache 100M, plus 200M default key cache. If the index does not specify key caching, it is 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)
If you want to display the values of a set of multiple-key caches, you can use:
Mysql> SELECT @ @global. hot_cache.key_buffer_size;
+------------------------------------+
| @ @global. hot_cache.key_buffer_size |
+------------------------------------+
| 102400 |
+------------------------------------+
1 row in Set (0.03 sec)
Mysql> SELECT @ @global. cold_cache.key_buffer_size;
+-------------------------------------+
| @ @global. cold_cache.key_buffer_size |
+-------------------------------------+
| 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)
Usually when the database is just started, you need to wait for the database to heat up, that is, wait for the data to be cached in the buffer, this time the database will be due to the low percentage of buffer hit the application is inefficient access. When using the key cache, you can preload the index into the cache by command, greatly shortening the time for database warm-up. The specific mode of operation is:
Mysql> LOAD INDEX into the 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 already used the cache index statement to assign a key buffer to the index, preload can place the index block into the cache, otherwise the index block will be loaded into the default key buffer.

4), Back_log:
Require MySQL to have the number of connections. This works when the main MySQL thread gets a lot of connection requests in a very short time, and then the main thread takes some time (albeit very short) to check the connection and start a new thread.
The Back_log value indicates how many requests can be on the stack within a short time before MySQL temporarily stops answering the new request. Only if you expect to have a lot of connections in a short time, you need to increase it, in other words, this value is the size of the listening queue for incoming TCP/IP connections. Your operating system has its own limits on the size of this queue. Attempting to set Back_log above your operating system will be ineffective.
When you look at your host process list, find a lot of 264084 | Unauthenticated user | xxx.xxx.xxx.xxx | null | Connect | null | Login | Null to connect process, it is necessary to increase the value of Back_log. The default value is 50, and I'll change it to 500.

(5), Interactive_timeout:
The number of seconds 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, and I'll change it to 7200.

(6), Sort_buffer:
Each thread that needs to be sorted allocates a buffer of that size. Increase this value to accelerate the order by or group by action. The default value is 2097144 (2m) and I change it to 16777208 (16m).

(7), Table_cache:
The number of tables opened for all threads. Increasing this value can increase the number of file descriptors required by mysqld. MySQL requires 2 file descriptors for each unique open table. The default value is 64, and I'll change it to 512.

(8), Thread_cache_size:
The number of threads stored in that can be reused. If there is, a new thread is obtained from the cache, and if there is space when disconnected, the client's line is placed in the cache. If there are a lot of new threads, in order to improve performance you can have this variable value. By comparing the variables of connections and threads_created states, we can see the effect of this variable. I set it to 80.

(9) MySQL search function
Use MySQL to search, the purpose is to be able to do not separate case, but also in Chinese to search
Just specify--default-character-set=gb2312 when starting mysqld

(10), Wait_timeout:
The number of seconds the server waits for action on a connection before closing it. The default value is 28800, and I'll change it to 7200.

(11), Innodb_thread_concurrency:
A few of your server CPUs are set to a few, and the default is 8.

(12), Query_cache_size and Query_cache_limit
The negative effects of Querycache:
A the hash operation of the Query statement and the hash lookup resource consumption. After we use Query Cache, each Select
After the type of query arrives at MySQL, a hash operation is required to find out if the query's
Cache, although the hash algorithm may have been very efficient, the hash lookup process has been enough to optimize
, the resources consumed by a query are really very, very little, but when we have thousands or even thousands of per second
Query, we cannot completely ignore the resulting CPU consumption.
b The failure of Query Cache. If we change the table more frequently, it will cause query Cache is very inefficient
High. The table changes here are not just changes to the data in the table, but also any changes to the structure or index. Which means I
The cache data cached in query caches each time may be stored soon after the data in the table is changed
Clear, and then the new same query comes in and cannot be used to the previous cache.
c the Query cache caches the result Set, not the data page, that is, there is one record being cached more than the other
The possibility of the times exists. Thus causing the transition consumption of memory resources. Of course, some might say that we can limit query
The size of the Cache. Yes, we can actually limit the size of the query cache, but in this way, query cache is
It is easy to be swapped out because of insufficient memory, resulting in a drop in hit rate.

Correct use of Querycache:
Although the use of query Cache will have some negative effects, but we should also believe that its existence must have a certain value. I
There is no need to completely lose faith in query cache because of the above three negative effects of query cache. As long as we understand.
Query cache Implementation principle, then we can completely through a certain means in the use of query cache to avoid weaknesses, heavy
Give play to its advantages and effectively avoid its disadvantages.
First, we need to determine which tables are appropriate to use query, based on the query Cache invalidation mechanism. Because query
Cache expiration is mainly because the data on the table that query relies on has changed, causing query's result Set to be
Changes that cause the query Cache to be all invalidated, then we should avoid the query on the table that is changing frequently
, but should be used in query on a table that has a smaller frequency of changes. There are two specialized in MySQL for query Cache
SQL Hint (hint): Sql_no_cache and Sql_cache, respectively, are forced to not use query CACHE and forced to use
Query Cache. We can take advantage of these two SQL Hint to let MySQL know what SQL we want to use query Cache and
Which SQL is not used. This will not only allow the change frequently table query waste query Cache memory, but also can
Reduce the query Cache detection amount.
Second, for those data that change very little, mostly static, we can add Sql_cache SQL Hint,
Force MySQL to use query Cache to improve query performance for this table.
Finally, some SQL result Set is very large, if the use of query cache can easily cause the cache memory is insufficient, or will
Before some old cache was washed out. There are two ways we can solve this type of query, one is to use the Sql_no_cache parameter
Number to force him to not use query Cache and each time directly from the actual data to find, the other way is by setting
The "query_cache_limit" parameter value controls the maximum result Set that is cache in query cache, and the system defaults to
1M (1048576). When a query's result set is greater than the value set by "Query_cache_limit", query
Cache is not cache this query.

(13), innodb_buffer_pool_size
Innodb_buffer_pool_size defines the maximum memory buffer size for the table data and index data of the INNODB storage engine. Unlike the MyISAM storage engine, MyISAM key_buffer_size can only cache index keys, while innodb_buffer_pool_size caches data blocks and index keys. By appropriately increasing the size of this parameter, you can effectively reduce disk I/O for InnoDB types of tables. On a InnoDB dedicated database server, consider setting this parameter to the size of physical memory 60%-80%

InnoDB occupied memory, in addition to the innodb_buffer_pool_size used to store page cache data, in addition to the normal situation also has about 8% of the overhead, mainly used in each cache page frame description, adaptive hash and other data structures, if not safe shutdown, When you start to recover, you also need to open about 12% of the memory for recovery, and the two add up to almost 21% of the overhead.

In this way, 12G of Innodb_buffer_pool_size, most of the time InnoDB may occupy 14.5G (12G X 21%) of memory, plus the operating system with hundreds of M, nearly thousands of thread stacks, almost 16G.

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;
(5 DB Users control the number of users opening new connections per hour on a dbname database)

Max_user_connections limit how many users are connected to the MySQL server:
Mysql> Grant all on dbname. * To Db@localhost identified by "123456" with max_user_connections 2;
(The maximum number of connections that DB users can connect at one time in a dbname database account is 2)

Max_updates_per_hour is used to limit the number of user changes to database data per hour:
Mysql> Grant all on dbname. * To Db@localhost identified by "123456" with Max_updates_per_hour 5;
(5 times the number of times a DB user controls a user to modify the update database per hour on a dbname database)
Max_user_connections is used to limit the number of user changes to database data per hour:
Mysql> Grant all on dbname. * To Db@localhost identified by "123456"
With max_queries_per_hour 20; Maximum number of connections for MySQL single user
(DB users control the number of connections per hour to 20 users on dbname databases)

Examples of tuning
to optimize for my.cnf files:
[Mysqld]
Skip-locking (Cancel the external lock on the file system)
Skip-name-resolve (do not parse the domain name, pay attention to the permissions/authorization issues arising therefrom)
Key_buffer_size = 256M (total number of memory allocated to the MyISAM index cache) This parameter can be set to 256M or 384M for servers that have around 4GB.
Note: The value of this parameter set too large will be the overall efficiency of the server down!
Max_allowed_packet = 4M (maximum packet size allowed)
Thread_stack = 256K (size of each thread)
Table_cache = 128K (cache number of reusable threads)
Back_log = 384 (you can increase the number of requests in a short time before you temporarily stop responding to a new request, if you need to allow a large amount of connections in a short period of time)
Sort_buffer_size = 2M (assigned to handle sorting in each thread)
Read_buffer_size = 2M (read index buffer size)
Join_buffer_size = 2M (allocated memory for processing scan table connections and indexes in each thread)
Myisam_sort_buffer_size = 64M (size of the MyISAM engine sort buffer)
Table_cache = 512 (number of cached data tables, avoiding the overhead of repeatedly opening tables)
Thread_cache_size = 64 (number of cached reusable threads, laughing at the cost of creating a new thread)
Query_cache_size = 64M (Controls the total amount of memory allocated to the query cache)
Tmp_table_size = 256M (Specify the memory size of the MySQL cache)
Max_connections = 768 (maximum number of connections) refers to the total number of MySQL connections
Max_connect_errors = 10000 (maximum connection error data)
Wait_timeout = 10 (timeout time to avoid attack)
Thread_concurrency = 8 (set according to number of CPUs)
SKIP-BDB Disable unnecessary engines
Skip-networking (Close MySQL TCP/IP connection)
Log-slow-queries =/var/log/mysqlslowqueries.log
Long_query_time = 4 (set time for slow queries)
Skip-host-cache (to improve MySQL speed)
Open_files_limit = 4096 (number of files open)
Interactive_timeout = 10 (the number of seconds the server waits to act on an interactive connection before closing it)
Max_user_connections = 500 (maximum number of user connections)

Key_buffer_size defaults to 218 to 128 best
Query_cache_size
Tmp_table_size defaults to 16M up to 64-256

Innodb_thread_concurrency=8 a few of your server CPUs are set to a few, default to 8

The larger the table_cache=1024 physical memory, the larger the setting. Defaults to 2402, to 512-1024 best
innodb_additional_mem_pool_size=8m defaults to 2M
innodb_flush_log_at_trx_commit=0 wait until Innodb_log_buffer_size queue is full and then unified storage, the default is 1
innodb_log_buffer_size=4m defaults to 1M

read_buffer_size=4m defaults to 64K
Read_rnd_buffer_size Random Read cache defaults to 256K
sort_buffer_size=32m defaults to 256K
max_connections=1024 defaults to 1210
thread_cache_size=120 defaults to 60

Performance Test
1, MySQL with test tools
Shell> perl-mcpan-e Shell
Cpan> Install DBI
Cpan> Install Dbd::mysql
Shell> CD Sql-bench
Shell> Perl run-all-tests--server=server_name
server_name is a supported server. To get all the options and supported servers, invoke the command:
Shell> Perl run-all-tests--help
2, Mysqlreport
Http://hackmysql.com/mysqlreport

Reference documentation
Http://dev.mysql.com/doc/refman/5.1/zh/optimization.html
Http://hackmysql.com/tools
http://www.imysql.cn/