… can use the same SQL queries for quality control: mysql> CREATE TABLE test.MY_KEY_COLUMN_USAGE LIKE INFORMATION_SCHEMA.KEY_COLUMN_USAGE; mysql> INSERT INTO test.MY_KEY_COLUMN_USAGE SET CONSTRAINT_SCHEMA = ‘ecommerce’, CONSTRAINT_NAME = ‘fk_lineitems’, TABLE_SCHEMA = ‘ecommerce…
Post: How to recover deleted rows from an InnoDB Tablespace
February 20, 2012 By 4 Comments
…table row format from the Information Schema: mysql (information_schema) > SELECT ROW_FORMAT from TABLES WHERE TABLE_SCHEMA=’employees’ AND TABLE_NAME=’salaries’; +————+…mysql (information_schema) > select i.INDEX_ID, i.NAME FROM INNODB_SYS_INDEXES as i INNER JOIN INNODB_SYS_TABLES as t USING…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 5 Comments
… at information_schema to see how much memory is being used by current MEMORY tables: mysql> select sum(data_length+index_length) from information_schema.tables… in memory and not): mysql> select * from information_schema.global_temporary_tables \G *************************** 1. row *************************** SESSION_ID: 7234 TABLE_SCHEMA: test TABLE_NAME: my…
Post: Find unused indexes
June 30, 2012 By Leave a Comment
… an improvement on Percona Server that adds some tables to Information Schema with useful information to understand the server activity and identify the source… unused indexes we’re going to use INFORMATION_SCHEMA.INDEX_STATISTICS but first we should enable User Statistics: mysql> SET GLOBAL userstat=on; After…
Post: INFORMATION_SCHEMA tables in the InnoDB pluggable storage engine
May 24, 2008 By Leave a Comment
…INFORMATION_SCHEMA tables that show some status information about InnoDB. Here are the tables: mysql> SHOW TABLES FROM INFORMATION_SCHEMA LIKE ‘INNODB%’; +—————————————-+ | Tables_in_INFORMATION_SCHEMA…about what would be useful to put in the INFORMATION_SCHEMA. I told them …
Post: Fun with the MySQL pager command
January 21, 2013 By 6 Comments
… is easy, just run pager: mysql> pager Default pager wasn’t set, using stdout. Or \n: mysql> \n PAGER set to stdout But… solved by querying INFORMATION_SCHEMA. For instance, counting the number of sleeping connections can be done with: mysql> SELECT COUNT(*) FROM INFORMATION_SCHEMA.PROCESSLIST WHERE…
Post: How to debug long-running transactions in MySQL
March 8, 2011 By 6 Comments
… exit 1 fi host=$(mysql -ss -e ‘SELECT p.HOST FROM information_schema.innodb_lock_waits w INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id INNER JOIN information_schema.processlist p on b.trx_mysql_thread… haven’t caught a false-positive). I also like to use the –timeline feature in mk-query-digest, which prints out…
Post: MySQL Wish for 2013 - Better Memory Accounting
December 31, 2012 By 6 Comments
… Schema does not accounts CPU usage directly but it is something which can be relatively easily derived from wait and stage information… by various database operations is reasonable. For example if some INFORMATION_SCHEMA query were to take couple of GB of memory we… limit memory usage by individual MySQL users or even track how much these users are using. Now as MySQL 5.6 is getting…
Post: MySQL 5.5 and MySQL 5.6 default variable values differences
February 21, 2013 By 8 Comments
…_BATCH_SIZE | 20 | 300 | | PERFORMANCE_SCHEMA_MAX_THREAD_INSTANCES | 1000 | 224 | | SOCKET | /tmp/mysql_sandbox5530.sock | /tmp/mysql_sandbox5610.sock | | INNODB_FILE_PER…_on_metadata is disabled by default in MySQL 5.6 Welcome to much faster information_schema queries! innodb_log_file_size – default has… unless opening and closing files is expensive operation (such as using MySQL on NFS file system). innodb_data_file_path got a…
Post: Auditing login attempts in MySQL
December 28, 2012 By 4 Comments
… of those attempts. Besides that information we can get other statistics that can be very useful if MySQL is running on a multi… so on. Very valuable information. It is important to mention that these tables are stored in INFORMATION_SCHEMA and that means that after a mysqld restart all the information will be lost. So if you really need that information…
