…schema to see how much memory is being used by current MEMORY tables: mysql> select sum(data_length+index_length) from information_schema…MySQL Support contract can be handy. Conclusion Understanding where MySQL can allocate memory can help us to find…
Post: Eventual Consistency in MySQL
November 18, 2011 By 8 Comments
…shows that the quality control query can find orphans. Because we have a way to generate quality control queries automatically, we can run …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_…
Post: Shard-Query turbo charges Infobright community edition (ICE)
May 6, 2011 By 22 Comments
… shards.ini [default] user=mysql db=ontime password= port=5029 column=date_id mapper=…queries has been rewritten to conform to the new schema, and additionally two new test queries have been added. To…the chance to review this document before publication, as a courtesy. All findings are represented …
Post: How to recover deleted rows from an InnoDB Tablespace
February 20, 2012 By 3 Comments
…SCHEMA that can help us to find the different indexes and types. mysql (information_schema…to use it the tool needs to know the table schema structure, that is, column types, names and attributes. This information needs to…
Post: Shard-Query EC2 images available
May 11, 2011 By 2 Comments
…query/run_worker to contain the following: #!/bin/bash while [ 1 ] do ./worker >> /dev/null 2>&1 < /dev/null done; Where to find…schema from one shard. The schema…columns to show (click show/hide to change the columns) That the “public dns” field is selected SSH to…
Post: How to debug long-running transactions in MySQL
March 8, 2011 By 6 Comments
… use mk-query-digest to convert this into a query log, and examine the log. In MySQL 5.1 … some INFORMATION_SCHEMA tables that make this simple. Just query the tables, and if something is blocked, find its thread ID. Match this up with the ID in SHOW PROCESSLIST, and look in the Host column…
Post: High-Performance Click Analysis with MySQL
December 22, 2008 By 13 Comments
… those aggregate tables, and include columns to support these queries. But beware of denormalizing with character data; try to make your rows fixed-length… ways to handle this now. You can have the clicks column record the total, and the blue_clicks column record only blue clicks; to find… long queries that read a lot of rows — MySQL can’t handle too many of those at once. Aside from star schemas, another…
Post: Finding out largest tables on MySQL Server
February 4, 2008 By 24 Comments
Finding largest tables on MySQL instance is no brainier in MySQL 5.0+ thanks to Information Schema but I still wanted to post little query… The last column shows how much does the index take compared to the …SCHEMA to the query so it works whatever database you have active. It does not work with MySQL…
Comment: Duplicate indexes and redundant indexes
January 19, 2012 By 38 Comments
…Query for below output is at the end) mysql> source list.sql +————–+————+—————–+—————–+————-+———————–+————————+ | table_schema | table_name | constraint_name | constraint_type | column…
Post: PROCEDURE ANALYSE
March 23, 2009 By 9 Comments
Quite common task during schema review is to find the optimal data type for the column value – for example column is defined as INT but… queries while really MySQL has a native feature to perform this task – PROCEDURE ANALYSE Here is sample run of PROCEDURE ANALYSE on drupal schema…
