June 18, 2013

Post: Hacking to make ALTER TABLE online for certain changes

October 29, 2007 By 26 Comments

Suppose you want to remove auto_increment from 100G table. No matter if it’s InnoDB or MyISAM, you’d usually ALTER TABLE `huge_table` CHANGE `id` `id` int(6) NOT NULL and then wait hours for table rebuild to complete…

Filed Under: Insight for DBAs Tagged With: , ,

Post: How to recover a single InnoDB table from a Full Backup

January 25, 2012 By 10 Comments

…tables. In this particular scenario is faster to recover single tables than a full backup. This is easy with MyISAM but if your tables are InnoDB…prerequisites to comply. Percona Server relaxes a lot of limitations and is able to import tables from different Server instance, when table was altered or…

Filed Under: Insight for DBAs, MySQL, Percona Software

Post: AUTO_INCREMENT and MERGE TABLES

September 11, 2008 By Leave a Comment

…any warnings) Neither setting auto_increment value for underlying MyISAM tables works: mysql> alter table a1 auto_increment=100; Query OK, 1 row …remember auto_increment for Merge Tables works differently from both MyISAM and Innodb tables and being similar to what ISAM tables used to have. As the …

Filed Under: Insight for DBAs Tagged With: ,

Post: InnoDB Full-text Search in MySQL 5.6: Part 2, The Queries!

March 4, 2013 By 7 Comments

… MyISAM stopwords, insert them into a table, and configure that table to be our stopword list for InnoDB? This is the table that we’re trying to… going to be people who believe that switching to InnoDB FTS is simply a matter of upgrading to 5.6 and running ALTER TABLE foo ENGINE=InnoDB…

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

Post: Impossible - possible, moving InnoDB tables between servers

June 8, 2009 By 9 Comments

… prepare InnoDB/XtraDB tables, which later can be imported into XtraDB. To be objective the process is still not so easy as with MyISAM tables…with innodb_expand_import=1 setting) 4) run alter table IMPORTEDTABLE discard tablespace; 5) copy .exp and .ibd files to destination server 6) run alter table…

Filed Under: Percona Software Tagged With: ,

Post: MySQL Users Conference - Innodb

May 9, 2007 By 6 Comments

… days as Innodb user when I found out ALTERing Innodb tables may take a lot of time. Fast Index creation will allow Innodb to build indexes… MyISAM tables by separate phase of building Indexes ? Will be UNIQUE indexes built by sorting in case of ALTER TABLE (the big Gotcha for MyISAM tables). Are there any plans to be able to build indexes in parallel by…

Filed Under: Events and Announcements Tagged With:

Post: 10+ Ways to Crash or Overload MySQL

November 13, 2007 By 20 Comments

… all 100 of his allowed connections to ALTER 100 different tables ? This can be offset by keeping myisam_sort_buffer_size low, but then… Space For MyISAM tables hosting providers used to use disk quotas for MyISAM tables. You can also use similar technique with innodb_file_per_table. However you…

Filed Under: Insight for DBAs Tagged With:

Post: My Innodb Feature wishes

May 4, 2006 By 30 Comments

…ALTER TABLE (expecially together with ability to create index without table rebuilding) Buffer pool improvements. I would like to see couple of things here. To…to innodb_file_io_threads=4. Some people do not know it but setting it to…

Filed Under: Insight for Developers Tagged With:

Post: Innodb Recovery Update - The tricks what failed.

May 24, 2007 By 3 Comments

…. Innodb tables rarely become corrupt Indeed even if you leave out crashes in my experience Innodb tables become corrupted more seldom than MyISAM. Thanks to checksums…, so simple ALTER TABLE fixes it This also seems to be the case. In my experience probably 80-90% of Innodb corruptions are fixed…

Filed Under: Insight for DBAs Tagged With:

Post: Working with large data sets in MySQL

July 5, 2007 By 12 Comments

…MyISAM they will lock tables for very long time and Innodb can get too many old row versions to…table to fit in memory for decent performance. Typically it would be some portion of Index BTREE (even MyISAM…

Filed Under: Insight for Developers Tagged With: ,
« Older Posts
Newer Posts »