… really require table to be rebuilt – adding/dropping columns or indexes, changing data type, converting data to different character set – MySQL master… one of our MySQL support customers as they were upgrading their application and mysql schema. We deployed and used MySQL Master-Master replication… 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
… from the table. As a rule of thumb, this usually involves altering huge InnoDB tables and huge tables take time to…mysql test # mv huge_table.frm huge_table_old.frm && mv huge_table_new.frm huge_table.frm && mv huge_table_old.frm huge_table_new.frm # fg mysql test — 4. mysql> UNLOCK TABLES…
Post: High-Performance Click Analysis with MySQL
December 22, 2008 By 13 Comments
…cases. Especially on huge tables, it lets you scan portions of a table instead of the whole table if you a…doesn’t save you from the downtime caused by ALTER TABLE — a typical reason to think about master-master … work by either using MySQL 5.1′s row-based replication, or in MySQL 5.0 and earlier, …
Post: How Percona does a MySQL Performance Audit
November 24, 2008 By 17 Comments
… | 255868807 | 11893 | | Com_alter_db…tables, I’ll paste in mysql> show global variables like ‘%table_size%’; +———————+———–+ | Variable_name | Value | +———————+———–+ | max_heap_table_size | 268435456 | | tmp_table…huge tables that should be archived, tables…
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: Improved InnoDB fast index creation
November 6, 2011 By 4 Comments
… trigger table rebuilds without affecting the table size. mysql> SET expand_fast_index_creation=ON; Query OK, 0 rows affected (0.00 sec) mysql> ALTER TABLE…) So, as expected, a small buffer pool had a huge impact on ALTER TABLE with the optimization disabled, and absolutely no effect on…
Post: How to load large files safely into InnoDB with LOAD DATA INFILE
July 3, 2008 By 28 Comments
…let us load with many transactions instead of one huge transaction. The smaller file is 4.1GB and …million rows each. (InnoDB internally does operations such as ALTER TABLE in 10k row chunks, by the way; I …other terminal: while [ -e /tmp/my-fifo ]; do time mysql -e “set foreign_key_checks=0; set sql_…
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… handling insanely huge loads. 2.8 What the point here, do you use SSL between your application servers and your MySQL servers…
Post: BoardReader - Forum Search Engine
November 29, 2006 By 12 Comments
… needed as traffic growths as well as makes ALTER TABLE much less painful. The other technique which I … use it to Search MySQL Forums Notice simple link structure – you can replace mysql.com in it with… data, for example searching Britney Divorce will show huge spike then news came out and quick calm…
