… 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… 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
…’ve just hit the INFORMATION_SCHEMA slowness today when I began the tests to integrate the pt-online-schema-change tool into our… one hour just to finish this query: “SELECT table_schema, table_name FROM information_schema.key_column_usage WHERE …” After setting innodb_stats… to the rest of the percona tools that use the MySQL INFORMATION_SCHEMA ) allowing it to momentarily set innodb_stats_on_metadata=0…
Comment: Finding out largest tables on MySQL Server
April 24, 2012 By 24 Comments
… 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
… don’t just write sane install and init scripts for MySQL? The most 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…
Comment: Introducing new type of benchmark
February 26, 2012 By 16 Comments
… cost benefit immediately in terms of those incremental schema changes or query changes instead of a tool … around being able to gage this type of information for people that are not at the scale … sharded datastore. A possible solution is to use mysql proxy and an alterate server with a snapshotted …
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… OK, 0 rows affected (0.00 sec) mysql> select count(*),sum(data_length) from information_schema.tables; +———-+——————+ | count(*) | sum(data_length) | +———-+——————+ | 130 | 2856365892…
Post: Eventual Consistency in MySQL
November 18, 2011 By 8 Comments
… | mysql -N | mysql -E *************************** 1. row *************************** test.Bar.ID: 42 DIY RI We can get information about foreign key constraints from the INFORMATION_SCHEMA, but… 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
….mysql.com/doc/refman/5.5/en/innodb-physical-record.html You can also get the table row format from the Information Schema: mysql (information_schema) > SELECT ROW_FORMAT from TABLES WHERE TABLE_SCHEMA=’employees’ AND TABLE_NAME=’salaries’; +————+ | ROW…
Post: INFORMATION_SCHEMA tables in the InnoDB pluggable storage engine
May 24, 2008 By Leave a Comment
… is 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 (INNODB… opinion about what would be useful to put in the INFORMATION_SCHEMA. I told them the single biggest thing I could not…
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 … /var/lib/mysql/”,table_schema,”/”,table_name, “.frm”) | +———————————————————————+ | rm -f /var/lib/mysql/art73/article73.frm | | rm -f /var/lib/mysql/art73/author73.frm…
