Комментарии 52
в deleteWhere нельзя указать то же условие, что и в findAll ?
В данном случае нет, так как сообщения постоянно добавляются в БД, и вполне может быть ситуация, когда в это условие добавятся новые вхождения за те миллисекунды, пока происходила обработка данных. Да, именно в примере с сообщениями это вряд ли возможно, но это всего лишь один пример)
Используйте в фильтрах фиксированное время для обработки сообщений, тогда новые сообщения никак не будут влиять на обработку.
Будут, если новое сообщение пришло в ту же секунду, что и последнее, полученное в SELECT, но после выполнения SELECT.
Зачем использовать фиксированное возрастающее время для идентификации записей, если можно использовать фиксированный возрастающий идентификатор записи?
Потому что идентификатор записи не обязан отвечать за порядок вставки, а отвечает за уникальность, в первую очередь.
В один прекрасный день придет сисадмин, посмотрит что INTEGER скоро переполнится и сделает ALTER TABLE tablename AUTO_INCREMENT = 1
Потому что идентификатор записи не обязан отвечать за порядок вставки, а отвечает за уникальность, в первую очередь.
Это если Вы говорите о программной генерации идентификатора или об идентификаторе типа UUID(). Если же речь идёт об автоинкрементном генераторе, то он обязан гарантировать соблюдение двух правил-условий, а именно то, что следующее сгенерированное им для поля значение будет (1) больше предыдущего сгенерированного (2) больше максимального в этом поле.
Допустимость несоблюдения любого из этих условий немедленно приводит к потенции генерации уже существующего в таблице значения и, соответственно, к возникновению ошибки дублирования. Некоторые СУБД вообще блокируют прямое присвоение значения автоинкрементному полю.
Кстати, из этих же требований проистекает и то, что "дыры" в значении автоинкремента для двух последовательно сгенерированных значений - штатное событие. Ибо неразрывность - не гарантируется.
Но отчасти Вы правы. Порядок генерации значений и порядок их записи в таблицу (точнее, порядок моментов обращения к генератору и порядок моментов выполнения фиксации транзакций) имеют право не совпадать.
В один прекрасный день придет сисадмин, посмотрит что INTEGER скоро переполнится и сделает
ALTER TABLE tablename AUTO_INCREMENT = 1
Если автоинкремент обрабатывается правильно, то введённое админом значение будет скорректировано (увеличено), чтобы выполнялось правило 2..
Вот, кстати, не факт что у автора статьи в базе id был целочисленный автоинкремент, а не тот же uuid. В таком случае бессмыссленно здесь спорить о том что ему нужно по "id < X" фильтровать. Впрочем, сдается мне что там int, все-таки.
немедленно приводит к потенции генерации уже существующего в таблице значения и, соответственно, к возникновению ошибки дублирования.
Верно. Поменял вручную - можешь получить по рукам (потом, при вставке).
Некоторые СУБД вообще блокируют прямое присвоение значения автоинкрементному полю.
Это какие, например? MySQL, MSSQL, Postgres, Oracle - все имеют штатный механизм для (ручного) сброса счетчика.
Что касается автоинкремента - конкретно он, да - является частным случаем генератора уникального идентификатора и, видимо, расти будет только вверх. Я точно помню что давно читал что-то про внутренние механизмы БД по использованию пустых промежутков для автогенерации primary key, но не могу найти это нигде и уже начинаю сомневаться в том что это имеет смысл.
В ANSI SQL вроде как есть определение счетчика, который может "зацикливаться", с конструкцией вроде такого:
id integer GENERATED ALWAYS AS IDENTITY (start with 42 increment by 10 cycle)
Но такой синтаксис, видимо, не поддерживается MySQL. Статья по ссылке выше немного тяжеловата для чтения, я нашел ссылку на нее на SO - там чуть понятнее.
Если автоинкремент обрабатывается правильно, то введённое админом значение будет скорректировано (увеличено), чтобы выполнялось правило 2..
Сейчас лень проверять, но, по-моему, при попытке вставить строку в таблицу в PostgreSQL, если в таблице уже есть строка с таким уникальным id, который предлагает sequence, то база выругается на ошибку, строка вставлена не будет, но счетчик у sequence увеличится.
Потому что идентификатор записи не обязан отвечать за порядок вставки, а отвечает за уникальность
Так и не надо чтобы он отвечал за порядок вставки. В статье описано удаление по списку id, независимо от порядка.
В статье описано удаление по списку id, независимо от порядка.
Статья в качестве СУБД использует MySQL. Который вполне допускает прямое присвоение в автоинкрементное поле, в том числе значение менее текущего атрибута таблицы, если оно "свободно". Если вставить такую запись во время выполнения процесса очистки - код, ориентирующийся на значение id по диапазону, эту запись смахнёт в корзину и не почешется. Чтобы этого не произошло, код должен "вытянуть" на себя все значения, подлежащие удалению, и потом возвращать их в запросе на удаление очередного чанка.
код, ориентирующийся на значение id по диапазону
Но в статье нет удаления по диапазону id, как это связано с темой обсуждения?
код должен "вытянуть" на себя все значения, подлежащие удалению, и потом возвращать их в запросе на удаление очередного чанка
Ну так в статье так и делается, а человек говорит, что вместо этого надо использовать время в запросе на удаление.
А где транзакция-то? Это где такой код пишут?
Я правильно понял что вы засасываете в память из базы десяток миллионов записей, причем в виде ORM-обьектов, а потом вставляете их в другую таблицу в той же базе? Ничего тут оптимизировать нельзя? Подсказка: SQL.
Оптимизируем больше - можно сразу только айдишники выбирать. Вместо того, чтобы выбрать сначала записи полностью, а дальше вытаскивать в массив айдишники через foreach, выбрать сразу только колонку ID.
(хотя нет, это бессмысленно - вы всё равно выбираете сообщения полностью, чтобы переложить в архив)
Почитайте хотя бы https://mariadb.com/kb/en/big-deletes/
И про isolation levels тоже.
А то такую фигню пишете что даже комментарий сложно написать.
InnoDB не блокирует таблицу. Почитайте что действительно происходит при удалении большого количества записей.
Почему нельзя сделать просто запрос DELETE ... WHERE time < ...
Потому что нужно еще и перенести сообщения в другую таблицу. И суть проблемы не изменится, операция удаления все еще будет боттлнеком.
Нет, не будет. Проблема не в удалении, а в условии "where id in (...)" - когда передаются 50к идентификаторов и база начинает все их искать сканированием, пусть даже и по индексу.
Давайте я еще раз объясню как должен выглядеть правильный способ, полностью на стороне базы в SQL:
открываем транзакцию с уровнем изоляции REPEATABLE READ
выполняем INSERT INTO history_table (...) SELECT ... FROM messages_table WHERE message_time < '2022-11-01' (дата для примера, можно смещение от сегодняшней взять)
Выполняем DELETE FROM messages_table WHERE message_time < '2022-11-01'
COMMIT
Всё! Не надо копировать миллионы записей из базы на сервер, переводить их в объекты или массивы на PHP, потом из них выбирать id, потом из них формировать какие-то слайсы и новые SQL-запросы на миллионы вставок, а потом еще и пачку удалений по id.. Про транзакции в статье не сказано ни слова и я вообще не уверен что они там были.
Автор очень мудро не появляется больше в комментариях и не пытается научить нас еще каким-то новым "PHP-трюкам".
Ну, вы ошибаетесь. Проблема из за лога транзакций при удалении, который пишется на диск. Почитайте остальные комментарии, там есть ссылка на сайт mariadb, где описывается эта проблема и как с ней бороться.
То что вы описали действительно увеличит скорость, но несущественно, относительно времени именно удаления, так что это экономия на спичках.
На самом деле без замеров и планов реальных запросов это все неубедительно.
Мне кажется, например, странным что выборка и вставка в другую таблицу этих миллионов строк работает за секунды (условие с временем), а удаление занимает минуты (условие с многими id). При вставке журнал тоже пишется на диск.
https://mariadb.com/kb/en/big-deletes/
Ну вы почитайте и вопросы отпадут.
А что мешает сделать таблицы партиционированными, например, посуточно? Тогда вместо удаления записей просто удаляем партицию - практически мгновенная операция.
Но самое главное - непонятно, зачем вообще мучать PHP, когда всё описанное элементарно и гораздо менее нагрузочно выполняется на стороне MySQL.
А диагностику проблемы он правильно описал? Про создание слепка, которое замедляет удаление?
Вот даже не вчитывался в эти подробности. Привык, что те, кто работает из языка или фреймворка, уровень СУБД просто "пробежали по верхам".
На сколько это верно, и верно ли вообще - надо копать internals. Но мне сентенция о создании слепка на каждый чих кажется крайне сомнительной.
Так может быть стоит разобраться, о чем идет речь, прежде чем писать своё мнение? Если так сделать, то окажется что и PHP и ORM тут вообще не при чем, и проблема как раз таки в том, как работает DELETE в MySql и его форках.
Автор взял неэффективный метод выполнения задачи, и пытается его оптимизировать. Какая разница, в чём именно источник этой неэффективности, если автор заведомо не затрагивает его в своих попытках оптимизации?
Да, почти наверняка проблема выбранного метода удаления записей именно там, на стороне MySQL.Но проблема не в методе. Проблема - в неправильно сделанном выборе.
И если выбор сделан неверно, то какая разница, почему выбранный метод неверен? Надо не оптимизировать выбранный метод, а менять его на другой, более эффективный, метод.
Вы бы поняли что не правы, если бы почитали пост и статью о которой я говорю https://mariadb.com/kb/en/big-deletes/
Еще раз повторяю - проблема тут не в коде и не в том как автор делает запрос и вставку данных. Современные орм делают оптимизацию запросов лучше чем 90% разработчиков. Так же как и современный PHP достаточно быстр чтобы в таких операциях не быть узким местом.
Автор как раз таки использует один из вариантов решения, описанный разработчиками марии.
И я вам еще раз повторю - решение плохое.
Нет транзакции - это вообще что за фигня при такой операции?
Вычитывание данных из базы через ОРМ и потом сразу вставка обратно - это чрезвычайно неэффективное использование ресурсов. Да, повезло что база где-то рядом, а не в соседней сети и что памяти хватило и еще ряд факторов. Но это очень плохо и показывает уровень автора.
Автор не приводит никаких объективных метрик. Какие-то внешние тайминги не говорят ничего. Мы не знаем что происходит на базе в это время и как хранятся данные, ни параметры серверов. Даже тип движка влияет на все это. Были ли это проблемы с IO? А неизвестно. Репликация? А хз. Почему при вставке не тормозит а при удалении долго? Неизвестно.
Я согласен что у MySQL могут быть проблемы с производительностью в отдельных операциях. Но я совсем не уверен что ссылка, которую вы пихаете во все ваши ответы реально применима в данном случае. Потому что никто это не исследовал здесь на самом деле.
Кстати, в той же статье по вашей ссылке снова предлагается фльтрация по id для выборки. И я снова повтрою свою мысль - это антипаттерн и не надо так делать, даже если это кажется умным.
Мы с вами в другей ветке общались но ничего.
Нет транзакции - это вообще что за фигня при такой операции?
Возможно вы правы, но я например не очень понимаю, зачем тут транзакция, если работа идет со старыми данными, которые не изменяются.
Вычитывание данных из базы через ОРМ и потом сразу вставка обратно - это чрезвычайно неэффективное использование ресурсов.
Почему вы думаете что там идет маппинг именно в ОРМ сущности а не в простейший обьект?
Да, повезло что база где-то рядом, а не в соседней сети и что памяти хватило и еще ряд факторов. Но это очень плохо и показывает уровень автора.
Задачи и требования к коду бывают разные, зачем никому не нужные оптимизации для скрипта который работает ночью раз в несколько дней? Допустим, выполнится SELECT и INSERT не за секунду-две а за пол секунды. Что от этого изменится и зачем это нужно?
Ещё раз напишу - не стоит думать что ORM пишут дураки и она генерирует плохие запросы. Это было актуально 10 лет назад.
Автор не приводит никаких объективных метрик. Какие-то внешние тайминги не говорят ничего. Мы не знаем что происходит на базе в это время и как хранятся данные, ни параметры серверов. Даже тип движка влияет на все это. Были ли это проблемы с IO? А неизвестно. Репликация? А хз. Почему при вставке не тормозит а при удалении долго? Неизвестно.
Вопросы к качеству статьи конечно есть, не спорю.
Почему при вставке не тормозит а при удалении долго? Неизвестно
Вообще то это не секрет, а общеизвестный факт, что операция вставки быстрее операции удаления, по крайней мере в Mysql подобных системах.
Я согласен что у MySQL могут быть проблемы с производительностью в отдельных операциях. Но я совсем не уверен что ссылка, которую вы пихаете во все ваши ответы реально применима в данном случае. Потому что никто это не исследовал здесь на самом деле.
Почитайте лучше всю статью и сомнения уйдут. Проблема существует, это не чьи то домыслы. Вот подобная статья для oracle https://blogs.oracle.com/sql/post/how-to-delete-millions-of-rows-fast-with-sql
Кстати, в той же статье по вашей ссылке снова предлагается фльтрация по id для выборки. И я снова повтрою свою мысль - это антипаттерн и не надо так делать, даже если это кажется умным.
Тут спорить не буду, но несколько раз натыкался на рекомендацию использовать ORDER BY вместе с LIMIT для удаления, но мб я не понял о чем вы.
Что вам пхп плохого сделал? Там боттлнек в удалении, где и так sql.
Господа дизлайкеры, прежде чем миносовать, рекомендую ознакомиться с постом, комментариями и вот этой ссылкой - https://mariadb.com/kb/en/big-deletes/, где написано, в чем заключается проблема и какие есть варианты решения.
`TRUNCATE TABLE` отработало бы ещё быстрее /s
Не понял про лимит 50000: вы же, вроде, в коде не количество прописываете, а дату.
А если добавлять разбивку по 100 сообщений, почему бы сразу не запускать скрипт с лимитом в 100?
Берём id последнего сообщения на удаление (если они по порядку), далее:
DELETE FROM messages WHERE id <= ?;
А вот это антипаттерн. Не надо считать id только растущим. Даже если это обычно так в большинстве случаев. Где-то может быть долгая транзакция, которая выделила себе id раньше всех, а закрылась позже всех. Где-то база может решить оптимизировать индексы и последовательности..
Я бы даже сказал - не надо считать, что значение id не может быть присвоено явным заданием, минуя генератор.
Спасибо, буду иметь в виду. А что, если пометить флагом сообщения на перенос в историю, далее смотреть на этот флаг?UPDATE `messages` SET `to_history` = 1 WHERE `time` <= ?;
INSERT INTO `messages_history` SELECT * FROM `messages` WHERE `to_history` = 1;
DELETE FROM message WHERE `to_history` = 1;
Так иногда делают, но нужно еще учитывать такие вещи как движок БД.
В PostgreSQL из-за механизма MVCC операция UPDATE создаст новую строку взамен старой. Старая будет не видна для новых транзакций, но все еще будет занимать место на диске. Это проблема Bloating для Postgres. После удаления строк место освободится только после autovacuum (а может и нет) или ручного запуска vacuum.
В MySQL если старый движок MyISAM (что вряд ли), то там таких проблем не будет (нет транзакций - нет проблем. Или есть, но другие). Как это работает в InnoDB - я уже не знаю точно, но подозреваю что тоже не бесплатно.
Однако, нужно определиться с тем какую проблему вы решаете этим. Боитесь что во время копирования придут новые строки и влезут в диапазон дат на удаление? Напрасно боитесь если вы все делаете в транзакции с правильным уровнем изоляции.
У вас две транзакции? Тогда все еще сложнее - а что если этот флаг уже стоит у части строк - предыдущий запуск скрипта пытался отправить сообщения в историю, выбрал их, но что-то пошло не так... Или отправил в историю, а удалить не смог..
Простой delete where... Зачем городить? Было бы быстрее и проще.
Где же вас размножают то, горе трюкачей... мрак
Удаляем блоками или разделами...
innodb не блокирует таблицу, но при больших кол-вах записей на удаление расходует ресурсы и долбит журнал увеличивая ввод/вывод.
Сделайте партиционирование сообщений по дате и удаляйте партиции, а не записи в таблицах.
Поправьте, если ошибаюсь
Разве такая конструкция не подойдёт?
With tt as( delete from messages returning *) insert into mess_history select * from tt
PS пишу с телефона , возможны ошибки
И что это изменит? Операция удаления такого количества строк всё ещё будет катастрофически долгой. Читать почему и как бороться тут - https://mariadb.com/kb/en/big-deletes/
Был бы у автора Постгресс - подошло бы. А вот в MySQL/MariaDB в CTE может быть только и исключительно SELECT, а RETURNING вообще не поддерживается.
PHP трюк для быстрого DELETE на большой таблице MySQL