… 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…
Comment: Solving INFORMATION_SCHEMA slowness
April 20, 2012 By 16 Comments
… one hour just to finish this query: “SELECT table_schema, table_name FROM information_schema.key_column_usage WHERE …” After setting innodb_stats… might apply to the rest of the percona tools that use the MySQL INFORMATION_SCHEMA ) allowing it to momentarily set innodb_stats_on_metadata…
Comment: Finding out largest tables on MySQL Server
April 24, 2012 By 24 Comments
… to the size of the tables on disk? For example, using file_per_table, if the total_size column equals 1GB… like this from the query in this article: +—————————————+———+——–+——–+————+———+ | CONCAT(table_schema, ‘.’, table_name) | rows | DATA | idx | total_size | idxfrac….frm -rw-rw—- 1 mysql mysql 58G 2012-04-24 13:41 tablename.ibd On disk vs the information_schema query, there is around…
Comment: Which Linux distribution for a MySQL database server? A specific point of view.
March 15, 2012 By 37 Comments
… people use what they like. But why the package maintainers don’t just write sane install and init scripts for MySQL? The… annoying for me part in Debian/Ubuntu is the /etc/mysql/debian-start and the ‘check_for_crashed_tables’ function (found in /usr/share/mysql/debian-start.inc.sh) which, among other things, does nasty select from information_schema. On an instance having…
Post: Solving INFORMATION_SCHEMA slowness
December 23, 2011 By 16 Comments
…mysql> select count(*),sum(data_length) from information_schema.tables; +———-+——————+ | count(*) | sum(data_length) | +———-+——————+ | 130 | 2856365892 | +———-+——————+ 1 row in set (1.08 sec) mysql…
Post: Using INFORMATION_SCHEMA instead of shell scripting
November 24, 2008 By 7 Comments
INFORMATION_SCHEMA, in particular by favorite TABLES table is not only helpful … ? Well just use INTO OUTFILE to create very simple shell script: mysql> select concat(“mysqldump “,table_schema,” “,table_name, ” >> “,table_schema,”.sql”) from tables… to leave in tact. Using similar approach we can do: mysql> select concat(“rm -f /var/lib/mysql/”,table_schema,”/”,table_name, “.frm”) from…
Post: Eventual Consistency in MySQL
November 18, 2011 By 8 Comments
… 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 3 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: 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: 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…
