June 19, 2013

Post: Implementing SchemaSpy in your MySQL environment

June 4, 2013 By 2 Comments

…of a schema in a database and generates a visual representation of it in a browser-displayable format. It lets you click through the hierarchy of database tables…to leave you with the impression that this is the only component of the tool.  There are additional tabs: Tables – Names, number of…

Filed Under: Insight for DBAs, Insight for Developers, MySQL Tagged With: ,

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…to 2010′; mysql> use ontime1; Database changed mysql> show table…of 1 to 20 nodes. cd shard-query #generate a config for 20 shards (adjust to your number of…

Filed Under: MySQL

Post: Moving Subtrees in Closure Table Hierarchies

February 14, 2011 By 18 Comments

…querying trees in an SQL database, including the design I call Closure Table. In Closure Table, we store every path in…to create new paths for each one, as many new paths as the number of ancestors of the new location times the number of nodes in…

Filed Under: Insight for Developers, MySQL

Post: Find and remove duplicate indexes

June 20, 2012 By 9 Comments

…in our schemas can hurt the performance of our database: They make the optimizer phase slower because MySQL needs to examine more query…number at the end of the name. This type of indexes are easy to find out and to…

Filed Under: Insight for DBAs, Insight for Developers, MySQL, Percona Software

Post: InnoDB Full-text Search in MySQL 5.6 (part 1)

February 26, 2013 By 1 Comment

…of “new” InnoDB tablespace files in our database…table by querying the various I_S.INNODB_FT_* tables. In particular, the INNODB_FT_CONFIG table is supposed to…of the other I_S tables, I do find that the number of rows in I_S.innodb_ft_index_table…

Filed Under: Insight for DBAs, Insight for Developers, MySQL Tagged With: , , , , ,

Post: SHOW INNODB STATUS walk through

July 17, 2006 By 40 Comments

…tables in use 1, locked 0 MySQL thread id 8079, query id 728899 localhost root Sending data select sql…number of tables used by transaction in question (meaning it was accessed) and number of tables locked by transactions. Innodb does not lock tables for normal operation so number of tables…

Filed Under: Insight for DBAs Tagged With:

Post: Quick comparison of MyISAM, Infobright, and MonetDB

September 29, 2009 By 30 Comments

…in part because of the number of columns, which is over 200. The queries are generally pretty simple (sums of…to go to the mailing lists to find the correct COPY syntax — it wasn’t that listed in…database use — there are a number of…

Filed Under: MySQL Tagged With: , , ,

Post: Shard-Query turbo charges Infobright community edition (ICE)

May 6, 2011 By 23 Comments

…table). In order to demonstrate the power of Shard-Query it is important to test complex queries…in the dimension tables. It will be very interesting to test these queries on various engines and databases. Following is a list of the queries…of parallelism is controlled by the number of…

Filed Under: MySQL

Post: Why MySQL could be slow with large tables ?

June 9, 2006 By 141 Comments

…to several tables or perform complex queries finding relationships between objects. Normalized structure and a lot of joins is right way to design your database…number of CPUs will not help. Sometimes it is good idea to manually split query into several, run in…

Filed Under: Insight for DBAs Tagged With: ,

Post: MySQL Crash Recovery

July 30, 2006 By 30 Comments

…queries. Full table scan queries allow to preload table data ether in storage engine caches or in OS cache. You can save these into .sql…number of tables (Some users have hundreds of thousands of tables per database host). This one is pretty much part of cold start problems but I wanted to…

Filed Under: Insight for DBAs Tagged With: , , ,
« Older Posts