MySQL Tools and Utilities

In the past, administrators typically used character-based tools and utilities to examine their MySQL environment. This software is still available, but a new class of graphical technologies makes it even easier to gain insight into MySQL's performance profile. The next two sections examine these tools and utilities, from a character-based as well as a graphical viewpoint.

Character-Based Tools and Utilities

Over the years, MySQL AB has continually improved the number of character-based database tools and diagnostics found in the MySQL products. Many of these data can be used to help determine and correct performance anomalies. The following sections look at each command in more detail, beginning with the mysqladmin utility, followed by several flavors of the SHOW command that specifically report performance-related information.

mysqladmin

Mysqladmin provides a host of administrative and diagnostic functions for maintaining and tuning your MySQL database server. The following lists some of the commands that mysqladmin offers from a performance monitoring and management perspective:

 extended-status       Show system status flush-hosts           Flush all cached hosts flush-logs            Flush all logs flush-status          Clear status variables flush-tables          Flush all tables flush-threads         Flush the thread cache flush-privileges      Reload grant tables (same as reload) kill id,id,...        Kill mysql threads processlist           Show list of active threads in server refresh               Flush all tables and close and open logfiles variables             Prints variables available 

Specific use cases for these commands are cited throughout the book.

SHOW ENGINES

As you'll soon see, MySQL offers database developers and administrators a wide variety of storage engines. The situations in which each engine is appropriate are discussed as part of Chapter 4, "Designing for Speed," a detailed discussion on designing for speed. For now, to see the engines supported by MySQL's port for your operating system, run the SHOW ENGINES command:

 mysql> SHOW ENGINES; +------------+---------+-----------------------------------------------------------+ | Engine     | Support | Comment                                                   | +------------+---------+-----------------------------------------------------------+ | MyISAM     | YES     | Default engine as of MySQL 3.23 with great performance    | | HEAP       | YES     | Alias for MEMORY                                          | | MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables | | MERGE      | YES     | Collection of identical MyISAM tables                     | | MRG_MYISAM | YES     | Alias for MERGE                                           | | ISAM       | NO      | Obsolete storage engine, now replaced by MyISAM           | | MRG_ISAM   | NO      | Obsolete storage engine, now replaced by MERGE            | | InnoDB     | DEFAULT | Supports transactions, row-level locking, and foreign keys| | INNOBASE   | YES     | Alias for INNODB                                          | | BDB        | YES     | Supports transactions and page-level locking              | | BERKELEYDB | YES     | Alias for BDB                                             | | NDBCLUSTER | NO      | Clustered, fault-tolerant, memory-based tables            | | NDB        | NO      | Alias for NDBCLUSTER                                      | | EXAMPLE    | NO      | Example storage engine                                    | | ARCHIVE    | NO      | Archive storage engine                                    | | CSV        | NO      | CSV storage engine                                        | +------------+---------+-----------------------------------------------------------+ 

In addition to this information, MySQL reports on your current default storage engine. This is the engine setting that all new tables will have unless explicitly specified otherwise when creating the table.

SHOW VARIABLES

MySQL is highly configurable: Administrators have dozens of settings to tinker with to affect a host of database behaviors, including performance. To see your current settings, run SHOW VARIABLES, as shown in Figure 2.1.

Figure 2.1. A selection of output from the SHOW VARIABLES command.

As you can see from this small subset, the amount of information can be overwhelming, especially if you're looking for a particular variable's value. You can narrow the scope of SHOW VARIABLES by including the LIKE option, as shown in Figure 2.2.

Figure 2.2. A more restrictive collection of SHOW VARIABLES output.

The differences between SESSION and GLOBAL variables are described a little later; in addition, many of these settings are discussed throughout the book.

SET/SELECT @@

The SHOW VARIABLES command doesn't tell the whole story: A number of system variables are viewed and set via SQL. Performance-affecting variables include the following:

• AUTOCOMMIT

