MySQL Memory tuning

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-high-speed setup scheme
Assuming that only the MyISAM storage engine is used, setkey_buffer_size20% of available memory (plus set innodb_buffer_pool_size = 0)
Assuming that only the InnoDB storage engine is used, setinnodb_buffer_pool_sizeIs 70% of the available memory (set key_buffer_size = 10M, very 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 the available memory.
• The fix for slow queries (Slow queries) is usually 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 encounter a problem (such as Max connections).
• Make sure the content of the [mysqld] section is changed, not the rest.

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 spare 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 spare memory.
SHOW GLOBAL STATUS like ' key% ';After running, calculate the value of key_read_requests/key_reads, assuming a large ratio (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 controlled by Innodb_buffer_pool_size . Includes 16KB pieces of data/index blocks in the open tables, plus some additional overhead.
MySQL 5.5 (and the 5.1 version with plug-in) agrees that you specify a block size of 8 KB or 4 KB. MySQL 5.5 can have multiple buffer pools, because each cache pool has a mutually exclusive lock, so setting up multiple pools can alleviate some mutually exclusive lock bottlenecks.
Many other InnoDB tuning information
There is also a way to calculate the cache size
Set the primary cache to the minimum value; If there are many other applications running on the same machine, and/or RAM memory is less than 2GB, then this can be specified.
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 tables Data_length + index_length values. Set Innodb_buffer_pool_size to 110% that does not exceed the sum value.

Assuming there is a memory exchange (swapping), it is necessary to reduce the number of two parameters by a moderate amount.
Run the following SQL statement to see the appropriate number of references. (assuming there are very 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;

Mutual exclusion 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). There are few (very few) "mutual repulsion" (mutexes) in several important processes. Including:

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

With the prevalence of multicore CPUs, the problem of MySQL performance is caused by mutual exclusion. 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 multi-core 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 is mostly referred to as Hyper-Threading technology, so this part may not 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. Assuming that these two threads are doing the same thing, the cache will be quite efficient. Assuming these two threads are doing something different, they will interfere with one another (hyper) thread's cache entry.
In general, MySQL does not dominate multi-core processing. So, assuming you disable Hyper-threading (HT), the rest of the core 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 (as well as the hardware?) restricts the process from using all of 4GB RAM and has 4G of memory. Assuming that the physical RAM exceeds 4 GB, the excess portion is not available 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 feeling is MB.
Demo Sample:

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

Therefore, it is possible to set the amount of memory available for mysqld to 20% to 70%, but it needs to be reduced appropriately.
Suppose the system has an error, such as [ERROR]/usr/libexec/mysqld:out of memory (Needed xxx bytes), it may be that MySQL has requested more memory than the operating system agrees. 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.
Assuming you have more than 4 GB of memory, 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 number.
64-bit OS with 64-bit MySQL

• using only the MyISAM engine: (5.0.52 ~ 5.1.23) key_buffer_size has a hard limit of 4GB. For details, please refer to MySQL 5.1 Limit (restrictions) in the higher version number, set Key_buffer_size to 20% RAM. In the (My. Cnf/my.ini) plus innodb_buffer_pool_size = 0.
• Just use the InnoDB engine: Set innodb_buffer_pool_size = 70% RAM. Assuming that the memory is very large and uses the 5.5 (and above) version number, you can 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)
Suppose you mix multiple engines in a database, reducing 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. Assumptions set max_connections= 1000, that would probably require a few megabytes, or many others. It is important to note that the number of connections at the same time may 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.
The thread stack overflow is very rare. Assuming it does occur, can be set: Thread_stack = 256K
Click to see a lot of other discussions about Max_connections, wait_timeout, connection pooling
Table_cache (table_open_cache)
(some version numbers have different names).
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 of the tables. 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 usually tens of thousands, but it can be set to 1024.  This limits the ability to open only about 300 tables. A lot of other discussions about Ulimit please click here
( this paragraph is controversial..) On the one hand, table cache (past?) Implementation is very inefficient--look for a linear scan to complete. Therefore, setting Table_cache to thousands of does make MySQL slow. (The benchmark test 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, assuming that the value is larger, such as greater than 5, then the table_cache should be increased; The hypothesis is very small, for example, 1, which 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).
The specific 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 test that runs 1000 times times faster.
• In QC there is only one mutually exclusive lock (translator Note: The less the lock, the less the lock key, high concurrency will be fierce competition/waiting).
• Unless the QC is set to off and 0, each query will be checked again.
• Truth, the mutual exclusion of the lock will collide, even if Query_cache_type = DEMAND (2).
• Truth, the mutual exclusion of the lock will collide, even if the Sql_no_cache is set.
• A query statement that only changes a little bit (even if you have more than one space) can result in multiple different cache entries being generated in QC.

“ Changes"is expensive and frequent:

• Any write event occurs in a table, and all entries in the QC corresponding to this table will be cleared.
• This is true even on a read-only from server (readonly Slave).
• Clear uses a linear algorithm to run, so a large QC (for example, 200MB) can cause a noticeable slowdown in speed.

To see how efficiently the QC is running, run SHOW GLOBAL STATUS like ' qc% ';Then calculate the hit ratio of read: Qcache_hits/qcache_inserts, assuming more than 5, then the efficiency of the QC is good.
If QC is right 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. Setting to a smaller value (for example, 10) is a good comparison. 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
Assuming that binary logging is 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 =, keeping only 14 days of log records.
swappiness

RHEL, very wisely, agrees that the user controls how the OS makes pre-memory swap allocations. This is a very 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's performance.
Setting a very high swappiness value loses some memory, because the operating system attempts to retain a large amount of free space for future allocations (MySQL is usually 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 (private) server, the median (for example, 5?) May be a very 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). Each CPU (or each socket (CPU socket) in the server) mounts a portion of memory. This makes access to local ram very fast, while access to the RAM mounted on other CPUs is slower than dozens of cycles.
Then look at the operating system. In (RHEL?

In very many cases, there are two behaviors:

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

Now the problem has come.

• The OS and MySQL allocate 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 part of the MySQL memory. Slag residue!
Possible WORKAROUND: Configure BIOS memory allocation to "Interleave" (interleaved). This will prevent premature swapping (premature swapping), at the cost of about half the RAM access to cross the CPU (OFF-CPU). Well, no matter how expensive the interview is, suppose you really want to use all of the memory.
Overall performance loss/benefit: several percentage points.
Large Memory paging (huge pages)
There is also a hardware performance trap.
CPU access to RAM, especially mapping 64-bit addresses to somewhere, such as GB or "real" RAM, uses TLB. (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 "misses" (misses), it goes into physical RAM to look for. This is two times the find is very time consuming operation, so 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, the 4 GB 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

22,384707,385002 >innodb memory usage vs swap  This post includes a lot of attention and details about how to set it up.  
Overall Performance benefits: A few percentage points. YAWN.&NBSP
memory engine (engine=memory)
This is an infrequently used storage engine. 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 change the value too much; This will secretly occupy the available RAM.
How to set the variable (VARIABLEs)
in the text file my.cnf (My.ini on Windows), Add a row, such as
innodb_buffer_pool_size = 5G
, which is the variable name, equals "=", and the value of the variable. Some values agree with abbreviations, such as M for million (1048576), g for billion. 
to let the server see these settings, you must place them in the "[Mysqld]" section of the configuration file.
The settings for MY.CNF or My.ini do not take effect immediately, you need to restart the server. ,
Most of the settings can be changed via the root account online   (other Super privileges account can also), such as:
set @ @global. key_buffer_size = 77000000;
Note: This does not agree to setting units such as M or G.
View setting information for global variables:

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 change two places (run set and change my.cnf) to make the change take effect immediately, and still be the same value after the next reboot (whether manually or for another reason)
Webserver
Like Apache this webserver uses multithreading to handle. Assuming that each thread opens a MySQL connection, it may exceed the agreed maximum number of connections. Make sure that the maxclients (or similar) of the webserver is set to a reasonable value (such as 50 or less).
Tools
Mysqltuner
Tuning-primer
There are several recommended tools for memory setup. There is a misleading entry:
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 running 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. It's just that you don't have to bother to optimize the table frequently. One months is probably enough.
article change 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 

MySQL Memory tuning