MySQL memory allocation

Original link: MySQL Memory Allocation-by Rick James
Original date: Created 2010; Refreshed Oct,, Jan, 2014
Translators: Anchor
Translation Date: May 28, 2014

MySQL Memory Allocation--Quick setup Scheme
If you use only the MyISAM storage engine, setkey_buffer_size20% of available memory (plus set innodb_buffer_pool_size = 0)
If you use only the InnoDB storage engine, setinnodb_buffer_pool_sizeIs 70% of the available memory (set key_buffer_size = 10M, small but not 0.)
practical experience in tuning MySQL:

• Copy the My.cnf/my.ini file first.
• Set Key_buffer_size and innodb_buffer_pool_size based on the storage engine used and available memory.
• The fix for slow queries (Slow queries) is typically done by adding an index (indexes), altering the table structure (schema), and changing the SELECT statement rather than tuning through the database.
• Don't set the query cache casually unless you really know the pros and cons of it and the scenarios that apply.
• Do not change other parameters unless you are experiencing a corresponding problem (such as Max connections) for the maximum number of connections.
• Make sure the content of this section of [Mysqld] is modified, not the other part.

Here are some practical details to show you. (This article does not involve NDB Cluster)
What is an index cache (key_buffer)?
The cache for the MyISAM engine is divided into two parts.

• Index blocks (index blocks, each 1 kb,btree structure, stored in. MYI file) is cached in "key buffer".
• Data block cache (caching, stored in. MYD file) to the operating system, so make sure to leave an appropriate amount of free memory (to the operating system).

Warning: Some types of operating systems always report that memory usage is more than 90%, although there is actually a lot of free memory.
SHOW GLOBAL STATUS like ' key% ';The value of key_read_requests/key_reads is calculated after execution, if the ratio is large (for example, greater than 10), then Key_buffer is sufficient.
What is a cache pool (buffer_pool)?
InnoDB all caches are placed in the buffer pool, the size of the cache pools is innodb_buffer_pool_sizeControl. Contains a 16KB piece of data/index block in the opened table (open tables), plus some additional overhead.
MySQL 5.5 (and version 5.1 with plug-ins) 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 one mutex, so setting up multiple pools can alleviate some mutex bottlenecks.
More InnoDB tuning information
another way to calculate cache size
Set the primary cache to the minimum value; This can be specified if there are many other applications running on the same machine, and/or RAM memory is less than 2GB.
SHOW TABLE STATUS;Displays the status of all tables in each database.

• Calculates the sum of the index_length values for all MyISAM tables. Let Key_buffer_size be less than or equal to this and value.
• Calculates the sum of all InnoDB table Data_length + index_length values. Set Innodb_buffer_pool_size to 110% that does not exceed the sum value.

