8.12 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 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.

 File: manual.info.tmp, Node: system-optimization, Next: disk-issues, Prev: optimizing-server, Up: optimizing-server

8.12.1 System Factors

Some system-level factors can affect performance in a major way:

 File: manual.info.tmp, Node: disk-issues, Next: symbolic-links, Prev: system-optimization, Up: optimizing-server

8.12.2 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. For information about optimizing an 'InnoDB' configuration to improve I/O performance, see *note optimizing-innodb-diskio::.

Using NFS with MySQL

You should be cautious when considering whether to use NFS with MySQL. Potential issues, which vary by operating system and NFS version, include the following:

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.

 File: manual.info.tmp, Node: symbolic-links, Next: optimizing-memory, Prev: disk-issues, Up: optimizing-server

8.12.4 Optimizing Memory Use

 File: manual.info.tmp, Node: memory-use, Next: monitor-mysql-memory-use, Prev: optimizing-memory, Up: optimizing-memory

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.

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.

'ps' and other system status programs may report that note 'mysqld': 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 note 'mysqld': mysqld. with several memory-leakage detectors (both commercial and Open Source), so there should be no memory leaks.

 File: manual.info.tmp, Node: monitor-mysql-memory-use, Next: large-page-support, Prev: memory-use, Up: optimizing-memory

8.12.4.2 Monitoring MySQL Memory Usage ......................................

The following example demonstrates how to use note Performance Schema: performance-schema. and note sys schema: sys-schema. to monitor MySQL memory usage.

Most Performance Schema memory instrumentation is disabled by default. Instruments can be enabled by updating the 'ENABLED' column of the Performance Schema *note 'setup_instruments': performance-schema-setup-instruments-table. 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 *note 'setup_instruments': performance-schema-setup-instruments-table. 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 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'

    Note:

    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 note 'setup_instruments': performance-schema-setup-instruments-table. table should report 'YES' for memory instruments that you enabled. The 'TIMED' column in the note 'setup_instruments': performance-schema-setup-instruments-table. 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 *note 'memory_summary_global_by_event_name': performance-schema-memory-summary-tables. 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. For column descriptions, see *note performance-schema-memory-summary-tables::.

      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 note 'sys': sys-schema. schema note 'memory_global_by_current_bytes': sys-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 *note 'sys': sys-schema. schema query aggregates currently allocated memory ('current_alloc') by code area:

      mysql> SELECT SUBSTRING_INDEX(event_name,'/',2) AS
         code_area, sys.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     |
  +---------------------------+---------------+

    For more information about note 'sys': sys-schema. schema, see note sys-schema::.

 File: manual.info.tmp, Node: large-page-support, Prev: monitor-mysql-memory-use, Up: optimizing-memory

8.12.4.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 *note 'InnoDB': innodb-storage-engine, 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 '--skip-super-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 'innodb_buffer_pool_size' (128MB) and using the 'Hugepagesize' value obtained from '/proc/meminfo' (2MB), this is 128MB / 2MB, or 64 Huge Pages. 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_hugepages=66

    Save the updated file.

  3. As system root, run the following command:

      $> sudo sysctl -p

    Note:

    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 *note 'mysqld': 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

 File: manual.info.tmp, Node: optimize-benchmarking, Next: thread-information, Prev: optimizing-server, Up: optimization