Mysql on twitter

Twitter has published improvements for MySQL.

The source code of the changes is distributed under a modified BSD license and is located on GitHub

Read more detailed changes under habrakatom.

Additional state variables

The developers added additional state variables, in particular, from the internal components of InnoDB, which made it possible to more effectively control the operation of the systems and better understand their behavior, especially at the moment of load. Among the added variables are:

• The number of open, closed, currently open InnoDB files and table space files. Previously, this information was not provided by the InnoDB subsystem.
• The number of freezes detected. Previously, this information was not provided by the InnoDB subsystem.
• The current transaction registration number in the log (LSN number), as well as the LSN number before the set point and the LSN number to which the reset is made. This information was previously available using the SHOW ENGINE INNODB STATUS command.

Optimize memory allocation based on NUMA

Most modern multiprocessor systems use the technology of uneven memory access, or as it is called the NUMA architecture, which allows you to share the total memory of the system between the "nodes" NUMA. When allocating a large amount of memory for the InnoDB buffer pool, there are usually serious problems. For more information on the typical problems encountered in the NUMA systems used by MySQL, see Jeremy Cole's blog. The developers made the following changes in order to optimize the system and troubleshoot problems:

• An option has been added to force the reallocation of the entire buffer pool memory during startup. First of all, this was done in order to make the system decide which pages to place and on which NUMA node. If the buffer pool memory cannot be fully allocated for any reason, the InnoDB subsystem will interrupt operation during startup.
• An option has been added to mysqld_safe that allows you to minimize the start of mysqld using the numactl - interleave = all command to divide memory between all available NUMA nodes. This ensures that no NUMA node has any advantage in allocating memory, and therefore the memory continues to be used evenly by all the many NUMA nodes throughout the entire time.
• An option has been added to mysqld_safe to clear the operating system buffer cache before launching (for Linux only). Linux, as a rule, does not release the cache in order to re-allocate memory, except when the system is in a state of low memory, which can lead to the fact that evenly distributed memory can be unevenly distributed, that is, preference when redistributing memory before launch, it will be given to the node with the smallest amount of cached information. Clearing the cache before starting ensures that the system does not allocate a large amount of memory for the cache before InnoDB allocates buffer pool memory.

')

Server timeout request

The amount of unnecessary work performed by the server has been reduced, which allowed the server to cancel in advance requests that last more than a certain wait time to the nearest millisecond.

Exporting and restoring the buffer pool using prefetching

Exporting and restoring the InnoDB buffer pool was made possible through the use of a safe and lightweight method that allows you to create tools for restarting services with minimal impact.

Optimization for solid-state drives (SSD drives)

MySQL has been optimized for machines using SSDs, including optimizing the process of updating pages and reducing the number of records on a disk in order to increase the lifespan of disks.

News source: engineering.twitter.com/2012/04/mysql-at-twitter.html

Text taken from README file .