How To Make MySQL Sing

First things first: my last article on MySQL boldly stated that transaction processing -- the ability to "back-out" of incomplete SQL operations -- was missing in MySQL. Not in the current version of MySQL! I was using an older version: The current version, available at http://dev.MySQL.com/downloads/, does have transaction processing. Whoops.

I updated immediately and suggest you do, too. With transaction processing, MySQL answers all my needs, absolutely.

MySQL gives up phenomenal amounts of statistical and performance information on its real-time server operation(s) through the SHOW STATUS database server command. It allows administrators, developers and those interested in optimizing RDBMS performance to get detailed information of server requests, allowing bottlenecks to be diagnosed and repaired quickly.

This information includes:

Logging onto the server and issuing the command:

MYSQL>SHOW STATUS;

brings up information and usage statistics on over 100 variables, such as thread and table usage. It's in nice tableized format, such as:

+-------------------+-------+

| Variable_name     | Value 
+-------------------+-------+
| Aborted_clients   | 2     
| Aborted_connects  | 1     
...
| Threads_created   | 32   
| Threads_connected | 2    
| Threads_running   | 1    
| Uptime            | 4710  
+---------------------------+


126 rows in set (0.00 sec)

That's a lot of rows of information! To be a bit particular in the output, a command such as:

MySQL>SHOW STATUS LIKE 'threads%';

allows some specificity in the output. This would generate a table such as:

+-------------------+-------+
| Variable_name     | Value
+-------------------+-------+
| Threads_cached    | 0
| Threads_created   | 27
| Threads_connected | 1
| Threads_running   | 1
+-------------------+-------+


4 rows in set (0.00 sec)

showing only those table entries with the word "threads" in their variable names. Likewise, the command:

MySQL>SHOW STATUS LIKE 'tables%';

shows rows, in tabular format, containing rows with the word "tables" in their variable names.

Logging onto the server as an admin and issuing a command such as:

$ MySQLadmin status

generates output such as:

Uptime:  4717 Threads:  2 Questions:  312
Slow queries: 1 Opens: 14 Flush tables: 1
Open tables: 8 Queries per second avg: 0.041

Among other interesting statistics available with SHOW STATUS, Open_tables shows the current count of tables open while the Opened_tables variable shows the total number of tables opened since the database server had last restarted. Look at the ratio between the two: A high total opened count and a low currently open may indicate a poor design with a large number of tables being opened only momentarily. Opening a table takes a bit of CPU, so it would likely be better to leave a table open for the duration of its usage. Your mileage, as they say, may vary: Each database is creates a different situation.

The Slow_queries count gives a good idea of queries that take longer than some pre-defined time-limit on a per-query basis. These queries indicate ones which are taking longer than they should: looking at the slow-query log will let you know which queries are unusually long or complex.

Qcache_hits indicates if your queries are mostly in the cache instead of having to go out to disk frequently. This number should be as high as possible: Cache is, of course, considerably faster then disk I/O.

The Select_scan status variable indicates which selects -- used mostly on joins -- on databases require a full scan of a database, resulting in this time- and resource-intensive operation. A high count here shows your queries are operating inefficiently: Examining these problem spots can help you speed up database operations.

Select_full_join shows the join count not making use of indexes. Obviously, keeping this count as low as possible is a good idea, whereas a high count indicates inefficient operation. Running an index on important, frequently queried fields can really speed up database access.

There are a number of more sophisticated status indicators which can help the savvy DBA really get a handle on their database server and its operation.

Among them are:

SHOW TABLE STATUS

Shows more table-specific information for a given database, such as the table type, size, number of rows, number and size of indexes, available space, and so on.. This provides lots of useful information on table properties without running SELECT COUNT (*) commands, which can be both tedious and time-consuming.

SHOW INNODB STATUS

Reveals internal performance statistics for the sophisticated database table handler, including large table files, crash recovery, and the aforementioned transactions (including COMMIT and ROLLBACK status). Use this command to ensure that listed values are within the best and optimal limits.

SHOW LOGS

Shows which log files are currently in use.

SHOW ERRORS

SHOW WARNINGS

These two commands show results, errors, warnings, and anything unusual generated by the last command sent to the database server.

MySQL has a great deal of power within it. It's a full-fledged database server, and as such, it needs to be cared for and administered in order to get the most out of it. Knowing what kind of information is available to you, the database administrator, can make the job a lot easier.

Ross M. Greenberg ([email protected]) is busily learning how to use transactions in MYSQL.