Пара слов про Alter Table, или как делать не надо

[syavadee]

percona online schema change
пробовали?

[eaa]

Не было особой нужды, но судя по описанию того, как это работает, сей тул действует примерно так же, как я предлагал ручной алгоритм, правда там еще триггеры наброшены для апдейта новой таблицы, если были изменения за время процедуры.

Спасибо, может быть пригодится, не нам, так другим.

[VuX]

Значение из SHOW TABLE STATUS судя по документации может отличаться на 40%-50% от реальных и рекомендуется использовать SELECT COUNT(*) для точных значений.

[eaa]

Вопрос в том, что именно Вы хотите получить. Если Вас не пугает время выполнения и лишняя нагрузка и Вам реально нужно более-менее точное значение, то COUNT как раз то, что нужно.
Но даже пока вы считаете SELECT COUNT, значения могут быть добавлены или удалены, т.е. не факт, что значение будет точным.
Но часто точное значение и не нужно, и тогда приходят альтернативные варианты. Т.е., например, исходя из того же SHOW TABLE STATUS Вы можете приблизительно понять, точно ли Вы хотите запустить SELECT COUNT, или на таких объемах это нежелательно, или может запустить-то надо, но когда нагрузка спадет. Это уже область нюансов, где единственного верного решения нет.

[apapacy]

Пара слов про Alter Table

Недавно был удивлен тем что в Postgres добавление колонки это очень быстро.
Раньше был ошибочно уверен, что это общая для всех систем управления реляционными базами данных проблема

[Antohin]

Все очень сильно зависит как от архитектуры словаря базы, так и принципов хранения данных.
Postgres фактически меняет только метаданные о таблице, никак не затрагивая самих данных. Поскольку Postgres это версионная СУБД, то при заполнении нового столбца значениями, будут создаваться новые версии строк, но точно также они бы создавались при изменении значений «старых» столбцов. С одной стороны мы растягиваем во времени процесс увеличения места под таблицу и делаем это только по необходимости, но платим за это тем, что в таблице остаются старые версии строк даже когда они нам уже не нужны. Таким образом если мы проставим значения нового столбца для всех строк, то таблица может вырасти более чем в 2 раза и придется ее жать vacuum'ом. И да, ему нужен эксклюзивный доступ к таблице. Так что проблема та же, просто она откладывается на потом.
В Oracle добавление нового поля тоже выполняется быстро. Там фишка в том что «хвостовые» значения столбцов не хранятся в блоках данных пока их значения null. Но при попытке установки значения, если в блоке не хватит места, то «хвост» строки уедет в другой блок, т.н. chained rows и чтобы прочитать строку придется физически читать 2 блока. Если таких строк много, то производительность может упасть в 2 раза. И да, придется фактически пересоздавать таблицу (alter table move...) что тоже требует эксклюзивной блокировки на таблицу и требует больших ресурсов.
Так что хотя везде свои тонкости, закон сохранения никто не отменял

[NickyX3]

Но на больших и нагруженных таблицах это тоже не самая быстрая операция, особенно когда у вас с пол миллиона строк и больше.

SELECT COUNT(*) на любых таблицах с автоинкрементным первичным ключом очень быстрая операция, ибо сервер и так хранит количество записей в схеме. Более того, для движков MyISAM/Aria вообще не будут дергаться никакие данные и получится план «Select tables optimized away». Для InnoDB/XtraDB такие запросы тоже достаточно быстрые, сервер знает количество ключей в индексе.

P.S> только что проверил на Aria табличке в 800к записей. Нулевое время. Завтра попрошу коллегу прогнать на XtraDB в 17кк записей, будет примерно тоже самое

[eaa]

Я вынужден Вас разочаровать, это отнюдь не так быстро, как хотелось бы, ниже пример для базы на SSD — полторы минуты:

mysql> select count(*) from offer;
+-----------+
| count(*) |
+-----------+
| 681382000 |
+-----------+
1 row in set (1 min 34.53 sec)

[NickyX3]

InnoDB скорее всего.
Сейчас проверили на табличке с сообщениями форума
Aria Engine
30кк строк. Время «нулевое», ну и план «Select tables optimized away» конечно.
Aria как раз потому, что чтения там очень много. InnoDB тормознее

[NickyX3]

И да, оно живет на RAID 5 из SATA HDD.

[Naves]

ENGINE=InnoDB
46М записей, 38Гб таблица
другой вопрос, что ключ не автоинкрементный

