I have a case where I must know if group_concat_max_len is at its default value (1024), which means there are some operation I cannot work out. I’ve ranted on this here. Normally, I would simply: SELECT @@group_concat_max_len However, I am using views, where session variables are not allowed. Using a stored function can do the [...]
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 [...]
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 * [...]
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