MySQL 8.0 Reference Manual（读书笔记60节--Optimizing the MySQL Server 和 Measuring Performance）
第一部分 Optimizing the MySQL Server
This section discusses optimization techniques for the database server, primarily dealing with system configuration rather than tuning SQL statements. The information in this section is appropriate for DBAs who want to ensure performance and scalability【skeɪləˈbɪlɪti 可扩展性；可伸缩性；可量测性;】 across the servers they manage; for developers constructing installation scripts that include setting up the database; and people running MySQL themselves for development, testing, and so on who want to maximize their own productivity.
1 Optimizing Disk I/O
This section describes ways to configure storage devices when you can devote more and faster storage hardware to the database server.
• Disk seeks are a huge performance bottleneck. This problem becomes more apparent【əˈpærənt 显然的;显然;显而易见;表面上的;貌似的;明白易懂;未必真实的;】 when the amount of data starts to grow so large that effective caching becomes impossible. For large databases where you access data more or less randomly, you can be sure that you need at least one disk seek to read and a couple of disk seeks to write things. To minimize this problem, use disks with low seek times.
• Increase the number of available disk spindles【ˈspɪndəlz 轴;心轴;(手纺用的)绕线杆，纺锤;指轴;】 (and thereby reduce the seek overhead) by either symlinking files to different disks or striping the disks:
（1）Using symbolic links
This means that, for MyISAM tables, you symlink the index file and data files from their usual location in the data directory to another disk (that may also be striped). This makes both the seek and read times better, assuming that the disk is not used for other purposes as well.
Symbolic links are not supported for use with InnoDB tables. However, it is possible to place InnoDB data and log files on different physical disks.
（2）Striping
Striping means that you have many disks and put the first block on the first disk, the second block on the second disk, and the N-th block on the (N MOD number_of_disks) disk, and so on. This means if your normal data size is less than the stripe size (or perfectly aligned), you get much better performance. Striping is very dependent on the operating system and the stripe size, so benchmark your application with different stripe sizes.
The speed difference for striping is very dependent on the parameters. Depending on how you set the striping parameters and number of disks, you may get differences measured in orders of magnitude. You have to choose to optimize for random or sequential access.
• For reliability, you may want to use RAID 0+1 (striping plus mirroring), but in this case, you need 2 × N drives to hold N drives of data. This is probably the best option if you have the money for it. However, you may also have to invest in some volume-management software to handle it efficiently.
• A good option is to vary the RAID level according to how critical a type of data is. For example, store semi-important data that can be regenerated on a RAID 0 disk, but store really important data such as host information and logs on a RAID 0+1 or RAID N disk. RAID N can be a problem if you have many writes, due to the time required to update the parity bits.
• You can also set the parameters for the file system that the database uses:
If you do not need to know when files were last accessed (which is not really useful on a database server), you can mount your file systems with the -o noatime option. That skips updates to the last access time in inodes on the file system, which avoids some disk seeks.
On many operating systems, you can set a file system to be updated asynchronously by mounting it with the -o async option. If your computer is reasonably stable, this should give you better performance without sacrificing too much reliability. (This flag is on by default on Linux.)
Using NFS with MySQL
You should be cautious【ˈkɔːʃəs 小心的;谨慎的;】 when considering whether to use NFS with MySQL. Potential issues, which vary by operating system and NFS version, include the following:
• MySQL data and log files placed on NFS volumes becoming locked and unavailable for use. Locking issues may occur in cases where multiple instances of MySQL access the same data directory or where MySQL is shut down improperly, due to a power outage, for example. NFS version 4 addresses underlying locking issues with the introduction of advisory and lease-based locking. However, sharing a data directory among MySQL instances is not recommended.
• Data inconsistencies introduced due to messages received out of order or lost network traffic. To avoid this issue, use TCP with hard and intr mount options.
• Maximum file size limitations. NFS Version 2 clients can only access the lowest 2GB of a file (signed 32 bit offset). NFS Version 3 clients support larger files (up to 64 bit offsets). The maximum supported file size also depends on the local file system of the NFS server.
Using NFS within a professional SAN environment or other storage system tends to offer greater reliability than using NFS outside of such an environment. However, NFS within a SAN environment may be slower than directly attached or bus-attached non-rotational storage.
If you choose to use NFS, NFS Version 4 or later is recommended, as is testing your NFS setup thoroughly before deploying into a production environment.
2 Using Symbolic Links
You can move databases or tables from the database directory to other locations and replace them with symbolic links to the new locations. You might want to do this, for example, to move a database to a file system with more free space or increase the speed of your system by spreading your tables to different disks.
For InnoDB tables, use the DATA DIRECTORY clause of the CREATE TABLE statement instead of symbolic links. This new feature is a supported, cross-platform technique.
The recommended way to do this is to symlink entire database directories to a different disk. Symlink MyISAM tables only as a last resort.
To determine【dɪˈtɜːrmɪn 查明;准确算出;】 the location of your data directory, use this statement:
SHOW VARIABLES LIKE 'datadir';
2.1 Using Symbolic Links for Databases on Unix
On Unix, symlink a database using this procedure【prəˈsiːdʒər (商业、法律或政治上的)程序;步骤;(正常)手续;手术;】:
1. Create the database using CREATE DATABASE:
mysql> CREATE DATABASE mydb1;
Using CREATE DATABASE creates the database in the MySQL data directory and permits the server to update the data dictionary with information about the database directory.
2. Stop the server to ensure that no activity occurs in the new database while it is being moved.
3. Move the database directory to some disk where you have free space. For example, use tar or mv. If you use a method that copies rather than moves the database directory, remove the original database directory after copying it.
4. Create a soft link in the data directory to the moved database directory:
$> ln -s /path/to/mydb1 /path/to/datadir
The command creates a symlink named mydb1 in the data directory.
5. Restart the server.
2.2 Using Symbolic Links for MyISAM Tables on Unix
【Symbolic link support as described here, along with the --symbolic-links option that controls it, and is deprecated; expect these to be removed in a future version of MySQL. In addition, the option is disabled by default.】
Symlinks are fully supported only for MyISAM tables. For files used by tables for other storage engines, you may get strange problems if you try to use symbolic links.
Do not symlink tables on systems that do not have a fully operational realpath() call. (Linux and Solaris support realpath()). To determine whether your system supports symbolic links, check the value of the have_symlink system variable using this statement:
SHOW VARIABLES LIKE 'have_symlink';
The handling of symbolic links for MyISAM tables works as follows:
• In the data directory, you always have the data (.MYD) file and the index (.MYI) file. The data file and index file can be moved elsewhere and replaced in the data directory by symlinks.
• You can symlink the data file and the index file independently【ˌɪndɪˈpɛndəntlɪ 独立地；自立地;无关地;】 to different directories.
• To instruct a running MySQL server to perform the symlinking, use the DATA DIRECTORY and INDEX DIRECTORY options to CREATE TABLE. Alternatively, if mysqld is not running, symlinking can be accomplished manually using ln -s from the command line.
【The path used with either or both of the DATA DIRECTORY and INDEX DIRECTORY options may not include the MySQL data directory. (Bug #32167)】
• myisamchk does not replace a symlink with the data file or index file. It works directly on the file to which the symlink points. Any temporary files are created in the directory where the data file or index file is located. The same is true for the ALTER TABLE, OPTIMIZE TABLE, and REPAIR TABLE statements.
【When you drop a table that is using symlinks, both the symlink and the file to which the symlink points are dropped. This is an extremely good reason not to run mysqld as the root operating system user or permit operating system users to have write access to MySQL database directories.】
• If you rename a table with ALTER TABLE ... RENAME or RENAME TABLE and you do not move the table to another database, the symlinks in the database directory are renamed to the new names and the data file and index file are renamed accordingly.
• If you use ALTER TABLE ... RENAME or RENAME TABLE to move a table to another database, the table is moved to the other database directory. If the table name changed, the symlinks in the new database directory are renamed to the new names and the data file and index file are renamed accordingly.
• If you are not using symlinks, start mysqld with the --skip-symbolic-links option to ensure that no one can use mysqld to drop or rename a file outside of the data directory.
These table symlink operations are not supported:
• ALTER TABLE ignores the DATA DIRECTORY and INDEX DIRECTORY table options.
2.3 Using Symbolic Links for Databases on Windows
On Windows, symbolic links can be used for database directories. This enables you to put a database directory at a different location (for example, on a different disk) by setting up a symbolic link to it. Use of database symlinks on Windows is similar to their use on Unix, although the procedure for setting up the link differs.
Suppose that you want to place the database directory for a database named mydb at D:\data\mydb. To do this, create a symbolic link in the MySQL data directory that points to D:\data\mydb. However, before creating the symbolic link, make sure that the D:\data\mydb directory exists by creating it if necessary. If you already have a database directory named mydb in the data directory, move it to D:\data. Otherwise, the symbolic link has no effect. To avoid problems, make sure that the server is not running when you move the database directory.
On Windows, you can create a symlink using the mklink command. This command requires administrative privileges.
1. Make sure that the desired path to the database exists. For this example, we use D:\data\mydb, and a database named mydb.
2. If the database does not already exist, issue CREATE DATABASE mydb in the mysql client to create it.
3. Stop the MySQL service.
4. Using Windows Explorer or the command line, move the directory mydb from the data directory to D: \data, replacing the directory of the same name.
5. If you are not already using the command prompt, open it, and change location to the data directory, like this:
C:\> cd \path\to\datadir
If your MySQL installation is in the default location, you can use this:
C:\> cd C:\ProgramData\MySQL\MySQL Server 8.0\Data
6. In the data directory, create a symlink named mydb that points to the location of the database directory:
C:\> mklink /d mydb D:\data\mydb
7. Start the MySQL service.
After this, all tables created in the database mydb are created in D:\data\mydb.
Alternatively, on any version of Windows supported by MySQL, you can create a symbolic link to a MySQL database by creating a .sym file in the data directory that contains the path to the destination directory. The file should be named db_name.sym, where db_name is the database name.
Support for database symbolic links on Windows using .sym files is enabled by default. If you do not need .sym file symbolic links, you can disable support for them by starting mysqld with the --skip-symbolic-links option. To determine whether your system supports .sym file symbolic links, check the value of the have_symlink system variable using this statement:
SHOW VARIABLES LIKE 'have_symlink';
To create a .sym file symlink, use this procedure:
1. Change location into the data directory:
C:\> cd \path\to\datadir
2. In the data directory, create a text file named mydb.sym that contains this path name: D:\data\mydb \
【The path name to the new database and tables should be absolute. If you specify a relative path, the location is relative to the mydb.sym file.】
After this, all tables created in the database mydb are created in D:\data\mydb.
3 Optimizing Memory Use
3.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【əˈprɑːksɪmətli 大概;大约;约莫;】 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.
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.
• The InnoDB buffer pool is a memory area that holds cached InnoDB data for tables, indexes, and other auxiliary【ɔːɡˈzɪliəri 辅助的;备用的;】 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.
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.
- 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.
- 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.
• The storage engine interface enables the optimizer to provide information about the size of the record buffer to be used for scans that the optimizer estimates are likely to read multiple rows. The buffer size can vary based on the size of the estimate. InnoDB uses this variable-size buffering capability to take advantage of row prefetching, and to reduce the overhead of latching and B-tree navigation.
• All threads share the MyISAM key buffer. The key_buffer_size system variable determines its size.
For each MyISAM table the server opens, the index file is opened once; the data file is opened once for each concurrently【kənˈkɜrəntli 同时;兼;】 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.
• The myisam_use_mmap system variable can be set to 1 to enable memory-mapping for all MyISAM tables.
• 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. As of MySQL 8.0.16, on-disk temporary tables always use the InnoDB storage engine. (Previously, the storage engine employed for this purpose was determined by the internal_tmp_disk_storage_engine system variable, which is no longer supported.)
For MEMORY tables explicitly created with CREATE TABLE, only the max_heap_table_size system variable determines how large a table can grow, and there is no conversion to on-disk format.
• The MySQL Performance Schema is a feature for monitoring MySQL server execution at a low level. The Performance Schema dynamically allocates memory incrementally, scaling its memory use to actual server load, instead of allocating required memory during server startup. Once memory is allocated, it is not freed until the server is restarted.
• Each thread that the server uses to manage client connections requires some thread-specific space. The following list indicates these and which system variables control their size:
• A stack (thread_stack)
• A connection buffer (net_buffer_length)
• A result buffer (net_buffer_length)
The connection buffer and result buffer each begin with a size equal to net_buffer_length bytes, but are dynamically enlarged up to max_allowed_packet bytes as needed. The result buffer shrinks to net_buffer_length bytes after each SQL statement. While a statement is running, a copy of the current statement string is also allocated.
Each connection thread uses memory for computing statement digests. The server allocates max_digest_length bytes per session.
• All threads share the same base memory.
• When a thread is no longer needed, the memory allocated to it is released and returned to the system unless the thread goes back into the thread cache. In that case, the memory remains allocated.
• Each request that performs a sequential scan of a table allocates a read buffer. The read_buffer_size system variable determines the buffer size.
• When reading rows in an arbitrary sequence (for example, following a sort), a random-read buffer may be allocated to avoid disk seeks. The read_rnd_buffer_size system variable determines the buffer size.
• All joins are executed in a single pass, and most joins can be done without even using a temporary table. Most temporary tables are memory-based hash tables. Temporary tables with a large row length (calculated as the sum of all column lengths) or that contain BLOB columns are stored on disk.
• Most requests that perform a sort allocate a sort buffer and zero to two temporary files depending on the result set size.
• Almost all parsing and calculating is done in thread-local and reusable memory pools. No memory overhead is needed for small items, thus avoiding the normal slow memory allocation and freeing. Memory is allocated only for unexpectedly large strings.
• For each table having BLOB columns, a buffer is enlarged dynamically to read in larger BLOB values. If you scan a table, the buffer grows as large as the largest BLOB value.
• MySQL requires memory and descriptors for the table cache. Handler structures for all in-use tables are saved in the table cache and managed as “First In, First Out” (FIFO). The table_open_cache system variable defines the initial table cache size.
MySQL also requires memory for the table definition cache. The table_definition_cache system variable defines the number of table definitions that can be stored in the table definition cache. If you use a large number of tables, you can create a large table definition cache to speed up the opening of tables. The table definition cache takes less space and does not use file descriptors, unlike the table cache.
• A FLUSH TABLES statement or mysqladmin flush-tables command closes all tables that are not in use at once and marks all in-use tables to be closed when the currently executing thread finishes. This effectively frees most in-use memory. FLUSH TABLES does not return until all tables have been closed.
• The server caches information in memory as a result of GRANT, CREATE USER, CREATE SERVER, and INSTALL PLUGIN statements. This memory is not released by the corresponding REVOKE, DROP USER, DROP SERVER, and UNINSTALL PLUGIN statements, so for a server that executes many instances of the statements that cause caching, there is an increase in cached memory use unless it is freed with FLUSH PRIVILEGES.
• In a replication topology, the following settings affect memory usage, and can be adjusted as required:
- The max_allowed_packet system variable on a replication source limits the maximum message size that the source sends to its replicas for processing. This setting defaults to 64M.
- The system variable replica_pending_jobs_size_max (from MySQL 8.0.26) or slave_pending_jobs_size_max (before MySQL 8.0.26) on a multithreaded replica sets the maximum amount of memory that is made available for holding messages awaiting processing. This setting defaults to 128M. The memory is only allocated when needed, but it might be used if your replication topology handles large transactions sometimes. It is a soft limit, and larger transactions can be processed.
- The rpl_read_size system variable on a replication source or replica controls the minimum amount of data in bytes that is read from the binary log files and relay log files. The default is 8192 bytes. A buffer the size of this value is allocated for each thread that reads from the binary log and relay log files, including dump threads on sources and coordinator threads on replicas.
- The binlog_transaction_dependency_history_size system variable limits the number of row hashes held as an in-memory history.
- The max_binlog_cache_size system variable specifies the upper limit of memory usage by an individual transaction.
- The max_binlog_stmt_cache_size system variable specifies the upper limit of memory usage by the statement cache.
ps and other system status programs may report that mysqld uses a lot of memory. This may be caused by thread stacks on different memory addresses. For example, the Solaris version of ps counts the unused memory between stacks as used memory. To verify this, check available swap with swap -s. We test mysqld with several memory-leakage detectors (both commercial and Open Source), so there should be no memory leaks.
3.2 Monitoring MySQL Memory Usage
The following example demonstrates how to use Performance Schema and sys schema to monitor MySQL memory usage.
Most Performance Schema memory instrumentation【ˌɪnstrəmenˈteɪʃn (一套)仪器，仪表;器乐谱写;】 is disabled by default. Instruments can be enabled by updating the ENABLED column of the Performance Schema setup_instruments table. Memory instruments have names in the form of memory/code_area/instrument_name, where code_area is a value such as sql or innodb, and instrument_name is the instrument detail.
1. To view available MySQL memory instruments, query the Performance Schema setup_instruments table. The following query returns hundreds of memory instruments for all code areas.
mysql> SELECT * FROM performance_schema.setup_instruments WHERE NAME LIKE '%memory%';
You can narrow【ˈnæroʊ 缩小;变窄;使窄小;】 results by specifying a code area. For example, you can limit results to InnoDB memory instruments by specifying innodb as the code area.
mysql> SELECT * FROM performance_schema.setup_instruments WHERE NAME LIKE '%memory/innodb%'; +-------------------------------------------+---------+-------+ | NAME | ENABLED | TIMED | +-------------------------------------------+---------+-------+ | memory/innodb/adaptive hash index | NO | NO | | memory/innodb/buf_buf_pool | NO | NO | | memory/innodb/dict_stats_bg_recalc_pool_t | NO | NO | | memory/innodb/dict_stats_index_map_t | NO | NO | | memory/innodb/dict_stats_n_diff_on_level | NO | NO | | memory/innodb/other | NO | NO | | memory/innodb/row_log_buf | NO | NO | | memory/innodb/row_merge_sort | NO | NO | | memory/innodb/std | NO | NO | | memory/innodb/trx_sys_t::rw_trx_ids | NO | NO | ...
Depending on your MySQL installation, code areas may include performance_schema, sql, client, innodb, myisam, csv, memory, blackhole, archive, partition, and others.
2. To enable memory instruments, add a performance-schema-instrument rule to your MySQL configuration file. For example, to enable all memory instruments, add this rule to your configuration file and restart the server:
performance-schema-instrument='memory/%=COUNTED'
【Enabling memory instruments at startup ensures that memory allocations that occur at startup are counted.】
After restarting the server, the ENABLED column of the Performance Schema setup_instruments table should report YES for memory instruments that you enabled. The TIMED column in the setup_instruments table is ignored for memory instruments because memory operations are not timed.
mysql> SELECT * FROM performance_schema.setup_instruments WHERE NAME LIKE '%memory/innodb%'; +-------------------------------------------+---------+-------+ | NAME | ENABLED | TIMED | +-------------------------------------------+---------+-------+ | memory/innodb/adaptive hash index | NO | NO | | memory/innodb/buf_buf_pool | NO | NO | | memory/innodb/dict_stats_bg_recalc_pool_t | NO | NO | | memory/innodb/dict_stats_index_map_t | NO | NO | | memory/innodb/dict_stats_n_diff_on_level | NO | NO | | memory/innodb/other | NO | NO | | memory/innodb/row_log_buf | NO | NO | | memory/innodb/row_merge_sort | NO | NO | | memory/innodb/std | NO | NO | | memory/innodb/trx_sys_t::rw_trx_ids | NO | NO | ...
3. Query memory instrument data. In this example, memory instrument data is queried in the Performance Schema memory_summary_global_by_event_name table, which summarizes data by EVENT_NAME. The EVENT_NAME is the name of the instrument.
The following query returns memory data for the InnoDB buffer pool.
mysql> SELECT * FROM performance_schema.memory_summary_global_by_event_name WHERE EVENT_NAME LIKE 'memory/innodb/buf_buf_pool'\G EVENT_NAME: memory/innodb/buf_buf_pool COUNT_ALLOC: 1 COUNT_FREE: 0 SUM_NUMBER_OF_BYTES_ALLOC: 137428992 SUM_NUMBER_OF_BYTES_FREE: 0 LOW_COUNT_USED: 0 CURRENT_COUNT_USED: 1 HIGH_COUNT_USED: 1 LOW_NUMBER_OF_BYTES_USED: 0 CURRENT_NUMBER_OF_BYTES_USED: 137428992 HIGH_NUMBER_OF_BYTES_USED: 137428992
The same underlying data can be queried using the sys schema memory_global_by_current_bytes table, which shows current memory usage within the server globally, broken down by allocation type.
mysql> SELECT * FROM sys.memory_global_by_current_bytes WHERE event_name LIKE 'memory/innodb/buf_buf_pool'\G *************************** 1. row *************************** event_name: memory/innodb/buf_buf_pool current_count: 1 current_alloc: 131.06 MiB current_avg_alloc: 131.06 MiB high_count: 1 high_alloc: 131.06 MiB high_avg_alloc: 131.06 MiB
This sys schema query aggregates currently allocated memory (current_alloc) by code area:
mysql> SELECT SUBSTRING_INDEX(event_name,'/',2) AS code_area, FORMAT_BYTES(SUM(current_alloc)) AS current_alloc FROM sys.x$memory_global_by_current_bytes GROUP BY SUBSTRING_INDEX(event_name,'/',2) ORDER BY SUM(current_alloc) DESC; +---------------------------+---------------+ | code_area | current_alloc | +---------------------------+---------------+ | memory/innodb | 843.24 MiB | | memory/performance_schema | 81.29 MiB | | memory/mysys | 8.20 MiB | | memory/sql | 2.47 MiB | | memory/memory | 174.01 KiB | | memory/myisam | 46.53 KiB | | memory/blackhole | 512 bytes | | memory/federated | 512 bytes | | memory/csv | 512 bytes | | memory/vio | 496 bytes | +---------------------------+---------------+
【Prior to MySQL 8.0.16, sys.format_bytes() was used for FORMAT_BYTES().】
3.3 Enabling Large Page Support
Some hardware and operating system architectures support memory pages greater than the default (usually 4KB). The actual implementation of this support depends on the underlying hardware and operating system. Applications that perform a lot of memory accesses may obtain performance improvements by using large pages due to reduced Translation Lookaside Buffer (TLB) misses.
In MySQL, large pages can be used by InnoDB, to allocate memory for its buffer pool and additional memory pool.
Standard use of large pages in MySQL attempts to use the largest size supported, up to 4MB. Under Solaris, a “super large pages” feature enables uses of pages up to 256MB. This feature is available for recent SPARC platforms. It can be enabled or disabled by using the --super-large-pages or --skipsuper-large-pages option
MySQL also supports the Linux implementation of large page support (which is called HugeTLB in Linux).
Before large pages can be used on Linux, the kernel must be enabled to support them and it is necessary to configure the HugeTLB memory pool. For reference, the HugeTBL API is documented in the Documentation/vm/hugetlbpage.txt file of your Linux sources.
The kernels for some recent systems such as Red Hat Enterprise Linux may have the large pages feature enabled by default. To check whether this is true for your kernel, use the following command and look for output lines containing “huge”:
$> grep -i huge /proc/meminfo AnonHugePages: 2658304 kB ShmemHugePages: 0 kB HugePages_Total: 0 HugePages_Free: 0 HugePages_Rsvd: 0 HugePages_Surp: 0 Hugepagesize: 2048 kB Hugetlb: 0 kB
The nonempty command output indicates that large page support is present, but the zero values indicate that no pages are configured for use.
If your kernel needs to be reconfigured to support large pages, consult the hugetlbpage.txt file for instructions.
Assuming that your Linux kernel has large page support enabled, configure it for use by MySQL using the following steps:
1. Determine the number of large pages needed. This is the size of the InnoDB buffer pool divided by the large page size, which we can calculate as innodb_buffer_pool_size / Hugepagesize. Assuming the default value for the buffer pool size and using the Hugepagesize value obtained from /proc/meminfo, this is 134217728 / 2048, or 65536 (64K). We call this value P.
2. As system root, open the file /etc/sysctl.conf in a text editor, and add the line shown here, where P is the number of large pages obtained in the previous step:
vm.nr_hugepages=P
Using the actual value obtained previously, the additional line should look like this:
vm.nr_huge_pages=65536
Save the updated file.
3. As system root, run the following command:
$> sudo sysctl -p
【On some systems the large pages file may be named slightly differently; for example, some distributions call it nr_hugepages. In the event sysctl returns an error relating to the file name, check the name of the corresponding file in / proc/sys/vm and use that instead.】
To verify the large page configuration, check /proc/meminfo again as described previously. Now you should see some additional nonzero values in the output, similar to this:
$> grep -i huge /proc/meminfo AnonHugePages: 2686976 kB ShmemHugePages: 0 kB HugePages_Total: 233 HugePages_Free: 233 HugePages_Rsvd: 0 HugePages_Surp: 0 Hugepagesize: 2048 kB Hugetlb: 477184 kB
4. Optionally, you may wish to compact the Linux VM. You can do this using a sequence of commands, possibly in a script file, similar to what is shown here:
sync sync sync echo 3 > /proc/sys/vm/drop_caches echo 1 > /proc/sys/vm/compact_memory
See your operating platform documentation for more information about how to do this.
5. Check any configuration files such as my.cnf used by the server, and make sure that innodb_buffer_pool_chunk_size is set larger than the huge page size. The default for this variable is 128M.
6. Large page support in the MySQL server is disabled by default. To enable it, start the server with -- large-pages. You can also do so by adding the following line to the [mysqld] section of the server my.cnf file:
large-pages=ON
With this option enabled, InnoDB uses large pages automatically for its buffer pool and additional memory pool. If InnoDB cannot do this, it falls back to use of traditional memory and writes a warning to the error log: Warning: Using conventional memory pool.
You can verify that MySQL is now using large pages by checking /proc/meminfo again after restarting mysqld, like this:
$> grep -i huge /proc/meminfo AnonHugePages: 2516992 kB ShmemHugePages: 0 kB HugePages_Total: 233 HugePages_Free: 222 HugePages_Rsvd: 55 HugePages_Surp: 0 Hugepagesize: 2048 kB Hugetlb: 477184 kB
第二部分 Measuring Performance (Benchmarking)
To measure performance, consider the following factors:
• Whether you are measuring the speed of a single operation on a quiet system, or how a set of operations (a “workload”) works over a period of time. With simple tests, you usually test how changing one aspect (a configuration setting, the set of indexes on a table, the SQL clauses in a query) affects performance. Benchmarks are typically long-running and elaborate performance tests, where the results could dictate high-level choices such as hardware and storage configuration, or how soon to upgrade to a new MySQL version.
• For benchmarking, sometimes you must simulate a heavy database workload to get an accurate picture.
• Performance can vary depending on so many different factors that a difference of a few percentage points might not be a decisive victory. The results might shift the opposite way when you test in a different environment.
• Certain MySQL features help or do not help performance depending on the workload. For completeness, always test performance with those features turned on and turned off. The most important feature to try with each workload is the adaptive hash index for InnoDB tables.
This section progresses from simple and direct measurement techniques that a single developer can do, to more complicated ones that require additional expertise to perform and interpret the results.
1 Measuring the Speed of Expressions and Functions
To measure the speed of a specific MySQL expression or function, invoke the BENCHMARK() function using the mysql client program. Its syntax is BENCHMARK(loop_count,expr). The return value is always zero, but mysql prints a line displaying approximately how long the statement took to execute. For example:
mysql> SELECT BENCHMARK(1000000,1+1); +------------------------+ | BENCHMARK(1000000,1+1) | +------------------------+ | 0 | +------------------------+ 1 row in set (0.32 sec)
This result was obtained on a Pentium II 400MHz system. It shows that MySQL can execute 1,000,000 simple addition expressions in 0.32 seconds on that system.
The built-in MySQL functions are typically highly optimized, but there may be some exceptions. BENCHMARK() is an excellent tool for finding out if some function is a problem for your queries.
2 Using Your Own Benchmarks
Benchmark your application and database to find out where the bottlenecks are. After fixing one bottleneck (or by replacing it with a “dummy” module), you can proceed to identify the next bottleneck. Even if the overall【ˌoʊvərˈɔːl , ˈoʊvərɔːl 总体的;全面的;综合的;】 performance for your application currently is acceptable, you should at least make a plan for each bottleneck and decide how to solve it if someday you really need the extra performance.
A free benchmark suite【swiːt 一套;套;组曲(由三个或更多相关部分组成);(尤指旅馆的)一套房间;一套家具;】 is the Open Source Database Benchmark, available at http://osdb.sourceforge.net/.
It is very common for a problem to occur only when the system is very heavily loaded. We have had many customers who contact us when they have a (tested) system in production and have encountered load problems. In most cases, performance problems turn out to be due to issues of basic database design (for example, table scans are not good under high load) or problems with the operating system or libraries. Most of the time, these problems would be much easier to fix if the systems were not already in production.
To avoid problems like this, benchmark your whole application under the worst possible load:
• The mysqlslap program can be helpful for simulating a high load produced by multiple clients issuing queries simultaneously.
• You can also try benchmarking packages such as SysBench and DBT2, available at https:// launchpad.net/sysbench, and http://osdldbt.sourceforge.net/#dbt2.
These programs or packages can bring a system to its knees【niːz 膝盖;膝关节;膝;】, so be sure to use them only on your development systems.
3 Measuring Performance with performance_schema
You can query the tables in the performance_schema database to see real-time information about the performance characteristics of your server and the applications it is running.