High-performance MySQL reading notes-Optimizing server settings

MySQL has a large number of parameters that can be modified--but should not be modified at random. Usually only the basic items are configured correctly (in most cases only a few parameters are really important) and more time should be spent on schema optimization, indexing, and query design. After properly configuring MySQL's basic configuration items, it is usually relatively small to take the effort to modify other configuration items.

1. Create a MySQL configuration file

It is recommended that you do not use the configuration file that comes with your operating system's installation package, and you may want to create a profile from scratch. (First make sure which configuration file is used by MySQL!) ）

2.InnoDB Buffer Pool

There is a popular rule of thumb that the buffer pool size should be set to about 75~80% of server memory, but not always correct. There is a better way to set the buffer pool size, roughly as follows:

1. Start with the total amount of server memory

2, minus the operating system memory consumption

3. Subtract some of the memory required by MySQL itself

4. Subtract enough memory for the operating system to cache InnoDB log files, at least enough to cache the most recently accessed portions.

5. Subtract other configured MySQL buffers and cache memory required, such as query cache, MyISAM key cache

6, divided by 105%, the result down to a reasonable value.

If most of these are InnoDB tables, the INNODB buffer pool may need more memory than anything else. The InnoDB buffer pool does not only cache indexes: It also caches rows of data, adaptive hash indexes, insert caches (insert buffer), locks, and other internal data structures. InnoDB also uses a buffer pool to help delay writes, so that multiple writes can be combined and then written back together sequentially. In summary InnoDB heavily relies on the buffer pool, you must confirm that it is allocated enough memory.

If the amount of data is small and does not grow fast, there is no need to allocate too much memory to the buffer pool. It doesn't really make sense to configure the buffer pool much more than the tables and indexes that need to be cached. Of course, there is no problem in planning ahead for a fast-growing database, but sometimes we also see a huge buffer pool that caches only a little bit of data, which is not necessary.

A large buffer pool also brings some challenges, such as preheating and closing, which can take a long time. If there are many dirty pages in the buffer pool, InnoDB may take a long time to write the dirty pages back to the data file when it is closed. Of course, it can be forced to shut down quickly, but more recovery work is required to restart.

When the percentage of dirty pages exceeds the innodb_max_dirty_pages_pct threshold, InnoDB will quickly brush the dirty pages and try to make the number of dirty pages even lower. When the transaction log does not have enough space left, InnoDB will enter "intense brush write" mode, which is one of the reasons why large logs can improve performance.

If you cannot warm up quickly, you can perform a full table scan or index scan immediately after a reboot to load the index into the buffer pool. You can also use the Init_file settings, put SQL in a file, and then execute when MySQL starts.

3. Thread Caching

The thread cache holds threads that are not currently associated with a connection but that are behind a new connection service. When a new connection is created, if the thread in the cache is present, MySQL removes one of the threads from the cache and assigns it to the new connection. When the connection is closed, MySQL will put the thread back into the cache if there is room for the thread cache. If there is no space, MySQL will destroy this thread. As long as MySQL has an idle thread in the cache, it can respond quickly to connection requests because it does not create a new thread for each connection.

A good way is to observe the threads_commected variable and try to set the Thread_cache_size large enough to handle fluctuations in normal business pressure. Threads in each thread cache, or threads in a dormant state, typically use about 256K of memory. This memory is not very large compared to the thread that is processing the query.

4. Table Caching

Table caches can reuse resources. When a query requests access to a MyISAM table, MySQL can get the file descriptor from the cached object, avoiding the overhead of opening a file descriptor. The real benefit of table caching for MyISAM tables is that the server avoids modifying the MyISAM header to mark the table "in use", and the table cache is much less important to InnoDB because InnoDB does not rely on it to do so many things, such as holding file descriptors.

5.InnoDB I/O configuration (transaction log)

InnoDB uses logs to reduce the overhead of committing transactions. Because transactions are already logged in the log, it is not necessary to flush (flush) The buffer pool's dirty chunks to disk on each transaction commit. Transactional modified data and indexes are usually mapped to random locations in the tablespace, so it takes a lot of random IO to flush those changes to disk. InnoDB assumes that using regular disks, random IO is much more expensive than sequential IO because an IO request takes time to move the head to the correct position, and then waits for the disk to read the desired portion before moving on to the start position.

InnoDB uses logs to turn random io into sequential io. Once the log is securely written to disk, the transaction is persisted and, even if the power is lost, the InnoDB can replay the log and restore the committed transaction.

InnoDB uses a background thread to intelligently refresh these changes to the data file. This thread can be written in batches, making the data more sequential in order to improve efficiency.

The overall log file size is controlled by the innodb_log_file_size and innodb_log_files_in_group two parameters, which is important for write performance. The total size of the log file is the sum of the size of each file.

