Mysql Optimizing Memory Use

8.12.4 Optimizing Memory Use

8.12.4.1 How MySQL Uses Memory

   MySQL allocates buffers and caches to improve performance of database operations. The default configuration is designed to permit a MySQL server to start on a virtual machine that has approximately 512MB of RAM. You can improve MySQL performance by increasing the values of certain cache and buffer-related system variables. You can also modify the default configuration to run MySQL on systems with limited memory.

   MySQL分配缓冲区和缓存以提高数据库操作的性能。默认配置设计为允许MySQL服务器在具有大约512MB RAM的虚拟机上启动。您可以通过增加某些缓存和缓冲区相关系统变量的值来提高MySQL的性能。您还可以修改默认配置，以便在内存有限的系统上运行MySQL。

 

   The following list describes some of the ways that MySQL uses memory. Where applicable, relevant system variables are referenced. Some items are storage engine or feature specific.

下面的列表描述了MySQL使用内存的一些方法。在适用的情况下，引用相关的系统变量。有些项目是存储引擎或功能特定的。

   • The InnoDB buffer pool is a memory area that holds cached InnoDB data for tables, indexes, and other auxiliary buffers. For efficiency of high-volume read operations, the buffer pool is divided into pages that can potentially hold multiple rows. For efficiency of cache management, the buffer pool is implemented as a linked list of pages; data that is rarely used is aged out of the cache, using a variation of the LRU algorithm. For more information, see Section 14.5.1, “Buffer Pool”.

    InnoDB缓冲池是一个内存区域，用于保存表、索引和其他辅助缓冲区的缓存InnoDB数据。为了提高大容量读取操作的效率，缓冲池被划分为可能容纳多行的页。为了提高缓存管理的效率，将缓冲池实现为页面的链接列表；使用LRU算法的变化。有关更多信息，请参阅第14.5.1节“缓冲池”。

    The size of the buffer pool is important for system performance:

      缓冲池的大小对系统性能很重要：

       • InnoDB allocates memory for the entire buffer pool at server startup, using malloc() operations.The innodb_buffer_pool_size system variable defines the buffer pool size. Typically, a recommended innodb_buffer_pool_size value is 50 to 75 percent of system memory. innodb_buffer_pool_size can be configured dynamically, while the server is running. For more information, see Section 14.8.3.1, “Configuring InnoDB Buffer Pool Size”.

       InnoDB在服务器启动时使用malloc（）为整个缓冲池分配内存运营部innodb_buffer_pool_size系统变量定义缓冲池大小。通常，建议的innodb_buffer_pool_size值是系统内存的50%到75%。可以在服务器运行时动态配置innodb_buffer_pool_size大小。有关更多信息，请参阅第14.8.3.1节“配置InnoDB缓冲池大小”。

 

          • On systems with a large amount of memory, you can improve concurrency by dividing the buffer pool into multiple buffer pool instances. The innodb_buffer_pool_instances system variable defines the number of buffer pool instances.

          在具有大量内存的系统上，可以通过将缓冲池划分为多个缓冲池实例来提高并发性。innodb_buffer_pool_instances系统变量定义缓冲池实例数。

 

             • A buffer pool that is too small may cause excessive churning as pages are flushed from the buffer pool only to be required again a short time later.

            缓冲池太小可能会导致过度抖动，因为页面从缓冲池中刷新后不久才需要再次刷新。

             • A buffer pool that is too large may cause swapping due to competition for memory.

               缓冲池太大可能会由于内存竞争而导致交换。

       • All threads share the MyISAM key buffer. The key_buffer_size system variable determines its size.

       所有线程共享MyISAM key buffer。key_buffer_size系统变量确定其大小。

 

          For each MyISAM table the server opens, the index file is opened once; the data file is opened once for each concurrently running thread that accesses the table. For each concurrent thread, a table structure, column structures for each column, and a buffer of size 3 * N are allocated (where N is the maximum row length, not counting BLOB columns). A BLOB column requires five to eight bytes plus the length of the BLOB data. The MyISAM storage engine maintains one extra row buffer for internal use.

          对于服务器打开的每个MyISAM表，索引文件打开一次；对于访问该表的每个并发运行线程，数据文件打开一次。对于每个并发线程，分配一个表结构、每列的列结构和大小为3*N的缓冲区（其中N是最大行长度，不计算BLOB列）。BLOB列需要5到8个字节加上BLOB数据的长度。MyISAM存储引擎维护一个额外的行缓冲区供内部使用。

 

      • The myisam_use_mmap system variable can be set to 1 to enable memory-mapping for all MyISAM tables.

        myisam_use_mmap系统变量可以设置为1，以便为所有myisam表启用内存映射。

        • If an internal in-memory temporary table becomes too large (as determined using the tmp_table_size and max_heap_table_size system variables), MySQL automatically converts the table from in-memory to on-disk format. On-disk temporary tables use the storage engine defined by the internal_tmp_disk_storage_engine system variable. You can increase the permissible temporary table size as described in Section 8.4.4, “Internal Temporary Table Use in MySQL”.

          如果内存中的临时表变得太大（使用tmp_table_size和max_heap_table_size系统变量确定），MySQL会自动将表从内存格式转换为磁盘格式。磁盘上临时表使用由internal_tmp_disk_storage_engine系统变量定义的存储引擎。您可以按照第8.4.4节“MySQ