If there is a memory exchange (swapping occurs), you need to reduce the two parameters by a modest amount.
Execute the following SQL statement to see the appropriate parameter values. (if there are many tables, it can 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", "Performance_schema")    GROUP by  ENGINE;

Mutex Lock Bottleneck
MySQL is designed for the single-core CPU era and can be easily ported to different hardware architectures. Unfortunately, this resulted in a messy operation on the Nexus Lock (interlock). In several important processes there is a small (very few) "mutex" (mutexes). Including:

• MyISAM's Key_buffer
• Query cache
• InnoDB's Buffer_pool

With the prevalence of multicore CPUs, the mutex problem caused the performance problem of MySQL. In general, more CPUs than 4~8 cores, the slower MySQL becomes, and not faster. The enhanced version of InnoDB in MySQL 5.5 Percona XtraDB support for multicore CPUs is much better; The actual limit is roughly +Cores, the CPU core exceeds this number of performance will reach the bottleneck, but no longer decline. MySQL version 5.6 claims to support up to 48 cores.
Hyper-Threading and multicore CPUs
Simple handling:

• Disabling Hyper-Threading (hyperthreading)
• Deactivate more than 8 core parts
• Hyper-Threading here refers primarily to previous hyper-Threading technology, so this section may not necessarily be correct.

Hyper-Threading is suitable for marketing campaigns, but is extremely unfriendly to the performance of (dedicated applications). There are two processing units sharing the same physical cache. If these two threads are doing the same thing, the cache will be quite efficient. If these two threads are doing something different, they will interfere with each other's cache entries for another (hyper) thread.
In general, MySQL does not dominate multi-core processing. So, if you disable Hyper-threading (HT), the remaining cores will run faster.
32-bit operating system and MySQL
(Translator Note: 64-bit MySQL is definitely not running on 32-bit OS ...)
First, the operating system (and hardware?) restricts the process from using all of the 4GB RAM, if there is 4G memory. If the physical RAM exceeds 4 GB, the excess portion is not accessible in the 32-bit operating system and is not available.
Second, the operating system may limit the maximum amount of memory that a single process can use.
For example: FreeBSD's Maxdsiz, the default is MB.
Example:

$ Ulimit-a...max Memory Size (Kbytes,-m) 524288

As a result, you can set the amount of memory available to mysqld to 20% to 70%, but you need to reduce it appropriately.
If the system errors, such as [ERROR]/usr/libexec/mysqld:out of memory (Needed xxx bytes), it is possible that MySQL has requested more memory than the operating system allows. You need to reduce the cache settings.
64-bit OS with 32-bit MySQL
64-bit operating systems are not limited by 4 GB of memory, but 32-bit MySQL is still subject to this limitation.
If you have more than 4 GB of memory, then you can set:

• Key_buffer_size = 20% (all RAM), but not more than 3 GB.
• Buffer_pool = 3G

Of course, the best way is to replace MySQL with a 64-bit version.
64-bit OS with 64-bit MySQL

• use only the MyISAM engine: (5.0.52 to 5.1.23) key_buffer_size has a hard limit of 4GB. For details, please refer to MySQL 5.1 Limit (restrictions) in later versions, set Key_buffer_size to 20% of RAM. In the (my.c Nf/my.ini) plus innodb_buffer_pool_size = 0.
• Use only the InnoDB engine: Set innodb_buffer_pool_size = 70% RAM. If you have a large memory and use 5.5 (and more) versions, consider using multiple cache pools. It is recommended to set 1-16 innodb_buffer_pool_instances, each of which is not less than 1 GB. (Sorry, there are no specific reference indicators for the optimal setting, but should not be set too much).

At the same time, set key_buffer_size = 20M (very small, but not 0)
If you mix multiple engines in a database, reduce the two values by a few.
Maximum number of connections, line stacks
(Max_connections,thread_stack)
Each "thread" consumes a certain amount of memory. Usually about a few KB; So 100 threads is probably about a MB. If you set max_connections= 1000, that would probably take up to a few megabytes, or more. It is important to be aware that the number of simultaneous connections is too large to cause some other problems.
In 5.6 (or MariaDB5.5), you can select the thread pool to interact with the max_connections. This is a high-level topic.
Thread stack overflow rarely occurs. If it does occur, you can set: Thread_stack = 256K
See more about Max_connections, wait_timeout, connection pooling
Table_cache (table_open_cache)
(names are different in some editions).
The operating system has a limit on the number of files that a single process can open. 1-3 files are required to open each table. Each table partition (PARTITION) is equivalent to one table. Most operations on a partitioned table will open all partitions.
In *nix, Ulimit shows how much the file limit is. The maximum value is typically tens of thousands, but it can be set to 1024.  This restricts the opening of only 300 or so tables. For more discussion on Ulimit, please click here
( this paragraph is controversial..) On the other hand, table cache (past?) Implementation is inefficient-finding is done with a linear scan. Therefore, setting Table_cache to thousands of does make MySQL slow. (The benchmark also proves this point.)
You can pass SHOW GLOBAL STATUS;View the performance information of the system and calculate the number of open per second (Opens/second): Opened_files/uptime, if the value is larger, for example, greater than 5, then the table_cache should be increased; If it is small, such as 1, it may improve performance by reducing the Table_cache value.
Query Cache
A short answer: set Query_cache_type = OFFAnd query_cache_size = 0
The QC (Query Cache) is actually a hash mapping of the SELECT statement to the result set (resultsets).
A detailed answer ... There are many viewpoints about "query caching"; Many of them are negative.

• Novice Warning! QC is completely unrelated to Key_buffer and Buffer_pool.
• When hit, the QC speed is as fast as lightning. It is not difficult to create a benchmark that runs 1000 times times faster.
• There is only one mutex in QC (translator note: The fewer locks, the less lock keys, high concurrency will be fierce competition/waiting).
• Unless the QC is set to off and 0, each query will be compared again.
• The truth is that the mutex will collide even if Query_cache_type = DEMAND (2).
• Truth, the mutex will collide, even if the Sql_no_cache is set.
• A query statement that changes a little bit (even if there are more spaces) can result in multiple different cache entries being generated in QC.

“ Modify"is expensive and frequent:

• Any write event occurs in a table, and all entries in the QC corresponding to the table are purged.
• This is true even on read-only slave servers (readonly Slave).
• Clear uses a linear algorithm to execute, so a large QC (such as 200MB) can cause the speed to become noticeably slower.

To see how efficiently the QC is performing, perform SHOW GLOBAL STATUS like ' qc% ';Then calculate the hit ratio of read: Qcache_hits/qcache_inserts, if more than 5, then the efficiency of the QC is good.
If the QC is suitable for your application, then I recommend:

• Query_cache_size = no more than 50M
• Query_cache_type = DEMAND
• Indicate Sql_cache or sql_no_cache in all SELECT statements, depending on which queries might be hit from the QC cache.

Learn more about query Cache
thread_cache_size
This is a very small tuning item. Setting to 0 reduces the speed at which threads (connections) are created. It is better to set a smaller value (such as 10). This option has little effect on RAM.
It is the number of additional threads that the server keeps, without affecting the actual number of threads; The limiting effect is max_connections.
Binary Log
If you enable binary logging (enabled via Og_bin) for replication (replication) or point-in-time recovery (point-in-time recovery), the server keeps track of binary logs (binary logs). That is, the disk may be slowly occupied. Recommended settings expire_logs_days =, only 14 days of log records are retained.
swappiness

RHEL, very wisely, allows the user to control how the OS makes pre-memory swap allocations. This is a good strategy in general, but it's a disaster for MySQL.

(Feeling the translation is a little fluid, this paragraph is originally: RHEL, in its infinite wisdom, decided-let's control how aggressively the OS would preemptively swap Ram. This is good the general, but lousy for MySQL)

MySQL expects fairly stable memory allocations-caches (most of them) to be pre-allocated; The number of threads (mostly) is limited. Any memory exchange can significantly damage MySQL performance.
By setting a high swappiness value, some memory is lost because the operating system attempts to retain a large amount of free space for future allocations (MySQL is generally not required).
Set swappiness = 0, do not swap, the operating system may crash when memory is low. I'd rather have MySQL on one card than I would have him shoot.
For Mysql-only (dedicated) servers, median (e.g. 5?) Might be a good value.
NUMA
OK, it's time to learn some CPU-managed memory architectures. Let's look first. NUMA(Non-uniform memories Access, non-uniform memory addressing). A portion of memory is mounted on each CPU (or each socket (CPU socket) in a multiple server). This makes it very fast to access locally (local) RAM, while accessing the RAM that is mounted on other CPUs is slower than dozens of cycles.
Then look at the operating system. In (RHEL?) In many cases, there are two behaviors:

• The memory allocated by the OS is pinned to the "first" CPU name.
• The other allocated memory is then assigned to the first CPU name until it is full.

Now that's the problem.

• The OS and MySQL have allocated all the RAM for the first CPU.
• MySQL allocates part of the memory for a second CPU.
• Operating system OS also needs to allocate some additional memory.

ouch--a CPU needs to allocate memory, but the RAM under its own name is exhausted, so it displaces some of MySQL's memory. Slag residue!
Possible solution: Configure BIOS memory allocation to "Interleave" (interleaved). This prevents premature swapping (premature swapping), at the cost of having about half of the RAM access to cross the CPU (OFF-CPU). Well, regardless of how the cost of access is large, if you really want to use all memory.
Overall performance loss/gain: several percentage points.
Large Memory paging (huge pages)
Here is another hardware performance trap.
The CPU accesses RAM, especially if the 64-bit address is mapped to a place, such as GB or "real" ram, and the TLB is used. (TLB =translation lookaside buffer, bypass conversion buffer.) TLB is a hardware-implemented Memory Association lookup table; Converts a 64-bit virtual address to the actual physical address.
Because the TLB is a small, virtual-addressing cache that sometimes occurs "misses" (misses), it goes into physical RAM to find it. This is a time-consuming operation for two lookups, so it should be avoided.
Typically, memory is "paged" to a page of 4 KB, and the TLB actually maps a high-level (64-12) bit to a specific page. The lower 12 bits are converted through the virtual address to get the full address.
For example, 4 GB of RAM requires 32M (32 million) Page-table entries for paging. It's too big, far more than the TLB's capacity. So caught in the "Huge page" scam.
With the support of hardware and operating systems, it is possible to make part of the Ram a giant page, say 4 MB (instead of 4 KB). This makes the TLB entry less dramatic, and the paging unit is 4 MB for this portion of RAM. As a result, huge pages are equivalent to non-paged (non-pagable).
Memory is now divided into pagable and non pagable two parts; Which parts of non pagable are reasonable? In MySQL, Innodb_buffer_pool is the perfect user. By properly configuring these, InnoDB can run faster:

• Enable Huge pages
• Notifies the operating system that the appropriate number is assigned (consistent with the number of Buffer_pool)
• Notify MySQL using huge pages

InnoDB memory usage vs swapThe post contains a lot of details that need attention and how to set it up.
Overall performance Benefits: a few percentage points. Yawn.
MEMORY engine (engine=memory)
This is an infrequently used storage engine, and is considered a substitute for MyISAM and InnoDB. Its data is not persistent, so its scope of application is quite limited. The size of the memory table is limited by max_heap_table_size, and the default value is MB. I mention it in case you modify this value too much; This will secretly occupy the available RAM.
How to set a variable (VARIABLEs)
In the text file my.cnf (My.ini on Windows), add a line, for example
innodb_buffer_pool_size = 5G
That is: The variable name, the equal sign "=", the value of the variable. Some values allow abbreviations, such as M for million (1048576), and g for billion.
For the server to see these settings, it must be placed under the "[Mysqld]" section of the configuration file.
Settings for MY.CNF or My.ini do not take effect immediately and require you to restart the server.
Most of the settings can be changed online via the root account (other Super privilege accounts can also), for example:
SET @ @global. key_buffer_size = 77000000;
Note: setting units such as M or G are not allowed here.
To view the settings information for a global variable:

Mysql> SHOW GLOBAL VARIABLES like "key_buffer_size", +-----------------+----------+| Variable_name   | Value    |+-----------------+----------+| key_buffer_size | 76996608 |+-----------------+----------+

Note that this section sets MySQL to be rounded down and aligned to a certain number.
You may need to modify two places (execute set and modify MY.CNF) so that the changes take effect immediately and remain the same value after the next reboot (whether manually or otherwise)
Web Server
Web servers such as Apache use multithreading to handle. If each thread opens a MySQL connection, it may exceed the maximum number of connections allowed. Make sure that the maxclients (or similar parameter) of the Web server is set to a reasonable value (such as 50 or less).
Tools
Mysqltuner
Tuning-primer
There are several recommended tools for memory setup. One of the misleading entries is:
Maximum Possible memory usage:31.3g(266% of installed RAM)
The maximum memory that may be used is: 31.3G (266% of physical memory may be)
Don't let it scare you, these tools use formulas that are too conservative. They assume that all max_connections are in use and are active, and are doing some memory-intensive work.

Total Fragmented tables:23
A tables:23 with fragments.
This means that OPTIMIZE TABLE may be useful. I recommend that you set a high percentage of "free space" on the table (see Show Table STATUS) or that you know what table is doing a lot of delete/update operations. However, you do not have to worry about optimize optimization of table frequently. One months is probably enough.
Article modification record
2010 creation; October 2012 Update, January 2014 update;
more in-depth articles:
Tuning for MySQL 5.6
InnoDB basic knowledge of Performance Optimization (Ultimate Edition)
10 optimization settings after MySQL installation

via MySQL Forum:: Performance Contact Author--Rick James
Rick James mysql-related documentation

Tips, debugging, howto, optimization related, etc...

Rick ' s rots(Rules of Thumb--lots of tips)
Memory Allocation (caching, etc) 
Character Set and Collation problem solver 
Converting from MyISAM to InnoDB--Includes differences between them
Big Deletes-how to optimize 
Compound INDEXesplus other insights into the mysteries of indexing
Partition Maintenance (drop+reorg)For Time series
Entity-attribute-value--a common, poorly performing, design patter; plus an alternative
Find the nearest pizza parlors(Efficient searching on Latitude + longitude)
Alter of a Huge table 
Latest news Articles-- How to optimize the schema and code for such
pagination, not with OFFSET, LIMIT 
Data Warehouse techniques (esp., Summary Tables) 
Techniques on efficiently finding a random row(on Beyond ORDER by RAND ())
Guid/uuid Performance (type 1 only) 
IP Range Table Performance 
MySQL Limits 
Galera Limitations(with Percona XtraDB cluster/mariadb)
Rollup Unique User Counts 
Best of MySQL Forum