• BIG_TABLES

• FOREIGN_KEY_CHECKS

• SQL_BIG_SELECTS

• SQL_BUFFER_RESULT

• SQL_LOG_BIN

• SQL_LOG_OFF

• SQL_LOG_UPDATE

• SQL_SELECT_LIMIT

• UNIQUE_CHECKS

How these variables affect performance is explored throughout the rest of the book.

SHOW STATUS

MySQL captures copious real-time statistics in addition to the dozens of database server-controlling variables that you just saw. To view these statistics, run the SHOW STATUS command:

 mysql> SHOW STATUS; +--------------------------+----------+ | Variable_name            | Value    | +--------------------------+----------+ | Aborted_clients          | 2        | | Aborted_connects         | 0        | ... ... | Handler_read_first       | 2        | | Handler_read_key         | 8        | | Handler_read_next        | 8        | | Handler_read_prev        | 0        | | Handler_read_rnd         | 4        | | Handler_read_rnd_next    | 30       | ... ... | Qcache_free_blocks       | 0        | | Qcache_free_memory       | 0        | | Qcache_hits              | 0        | | Qcache_inserts           | 0        | | Qcache_lowmem_prunes     | 0        | | Qcache_not_cached        | 0        | | Qcache_queries_in_cache  | 0        | | Qcache_total_blocks      | 0        | | Questions                | 104391   | ... ... | Threads_created          | 3142     | | Threads_running          | 175      | | Uptime                   | 202083   | +--------------------------+----------+ 157 rows in set (0.00 sec) 

You can also reduce the volume of this report with the LIKE option:

 mysql> SHOW STATUS LIKE 'KEY%'; +------------------------+--------+ | Variable_name          | Value  | +------------------------+--------+ | Key_blocks_not_flushed | 0      | | Key_blocks_used        | 790    | | Key_blocks_unused      | 6458   | | Key_read_requests      | 283615 | | Key_reads              | 791    | | Key_write_requests     | 100177 | | Key_writes             | 100177 | +------------------------+--------+ 7 rows in set (0.00 sec) 

SHOW PROCESSLIST

To retrieve a report of all active threads and user connections, along with the active database operation, run SHOW PROCESSLIST, as shown in Figure 2.3.

Figure 2.3. Output from the SHOW PROCESSLIST command.

Armed with this information, you can elect to (carefully) kill a thread that is consuming too many system resources.

SHOW TABLES/SHOW TABLE STATUS

To get a simple list of the tables in your environment, just run SHOW TABLES:

 mysql> SHOW TABLES; +---------------------+------------+ | Tables_in_high_hat  | table_type | +---------------------+------------+ | awards              | BASE TABLE | | awards_pre_2000     | BASE TABLE | | ...                 | ...        | | ...                 | ...        | | v_customer          | VIEW       | | v_customer_europe   | VIEW       | | ...                 | ...        | | ...                 | ...        | +---------------------+------------+ 52 rows in set (0.00 sec) 

However, adding STATUS to this command retrieves a much more comprehensive report. Append \G to make the report display vertically:

 mysql> SHOW TABLE STATUS \G *************************** 1. row ***************************            Name: awards          Engine: MyISAM         Version: 9      Row_format: Fixed            Rows: 98483  Avg_row_length: 22     Data_length: 2166626 Max_data_length: 94489280511    Index_length: 808960       Data_free: 0  Auto_increment: 98484     Create_time: 2003-07-22 06:25:17     Update_time: 2005-12-10 16:28:37      Check_time: 2005-11-19 11:13:06       Collation: latin1        Checksum: NULL  Create_options:         Comment: ... ... *************************** 7. row ***************************            Name: lost_luggage          Engine: InnoDB         Version: 9      Row_format: Fixed            Rows: 46748  Avg_row_length: 56     Data_length: 2637824 Max_data_length: NULL    Index_length: 0       Data_free: 0  Auto_increment: 46364     Create_time: NULL     Update_time: NULL      Check_time: NULL       Collation: latin1        Checksum: NULL  Create_options:         Comment: InnoDB free: 29696 kB ...  ... *************************** 10. row ***************************            Name: v1          Engine: NULL         Version: NULL      Row_format: NULL            Rows: NULL  Avg_row_length: NULL     Data_length: NULL Max_data_length: NULL    Index_length: NULL       Data_free: NULL  Auto_increment: NULL     Create_time: NULL     Update_time: NULL      Check_time: NULL       Collation: NULL        Checksum: NULL  Create_options: NULL         Comment: view 

