… obviously take more time! The tool processes the database one table at a time, and processes each table one chunk at a time, checksumming each chunk with a single…-schema-change, does renaming the table take a long time, and what about the transaction in the meanwhile? The rename table itself should be fast, but… data set sizes, it may be faster to run the alter table command directly (specially so when dropping an index, based on…
Post: How Percona does a MySQL Performance Audit
November 24, 2008 By 17 Comments
…| 216162991863 | 7796961 | | Com_admin_commands | 255868807 | 11893 | | Com_alter_db | 0 | 0 | …snip This …tables and so forth. If the server is heavily loaded or has a lot of data, touching the INFORMATION_SCHEMA can take a…; others may be run seldom but take a long time to finish. And it’s …
Post: ALTER TABLE: Creating Index by Sort and Buffer Pool Size
June 27, 2012 By 4 Comments
… I’ve updated column to have bunch of long random strings update sbtest set c=concat(…creating a table and when builds all indexes by sort. The speedup we can observe is over 20 times…. Take Away: If you’re having large tables and need to run ALTER TABLE which rebuilds the table or OPTIMIZE TABLE do…
Post: Working with large data sets in MySQL
July 5, 2007 By 12 Comments
… simply because things are going to take a lot of time. Think about 500GB table for example – ALTER TABLE make take days or even weeks depending on…’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 slave…
Post: Small things are better
October 8, 2006 By 15 Comments
… add extra column to 500GB Innodb table. It will probably take long hours or even days for ALTER TABLE to complete and about 500GB of…, but if alter table takes several days do you really can afford having no box to fall back to for such a long time ? On other hand if you would have 500 of 1GB tables it would be very easy…
Post: High-Performance Click Analysis with MySQL
December 22, 2008 By 13 Comments
…ALTER TABLE — a typical reason to think about master-master with failover and failback for maintenance. As with anything, it’s a…a partner site, and you upload bad data there, you’ll be getting bad data back for a long time. And every time… an ad that gets a million impressions takes one row, and …
Post: Faster Point In Time Recovery with LVM2 Snaphots and Binary Logs
February 23, 2012 By 3 Comments
…there can be a lot more events from the time of the…logs]# /root/bin/lvmsnap.sh snapshot Taking a new snapshot .. done Trimming excess snapshots .. …table salaries; — Ooops! Query OK, 0 rows affected (0.49 sec) mysql> alter table…you are planning to execute a long running ALTER or server upgrade, this…
Post: Find unused indexes
June 30, 2012 By Leave a Comment
…how to find duplicate indexes. This time we’ll learn how to … type:non-unique ALTER TABLE `tpcc`.`orders` DROP KEY `idx_orders`; — type:non-unique ALTER TABLE `tpcc`.`…taking a long enough period of logs or statistics to analyze all relevant workload. In some cases there are processes that run once a…
Post: MySQL optimizer: ANALYZE TABLE and Waiting for table flush
February 27, 2013 By 3 Comments
…A long running query – Run an ANALYZE TABLE on a table accessed by the long running query So first we need a long running query against table…a write query on InnoDB the rollback process could take even more time…
Comment: Using MMM to ALTER huge tables
March 28, 2008 By 11 Comments
… idea, but what do you do if you have to alter the structure due to better performance? While rading this article… have to alter the relationship of tables). Then you will change application to use these new views instead of the old tables, because… tables. Now you turn off one of the masters, and change the tables according to the views you have created. This will take a long long time…
