'INFORMATION_SCHEMA' Tag

  • How to calculate a good InnoDB log file size – recap

    October 20, 2009

    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 [...]

  • SQL: querying for status difference over time

    October 20, 2009

    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 [...]

  • INFORMATION_SCHEMA.GLOBAL_STATUS: watch out

    October 14, 2009

    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 [...]

  • Useful database analysis queries with INFORMATION_SCHEMA

    November 26, 2008

    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

 
Powered by Wordpress and MySQL. Theme by openark.org