Following Baron Schwartz’ post: How to calculate a good InnoDB log file size, which shows how to make an estimate for the InnoDB log file size, and based on SQL: querying for status difference over time, I’ve written a query to run on MySQL 5.1, which, upon sampling 60 seconds of status, estimates the InnoDB [...]
The InnoDB plugin has a nice INFORMATION_SCHEMA concept: resetting tables. For example, the INNODB_CMP table lists information about compression operation. A similar table, INNODB_CMP_RESET, provides the same information, but resets the values. The latter can be used to measure, for example, number of compression operations over time.
I wish to present a SQL trick which does [...]
MySQL 5.1 boasts some new and useful INFORMATION_SCHEMA tables. Among them is the GLOBAL_STATUS table.
At last, it is possible to ask questions like:
node1> SELECT * FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = ‘innodb_os_log_written’;
+———————–+—————-+
| VARIABLE_NAME | VARIABLE_VALUE |
+———————–+—————-+
| INNODB_OS_LOG_WRITTEN | 512 |
+———————–+—————-+
1 row in set (0.00 sec)
node1> SELECT * FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = ‘questions’;
+—————+—————-+
| VARIABLE_NAME | VARIABLE_VALUE [...]
A set of useful queries on INFORMATION_SCHEMA follows. These queries can be used when approaching a new database, to learn about some of its properties, or they can be regularly used on an existing schema, so as to verify its integrity.
I will present queries for:
* Checking on database engines and size
* Locating duplicate and redundant indexes
* Checking on character sets for columns and tables, looking for variances
* Checking on processes and long queries