mysql

mysql> explain SELECT COUNT(SOPInstanc) FROM dicomimages USE INDEX (PRIMARY);
+----+-------------+-------------+-------+---------------+---------+---------+--
----+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | r
ef | rows | Extra |
+----+-------------+-------------+-------+---------------+---------+---------+--
----+----------+-------------+
| 1 | SIMPLE | dicomimages | index | NULL | PRIMARY | 194 | N
ULL | 57212716 | Using index |
+----+-------------+-------------+-------+---------------+---------+---------+--
----+----------+-------------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(SOPInstanc) FROM dicomimages USE INDEX (PRIMARY);
+-------------------+
| COUNT(SOPInstanc) |
+-------------------+
| 46977734 |
+-------------------+
1 row in set (16 min 30.45 sec)

так и живем

[edo1h]

а чем бы помог автоинкрементный индекс? select max(id) он даст посчитать быстро, но это же не число строк

[NickyX3]

Вдогонку. Скорость Alter Table зависит не только от скорости дисков/объема доступной памяти и всех выше упомянутых вещей. Она сильно зависит от фрагментации таблицы в innodb, количества записей, типа добавляемой колонки (колонка int1 может добавится существенно быстрее varchar 256) и наличия индексом, которые возможно будет перестроены

[skymal4ik]

Интересно, а использование MariaDB дало бы какие-то бонусы, или нет? Есть среди местных жителей кто-то с такой экспертизой?

[arheops]

У меня mariadb 10.4 и большие таблицы постоянно(200m+).
Последней колонку добавляет быстро. Если в средину — долго.

[disc]

Начиная с MariaDB 10.3 появилась возможность мгновенного добавления новых колонок в таблицу, с некоторыми ограничениями.
Instant ADD COLUMN for InnoDB

[NickyX3]

Бонусы — Aria Engine. Правда есть и плюсы и минусы по сравнению с MyISAM, но плюсы часто перевешивают

[alexkuzmenko7]

zfs'a вам не хватает, он бы при компресии не увеличил на столько объем диска

[eaa]

В реальности у нас mysql живет на zfs, иначе нам SSD мы бы просто не поместились.

[arheops]

Вопрос с репликацией решается через parallel replication/gtid. Естественно, если ваши транзакции не используют эту таблицу. Просто выставляете другой gtid для данной операции и репликация работает.
А вообще на больших таблицах я предпочитают сначала попробывать на slave чтоб узнать время, потом выбирается время минимальной загрузки и клиент предупреждается.

[Akina]

Вообще для понимания среды желательно было бы указать:
1) Исходную структуру таблицы (расчёты показывают, что у Вас там чуть ли не 14 килобайт на запись) — ибо от неё в значительной степени зависит, насколько сложным получится преобразование.
2) Сведения о том, какой алгоритм использовался реально для выполнения операции — COPY или INPLACE. В тексте запроса на изменение таблицы Вы вообще не указываете метод. С другой стороны, то, что потребовалось много пространства во временном каталоге, намекает на COPY, что на времени выполнения операции не могло не сказаться в худшую сторону.
3) innodb_file_per_table (хотя весь текст просто кричит о том, что установлено, но хотелось бы об этом узнать явно). Да и значение innodb_page_size тоже играет не последнюю роль.

[gecube]

Не спец в мускуле. Но у вас там, что ли, логическая репликация? Тогда в принципе все понятно...

[eaa]

Что Вы имеете ввиду под логической репликацией?

[gecube]

Мне казалось, что это вполне устоявшийся термин. Нет?
Это когда на слейв передается сама команда sql, а он ее применяет. При этом могут быть спецэффекты, например, с автоинкрементными значениями или time().
Спрашиваю в противовес бинарной репликации, которая реальный diff измененных данных передает и, соответственно, лишена таких проблем, но и имеет свои особенности. Это если совсем кратко

[eaa]

С данными все понятно, оно так и есть (кстати, у нас стоит бинарная репликация в Ваших терминах), но вот при выполнении DDL на мастере, как по мне, наиболее логично все-таки менять структуру таблицы на слейве одной DDL командой, чем построчно, потому что структуру таблицы все равно менять надо, но зачем потом поштучно пустые значения новой колонки синхронизировать?

[Vasyalike]

Ещё вот такое может быть, я получал зависание на минуту, а бывает прям до смерти: https://blog.pythian.com/mysql-crashes-ddl-statement-lesson-purge-threads/