… executed the following query to know the fragmentation SELECT TABLE_SCHEMA, TABLE_NAME, CONCAT(ROUND(data_length / ( 1024 * 1024 ), 2), ‘MB…(data_free / ( 1024 * 1024 ), 2), ‘MB’)FREE from information_schema.TABLES where TABLE_SCHEMA=’xyz’ and table_name=’abc’ and Data_free > 0…(data_free / ( 1024 * 1024 ), 2), ‘MB’)FREE from information_schema.TABLES where TABLE_SCHEMA=’xyz’ and table_name=’abc’ and Data_free > 0…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 3 Comments
… information_schema to see how much memory is being used by current MEMORY tables: mysql> select sum(data_length+index_length) from information_schema… and not): mysql> select * from information_schema.global_temporary_tables \G *************************** 1. row *************************** SESSION_ID: 7234 TABLE_SCHEMA: test TABLE_NAME: my ENGINE…
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… 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…-04-24 13:41 tablename.ibd On disk vs the information_schema query, there is around 9GB discrepancy. Does this have any…
Comment: Which Linux distribution for a MySQL database server? A specific point of view.
March 15, 2012 By 37 Comments
….inc.sh) which, among other things, does nasty select from information_schema. On an instance having thousands+ of tables it has to…
Comment: Instrumentation and the cost of Foreign Keys
April 19, 2012 By 9 Comments
…; it’s unlikely that the “reference data” of the account information will be updated frequently. The blocking issue can be mitigated…, FKs provide a performance benefit because the optimizer has more information about what needs to be done and the plan is… application from creating inconsistent data. FKs can help make the schema the final gate for data consistency. So, how about some…
Comment: Introducing new type of benchmark
February 26, 2012 By 16 Comments
… knowing the cost benefit immediately in terms of those incremental schema changes or query changes instead of a tool to gage… database side of things. So I cannot certain whether the schema change will cause additional IO that will really blow out… gap is around being able to gage this type of information for people that are not at the scale of Facebook…
Post: Solving INFORMATION_SCHEMA slowness
December 23, 2011 By 16 Comments
… from information schema. The solution is simple, just set innodb_stats_on_metadata=0 which will prevent statistic update when you query information_schema… make information_schema to be stale when it comes to important stuff – data_length for example will be correctly returned by information schema as…
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… 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 …(“mysqldump “,table_schema,” “,table_name, ” >> “,table_schema,”.sql”) from tables where engine=’innodb’ limit 5; +——————————————————————————+ | concat(“mysqldump “,table_schema,” “,table_name, ” >> “,table_schema,”.sql…