SHOW INDEX

Chapter 6, "Understanding the MySQL Optimizer" and Chapter 7, "Indexing Strategies," conduct detailed explorations of the MySQL optimizer and indexing, respectively. For now, you can look at the results of the SHOW INDEX command for several tables, as shown in Figure 2.4.

Figure 2.4. Output from the SHOW INDEX command for several tables.

As you'll see in a little while, these results are vitally important factors to help us determine the correct indexing strategy.

SHOW INNODB STATUS

The InnoDB storage engine adds great value to the MySQL product line. Transactional support and enhanced concurrency are just two of its powerful capabilities. InnoDB also provides extensive configuration alternatives and status reports. Running SHOW INNODB STATUS yields abundant information:

 mysql> SHOW INNODB STATUS \G *************************** 1. row *************************** Status: ===================================== 051217 18:23:21 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 6 seconds ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 8296, signal count 8263 Mutex spin waits 18975, rounds 93704, OS waits 368 RW-shared spins 15507, OS waits 7344; RW-excl spins 463, OS waits 444 ------------ TRANSACTIONS ------------ Trx id counter 0 3254792 Purge done for trx's n:o < 0 3250733 undo n:o < 0 0 Total number of lock structs in row lock hash table 947 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 0, not started, process no 30901, OS thread id 1538513840 MySQL thread id 125, query id 124924 localhost daniellej SHOW INNODB STATUS ---TRANSACTION 0 3254645, not started, process no 30901, OS thread id 1556286384 MySQL thread id 122, query id 124343 localhost jdpassos ---TRANSACTION 0 3250651, not started, process no 30901, OS thread id 1554172848 MySQL thread id 36, query id 104360 10.68.0.136 root ---TRANSACTION 0 0, not started, process no 30901, OS thread id 1538919344 ... ... MySQL thread id 34, query id 102148 10.68.0.136 root ---TRANSACTION 0 3254791, ACTIVE 1 sec, process no 30901, OS thread id 1547549616 1 lock struct(s), heap size 320, undo log entries 1 MySQL thread id 86, query id 124923 localhost fflukee ---TRANSACTION 0 3250519, ACTIVE 2145 sec, process no 30901, OS thread id 1556622256 948 lock struct(s), heap size 109888, undo log entries 560 MySQL thread id 123, query id 107797 localhost rschneider -------- 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 (write thread) Pending normal aio reads: 0, aio writes: 0,  ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 3949 OS file reads, 9852 OS file writes, 7675 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 4.17 writes/s, 3.33 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf for space 0: size 1, free list len 0, seg size 2, is empty Ibuf for space 0: size 1, free list len 0, seg size 2, 0 inserts, 0 merged recs, 0 merges Hash table size 34679, used cells 13138, node heap has 17 buffer(s) 1.83 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 0 276377354 Log flushed up to   0 276377304 Last checkpoint at  0 276377039 0 pending log writes, 0 pending chkp writes 6744 log i/o's done, 3.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 13459410; in additional pool allocated 356480 Buffer pool size   512 Free buffers       0 Database pages     489 Modified db pages  4 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 31241, created 2033, written 26128 0.00 reads/s, 0.00 creates/s, 1.00 writes/s Buffer pool hit rate 1000 / 1000 -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue Main thread process no. 30901, id 1529514928, state: sleeping Number of rows inserted 88877, updated 727017, deleted 0, read 1747870 1.83 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ 

