MySQL Memory Allocation

MySQL memory allocation-Quick Setting Solution

If you only use the MyISAM storage engine, set key_buffer_size to 20% of the available memory (plus innodb_buffer_pool_size = 0)

If you only use the InnoDB Storage engine, set innodb_buffer_pool_size to 70% of available memory (set key_buffer_size = 10 M, small but not 0 .)

Practical experience in optimizing mysql:

First copy my. cnf/my. ini file copy. set key_buffer_size and innodb_buffer_pool_size Based on the storage engine and available memory. the revision of the Slow query (Slow queries) is generally implemented by adding indexes, changing the table schema, and changing the SELECT statement, instead of optimizing the database. do not set Query cache unless you have mastered its advantages and disadvantages and applicable scenarios. do not change other parameters unless you encounter problems (such as max connections ). make sure that the content in the [mysqld] section is modified, instead of other parts. the following shows you some actual details. (This article does not involve NDB Cluster)

What is index cache (key_buffer )?

The cache of the MyISAM engine is divided into two parts.

Index blocks (each 1 KB, B-tree structure, stored in. MYI files) are cached in "key buffer. data block caching is stored in. in the MYD file) to the operating system, so make sure that the amount of idle memory is left (for the operating system ). warning some types of operating systems always report that memory usage exceeds 90%, although there is actually a lot of idle memory.

Show global status like 'key % '; after execution, calculate the value of Key_read_requests/Key_reads. If the ratio is large (for example, greater than 10), then key_buffer is enough.

What is a cache pool (buffer_pool )?

InnoDB stores all the caches in the buffer pool. The cache pool size is controlled by innodb_buffer_pool_size. contains 16 KB data/index blocks in the open tables. In addition, there are additional overhead.

MySQL 5.5 (and plug-in 5.1) allows you to specify a block size of 8 KB or 4 KB. mySQL 5.5 can have multiple buffer pools. Because each cache pool has a mutex lock, setting multiple pools can alleviate some mutex lock bottlenecks.

More InnoDB tuning information

Another method for calculating the cache size

Set the master cache to the minimum value. If many other applications are running on the same machine and/or the RAM memory is smaller than 2 GB, you can specify this value.

Show table status; displays the STATUS of all tables in each database.

Calculates the total value of Index_length for all MyISAM tables. make key_buffer_size less than or equal to this value. calculate the total value of Data_length + Index_length in all InnoDB tables. set innodb_buffer_pool_size to 110% of the total value. if there is a memory swap (swapping occurs), you need to reduce the two parameters in a proper manner.

Execute the following SQL statement to view the appropriate parameter values. (if there are many tables, it may take several minutes .)
Select engine,
ROUND (SUM (data_length)/1024/1024, 1) AS "Data MB ",
ROUND (SUM (index_length)/1024/1024, 1) AS "Index MB ",
ROUND (SUM (data_length + index_length)/1024/1024, 1) AS "Total MB ",
COUNT (*) "Num Tables"
FROM INFORMATION_SCHEMA.TABLES
WHERE table_schema not in ("information_schema", "cece_schema ")
Group by engine;

Mutex lock bottleneck

MySQL is designed in the single-core CPU era and can be easily transplanted to different hardware architecture. unfortunately, this leads to messy interlock operations. there are a few (very few) mutexes in several important processes )". including:

MyISAM's key_buffer Query Cache (Query Cache) InnoDB buffer_pool with the prevalence of multi-core CPUs, mutual exclusion issues cause MySQL performance problems. In general, the CPU exceeds 4 ~ The more 8-core, the slower MySQL will become. in MySQL 5.5, the enhanced InnoDB Percona XtraDB has much better support for multi-core CPUs. The actual limit is about 32 cores. When the number of CPU cores exceeds this limit, the performance will reach the bottleneck, but it will not decrease. mySQL 5.6 claims that it can support up to 48 cores.

Hyper-threading and multi-core CPU

Simple solution:

Disable HyperThreading to stop more than 8 cores. hyper-threading is mainly used in the previous hyper-Threading Technology. Therefore, this Part may not be correct. hyper-threading is suitable for marketing and promotion, but it is extremely unfriendly to (dedicated application) performance. two processing units share the same physical cache. if the two threads are doing the same thing, the cache will be quite efficient. if the two threads are doing different things, they will interfere with the cache items of another (Super) thread.

In general, MySQL is not dominant in multi-core processing. Therefore, if hyper-threading (HT) is disabled, the remaining core will run faster.

32-bit operating system and MySQL

(Note: Certainly 64-bit MySQL cannot run on 32-bit OS ...)

First, the operating system (and hardware ?) The process cannot use all 4 gb ram resources, if 4 GB memory exists. if the physical RAM exceeds 4 GB, the excess part is not accessible in the 32-bit operating system and is also unavailable.

The operating system may limit the maximum memory used by a single process.

For example, the default value of maxdsiz of FreeBSD is 512 MB.

Example:
$ Ulimit-
...

Max memory size (kbytes,-m) 524288. Therefore, you can set mysqld to 20% ~ 70%, but it needs to be reduced.

If the system reports an ERROR, for example, [ERROR]/usr/libexec/mysqld: Out of memory (Needed xxx bytes), it may be that MySQL has applied for exceeding the memory range allowed by the operating system. the cache settings need to be reduced.

64-bit OS and 32-bit MySQL

The 64-bit operating system is not limited by 4 GB memory, but 32-bit MySQL is still subject to this limit.

If you have more than 4 GB memory, you can set it:

Key_buffer_size = 20% (all RAM), but do not exceed 3 GB. buffer_pool = 3G. Of course, the best way is to replace MySQL with a 64-bit version.

64-bit OS and 64-bit MySQL

Only use the MyISAM engine: (5.0.52 ~ Before 5.1.23) key_buffer_size has a hard limit of 4 GB. for more information, see MySQL 5.1 restrictions (restrictions). In a later version, set the key_buffer_size to 20% RAM. in (my. cnf/my. ini) add innodb_buffer_pool_size = 0. use the InnoDB engine only: Set innodb_buffer_pool_size = 70% RAM. if the memory is large and 5.5 (or later) version is used, you can consider using multiple cache pools. we recommend that you set 1-16 innodb_buffer_pool_instances, each of which is no less than 1 GB. (Sorry, there are no specific reference indicators with optimal settings; but too many indicators should be set ). meanwhile, set key_buffer_size = 20 M (small, but not zero)

If you mix multiple engines in the database, reduce the two values.

Max connections, thread Stack

(Max_connections, thread_stack)

Each "Thread" occupies a certain amount of memory. it is usually about 200 KB. Therefore, 100 threads are about 20 MB. if you set max_connections = 1000, it takes about 200 MB or more. too many connections may cause other problems.

In 5.6 (or MariaDB5.5), you can select the thread pool to interact with max_connections. This is a high-level topic.

Thread Stack Overflow rarely occurs. If it does, you can set it to thread_stack = 256 K.

Click to view