MySQL High Availability (2014)
Part II. Monitoring and Managing
Chapter 12. Storage Engine Monitoring
Joel was enjoying his morning latte when his office phone rang. It startled him because until now he had never heard it ring. He lifted the receiver and heard engine noises. Expecting the call was a wrong number, he said hesitantly, “Hello?”
“Joel! Glad I caught you.” It was Mr. Summerson, calling from his car.
“Yes, sir.”
“I’m on my way to the airport to meet with the sales staff in the Seattle office. I wanted to ask you to look into the new application database. The developers in Seattle tell me they think we need to figure out a better configuration for performance.”
Joel had expected something like this. He knew a little about InnoDB and MyISAM, but he wasn’t familiar with monitoring, much less tuning their performance. “I can look into it, sir.”
“Great. Thanks, Joel. I’ll email you.” The connection was severed before Joel could reply. Joel finished the last of his latte and started reading about storage engines in MySQL.
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.
InnoDB
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.
TIP
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.
SEPARATE TABLESPACES
One of the newest performance features permits the storage of undo logs as separate tablespaces. Because the undo logs can consume a lot of space during long-running transactions, placing the undo logs in separate or even multiple tablespaces reduces the size of the system tablespace. To place the undo logs in separate tablespaces, set the --innodb_undo_tablespaces configurationoption to a value greater than zero. You can also specify the location of the undo log tablespaces by using the --innodb_undo_directory option. MySQL assigns names to the undo log tablespaces using the form innodbn, where n is a sequential integer with leading zeros.
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.
NOTE
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=INNODBcommand. Do this after you have turned on the innodb_file_per_table feature.
Using the SHOW ENGINE Command
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.
Example 12-1. The SHOW ENGINE INNODB STATUS command
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.
Example 12-2. The SHOW ENGINE INNODB MUTEX command
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 (seeInnoTop) command communicates data this way.
Using InnoDB Monitors
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.
NOTE
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.
Example 12-3. The InnoDB lock monitor report
------------
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.
Example 12-4. The InnoDB table monitor report
===========================================
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.
Example 12-5. The InnoDB tablespace monitor 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.
Monitoring 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
The number of log write requests.
InnoDB_log_writes
The number of times data was written to the log.
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
The total number of bytes written to the log.
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.
Monitoring the Buffer Pool
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):
Free buffers
The number of buffer segments that are empty and available for buffering data.
Modified pages
The number of pages that have changes (dirty).
Pending reads
The number of reads waiting. This value should remain low.
Pending writes
The number of writes waiting. This value should remain low.
Hit rate
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
The number of pages that have changes (dirty).
InnoDB_buffer_pool_pages_flushed
The number of times the buffer pool pages have been flushed.
InnoDB_buffer_pool_pages_free
The number of empty (free) pages.
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
The total number of pages in the buffer pool.
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
The number of logical 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
The number of writes to the InnoDB buffer pool.
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.
Monitoring Tablespaces
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).
Using INFORMATION_SCHEMA Tables
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:
INNODB_CMP
Displays details and statistics for compressed tables.
INNODB_CMP_RESET
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.).
INNODB_CMPMEM
Displays details and statistics about compression use in the buffer pool.
INNODB_CMPMEM_RESET
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.).
INNODB_TRX
Displays details and statistics about all transactions, including the state and query currently being processed.
INNODB_LOCKS
Displays details and statistics about all locks requested by transactions. It describes each lock, including the state, mode, type, and more.
INNODB_LOCK_WAITS
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.
NOTE
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.
NOTE
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.
Using PERFORMANCE_SCHEMA Tables
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.
Other Parameters to Consider
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.
WARNING
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.
Troubleshooting Tips for InnoDB
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.
Errors
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.
Deadlocks
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 STATUSand may be informative if your application does not have its own error handlers to deal with deadlocks.
Data dictionary problems
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:
Orphaned temporary table
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 TABLEto drop this table to eliminate the orphaned table.
Cannot open a 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 somedb/somename..., it means there is an orphaned file named somename.frm inside the database folder. In this case, deleting the orphaned.frm file will correct the problem.
Tablespace does not exist
If you are using the --innodb_file_per_table option and encounter an error similar to InnoDB data dictionary has tablespace id N, but tablespace with the id or name does not exist..., 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.
Cannot create a table
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.
Observe console messages
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
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.
Corrupted databases
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.
CAUTION
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):
1. Skip corrupt pages when select statements are issued. Allows partial data recovery.
2. Do not start the master or purge thread.
3. Do not execute rollbacks after crash recovery.
4. Do not execute insert buffer operations. Do not calculate table statistics.
5. Ignore undo logs on startup.
6. Do not execute the redo log when running recovery.
MyISAM
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 Storage
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).
NOTE
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.
Repairing Your Tables
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.
Using the MyISAM Utilities
There are a number of special utilities included in the MySQL distribution that are designed for use with the MyISAM storage engine (tables):
§ myisam_ftdump allows you to display information about full-text indexes.
§ myisamchk allows you to perform analysis on a MyISAM table.
§ myisamlog allows you to view the change logs of a MyISAM table.
§ myisampack allows you to compress a table to minimize storage.
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.
WARNING
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.
Example 12-6. The myisamchk utility
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
Storing a Table in Index Order
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 Tables
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.
Defragmenting Tables
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.
Monitoring the Key Cache
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.
Example 12-7. The key cache status and system variables
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.
Preloading Key Caches
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.
Example 12-8. Preloading indexes into the key cache
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.
Using Multiple Key Caches
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.
Example 12-9. Using secondary key caches
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.
NOTE
You can save the configuration of multiple key caches by storing the statements in a file and using the init-file=path_to_file command in the [mysqld] section of the MySQL option file to execute the statements on startup.
Other Parameters to Consider
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.
MYSQL, REPLICATION, AND HIGH AVAILABILITY
There is a higher probability of corruption of MyISAM data than InnoDB data and, as a result, MyISAM requires longer recovery times. Also, because MyISAM does not support transactions, events are executed one at a time, which could lead to partial statement execution and therefore an incomplete transaction. Add to this the fact that the slave executes as a single thread, which could allow the slave to fall behind when processing long-running queries. Thus, using MyISAM on a slave in a high-availability solution with transactions can be problematic.
Conclusion
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.
Joel paused with his mouse over the Send button. He had prepared a report on the InnoDB monitoring data he collected and had written some recommendations in an email message to his boss. But he wasn’t sure he should send it without being asked. Shrugging, he figured it couldn’t hurt, so he hit Send.
About two minutes later, his email application popped up a box from his system tray proclaiming a new email message. Joel clicked on the message. It was from his boss.
“Joel, nice work on the InnoDB stuff. I want you to call a meeting with the developers and the IT folks. Have a sit-down and get everyone on board with your recommendations. Make it happen. I’ll be in the office on Monday.”
“OK,” Joel said, as he felt the burden of responsibility slip onto his shoulders. He realized this was his first meeting since being hired. He was a little nervous, so he decided to go for a walk before making an agenda and sending out an email message inviting people to attend. “Well, it can’t be any worse than my thesis defense.”