Now that you know when your servers are performing well (and when they aren’t), how do you know how well your storage engines are performing? If you are hosting one or more transactional databases or need your storage engine to perform at its peak for fast queries, you will need to monitor your storage engines. In this chapter, we discuss advanced storage engine monitoring, focusing on improving storage engine performance, by examining the two most popular storage engines: InnoDB and MyISAM. We will discuss how to monitor each and offer some practical advice on how to improve performance.
The InnoDB storage engine is the default storage engine for MySQL (as of version 5.5). InnoDB provides high reliability and high performance transactional operations that support full ACID compliance. InnoDB has been proven to be very reliable and continues to be improved. The latest improvements include multicore processor support, improved memory allocation, and finer grain performance tuning capabilities. The online reference manual contains a detailed explanation of all of the features of the InnoDB storage engine.
There are many tuning options for the InnoDB storage engine, and a thorough examination of all of them and the techniques that go along with each can fill an entire volume. For example, there are 50 variables that control the behavior of InnoDB and over 40 status variables to communicate metadata about performance and status. In this section, we discuss how to monitor the InnoDB storage engine and focus on some key areas for improving performance.
Rather than discussing the broader aspects of these areas, we provide a strategy organized into the following areas of performance improvement:
Using the SHOW ENGINE
command
Using InnoDB monitors
Monitoring logfiles
Monitoring the buffer pool
Monitoring tablespaces
Using INFORMATION_SCHEMA
tables
Using PERFORMANCE_SCHEMA
tables
Other parameters to consider
Troubleshooting InnoDB
We will discuss each of these briefly in the sections that follow. However, before we get started, let’s take a brief look at the InnoDB architectural features.
The InnoDB storage engine uses a very sophisticated architecture that is designed for high concurrency and heavy transactional activity. It has a number of advanced features that you should consider prior to attempting to improve performance. We focus on the features we can monitor and improve. These include indexes, the buffer pool, logfiles, and tablespaces.
The indexes in an InnoDB table use clustered indexes. Even if no index is specified, InnoDB assigns an internal value to each row so that it can use a clustered index. A clustered index is a data structure that stores not only the index, but also the data itself. This means once you’ve located the value in the index, you can retrieve the data without additional disk seeks. Naturally, the primary key index or first unique index on a table is built as a clustered index.
When you create a secondary index, the key from the clustered index (primary key, unique key, or row ID) is stored along with the value for the secondary index. This allows very fast search by key and fast retrieval of the original data in the clustered index. It also means you can use the primary key columns when scanning the secondary index to allow the query to use only the secondary index to retrieve data.
The buffer pool is a caching mechanism for managing transactions and writing and reading data to or from disks and, properly configured, can reduce disk access. The buffer pool is also a vital component for crash recovery, as the buffer pool is written to disk periodically (e.g., during shutdown). By default, the buffer pool state is saved in a file named ib_buffer_pool in the same directory as the InnoDB datafiles. Because the state is an in-memory component, you must monitor the effectiveness of the buffer pool to ensure it is configured correctly.
InnoDB also uses the buffer pool to store data changes and transactions. InnoDB caches changes by saving them to a page (block) of data in the buffer pool. Each time a page is referenced, it is placed in the buffer pool and when changed, it is marked as “dirty.” The changes are then written to disk to update the data and a copy is written into a redo log. These logfiles are stored as files named ib_logfile0 or ib_logfile1. You can see these files in the data directory of the MySQL server.
For more information about configuring and controlling the flushing of the buffer pool, see the section entitled “Improvements to Buffer Pool Flushing” in the online MySQL Reference Manual.
The InnoDB storage engine uses two disk-based mechanisms for storing data: logfiles and tablespaces. InnoDB also uses the logs to rebuild (or redo) data changes made prior to a shutdown or crash. On startup, InnoDB reads the logs and automatically writes the dirty pages to disk, thereby recovering buffered changes made before the crash.
Tablespaces are an organizational tool InnoDB uses as machine-independent files
that contain both data and indexes as well as a rollback mechanism (to
roll back transactions). By default, all tables share one tablespace
(called a shared tablespace). Shared tablespaces do
not automatically extend across multiple files. By default, a tablespace
takes up a single file that grows as the data grows. You can specify the autoextend
option to allow the tablespace to create new files.
You can also store tables in their own tablespaces (called file-per-table). File-per-table tablespaces contain both the data and the indexes for your tables. While there is still a central InnoDB file that is maintained, file-per-table permits you to segregate the data to different files (tablespaces). These tablespaces automatically extend across multiple files, thereby allowing you to store more data in your tables than what the operating system can handle. You can divide your tablespace into multiple files to place on different disks.
Use innodb_file_per_table
to
create a separate tablespace for each table. Any tables created
prior to setting this option will remain in the shared tablespace. Using
this command affects only new tables. It does not reduce or otherwise
save space already allocated in the shared tablespace. To apply the
change to existing tables, use the ALTER TABLE
... ENGINE=INNODB
command. Do this after you have turned on the innodb_file_per_table
feature.
The SHOW ENGINE INNODB STATUS
command (also known as the InnoDB monitor) displays statistical
and configuration information concerning the state of the InnoDB storage
engine. This is the standard way to see information about InnoDB. The
list of statistical data displayed is long and very comprehensive. Example 12-1 shows an excerpt of
the command run on a standard installation of MySQL.
mysql> SHOW ENGINE INNODB STATUS G *************************** 1. row *************************** Type: InnoDB Name: Status: ===================================== 2013-01-08 20:50:16 11abaa000 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 3 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 1 srv_active, 0 srv_shutdown, 733 srv_idle srv_master_thread log flush and writes: 734 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 2 OS WAIT ARRAY INFO: signal count 2 Mutex spin waits 1, rounds 19, OS waits 0 RW-shared spins 2, rounds 60, OS waits 2 RW-excl spins 0, rounds 0, OS waits 0 Spin rounds per wait: 19.00 mutex, 30.00 RW-shared, 0.00 RW-excl ------------ TRANSACTIONS ------------ Trx id counter 1285 Purge done for trx's n:o < 0 undo n:o < 0 state: running but idle History list length 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0, not started MySQL thread id 3, OS thread handle 0x11abaa000, query id 32 localhost 127.0.0.1 root init SHOW ENGINE INNODB STATUS -------- FILE I/O -------- I/O thread 0 state: waiting for i/o request (insert buffer thread) I/O thread 1 state: waiting for i/o request (log thread) I/O thread 2 state: waiting for i/o request (read thread) I/O thread 3 state: waiting for i/o request (read thread) I/O thread 4 state: waiting for i/o request (read thread) I/O thread 5 state: waiting for i/o request (read thread) I/O thread 6 state: waiting for i/o request (write thread) I/O thread 7 state: waiting for i/o request (write thread) I/O thread 8 state: waiting for i/o request (write thread) I/O thread 9 state: waiting for i/o request (write thread) Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] , ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 171 OS file reads, 5 OS file writes, 5 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 0, seg size 2, 0 merges merged operations: insert 0, delete mark 0, delete 0 discarded operations: insert 0, delete mark 0, delete 0 Hash table size 276671, node heap has 0 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 1625987 Log flushed up to 1625987 Pages flushed up to 1625987 Last checkpoint at 1625987 0 pending log writes, 0 pending chkp writes 8 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 137363456; in additional pool allocated 0 Dictionary memory allocated 55491 Buffer pool size 8191 Free buffers 8034 Database pages 157 Old database pages 0 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0 single page 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 157, created 0, written 1 0.00 reads/s, 0.00 creates/s, 0.00 writes/s No buffer pool page gets since the last printout Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 157, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue 0 read views open inside InnoDB Main thread id 4718366720, state: sleeping Number of rows inserted 0, updated 0, deleted 0, read 0 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ 1 row in set (0.00 sec)
The SHOW ENGINE INNODB MUTEX
command displays mutex information about InnoDB and can be very helpful when tuning threading
in the storage engine. Example 12-2 shows an excerpt of
the command run on a standard installation of MySQL.
mysql> SHOW ENGINE INNODB MUTEX; +--------+--------------------+---------------+ | Type | Name | Status | +--------+--------------------+---------------+ | InnoDB | trx/trx0rseg.c:167 | os_waits=1 | | InnoDB | trx/trx0sys.c:181 | os_waits=7 | | InnoDB | log/log0log.c:777 | os_waits=1003 | | InnoDB | buf/buf0buf.c:936 | os_waits=8 | | InnoDB | fil/fil0fil.c:1487 | os_waits=2 | | InnoDB | srv/srv0srv.c:953 | os_waits=101 | | InnoDB | log/log0log.c:833 | os_waits=323 | +--------+--------------------+---------------+ 7 rows in set (0.00 sec)
The Name
column displays the
source file and line number where the mutex was created. The Status
column displays the number of times the
mutex waited for the operating system (e.g., os_waits=5
). If the source code was compiled
with the UNIV_DEBUG
directive, the column can
display one of the following values:
count
The number of times the mutex was requested
spin_waits
The number of times a spinlock operation was run
os_waits
The number of times the mutex waited on the operating system
os_yields
The number of times a thread abandoned its time slice and returned to the operating system
os_wait_times
The amount of time the mutex waited for the operating system
The SHOW ENGINE INNODB STATUS
command displays a lot of information directly from the InnoDB storage engine. While it is
unformatted (it isn’t displayed in neat rows and columns), there are
several tools that use this information and redisplay it. For example,
the InnoTop (see InnoTop) command communicates data
this way.
The InnoDB storage engine is the only native storage engine that supports monitoring directly. Under the hood of InnoDB is a special mechanism called a monitor that gathers and reports statistical information to the server and client utilities. All of the following (and most third-party tools) interact with the monitoring facility in InnoDB, hence InnoDB monitors the following items via the MySQL server:
Table and record locks
Lock waits
Semaphore waits
File I/O requests
Buffer pool
Purge and insert buffer merge activity
The InnoDB monitors are engaged automatically via the SHOW ENGINE INNODB
STATUS
command, and the information displayed is generated by
the monitors. However, you can also get this information directly from
the InnoDB monitors by creating a special set of tables in MySQL. The
actual schema of the tables and where they reside are not important
(provided you use the ENGINE = INNODB
clause). Once they are created, each of the tables tells InnoDB to dump
the data to stderr
. You can see this
information via the MySQL error log. For example, a default install of
MySQL on Mac OS X has an error log named /usr/local/mysql/data/localhost.err. On
Windows, you can also display the output in a console by starting MySQL
with the --console
option.
To turn on the InnoDB monitors, create the following tables in a
database of your choice:
mysql> SHOW TABLES LIKE 'innodb%'; +---------------------------+ | Tables_in_test (innodb%) | +---------------------------+ | innodb_lock_monitor | | innodb_monitor | | innodb_table_monitor | | innodb_tablespace_monitor | +---------------------------+ 4 rows in set (0.00 sec)
To turn off the monitors, simply delete the table. The monitors automatically regenerate data every 15 seconds.
The tables are deleted on reboot. To continue monitoring after a reboot, you must recreate the tables.
Each monitor presents the following data:
innodb_monitor
The standard monitor that prints the same information as the
status SQL command. See Example 12-1 for an example
of the output of this monitor. The only difference between the SQL
command and the output of the innodb_monitor
is that the output to
stderr
is formatted the same
way as if you used the vertical display option in the MySQL
client.
innodb_lock_monitor
The lock monitor also displays the same information as the SQL command, but includes additional information about locks. Use this report to detect deadlocks and explore concurrency issues.
------------ TRANSACTIONS ------------ Trx id counter 2E07 Purge done for trx's n:o < 2C02 undo n:o < 0 History list length 36 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 2E06, not started mysql tables in use 1, locked 1 MySQL thread id 3, OS thread handle 0x10b2f3000, query id 30 localhost root show engine innodb status
innodb_table_monitor
The table monitor produces a detailed report of the internal data dictionary. Example 12-4 shows an excerpt of the report generated (formatted for readability). Notice the extensive data provided about each table, including the column definitions, indexes, approximate number of rows, foreign keys, and more. Use this report when diagnosing problems with tables or if you want to know the details of indexes.
=========================================== 2013-01-08 21:11:00 11dc5f000 INNODB TABLE MONITOR OUTPUT =========================================== -------------------------------------- TABLE: name SYS_DATAFILES, id 14, flags 0, columns 5, indexes 1, appr.rows 9 COLUMNS: SPACE: DATA_INT len 4; PATH: DATA_VARCHAR prtype 524292 len 0; DB_ROW_ID: DATA_SYS prtype 256 len 6; DB_TRX_ID: DATA_SYS prtype 257 len 6; DB_ROLL_PTR: DATA_SYS prtype 258 len 7; INDEX: name SYS_DATAFILES_SPACE, id 16, fields 1/4, uniq 1, type 3 root page 308, appr.key vals 9, leaf pages 1, size pages 1 FIELDS: SPACE DB_TRX_ID DB_ROLL_PTR PATH -------------------------------------- ... ----------------------------------- END OF INNODB TABLE MONITOR OUTPUT ==================================
innodb_tablespace_monitor
Displays extended information about the shared tablespace, including a list of file segments. It also validates the tablespace allocation data structures. The report can be quite detailed and very long, as it lists all of the details about your tablespace. Example 12-5 shows an excerpt of this report.
================================================ 2013-01-08 21:11:00 11dc5f000 INNODB TABLESPACE MONITOR OUTPUT ================================================ FILE SPACE INFO: id 0 size 768, free limit 576, free extents 3 not full frag extents 1: used pages 13, full frag extents 3 first seg id not used 180 SEGMENT id 1 space 0; page 2; res 2 used 2; full ext 0 fragm pages 2; free extents 0; not full extents 0: pages 0 SEGMENT id 2 space 0; page 2; res 1 used 1; full ext 0 fragm pages 1; free extents 0; not full extents 0: pages 0 SEGMENT id 3 space 0; page 2; res 1 used 1; full ext 0 fragm pages 1; free extents 0; not full extents 0: pages 0 SEGMENT id 4 space 0; page 2; res 1 used 1; full ext 0 fragm pages 1; free extents 0; not full extents 0: pages 0 SEGMENT id 5 space 0; page 2; res 1 used 1; full ext 0 fragm pages 1; free extents 0; not full extents 0: pages 0 SEGMENT id 6 space 0; page 2; res 0 used 0; full ext 0 fragm pages 0; free extents 0; not full extents 0: pages 0 SEGMENT id 7 space 0; page 2; res 1 used 1; full ext 0 fragm pages 1; free extents 0; not full extents 0: pages 0 SEGMENT id 8 space 0; page 2; res 0 used 0; full ext 0 fragm pages 0; free extents 0; not full extents 0: pages 0 SEGMENT id 9 space 0; page 2; res 1 used 1; full ext 0 fragm pages 1; free extents 0; not full extents 0: pages 0 SEGMENT id 10 space 0; page 2; res 0 used 0; full ext 0 fragm pages 0; free extents 0; not full extents 0: pages 0 SEGMENT id 11 space 0; page 2; res 1 used 1; full ext 0 fragm pages 1; free extents 0; not full extents 0: pages 0 SEGMENT id 12 space 0; page 2; res 0 used 0; full ext 0 fragm pages 0; free extents 0; not full extents 0: pages 0 SEGMENT id 13 space 0; page 2; res 1 used 1; full ext 0 fragm pages 1; free extents 0; not full extents 0: pages 0 SEGMENT id 14 space 0; page 2; res 0 used 0; full ext 0 fragm pages 0; free extents 0; not full extents 0: pages 0 SEGMENT id 15 space 0; page 2; res 160 used 160; full ext 2 fragm pages 32; free extents 0; not full extents 0: pages 0 SEGMENT id 16 space 0; page 2; res 1 used 1; full ext 0 fragm pages 1; free extents 0; not full extents 0: pages 0 SEGMENT id 17 space 0; page 2; res 1 used 1; full ext 0 fragm pages 1; free extents 0; not full extents 0: pages 0 SEGMENT id 18 space 0; page 2; res 1 used 1; full ext 0 fragm pages 1; free extents 0; not full extents 0: pages 0 SEGMENT id 19 space 0; page 2; res 1 used 1; full ext 0 fragm pages 1; free extents 0; not full extents 0: pages 0 SEGMENT id 20 space 0; page 2; res 1 used 1; full ext 0 fragm pages 1; free extents 0; not full extents 0: pages 0 ... NUMBER of file segments: 179 Validating tablespace Validation ok --------------------------------------- END OF INNODB TABLESPACE MONITOR OUTPUT =======================================
As you can see, the InnoDB monitor reports quite a lot of detail. Keeping these turned on for extended periods could add a substantial amount of data to your logfiles.
Because the InnoDB logfiles buffer data between your data and the operating system, keeping these files running well will ensure good performance. You can monitor the logfiles directly by watching the following system status variables:
mysql> SHOW STATUS LIKE 'InnoDB%log%'; +------------------------------+-------+ | Variable_name | Value | +------------------------------+-------+ | InnoDB_log_waits | 0 | | InnoDB_log_write_requests | 0 | | InnoDB_log_writes | 2 | | InnoDB_os_log_fsyncs | 5 | | InnoDB_os_log_pending_fsyncs | 0 | | InnoDB_os_log_pending_writes | 0 | | InnoDB_os_log_written | 1024 | +------------------------------+-------+
We saw some of this information presented by the InnoDB monitors, but you can also get detailed information about the logfiles using the following status variables:
InnoDB_log_waits
A count of the number of times the log was too small (i.e., did not have enough room for all of the data) and the operation had to wait for the log to be flushed. If this value begins to increase and remains higher than zero for long periods (except perhaps during bulk operations), you may want to increase the size of the logfiles.
InnoDB_log_write_requests
InnoDB_log_writes
InnoDB_os_log_fsyncs
The number of operating system file syncs (i.e., fsync()
method calls).
InnoDB_os_log_pending_fsyncs
The number of pending file sync requests. If this value begins to increase and stays above zero for an extended period of time, you may want to investigate possible disk access issues.
InnoDB_os_log_pending_writes
The number of pending log write requests. If this value begins to increase and stays higher than zero for an extended period of time, you may want to investigate possible disk access issues.
InnoDB_os_log_written
Because all of these options present numerical information, you can build your own custom graphs in MySQL Workbench to display the information in graphical form.
The buffer pool is where InnoDB caches frequently accessed data. Any changes you make to the data in the buffer pool are also cached. The buffer pool also stores information about current transactions. Thus, the buffer pool is a critical mechanism used for performance.
You can view information about the behavior of the buffer pool
using the SHOW ENGINE INNODB
STATUS
command, as shown in Example 12-1. We repeat the buffer
pool and memory section here for your convenience:
---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 138805248; in additional pool allocated 0 Dictionary memory allocated 70560 Buffer pool size 8192 Free buffers 760 Database pages 6988 Modified db pages 113 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 21, created 6968, written 10043 0.00 reads/s, 89.91 creates/s, 125.87 writes/s Buffer pool hit rate 1000 / 1000 LRU len: 6988, unzip_LRU len: 0 I/O sum[9786]:cur[259], unzip sum[0]:cur[0]
The critical items to watch for in this report are listed here (we discuss more specific status variables later):
The number of buffer segments that are empty and available for buffering data.
The number of pages that have changes (dirty).
The number of reads waiting. This value should remain low.
The number of writes waiting. This value should remain low.
A ratio of the number of successful buffer hits to the number of all requests. You want this value to remain as close as possible to 1:1.
There are a number of status variables you can use to see this information in greater detail. The following shows the InnoDB buffer pool status variables:
mysql> SHOW STATUS LIKE 'InnoDB%buf%'; +-----------------------------------+-------+ | Variable_name | Value | +-----------------------------------+-------+ | InnoDB_buffer_pool_pages_data | 21 | | InnoDB_buffer_pool_pages_dirty | 0 | | InnoDB_buffer_pool_pages_flushed | 1 | | InnoDB_buffer_pool_pages_free | 8171 | | InnoDB_buffer_pool_pages_misc | 0 | | InnoDB_buffer_pool_pages_total | 8192 | | InnoDB_buffer_pool_read_ahead_rnd | 0 | | InnoDB_buffer_pool_read_ahead_seq | 0 | | InnoDB_buffer_pool_read_requests | 558 | | InnoDB_buffer_pool_reads | 22 | | InnoDB_buffer_pool_wait_free | 0 | | InnoDB_buffer_pool_write_requests | 1 | +-----------------------------------+-------+
There are a number of status variables for the buffer pool that display key statistical information about the performance of the buffer pool. You can monitor such detailed information as the status of the pages in the buffer pool, the reads and writes from and to the buffer pool, and how often the buffer pool causes a wait for reads or writes. The following explains each status variable in more detail:
InnoDB_buffer_pool_pages_data
The number of pages containing data, including both unchanged and changed (dirty) pages.
InnoDB_buffer_pool_pages_dirty
InnoDB_buffer_pool_pages_flushed
The number of times the buffer pool pages have been flushed.
InnoDB_buffer_pool_pages_free
InnoDB_buffer_pool_pages_misc
The number of pages that are being used for administrative work by the InnoDB engine itself. This is calculated as:
X = InnoDB_buffer_pool_pages_total
–
InnoDB_buffer_pool_pages_free
–
InnoDB_buffer_pool_pages_data
InnoDB_buffer_pool_pages_total
InnoDB_buffer_pool_read_ahead_rnd
The number of random read-aheads that have occurred by InnoDB scanning for a large block of data.
InnoDB_buffer_pool_read_ahead_seq
The number of sequential read-aheads that have occurred as a result of a sequential full table scan.
InnoDB_buffer_pool_read_requests
InnoDB_buffer_pool_reads
The number of logical reads that were not found in the buffer pool and were read directly from the disk.
InnoDB_buffer_pool_wait_free
If the buffer pool is busy or there are no free pages, InnoDB may need to wait for pages to be flushed. This value is the number of times the wait occurred. If this value grows and stays higher than zero, you may have either an issue with the size of the buffer pool or a disk access issue.
InnoDB_buffer_pool_write_requests
Because all of these options present numerical data, you can build your own custom graphs in MySQL Workbench to display the information in graphical form.
InnoDB tablespaces are basically self-sufficient, provided you have allowed InnoDB to
extend them when they run low on space. You can configure tablespaces to
automatically grow using the autoextend
option for the innodb_data_file_path
variable. For example,
the default configuration of a MySQL installation sets the shared
tablespace to 10 megabytes and can automatically extend to more
files:
--innodb_data_file_path=ibdata1:10M:autoextend
See the “InnoDB Configuration” section in the online MySQL Reference Manual for more details.
You can see the current configuration of your tablespaces using
the SHOW ENGINE INNODB
STATUS
command, and you can see the details of the tablespaces
by turning on the InnoDB
tablespace monitor (see the “Using Tablespace Monitors” section in the
online MySQL Reference Manual for more details).
The INFORMATION_SCHEMA database includes a number of tables devoted to InnoDB. These tables are technically views, in the sense that the data they present is not stored on disk; rather, the data is generated when the table is queried. The tables provide another way to monitor InnoDB and provide performance information to administrators. These tables are present by default in version 5.5 and later.
There are tables for monitoring compression, transactions, locks, and more. Here we’ll describe some of the available tables briefly:
Displays details and statistics for compressed tables.
Displays the same information as INNODB_CMP, but has the side effect that querying the table resets the statistics. This allows you to track statistics periodically (e.g., hourly, daily, etc.).
Displays details and statistics about compression use in the buffer pool.
Displays the same information as INNODB_CMPMEM, but has the side effect that querying the table resets the statistics. This allows you to track statistics periodically (e.g., hourly, daily, etc.).
Displays details and statistics about all transactions, including the state and query currently being processed.
Displays details and statistics about all locks requested by transactions. It describes each lock, including the state, mode, type, and more.
Displays details and statistics about all locks requested by transactions that are being blocked. It describes each lock, including the state, mode, type, and the blocking transaction.
A complete description of each table, including the columns and examples of how to use each, is presented in the online reference manual.
You can use the compression tables to monitor the compression of your tables, including such details as the page size, pages used, time spent in compression and decompression, and much more. This can be important information to monitor if you are using compression and want to ensure the overhead is not affecting the performance of your database server.
You can use the transaction and locking tables to monitor your transactions. This is a very valuable tool in keeping your transactional databases running smoothly. Most important, you can determine precisely which state each transaction is in, as well as which transactions are blocked and which are in a locked state. This information can also be critical in diagnosing complex transaction problems such as deadlock or poor performance.
Older versions of InnoDB, specifically during the MySQL version
5.1 era, were built as a plug-in storage engine. If you are using the
older InnoDB storage engine plug-in, you also have access to seven
special tables in the INFORMATION_SCHEMA
database.
You must install the INFORMATION_SCHEMA
tables
separately. For more details, see the InnoDB plug-in
documentation.
As of MySQL version 5.5, InnoDB now supports the PERFORMANCE_SCHEMA
feature of the MySQL server. The PERFORMANCE_SCHEMA
feature was introduced in
Chapter 11. For InnoDB, this means you can
monitor the internal behavior of the InnoDB subsystems. This enables you
to tune InnoDB using a general knowledge of the source code. While it is
not strictly necessary to read the InnoDB source code to use the
PERFORMANCE_SCHEMA
tables to tune
InnoDB, expert users with this knowledge can obtain more precise
performance tuning. But that comes at a price. It is possible to over
tune such that the system performs well for certain complex queries at
the expense of other queries that may not see the same performance
improvements.
To use PERFORMANCE_SCHEMA
with
InnoDB, you must have MySQL version 5.5 or later, InnoDB 1.1 or later,
and have PERFORMANCE_SCHEMA
enabled
in the server. All InnoDB-specific instances, objects, consumers, and so
on are prefixed with “innodb” in the name. For example, the following
shows a list of the active InnoDB threads (you can use this to help
isolate and monitor how InnoDB threads are performing):
mysql> SELECT thread_id, name, type FROM threads WHERE NAME LIKE '%innodb%';
+-----------+----------------------------------------+------------+
| thread_id | name | type |
+-----------+----------------------------------------+------------+
| 2 | thread/innodb/io_handler_thread | BACKGROUND |
| 3 | thread/innodb/io_handler_thread | BACKGROUND |
| 4 | thread/innodb/io_handler_thread | BACKGROUND |
| 5 | thread/innodb/io_handler_thread | BACKGROUND |
| 6 | thread/innodb/io_handler_thread | BACKGROUND |
| 7 | thread/innodb/io_handler_thread | BACKGROUND |
| 8 | thread/innodb/io_handler_thread | BACKGROUND |
| 9 | thread/innodb/io_handler_thread | BACKGROUND |
| 10 | thread/innodb/io_handler_thread | BACKGROUND |
| 11 | thread/innodb/io_handler_thread | BACKGROUND |
| 13 | thread/innodb/srv_lock_timeout_thread | BACKGROUND |
| 14 | thread/innodb/srv_error_monitor_thread | BACKGROUND |
| 15 | thread/innodb/srv_monitor_thread | BACKGROUND |
| 16 | thread/innodb/srv_purge_thread | BACKGROUND |
| 17 | thread/innodb/srv_master_thread | BACKGROUND |
| 18 | thread/innodb/page_cleaner_thread | BACKGROUND |
+-----------+----------------------------------------+------------+
16 rows in set (0.00 sec)
You can find InnoDB-specific items in the rwlock_instances
,
mutex_instances
, file_instances
, file_summary_by_event_name
, and file_summary_by_instances
tables as
well.
There are many things to monitor and tune in the InnoDB storage engine. We have discussed only a portion of those and focused mainly on monitoring the various subsystems and improving performance. However, there are a few other items you may want to consider.
Thread performance can be improved under certain circumstances by
adjusting the innodb_thread_concurrency
option. The
default value is zero in MySQL version 5.5 and later (8 in
prior versions), meaning that there is infinite concurrency or many
threads executing in the storage engine. This is usually sufficient, but
if you are running MySQL on a server with many processors and many
independent disks (and heavy use of InnoDB), you may see a performance
increase by setting this value equal to the number of processors plus
independent disks. This ensures InnoDB will use enough threads to allow
maximum concurrent operations. Setting this value to a value greater
than what your server can support has little or no effect—if there aren’t any available
threads, the limit will never be reached.
If your MySQL server is part of a system that is shut down
frequently or even periodically (e.g., you run MySQL at startup on your
Linux laptop), you may notice when using InnoDB that shutdown can take a
long time to complete. Fortunately, InnoDB can be shut down quickly by
setting the innodb_fast_shutdown
option. This does not affect data integrity nor will it result in
a loss of memory (buffer) management. It simply skips the potentially
expensive operations of purging the internal caches and merging insert
buffers. It still performs a controlled shutdown, storing the buffer
pools on disk.
By setting the innodb_lock_wait_timeout
variable, you
can control how InnoDB deals with deadlocks. This variable
exists at both the global and session level, and controls how long
InnoDB will allow a transaction to wait for a row lock before aborting.
The default value is 50 seconds. If you are seeing a lot of lock-wait
timeouts, you can decrease the value to decrease the amount of time your
locks wait. This may help diagnose some of your concurrency problems or
at least allow your queries to time out sooner.
If you are importing lots of data, you can improve load time by
making sure your incoming datafiles are sorted in primary key order. In
addition, you can turn off the automatic commit by setting AUTOCOMMIT
to 0. This ensures the entire load
is committed only once. You can also improve bulk load by turning off
foreign key and unique constraints.
Remember, you should approach tuning InnoDB with great care. With so many things to tweak and adjust, it can be very easy for things to go wrong quickly. Be sure to follow the practice of changing one variable at a time (and only with a purpose) and measure, measure, measure.
The best tools to use are those listed earlier, including the InnoDB monitors,
the SHOW ENGINE INNODB STATUS
command
(another way to display the data from the InnoDB
monitors), and the PERFORMANCE_SCHEMA
features.
However, there are additional strategies that you may find helpful in
dealing with errors using InnoDB. This section provides some general
best practices for troubleshooting InnoDB problems. Use these practices
when faced with trying to solve errors, warnings, and data corruption
issues.
When encountering errors related to InnoDB, find the error
information in the error log. To turn on the error log, use the
--log-error
startup option.
If you encounter multiple deadlock failures, you can use the option
--innodb_print_all_deadlocks
(available
in version 5.5 and later) to write all deadlock messages
to the error log. This allows you to see more than the last deadlock
as shown in the SHOW ENGINE INNODB
STATUS
and may be informative if your application does not
have its own error handlers to deal with deadlocks.
Table definitions are stored in the .frm files having the same name as the table and stored under folders by database name. Definitions are also stored in the InnoDB data dictionary. If there is a storage corruption or file broken, you can encounter errors related to a mismatched data dictionary. Here are some of the more common symptoms and solutions:
If an ALTER TABLE
operation fails, the server may not clean up correctly,
leaving a temporary table in the InnoDB tablespace. When this
occurs, you can use the table monitor to identify the table name
(temporary tables are named starting with
#sql
). You can then issue a DROP
TABLE
to drop this table to eliminate the orphaned
table.
If you see an error like Can't open
file: 'somename.innodb'
along with an error message in
the error log like Cannot find table
, it
means there is an orphaned file named
somedb/somename
...somename
.frm inside the database folder. In
this case, deleting the orphaned .frm file will correct the
problem.
If you are using the --innodb_file_per_table
option and
encounter an error similar to InnoDB data dictionary has tablespace id
, you must drop the table and
recreate it. However, it is not that simple. You must first
recreate the table in another database, locate the .frm there and copy it to the
original database, then drop the table. This may generate a
warning about a missing .ibd file but it will correct the
data dictionary. From there, you can recreate the table and
restore the data from backup.N
, but tablespace with the id or name
does not exist...
If you encounter an error in the error log that tells you the table already exists in the data dictionary, you may have a case where there is no corresponding .frm file for the table in question. When this occurs, follow the directions in the error log.
Some errors and warnings are only printed to the console (e.g.,
stdout
, stderr
). When
troubleshooting errors or warnings, it is sometimes best to launch
MySQL via the command line instead of using the
mysqld_safe script. On Windows, you can use
the --console
option to prevent suppression
of console messages.
I/O problems are generally encountered on startup or when new objects are created or dropped. These types of errors are associated with the InnoDB files and vary in severity. Unfortunately, these problems are normally very specific to the platform or OS and therefore may require specific steps to correct. As a general strategy, always check your error log or console for errors, checking particularly for OS-specific errors because these can indicate why I/O errors are occurring. Also check for missing or corrupt folders in the data directory along with properly named InnoDB files.
You can also experience I/O problems when there are problems with the data disks. These normally appear at startup but can occur anytime there are disk read or write errors. Hardware errors can sometimes be mistaken for performance problems. Be sure to check your operating system’s disk diagnosis as part of your troubleshooting routine.
Sometimes the problem is a configuration issue. In this case,
you should double-check your configuration file to ensure InnoDB is
properly configured. For example, check to ensure the innodb_data_*
options are set
correctly.
If you encounter severe or critical errors in your databases that cause
InnoDB to crash or keep the server from starting, you can launch the
server with the innodb_force_recovery
recovery option in your configuration file, assigning it an
integer value ranging from 1 to 6 that causes InnoDB to skip certain
operations during startup.
This option is considered a last resort option and should be used only in the most extreme cases where all other attempts to start the server have failed. You should also export the data prior to attempting the procedure.
Here’s a brief description of each option (more information can be found in the online reference manual):
Skip corrupt pages when select statements are issued. Allows partial data recovery.
Do not start the master or purge thread.
Do not execute rollbacks after crash recovery.
Do not execute insert buffer operations. Do not calculate table statistics.
Ignore undo logs on startup.
There are very few things to monitor on the MyISAM storage engine. This is because the MyISAM storage engine was built for web applications with a focus on fast queries and, as such, has only one feature in the server that you can tune—the key cache. That doesn’t mean there is nothing else that you can do to improve performance. On the contrary, there are many things you can do, including using options like low priority and concurrent inserts. Most fall into one of three areas: optimizing storage on disk, using memory efficiently by monitoring and tuning the key cache, and tuning your tables for maximum performance.
Rather than discussing the broader aspects of these areas, we provide a strategy organized into the following areas of performance improvement:
Optimizing disk storage
Tuning your tables for performance
Using the MyISAM utilities
Storing a table in index order
Compressing tables
Defragmenting tables
Monitoring the key cache
Preloading key caches
Using multiple key caches
Other parameters to consider
We will discuss each of these briefly in the sections that follow.
Optimizing disk space for MyISAM is more of a system configuration option than a
MyISAM-specific tuning parameter. MyISAM stores each table as its own
.myd (datafile) and one or more
.myi (index) files. They are stored
with the .frm file in the folder
under the name of the database in the data directory specified by
the --datadir
startup
option. Thus, optimizing disk space for MyISAM is the same as optimizing
disk space for the server (i.e., you can see performance improvements by
moving the data directory to its own disk, and you can further improve
performance of the disk with RAID or other high availability storage
options).
The latest release of MySQL Utilities includes a new utility
named the .frm reader
(mysqlfrm), which allows you to read .frm files and
produce the CREATE
statement for
the table. You can use this utility whenever you need to
diagnose problems with .frm files. See the MySQL
Utilities documentation for more information about the
.frm reader.
There are a couple of SQL commands that you can use to keep your tables in
optimal condition. These include the ANALYZE
TABLE
, OPTIMIZE TABLE
, and
REPAIR TABLE
commands.
The ANALYZE TABLE
command
examines and reorganizes the key distribution for a table.
The MySQL server uses the key distribution to determine the
join order when joining on a field other than a constant. Key
distributions also determine which indexes to use for a query. We
discuss this command in more detail in Using ANALYZE TABLE.
The REPAIR TABLE
command is not
really a performance tool—you can use it to fix a
corrupted table for the MyISAM, Archive, and CSV storage engines. Use this command to try to recover
tables that have become corrupt or are performing very poorly (which is
usually a sign that a table has degraded and needs reorganizing or
repair).
Use the OPTIMIZE TABLE
command
to recover deleted blocks and reorganize the table for better
performance. You can use this command for MyISAM and InnoDB
tables.
While these commands are useful, there are a number of more advanced tools you can use to further manage your MyISAM tables.
There are a number of special utilities included in the MySQL distribution that are designed for use with the MyISAM storage engine (tables):
myisamchk is the workhorse utility for MyISAM. It can display information about your MyISAM tables or analyze, repair, and optimize them. You can run the command for one or more tables, but you can only use it when the server is running if you flush the tables and lock them. Alternatively, you can shut down the server.
Be sure to make a backup of your tables before running this utility in case the repair or optimization steps fail. In rare cases, this has been known to leave tables corrupted and irreparable.
The following list describes options related to performance improvement, recovery, and report status (see the online MySQL Reference Manual for a complete description of the available options):
analyze
Analyzes the key distribution of indexes to improve query performance.
backup
Makes a copy of the tables (the .myd file) prior to altering them.
check
Checks the table for errors (report only).
extended-check
Does a thorough check of the table for errors, including all indexes (report only).
force
Performs a repair if any errors are found.
information
Shows statistical information about the table. Use this
command first to see the condition of your table before running
recover
.
medium-check
Performs a more thorough check of the table (repair only).
This does less checking than extended-check
.
recover
Performs a comprehensive repair of the table, repairing the data structures. Repairs everything except duplicate unique keys.
safe-recover
Performs an older form of repair that reads through all rows in order and updates all of the indexes.
sort index
Sorts the index tree in high-low order. This can reduce seek time to index structures and make accessing the index faster.
sort records
Sorts the records in the order of a specified index. This can improve performance for certain index-based queries.
Example 12-6 shows the results of
running the myisamchk
command to
display information about a MyISAM table.
MyISAM file: /usr/local/mysql/data/employees/employees Record format: Packed Character set: latin1_swedish_ci (8) File-version: 1 Creation time: 2012-12-03 08:13:03 Status: changed Data records: 297024 Deleted blocks: 3000 Datafile parts: 300024 Deleted data: 95712 Datafile pointer (bytes): 6 Keyfile pointer (bytes): 6 Datafile length: 9561268 Keyfile length: 3086336 Max datafile length: 281474976710654 Max keyfile length: 288230376151710719 Recordlength: 44 table description: Key Start Len Index Type Rec/key Root Blocksize 1 1 4 unique long 1 2931712 1024
You can improve the efficiency of large data retrieval for range
queries (e.g., WHERE
a > 5
AND a < 15
) by storing table data in the
same order as it is stored in the index. This type of ordering allows
the query to access data in order, without having to search the disk
pages for the data. To sort a table in index order, use the
myisamchk utility sort records option (-R
) and
specify which index you want to use, starting with number 1 for the
first index. The following command sorts table1
from the test
database in the order of the second
index:
myisamchk -R 2 /usr/local/mysql/data/test/table1
You can accomplish the same effect using ALTER
TABLE
and ORDER BY
.
Sorting the table like this does not ensure the order will remain by index when new rows are added. Deletions do not disturb the order, but as new rows are added the table can become less ordered and cause your performance advantages to dwindle. If you use this technique for tables that change frequently, you may want to consider running the command periodically to ensure optimal ordering.
Compressing data saves space. While there are methods to compress data in MySQL, the MyISAM storage engine allows you to compress (pack) read-only tables to save space. They must be read-only because MyISAM does not have the capability to decompress, reorder, or compress additions (or deletions). To compress a table, use the myisampack utility as follows:
myisampack -b /usr/local/mysql/data/test/table1
Always use the backup (-b
)
option to create a backup of your table prior to compressing it. This
will allow you to make the table writable without having to rerun the
myisampack
command.
There are two reasons for compressing a read-only table. First and foremost, it can save a lot of space for tables that include data that is easy to compress (e.g., text). Second, when a compressed table is read and the query uses the primary key or unique index to find a row, only the single-row data is decompressed prior to additional comparisons.
There are a great many options to the myisampack command. If you are interested in compressing your read-only tables for space, see the online MySQL Reference Manual for more details about how to control the compression features.
When MyISAM tables have had a lot of changes in the form of deletions and
insertions, the physical storage can become fragmented. Often there are
small gaps in the physical store representing deleted data, or there are
records stored out of the original order, or both. To optimize the table
and reorganize it back to the desired order and form, use either the
OPTIMIZE TABLE
command or the
myisamchk utility.
You should run these commands periodically for tables where you have specified a sort order to ensure they are stored in the most optimal manner. You should also run one of these commands when data has undergone many changes over a period of time.
The key cache in MySQL is a very efficient structure designed to store frequently used index data. It is used exclusively for MyISAM and stores the keys using a fast lookup mechanism (usually a B-tree). The indexes are stored internally (in memory) as linked lists and can be searched very quickly. The key cache is created automatically when the first MyISAM table is opened for reading. Each time a query is issued for a MyISAM table, the key cache is examined first. If the index is found there, it performs the index search in memory rather than reading the index from disk first. The key cache is the secret weapon that makes MyISAM so much faster for rapid queries than some other storage engines.
A number of variables control the key cache, and you can monitor each using the SHOW VARIABLES
and SHOW STATUS
commands. The variables you can
monitor with the SHOW
commands are
shown in Example 12-7.
mysql> SHOW STATUS LIKE 'Key%'; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | Key_blocks_not_flushed | 0 | | Key_blocks_unused | 6694 | | Key_blocks_used | 0 | | Key_read_requests | 0 | | Key_reads | 0 | | Key_write_requests | 0 | | Key_writes | 0 | +------------------------+-------+ 7 rows in set (0.00 sec) mysql> SHOW VARIABLES LIKE 'key%'; +--------------------------+---------+ | Variable_name | Value | +--------------------------+---------+ | key_buffer_size | 8384512 | | key_cache_age_threshold | 300 | | key_cache_block_size | 1024 | | key_cache_division_limit | 100 | +--------------------------+---------+ 4 rows in set (0.01 sec)
As you can imagine, the key cache can be a very complicated mechanism. Tuning the key cache can therefore be a challenge. We recommend monitoring usage and making changes to the size of the key cache rather than changing how it performs, as it performs very well in the default configuration.
If you want to improve cache hit behavior, use one of the two techniques discussed next: preloading the cache and using multiple key caches along with adding more memory to the default key cache.
You can preload your indexes into the key cache. This ensures your queries will
be faster, because the index is already loaded in the key cache and the
index is loaded sequentially (rather than randomly, as would occur when
the key cache is loaded under concurrent operation, for example).
However, you must ensure there is enough room in the cache to hold the
index. Preloading can be a very effective way to speed up your queries
for certain applications or modes of use. For example, if you know there
will be a lot of queries against a particular table during the execution
of an application (e.g., a typical payroll audit), you can preload the
associated table indexes into the key cache, thereby improving
performance during this activity. To preload the key cache for a table,
use the LOAD INDEX
command
as shown in Example 12-8.
mysql> LOAD INDEX INTO CACHE salaries IGNORE LEAVES; +--------------------+--------------+----------+----------+ | Table | Op | Msg_type | Msg_text | +--------------------+--------------+----------+----------+ | employees.salaries | preload_keys | status | OK | +--------------------+--------------+----------+----------+ 1 row in set (1.49 sec)
This example loads the index for the salary
table into the key cache. The IGNORE LEAVES
clause preloads only the blocks for the nonleaf nodes of the index.
While there is no special command to flush the key cache, you can
forcibly remove an index from the key cache by modifying the table—for
example, by reorganizing the index or simply dropping and recreating the
index.
One of the little-known advanced features of MySQL is the creation of multiple key caches or custom key caches to reduce contention for the default key cache. This feature allows you to load the index of one or more tables into a special cache that you configure yourself. As you can imagine, this means allocating memory to the task and as such requires careful planning. However, the performance benefits may be substantial if there are periods in which you are executing many queries against a set of tables where the indexes are frequently referenced.
To create a secondary key cache, first define it with the SET
command by allocating memory, then issue
one or more CACHE INDEX
commands to
load the indexes for one or more tables. Unlike the default key cache,
you can flush or remove a secondary key cache by setting its size to 0.
Example 12-9 shows how to create a
secondary key cache and add the index for a table to the cache.
mysql> SET GLOBAL emp_cache.key_buffer_size=128*1024; Query OK, 0 rows affected (0.00 sec) mysql> CACHE INDEX salaries IN emp_cache; +--------------------+--------------------+----------+----------+ | Table | Op | Msg_type | Msg_text | +--------------------+--------------------+----------+----------+ | employees.salaries | assign_to_keycache | status | OK | +--------------------+--------------------+----------+----------+ 1 row in set (0.00 sec) mysql> SET GLOBAL emp_cache.key_buffer_size=0; Query OK, 0 rows affected (0.00 sec)
Notice that the secondary cache involves defining a new variable
named emp_cache
and setting its size
to 128 KB. This is a special syntax of the SET
command and while it appears to create a
new system variable, it is actually creating a new global user variable.
You can discover the existence or size of a secondary key cache as
follows:
mysql> select @@global.emp_cache.key_buffer_size; +------------------------------------+ | @@global.emp_cache.key_buffer_size | +------------------------------------+ | 131072 | +------------------------------------+ 1 row in set (0.00 sec)
Secondary key caches are global and thus exist until you flush them by setting their size to 0 or when the server is restarted.
There are a number of other parameters to consider (remember that you should change only one thing at a time and only if you have a good reason to do so; you should never change the configuration of a complex feature like a storage engine without a good reason and reasonable expectations for the outcome):
myisam_data_pointer_size
The default pointer size in bytes (2–7) used by CREATE
TABLE
if there is no value specified for MAX_ROWS
(the maximum number of rows to
store in the table). It has a default value of 6.
myisam_max_sort_file_size
Maximum size of a temporary file used when sorting data. Increasing this value may make repairs and reorganization of index operations faster.
myisam_recover_options
The recovery mode for MyISAM. You can use this for OPTIMIZE
TABLE
as well. The modes include default, backup, force,
and quick, and can take any combination of these options. Default
means recovery is performed without a backup, force, or quick
checking. Backup means a backup will be created before recovery.
Force means recovery continues even if data is lost (more than one
row). Quick means the rows in the table are not checked if there
are no blocks marked as deleted. Consider the severity of the
recovery when determining which options to use.
myisam_repair_threads
If set to a value greater than 1, repair and sorting operations are done in parallel and can make the operations a bit faster. Otherwise, they are done sequentially.
myisam_sort_buffer_size
The size of the buffer for sorting operations. Increasing this value can help with sorting indexes. However, values greater than 4 GB work only on 64-bit machines.
myisam_stats_method
Controls how the server counts NULL
values in index value distribution
for statistical operations. This can also affect the optimizer,
so use with care.
myisam_use_mmap
Turns the memory map option on for reading and writing MyISAM tables. Can be helpful in situations where there are a lot of small writes that contend with read queries that return large data sets.
We have discussed a number of strategies for monitoring and improving MyISAM performance. While the discussion is brief, it covers the most important aspects of using MyISAM effectively. For more information about the key cache and the MyISAM storage engine, see the online MySQL Reference Manual.
This chapter examined how to monitor and improve the performance of storage engines in the MySQL server. We have discussed the specifics of two of the most popular storage engines, and in the next chapter, we turn our attention to the more advanced topic of monitoring and improving the performance of replication.