… ALTER large tables with no blocking or downtime. As you know, MySQL locks tables for most ALTER operations, but pt-online-schema-change performs the ALTER without any locking. Client applications can continue reading and writing the table…
Comment: Percona Toolkit 2.1 with New Online Schema Change Tool
April 19, 2012 By 9 Comments
… a great tool. We have some large tables and sometime we have to alter these table for business requirement which is very painful. Even yesterday I got requirement from my bosses that if we could do online alter…
Comment: Give feedback on a pt-online-schema-change update
March 23, 2012 By 2 Comments
…, I have faced some of the problems with the pt-online-schema-change which are: – Primary Key with multiple fields is… col name in quotes inside the osc, which will allow altering a table without restrictions of col names. I have also seen… handle the part of rolling forward of changes of a table in chunks with sleep ? this cause a huge replication delays…
Post: Hacking to make ALTER TABLE online for certain changes
October 29, 2007 By 26 Comments
… 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. If… work for changing table comment as well, however – changing a comment with a help of ALTER TABLE does not rebuild the table, so we…
Post: Copying InnoDB tables between servers
July 31, 2009 By 10 Comments
… it online, no needs to lock or shutdown server. To export table you need XtraBackup, and you can just specify table or table by… to remove created .ibd file, it’s done by command: alter table order_line discard tablespace; And now you copy both order… MysqlDataDir/testimport dir. When it’s done, final import command: alter table order_line import tablespace; now some magic happens and you…
Post: Finding your MySQL High-Availability solution – Replication
November 13, 2009 By 6 Comments
… whole table and it is a blocking operations. Altering a large table may takes many hours. The trick here is to run the alter table on a slave and then, once done, we let the slave catch up with the master using the new table schema, we failover to the slave and repeat the alter table on the other server. Those online schema change are…
Post: Using MMM to ALTER huge tables
March 27, 2008 By 11 Comments
… ago, I wrote about a faster way to do certain table modifications online. It works well when all you want is to… assigned to db1 at this stage. If you’re altering gigabyte size tables, this step may take some time. Go get a… its tables. When replication on db1 has caught-up: 6.2 on monitor: set db1 on-line $ mmm_control @C1 set_online…
Post: Working with large data sets in MySQL
July 5, 2007 By 12 Comments
… to take a lot of time. Think about 500GB table for example – ALTER TABLE make take days or even weeks depending on your… use simple “online” solutions but have to do more complicated things instead. You typically can’t simply run ALTER TABLE because table will stay locked for too long you would need to do careful process of ALTERing table on the…
Post: High-Performance Click Analysis with MySQL
December 22, 2008 By 13 Comments
… customers who do click analysis, site analytics, search engine marketing, online advertising, user behavior analysis, and many similar types of work… need. Because I’ve built two such systems to manage online ads through Google Adwords, Yahoo, MSN and others, it’s…, it doesn’t save you from the downtime caused by ALTER TABLE — a typical reason to think about master-master with failover…
Comment: Finding your MySQL High-Availability solution – Replication
January 7, 2010 By 6 Comments
… the alter table on a slave and then, once done, we let the slave catch up with the master using the new table schema, we failover to the slave and repeat the alter table on the other server. Those online schema change are…
