High performance MySQL (8) Optimizing Server configuration: Memory

Configuring a MySQL server is inseparable from the configuration file, and then it starts with this section of the content.

Be sure to know the location of the configuration file first, and if you don't know, you can try the following:

/usr/local/mysql/bin/mysqld--verbose--help | Grep-a 1 ' Default options '
#结果如下
Default options are read from the following files in the given order:
/ETC/MY.CNF/ETC/MYSQL/MY.CNF/USR/LOCAL/MYSQL/ETC/MY.CNF ~/.my.cnf

Sometimes you can use the output of show global status to see the state, and sometimes you need to look at the state values that change over time, and you can use the command:

/usr/local/mysql/bin/mysqladmin extended-status-ri60-uroot-p123456

Next look at the memory configuration used.

The memory that MySQL can use is limited by the number of OS digits, and MySQL maintains a connection (thread) that requires only a small amount of memory, as well as a large number of queries to be reserved for peak periods.
Here is what we consider to be a more important cache:

A, InnoDB buffer pool

B, innodb the operating system cache for log files and MyISAM data

C, MyISAM key cache

D, Query caching

The InnoDB buffer pool does not just cache indexes, it also caches rows of data, adaptive hash indices, inserts buffers, locks, and other internal data structures. Very large buffer pool, preheat and shutdown are very long time, restarting the server will also take a long time, especially when the disk is slow, you can use the Percona server's rapid preheating function, if you can not use the reboot immediately after the full table scan or index Scan, the index load into the buffer pool. This functionality can be achieved by using the Init_file setting.

If most tables are Mysiam tables, you should allocate more memory for the key cache. The most important configuration is key_buffer_size, which requires understanding how much space the Mysiam index actually occupies before deciding how much memory to allocate for the key cache. Query the index_length fields of the Information_schem table and add their values to get the index space to occupy. As follows:

Linux can also use the following command:

Du-sch ' find/path/to/mysql/data/directory/-name ' *. Myi "'

By default, Mysiam all indexes in the default key cache, but you can also create multiple named keys, which you can add to the configuration file as follows

key_buffer_1.key_buffer_size=1g

key_buffer_2.key_buffer_size=1g

You can use the cache Index command to map a table to a corresponding buffer, such as the following statement, using key_buffer_1 to cache the index of the T1 and T2 tables

Cache index t1,t2 in key_buffer_1;

You can also preload the table's indexes into the cache, as follows:

Load index into cache t1,t2;

Even if all are innodb tables, there is no Mysiam table, you still need to set key_buffer_size to a smaller value because the server sometimes uses Mysiam internally, for example, the group BY statement might use Mysiam as a temporary table.

The thread cache holds those threads that are not currently connected but are ready for the subsequent connection service, and the thread_cache_size variable specifies the number of threads that MySQL can hold in the cache.

A good idea is to look at the threads_connected variable and try to thread_cache_size it without the need to set it very large.

InnoDB has its own table cache, which can be called a data dictionary, and when InnoDB opens a table, it adds a corresponding object to the data dictionary, which is not removed from the data dictionary when the table is closed.

Another performance problem is that opening the table for the first time calculates the statistics, which requires a lot of I/O operations, but instead of mysiam,innodb the statistics, it recalculates every time it is opened, including changing the contents of the table or querying the Information_ The schema table also recalculates statistics, and if there are many tables, the server may take several hours to start up and finish preheating. After MySQL5.6, you can persist the storage statistics to disk by using the Innodb_analyze_is_persistent option.

You can turn off the innodb_stats_on_metadata option to avoid the refreshing of time-consuming table statistics.

This article comes from "phper-every day a little ~" blog, please be sure to keep this source http://janephp.blog.51cto.com/4439680/1320197