InnoDB uses multiple files as a set of circular logs. You typically do not need to modify the default number of logs, only the size of each log file. To modify the log file size, you need to completely shut down MySQL, move the old log files elsewhere to save, reconfigure the parameters.

To determine the ideal log file size, you must weigh the cost of normal data changes and the time required for crash recovery, and if the log is too small, innodb will inevitably do more checkpoints, resulting in more log writes. If the log is too large, innodb may have to do a lot of work when the crash resumes.

When InnoDB changes any data, it writes a change record into the memory log buffer. When the buffer is full, when the transaction commits, or every second, these three conditions whichever comes first, InnoDB flushes the contents of the buffer to the disk log file. The variable innodb_log_buffer_size can control the size of the log buffer, which defaults to 1M. It is not usually necessary to set the log buffer to a very large size. The recommended range is 1~8m. As a rule of thumb, the full size of the log file should be sufficient to accommodate the server's one-hour active content.

InnoDB How to flush the log buffer? When InnoDB flushes the log buffer to the disk log file, it first uses a mutex to lock the buffer, flushes to the desired location, and moves the remaining entries to the front of the buffer. The log buffers must be flushed to the persisted storage to ensure that committed transactions are fully persisted. If you care more about performance than persistence, you can modify the Innodb_flush_log_at_trx_commit variable to control how frequently the log buffer refreshes. The possible settings are as follows:

0: Writes the log buffer to the log file once per second, but does not do anything when the transaction commits.

1: Writes the log buffer to the log file, and then flushes each transaction commit to the persisted store (the default and most secure setting), which guarantees that no committed transactions will be lost.

2: refreshes every second, but writes the log buffer to the log file each time it is committed, but does not flush to persistent storage.

"Write log buffers to log files" and "flush logs to persistent storage" are different. In most operating systems, writing a buffer to a log simply transfers the data from the InnoDB memory buffer to the operating system cache, in memory, and does not actually write the data to persistent storage.

If MySQL crashes or loses power, setting 0 and 2 usually results in a maximum of seconds of data loss because the data may exist in the operating system's cache.

Instead, flushing the log to persistent storage means that InnoDB requests the operating system to swipe the data out of the cache, and confirms that it is written to disk, which is a call to block IO until the data is completely written back before it is complete, when the write data to disk is slow, and the configuration entry is set to 1 o'clock, It is possible to significantly reduce the number of transactions that InnoDB can commit per second.

6.InnoDB Concurrent Configuration

InnoDB has its own thread scheduler that controls how threads enter the kernel to access data, and what they can do once in the kernel. The most basic way to limit concurrency is to use the innodb_thread_concurrency variable, which restricts how many threads can enter the kernel at a time, and 0 means no limit.

Theoretically, you can refer to the following formula: number of concurrent values =CPU * Number of disks * *

Alternatively, you can limit concurrency through the thread pool.

7. Optimized sorting (filesorts)

If the total size of all required columns and order BY columns in the query exceeds Max_length_for_sort_data bytes, the two-pass algorithm is used, otherwise the single-pass algorithm is used.

8. Other Configuration Items

1.max_connections: This setting acts like an emergency brake to ensure that the server is not overwhelmed by the proliferation of applications. If there is a problem with the application, or if the server encounters a problem such as a connection delay, many new connections are created, but if the query cannot be executed, there is no benefit in opening a connection, so the "too many connections" error rejection is a quick and costly failure.

Be wary of sudden attacks that may encounter connection restrictions. For example, if you restart the application server, you may not have the connection turned off cleanly, and MySQL may not be aware that they have been shut down. When the application server restarts and attempts to open a connection to the database, it is possible that the pending connection has not timed out, causing the new connection to be rejected. Observing that the Max_used_connections state variable changes over time, if the value reaches Max_connections, the client is rejected at least once.

2.thread_cahce_size: Observe the threads_connected state variable and find its maximum and minimum values in the general case. Can be set to a fluctuation range of double-size. But it doesn't have to be set very large, because it's not really useful to keep a lot of idle threads waiting to connect.

You can also observe changes in the threads_created state over time, and if the value is large or growing, this may tell you that you need to make the thread_cahce_size variable larger. View Threads_cached to see how many threads are already in the cache.

Another related state variable is slow_launch_threads. If this state is large, it means that some cases delay the connection allocation of new threads. In general, the system may be overloaded, causing the operating system not to dispatch CPUs for newly created threads.

In summary, if you are using InnoDB, the most important configuration items are the following two:innodb_buffer_pool_size and innodb_log_file_size

MySQL Focus: "High performance MySQL" Reading notes-index : http://blog.csdn.net/xifeijian/article/details/20312557

High-performance MySQL reading notes-Optimizing server settings