MySQL footprint memory and CPU high test and solution

In order to install the MySQL environment test, installed after the discovery of the MySQL memory after the start of a large, up to more than 800 trillion. Online search for a while, get expert advice My.ini. Never see the details again. Had to open My.ini row of gnawing, although the English is poor point, but how many m or see Ming-^

The following changes are made:
innodb_buffer_pool_size=576m->256m InnoDB engine buffer is the big head, the first is to take it.
query_cache_size=100m->16m Query Caching
tmp_table_size=102m->64m Temp Table size
key_buffer_size=256m->32m

After restarting the MySQL service, virtual memory drops below 200.
In addition, there are several files under the MySQL installation directory: My-huge.ini, My-large.ini, My-medium.ini ... These are based on the size of the proposed configuration of memory, the novice in the settings can also refer to the time.
2G memory MySQL database server My.ini optimization (my.ini)
2G memory, for station less, high-quality type of Setup, test Special:
table_cache=1024 The larger the physical memory, the larger the setting. Default is 2402, Tune 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 full and then unified storage, the default is 1
innodb_log_buffer_size=4m default to 1M
Innodb_thread_concurrency=8 your server CPU several set to a few, the default is 8
key_buffer_size=256m defaults to 218 to 128 best
tmp_table_size=64m to 64-256 for 16M
read_buffer_size=4m defaults to 64K
Read_r nd_buffer_size=16m defaults to 256K
sort_buffer_size=32m defaults to 256K
max_connections=1024 defaults to 1210

Test One:
table_cache=512 or 1024
Innodb_additional_mem_pool_size=2m
Innodb_flush_log_at_trx_commit=0
innodb_log_buffer_size=1m
Innodb_thread_concurrency=8 a few of your server CPUs are set to a few, default to 8
key_buffer_size=128m
tmp_table_size=128m
read_buffer_size=64k or 128K
read_rnd_buffer_size=256k
sort_buffer_size=512k
max_connections=1024

Test Two:
table_cache=512 or 1024
Innodb_additional_mem_pool_size=8m
Innodb_flush_log_at_trx_commit=0
innodb_log_buffer_size=4m
Innodb_thread_concurrency=8
key_buffer_size=128m
tmp_table_size=128m
read_buffer_size=4m
read_rnd_buffer_size=16m
sort_buffer_size=32m
max_connections=1024

So so:
table_cache=512
Innodb_additional_mem_pool_size=8m
Innodb_flush_log_at_trx_commit=0
innodb_log_buffer_size=4m
Innodb_thread_concurrency=8
key_buffer_size=128m
tmp_table_size=128m
read_buffer_size=4m
read_rnd_buffer_size=16m
sort_buffer_size=32m
max_connections=1024

Tested. There is no special case, it is best to use the default.
2G of memory, for stations, compression-type settings, the best:
The larger the table_cache=1024 physical memory, the larger the setting. Defaults to 2402, to 512-1024 best
innodb_additional_mem_pool_size=4m defaults to 2M
Innodb_flush_log_at_trx_commit=1
(set to 0 is to wait until the innodb_log_buffer_size queue is full and then unified storage, the default is 1)
innodb_log_buffer_size=2m defaults to 1M
Innodb_thread_concurrency=8 a few of your server CPUs are set to a few, recommended by default generally 8
key_buffer_size=256m defaults to 218 to 128 best
tmp_table_size=64m defaults to 16M up to 64-256
read_buffer_size=4m defaults to 64K
read_rnd_buffer_size=16m defaults to 256K
sort_buffer_size=32m defaults to 256K
max_connections=1024 defaults to 1210
thread_cache_size=120 defaults to 60
query_cache_size=64m

10 parameters to optimize MySQL database performance
(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:
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).
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.

Note: Parameters can be adjusted by modifying the/etc/my.cnf file and restarting the MySQL implementation. This is a more cautious work, the results above is only a few of my views, you can be based on your own host hardware conditions (especially memory size) to further modify.