Chapter 12, "InnoDB Parameters and Tuning," which provides guidance on tuning the InnoDB engine, illustrates how to interpret these results.

Graphical Tools

With the release of the MySQL Administrator and MySQL Query Browser, MySQL has come a long way toward matching the graphical tools offered by other major database platforms. The following sections explore each of these tools in more detail.

MySQL Administrator

This tool has many database management and monitoring capabilities. Database administrators can use the MySQL Administrator for a broad range of tasks, from setting server parameters and monitoring activity to backing up data, viewing logs, and examining performance. Its graphs and reports are also highly customizable and configurable; this capability is often overlooked by administrators who are just starting out with the tool.

The remainder of this book continually refers to MySQL Administrator. For now, the following figures show a few selected performance-monitoring-specific examples of its output.

Figure 2.5 examines the hit rates for the query and index cache.

Figure 2.5. Query cache and key buffer diagnostics from MySQL Administrator.

Figure 2.6 examines the actions of a particular database user. You have the option of looking at all threads for all users, or reviewing only the threads for one particular user.

Figure 2.6. User-specific output from MySQL Administrator.

Finally, Figure 2.7 reviews server status variables to get a better idea of the load on our system.

Figure 2.7. Server status variables as reported by MySQL Administrator.

You have dozens of available ways to use the MySQL Administrator to understand and tune your system. These concepts are all reviewed in more detail later in this book.

MySQL Query Browser

Prior to the availability of this graphical tool, your options to view a query's results were limited to the character-based mysql utility, or a third-party graphical tool such as Microsoft Access or one of many independent database query tools. This has changed; you can now use the MySQL Query Browser to perform a wide variety of database operations.

From a tuning perspective, the tool's ability to display query plans and result sets side by side is very helpful.

Figure 2.8 looks at a relatively simple query plan.

Figure 2.8. A query plan as reported by the MySQL Query Browser.

Figure 2.9 reviews the results of another query.

Figure 2.9. Results from a query as reported by the MySQL Query Browser.

Finally, Figure 2.10 splits the query screen and has rerun the query with more restrictive criteria. The split screen lets us compare the results.

Figure 2.10. Comparing the results from two different queries within the MySQL Query Browser.

Logging

MySQL offers numerous logging options to help developers and administrators investigate performance and other problems. This section takes a look at how you can use these logging settings to get more information about your environment. This section looks at both character output and graphic output from the MySQL Administrator.

General Query Log

The general query log tracks information about all queries that have been submitted to the MySQL server. Start mysqld with the l option, or set the system variable log to ON. You can also specify a filename to track the output. The following is an example of one minute's worth of data:

 051018 14:39:00             4 Init DB     high_hat        4 Query       SELECT * FROM table3 WHERE col1 BETWEEN 296561 AND 296584       12 Init DB     high_hat       12 Query       SELECT * FROM table3 WHERE col1 BETWEEN 192416 AND 192449        1 Init DB     high_hat        1 Query       SELECT * FROM table3 WHERE col1 BETWEEN 208225 AND 208251        2 Init DB     high_hat        2 Query       SELECT * FROM table3 WHERE col1 BETWEEN 41257 AND 41271       12 Init DB     high_hat       12 Query       SELECT * FROM table3 WHERE col1 BETWEEN 354771 AND 354793       24 Query       COMMIT       28 Init DB     high_hat       28 Query       BEGIN       28 Query       INSERT INTO table5 (col3) values (852031872)        2 Init DB     high_hat        2 Query       SELECT * FROM table3 WHERE col1 BETWEEN 383676 AND 383687       10 Init DB     high_hat       10 Query       SELECT * FROM table3 WHERE col1 BETWEEN 17865 AND 17879        9 Query       SHOW STATUS        9 Query       SHOW INNODB STATUS        4 Init DB     high_hat        4 Query       SELECT * FROM table3 WHERE col1 BETWEEN 50275 AND 50287       14 Init DB     high_hat       14 Query       SELECT * FROM table3 WHERE col1 BETWEEN 244074 AND 244107     1419 Query       BEGIN     1419 Query       DELETE FROM TABLE4 WHERE COL1 =  397967     1419 Query       COMMIT 

