… col name in quotes inside the osc, which will allow altering a table without restrictions of col names. I have also seen… of rolling forward of changes of a table in chunks with sleep ? this cause a huge replication delays with no sleep option… the lag to be very high just before the new table
Post: Using MMM to ALTER huge tables
March 27, 2008 By 11 Comments
… ENUM values. When it comes to changes that really require table to be rebuilt – adding/dropping columns or indexes, changing data… changes and other things that would break logical replication if tables on master and replica are different. binary log should be… assigned to db1 at this stage. If you’re altering gigabyte size tables, this step may take some time. Go get a…
Post: Hacking to make ALTER TABLE online for certain changes
October 29, 2007 By 26 Comments
… 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… PK auto_incremental from the table. As a rule of thumb, this usually involves altering huge InnoDB tables and huge tables take time to rebuild. Disclaimer…
Comment: Using MMM to ALTER huge tables
April 4, 2008 By 11 Comments
[...] huge queries to huge tables. On the MySQL performance blog, Aurimas Mikalauskas walks us through using MMM to ALTER huge tables. He writes, “When it comes to changes that really require table to be rebuilt – [...]
Post: High-Performance Click Analysis with MySQL
December 22, 2008 By 13 Comments
… cost of repairing huge MyISAM tables and taking downtime, I would not use MyISAM for anything but read-only tables when things get… many cases. Especially on huge tables, it lets you scan portions of a table instead of the whole table if you a) choose…, it doesn’t save you from the downtime caused by ALTER TABLE — a typical reason to think about master-master with failover…
Post: How Percona does a MySQL Performance Audit
November 24, 2008 By 17 Comments
… | | Bytes_sent | 216162991863 | 7796961 | | Com_admin_commands | 255868807 | 11893 | | Com_alter_db | 0 | 0 | …snip This output is very useful when…_tmp_tables, I’ll paste in mysql> show global variables like ‘%table_size%’; +———————+———–+ | Variable_name | Value | +———————+———–+ | max_heap_table_size | 268435456 | | tmp_table_size… analysis, and also might show me things like huge tables that should be archived, tables that I should be aware of if I…
Post: Improved InnoDB fast index creation
November 6, 2011 By 4 Comments
…) So, as expected, a small buffer pool had a huge impact on ALTER TABLE with the optimization disabled, and absolutely no effect on… in ALTER TABLE are ignored to enforce uniqueness where necessary when copying the data to a temporary table; ALTER TABLE and OPTIMIZE TABLE always process tables containing…
Post: How to load large files safely into InnoDB with LOAD DATA INFILE
July 3, 2008 By 28 Comments
… and let us load with many transactions instead of one huge transaction. The smaller file is 4.1GB and has 260M… a million rows each. (InnoDB internally does operations such as ALTER TABLE in 10k row chunks, by the way; I chose 1M… 1 million rows at a time. Here’s the CREATE TABLE: CREATE TABLE load_test ( col1 bigint(20) NOT NULL, col2 bigint…
Comment: Learning about MySQL Table Fragmentation
November 19, 2008 By 19 Comments
… make a huge difference. In other cases one may not know prevailing access or may not have time to run alter table order by regularly. Running ALTER TABLE … ORDER BY … is often much slower than OPTIMIZE TABLE.
Comment: Finding your MySQL High-Availability solution – The questions
October 19, 2009 By 36 Comments
… latest versions) adding new ndbd nodes and of course doing alter tables. For alter tables, only replication can reduce the downtime. I agree that… have worked with NDB having 24 ndbd nodes handling insanely huge loads. 2.8 What the point here, do you use…
