5 ms·

Sorry but adding a new index on a table with 6 billion rows is stupid. If you did this in production, it would take that table offline for possibly days. And if

Sorry but adding a new index on a table with 6 billion rows is stupid. If you did this in production, it would take that table offline for possibly days. And if it somehow failed, you would be SOL. You create a copy of the table with an updated set of indexes and copy the data over into the new table. I think Percona even has a utility that will copy all the next data over via stored procedure that is getting written to the table.

gregwebs 7y ago
Mariadb does this already, maybe even automatically depending on your settings, but I haven't used it myself. https://mariadb.com/kb/en/library/innodb-online-ddl-overview/ https://mariadb.com/kb/en/library/innodb-online-ddl-overview...
ro_sharp 7y ago
Wouldn’t it be preferable if the DBMS did this for you?
- merb 7y ago
  would be cool if there was an dbms that does that, hm... maybe there is?
zbentley 7y ago
Doesn't adding an index only take out a brief metadata lock at the start of the operation, and then leave the table safe for queries while the rest of indexing completes? https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl-operations.html https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl-op...
wolf550e 7y ago
https://www.percona.com/doc/percona-toolkit/LATEST/pt-online-schema-change.html https://www.percona.com/doc/percona-toolkit/LATEST/pt-online...
mkonecny 7y ago
Are you sure? This was the case in mysql 5.5 but 5.6 and up will not take the table offline anymore. > Several ALTER TABLE operations can be performed without copying the table, without blocking inserts, updates, and deletes to the table, or both. These enhancements are known collectively as online DDL. See Section 14.13, “InnoDB and Online DDL” for details. https://dev.mysql.com/doc/refman/5.6/en/mysql-nutshell.html https://dev.mysql.com/doc/refman/5.6/en/mysql-nutshell.html
- verelo 7y ago
  No, but it would take so much CPU and OI, it might as well. I doubt the application would be in great shape while this operation took place.

gregwebs 7y ago

Mariadb does this already, maybe even automatically depending on your settings, but I haven't used it myself. https://mariadb.com/kb/en/library/innodb-online-ddl-overview/ https://mariadb.com/kb/en/library/innodb-online-ddl-overview...

ro_sharp 7y ago

Wouldn’t it be preferable if the DBMS did this for you?

merb 7y ago
would be cool if there was an dbms that does that, hm... maybe there is?

zbentley 7y ago

Doesn't adding an index only take out a brief metadata lock at the start of the operation, and then leave the table safe for queries while the rest of indexing completes? https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl-operations.html https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl-op...

wolf550e 7y ago

https://www.percona.com/doc/percona-toolkit/LATEST/pt-online-schema-change.html https://www.percona.com/doc/percona-toolkit/LATEST/pt-online...

mkonecny 7y ago

Are you sure? This was the case in mysql 5.5 but 5.6 and up will not take the table offline anymore. > Several ALTER TABLE operations can be performed without copying the table, without blocking inserts, updates, and deletes to the table, or both. These enhancements are known collectively as online DDL. See Section 14.13, “InnoDB and Online DDL” for details. https://dev.mysql.com/doc/refman/5.6/en/mysql-nutshell.html https://dev.mysql.com/doc/refman/5.6/en/mysql-nutshell.html

verelo 7y ago
No, but it would take so much CPU and OI, it might as well. I doubt the application would be in great shape while this operation took place.