As you can see, this file has the potential to become very large. However, you have control over its size.

First, you can disable logging for a particular client simply by setting the SQL_LOG_OFF setting for that session to 1. This is particularly useful if you don't suspect a particular client (or set of clients) of causing a performance bottleneck; there's no need to intermingle their activity with that of potential problem clients.

You can also create a log rotation plan or just flush the contents periodically via the FLUSH LOGS command. The latter gives you the option of simply overlaying the existing log, or incrementing the name by one and creating a new log.

Slow Query Log

This log file captures information about queries that take longer than a predefined number of seconds to complete. MySQL tracks this information if you launch mysqld with -log-slow-queries or set the system variable log_slow_queries to the filename where you want the information stored.

You can define the threshold via the long_query_time system variable, which can be set at both the GLOBAL and SESSION levels. The information found in the slow query log is a great start toward finding and fixing performance problems, in many cases before users even notice the issues. The following is some sample output from the slow query log:

 /usr/local/mysql/bin/mysqld, Version: 5.0.1-alpha-max-log. started with: Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock Time                 Id Command    Argument # Time: 051211 14:21:07 # User@Host: [gperez] @ tty-3392 [] # Query_time: 4  Lock_time: 0  Rows_sent: 0  Rows_examined: 18701182 use high_hat; insert into report_gperez select * from flights where flight_code like '%aa%'; # Time: 041211 15:19:55 # User@Host: [rfelix] @ bellmr11710 [] # Query_time: 3  Lock_time: 0  Rows_sent: 0  Rows_examined: 0 use high_hat; ROLLBACK; # Time: 041211 15:21:51 # User@Host: [fsanford] @ crensha291 [] # Query_time: 312  Lock_time: 0  Rows_sent: 0  Rows_examined: 8609600 insert into daily_rept_fsanford select * from awards;  

Binary Log

The binary log, which replaces the earlier update log, serves many purposes. These include assisting in restoration, replication, as well providing a record of all changes that were made to the database. To enable the binary log, start mysqld with --log-bin, or set the GLOBAL variable log_bin to the filename where you want the information stored. The following shows some sample information from this log file, courtesy of the mysqlbinlog utility:

 ... ... high_hat BEGIN high_hat INSERT INTO table5 (col3) values (1887016705) high_hat COMMIT high_hat UPDATE table5 SET col2 = 21818 WHERE col1 = 1475 high_hat_dev BEGIN high_hat_dev INSERT INTO table5 (col3) values (282253923) high_hat COMMIT ... ...  

Error Log

The error log keeps a record of major events such as server start/stop, as well as any serious errors:

 Version: '5.0.1-alpha-max-log'  socket: '/var/lib/mysql/mysql.sock'  port: 3306 051216 06:29:36  mysqld started 051216  6:29:36  Warning: Changed limits: max_open_files: 1024 max_connections: 250  table_cache: 382 051216  6:29:36  InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... 051216  6:29:36  InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 0 3204554816. InnoDB: Doing recovery: scanned up to log sequence number 0 3204554816 InnoDB: Last MySQL binlog file position 0 95, file name ./HAL9000-bin.000025 051216  6:29:36  InnoDB: Flushing modified pages from the buffer pool... 051216  6:29:36  InnoDB: Started; log sequence number 0 3204554816 051216  6:29:37  Warning: Can't open time zone table: Table 'mysql.time_zone_leap_second' doesn't exist trying to live without them /usr/local/mysql/bin/mysqld: ready for connections.