MySQL can provide amazing amounts of statistical and performance information--if you know how to get it.
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:
Frequency a particular command was executed
Average time taken per query
Snapshots of number of running queries and the number of open tables
Query performance, including slow queries and joins (server-wide)
Open connection information and statistics on each client
Logging onto the server and issuing the command:
brings up information and usage statistics on over 100 variables, such as thread and table usage. It's in nice tableized format, such as:
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.
Shows which log files are currently in use.
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.
The Agile ArchiveWhen it comes to managing data, donít look at backup and archiving systems as burdens and cost centers. A well-designed archive can enhance data protection and restores, ease search and e-discovery efforts, and save money by intelligently moving data from expensive primary storage systems.
2014 Analytics, BI, and Information Management SurveyITís tried for years to simplify data analytics and business intelligence efforts. Have visual analysis tools and Hadoop and NoSQL databases helped? Respondents to our 2014 InformationWeek Analytics, Business Intelligence, and Information Management Survey have a mixed outlook.