…to store hierarchical data, such as threaded comments, personnel org charts, or nested bill-of-materials. Sometimes it’s tricky to… the design I call Closure Table. In Closure Table, we store every path in …another child F, and below F we have a subtree D -> E (see illustration): We want to move…
Post: A recovery trivia or how to recover from a lost ibdata1 file
June 3, 2011 By 8 Comments
… transfer from one SAN to another, something went wrong and they lost the ibdata1 file, where all the table meta-data is stored. Fortunately… root -e “alter table recover.$CURRENT_TABLENAME discard tablespace;” sleep 1 echo “moving the datafile in place” cp /mnt/tables/$CURRENT_TABLENAME.ibd…
Post: How much overhead is caused by on disk temporary tables
August 16, 2007 By 26 Comments
… does not make sense as there no data to be read from the disk as nothing …of random writes. OK. Now lets move to testing MEMORY tables for the same queries: mysql> set…tables are moderately sized). Unfortunately I ran into another bug which means I will have to postpone this part of test to another…
Post: How to recover a single InnoDB table from a Full Backup
January 25, 2012 By 10 Comments
… you cannot move your ibd files freely from one server to another or from one database to another. The reason is that the table definition is…_per_table must be enabled. Then, our first step is to get a consistent backup. First we need to copy all the data to an output directory: The –export option is the magic trick that will help us to get…
Post: Heikki Tuuri answers to Innodb questions, Part II
November 4, 2007 By 13 Comments
… the OS version. In our tests, removing one bottleneck moves the contention to another place. Thus, no general rules can be established. I… data manageable. MySQL Master Master Manager can help you to automate it a bit. I also like to keep tables small enough, “sharding” to many tables…
Post: Recovering CREATE TABLE statement from .frm file
December 17, 2008 By 5 Comments
… InnoDB data files but have forgotten to delete the corresponding .frm files of InnoDB tables, or you have moved .frm files to another database? or, the table… that frequently but I decided to cover this for completeness anyway. With MyISAM table MySQL comes with another creative error message if .frm…
Post: Estimating Replication Capacity
July 20, 2010 By 4 Comments
…environment you may need to schedule downtime or set object read only to move it to another shard. It is … are unable to catch up and application is suffering because of stale data. This especially applies to Software …0% (7k) Full_scan # 0% (10k) Tmp_table # 0% (4k) Tmp_table_on_disk There is a lot …
Comment: Innodb row size limitation
April 9, 2011 By 12 Comments
… won’t let you even create the table if it’s possible for the data to exceed the maximum size. The rule was… data into one row you probably don’t need all of it for every query and could probably improve performance by moving some of it to another table. A classic example is the address of a customer which probably doesn’t need to be…
Comment: Innodb row size limitation
April 7, 2011 By 12 Comments
… won’t let you even create the table if it’s possible for the data to exceed the maximum size. The rule was… data into one row you probably don’t need all of it for every query and could probably improve performance by moving some of it to another table. A classic example is the address of a customer which probably doesn’t need to be…
Post: High-Performance Click Analysis with MySQL
December 22, 2008 By 13 Comments
… data to their own clients, with no delays. Finally, the analysis is usually multi-dimensional. The typical user wants to be able to… still another reason to use InnoDB/XtraDB tables… Optimize For I/O It is pretty much inevitable: if you do this kind of data…? We dropped one column, and we just moved those other values around to store them “next, to in the same row” instead of…
