Итак. Сайт работает постоянно, значит нужен 100% онлайн, максимум могу несколько часов выделить в неактивное время.
Есть таблица на 22 млн. записей.
За сутки в неё попадает около 200-300 тысяч записей.
При обычном удалении: несколько часов удаляется 300-400 тысяч.
Необходимо почистить и получить в итоге около 10 млн. записей.
Сразу оговорюсь, я удаляю по 100 записей в цикле, потому что если поставить удалять 3000-4000 записей, удаление глючит, работать начинает очень долго.
Вот вопрос, как быстро почистить таблицу?
_____________
Обучаю веб-программированию качественно и не дорого: http://school-php.com
Фрилансер, принимаю заказы: PHP, JS, AS (видео-чаты). Писать в ЛС (Личные сообщения на phpforum).
Полная Версия: Долгое удаление из таблицы
Встречный вопрос, очень важный: каков критерий удаления данных? Это время осоздания записи в БД или что-то еще?
Вообще, первая и основная мысль - дробить на партиции. Причем так, чтобы удаляемые записи оказывались в отдельных партициях. Если критерием является время, то это легко организовать (а в Постгре так вообще элементарно
) Тогда удаление будет идти более быстро.
Да и вообще... 22 млн. записей - не так и много, на самом деле
_____________
* Хэлп по PHP
* Описалово по JavaScript
* Хэлп и СУБД для PostgreSQL
* Обучаю PHP, JS, вёрстке. Интерактивно и качественно. За разумные деньги.
* "накапливаю умение телепатии" (С) и "гуглю за ваш счет" (С)
Вообще, первая и основная мысль - дробить на партиции. Причем так, чтобы удаляемые записи оказывались в отдельных партициях. Если критерием является время, то это легко организовать (а в Постгре так вообще элементарно
) Тогда удаление будет идти более быстро.Да и вообще... 22 млн. записей - не так и много, на самом деле
_____________
* Хэлп по PHP
* Описалово по JavaScript
* Хэлп и СУБД для PostgreSQL
* Обучаю PHP, JS, вёрстке. Интерактивно и качественно. За разумные деньги.
* "накапливаю умение телепатии" (С) и "гуглю за ваш счет" (С)
sergeiss
Вопрос то не о будущих структурах, а о том, как удалить сейчас.
_____________
Обучаю веб-программированию качественно и не дорого: http://school-php.com
Фрилансер, принимаю заказы: PHP, JS, AS (видео-чаты). Писать в ЛС (Личные сообщения на phpforum).
Вопрос то не о будущих структурах, а о том, как удалить сейчас.
_____________
Обучаю веб-программированию качественно и не дорого: http://school-php.com
Фрилансер, принимаю заказы: PHP, JS, AS (видео-чаты). Писать в ЛС (Личные сообщения на phpforum).
Это из-за индексов. Вот тут первый ответ должен помочь.
В хелпе правда написано что disable keys только для неуникальных ключей, если что можно и удалить индекс перед операцией.
_____________
There never was a struggle in the soul of a good man that was not hard
В хелпе правда написано что disable keys только для неуникальных ключей, если что можно и удалить индекс перед операцией.
_____________
There never was a struggle in the soul of a good man that was not hard
Michael
Спасибо.
Сейчас этим занимаюсь... результат на первый взгляд, отключил раздел, который обращается к таблице. (остались пару запросов в админке). DISABLE KEYS применил, и через минуту выбило too many connections, хотя никого в админке не было. Отключил сайт (0 запросов). После часа ожидания решил перегрузить mysql. Перезагрузка не крахнула таблицу. Я снова же применил ещё раз DISABLE KEYS, в результате запустился процесс OPTIMAZE TABLE (зачем и как - пока не понял, потому что сам его не запускал). Уже жду 3 часа, пока завершится оптимизация таблицы... и это я ещё не приступил к удалению...
_____________
Обучаю веб-программированию качественно и не дорого: http://school-php.com
Фрилансер, принимаю заказы: PHP, JS, AS (видео-чаты). Писать в ЛС (Личные сообщения на phpforum).
Спасибо.
Сейчас этим занимаюсь... результат на первый взгляд, отключил раздел, который обращается к таблице. (остались пару запросов в админке). DISABLE KEYS применил, и через минуту выбило too many connections, хотя никого в админке не было. Отключил сайт (0 запросов). После часа ожидания решил перегрузить mysql. Перезагрузка не крахнула таблицу. Я снова же применил ещё раз DISABLE KEYS, в результате запустился процесс OPTIMAZE TABLE (зачем и как - пока не понял, потому что сам его не запускал). Уже жду 3 часа, пока завершится оптимизация таблицы... и это я ещё не приступил к удалению...
_____________
Обучаю веб-программированию качественно и не дорого: http://school-php.com
Фрилансер, принимаю заказы: PHP, JS, AS (видео-чаты). Писать в ЛС (Личные сообщения на phpforum).
| Цитата (inpost @ 4.01.2013 - 01:54) |
| Вопрос то не о будущих структурах, а о том, как удалить сейчас. |
Если есть возможность, как ты говоришь, приостановить таблицу на небольшое время, то можно так попробовать.
1. Делаешь выборку из таблицы нужных данных, вставляешь их в другую таблицу. Во второй сначала нет никаких индексов!
2. Если у тебя нету жестких связок этой таблицы с другими таблицами (иначе весь этот алгоритм не работает), то удаляешь первую таблицу (всю, целиком), переименовываешь вторую, давая ей имя первой.
3. Создаешь индексы для таблицы.
4. Данные готовы к работе.
Я подобным образом данные удалял (в Постгре), до тех пор, пока не стал работать с партициями. Муторно, но работает.
Если есть связи таблицы с другими таблицами, то так может и не получиться, как я описал.
PS. Сначала написал свой ответ, потом сходил по ссылке от Михаэля
Там второй ответ - то, что я тут написал.
_____________
* Хэлп по PHP
* Описалово по JavaScript
* Хэлп и СУБД для PostgreSQL
* Обучаю PHP, JS, вёрстке. Интерактивно и качественно. За разумные деньги.
* "накапливаю умение телепатии" (С) и "гуглю за ваш счет" (С)
А ты раньше OPTIMAZE TABLE после своих попыток массового удаления не запускал? Вообще то надо.
У disable/enable keys какой то свой специфичный алгоритм, может для его работы и понадобилась предварительная оптимизация таблицы.
_____________
There never was a struggle in the soul of a good man that was not hard
У disable/enable keys какой то свой специфичный алгоритм, может для его работы и понадобилась предварительная оптимизация таблицы.
_____________
There never was a struggle in the soul of a good man that was not hard
Раньше так же запускал OPTIMAZE TABLE.
20млн. записей. Индексы отключил. Запустил удаление: LIMIT 1000 (всего 1 тысяча). Теперь 1 час сижу жду... если через процессы грохну - крахнет таблица и снова REPEAR на 3 часа.
_____________
Обучаю веб-программированию качественно и не дорого: http://school-php.com
Фрилансер, принимаю заказы: PHP, JS, AS (видео-чаты). Писать в ЛС (Личные сообщения на phpforum).
20млн. записей. Индексы отключил. Запустил удаление: LIMIT 1000 (всего 1 тысяча). Теперь 1 час сижу жду... если через процессы грохну - крахнет таблица и снова REPEAR на 3 часа.
_____________
Обучаю веб-программированию качественно и не дорого: http://school-php.com
Фрилансер, принимаю заказы: PHP, JS, AS (видео-чаты). Писать в ЛС (Личные сообщения на phpforum).
myisam?
Вообще много смотрю по этим делам пишут, но экспериментировать лучше бы на тестовой базе/таблице сначала.
_____________
There never was a struggle in the soul of a good man that was not hard
Вообще много смотрю по этим делам пишут, но экспериментировать лучше бы на тестовой базе/таблице сначала.
_____________
There never was a struggle in the soul of a good man that was not hard
DELETE FROM `table` WHERE `user_id` = 17
Может надо было индекс ставить на user_id ? И всё быстрее было бы?
________
2 таблицы очищаю.
20млн = MyIsam.
8млн = InnoDB. Так вот, вторую на 8млн записей с отключенными индексами удаление по 500 записей за 2-4 минуты.
_____________
Обучаю веб-программированию качественно и не дорого: http://school-php.com
Фрилансер, принимаю заказы: PHP, JS, AS (видео-чаты). Писать в ЛС (Личные сообщения на phpforum).
Может надо было индекс ставить на user_id ? И всё быстрее было бы?
________
2 таблицы очищаю.
20млн = MyIsam.
8млн = InnoDB. Так вот, вторую на 8млн записей с отключенными индексами удаление по 500 записей за 2-4 минуты.
_____________
Обучаю веб-программированию качественно и не дорого: http://school-php.com
Фрилансер, принимаю заказы: PHP, JS, AS (видео-чаты). Писать в ЛС (Личные сообщения на phpforum).
| Цитата (inpost @ 4.01.2013 - 10:22) |
| DELETE FROM `table` WHERE `user_id` = 17 Может надо было индекс ставить на user_id ? И всё быстрее было бы? |
Естественно...
_____________
There never was a struggle in the soul of a good man that was not hard
Дело в том, что EXPLAIN DELETE FROM `table` WHERE `user_id` = 1 - не работает, ошибку выдаёт, то есть нельзя использовать explain, отсюда и проверить, необходим ли индекс.
Кроме этого смотрел на комментарии с хабры, где человек отключил индексы и добился удаления 10млн. за 1-2 минуты. Там не говорилось о том, что надо отключить все индексы "за исключением индекса, по которому идёт поиск строк".
______
Ладно, сейчас добавляется индекс по полю user_id , попробую ещё разок удалить уже по индексу.
_____________
Обучаю веб-программированию качественно и не дорого: http://school-php.com
Фрилансер, принимаю заказы: PHP, JS, AS (видео-чаты). Писать в ЛС (Личные сообщения на phpforum).
Кроме этого смотрел на комментарии с хабры, где человек отключил индексы и добился удаления 10млн. за 1-2 минуты. Там не говорилось о том, что надо отключить все индексы "за исключением индекса, по которому идёт поиск строк".
______
Ладно, сейчас добавляется индекс по полю user_id , попробую ещё разок удалить уже по индексу.
_____________
Обучаю веб-программированию качественно и не дорого: http://school-php.com
Фрилансер, принимаю заказы: PHP, JS, AS (видео-чаты). Писать в ЛС (Личные сообщения на phpforum).
inpost - а просто выбрать нужные (неудаляемые) данные в другую таблицу ты пробовал? Если да, то как насчет времени?
Ну и, естественно, по удаляемому параметру надо иметь индекс! Что в Мускуле, что в Постгре. Я так думал, что это "само собой", поэтому не говорил об этом.
_____________
* Хэлп по PHP
* Описалово по JavaScript
* Хэлп и СУБД для PostgreSQL
* Обучаю PHP, JS, вёрстке. Интерактивно и качественно. За разумные деньги.
* "накапливаю умение телепатии" (С) и "гуглю за ваш счет" (С)
Ну и, естественно, по удаляемому параметру надо иметь индекс! Что в Мускуле, что в Постгре. Я так думал, что это "само собой", поэтому не говорил об этом.
_____________
* Хэлп по PHP
* Описалово по JavaScript
* Хэлп и СУБД для PostgreSQL
* Обучаю PHP, JS, вёрстке. Интерактивно и качественно. За разумные деньги.
* "накапливаю умение телепатии" (С) и "гуглю за ваш счет" (С)
Ладно, индекс поставил, таблица номер 2 на 8.5млн. записей за пол часика обработалась и превратилась в 3.5 млн. записей, дело было в индексе. Странно, что EXPLAIN в таком случае не работает, написали бы нечто вроде: "индекс не используется, а надо"!
Первую таблицу решил обратно перегнать. Отключение индексов - 0.01 сек. Быстро, попробовал включить их обратно - более 6 часов был полёт, в итоге админы грохнули процесс...
sergeiss
Перегнать 10 млн. записей из одной в другую... учесть, что там 50на50, я посчитал, что быстрее будет удалить, а не скопировать...
Народ, что такое QUICK ? Точнее описание его я прочитал, но смысла его не понял, удаление будет быстрее, каждый раз не будет пересчитываться индекс, то есть? У меня выглядит удаление так:
Нужен ли quick в данном случае? Или иначе, в каких случаях он нужен?
_____________
Обучаю веб-программированию качественно и не дорого: http://school-php.com
Фрилансер, принимаю заказы: PHP, JS, AS (видео-чаты). Писать в ЛС (Личные сообщения на phpforum).
Первую таблицу решил обратно перегнать. Отключение индексов - 0.01 сек. Быстро, попробовал включить их обратно - более 6 часов был полёт, в итоге админы грохнули процесс...
sergeiss
Перегнать 10 млн. записей из одной в другую... учесть, что там 50на50, я посчитал, что быстрее будет удалить, а не скопировать...
Народ, что такое QUICK ? Точнее описание его я прочитал, но смысла его не понял, удаление будет быстрее, каждый раз не будет пересчитываться индекс, то есть? У меня выглядит удаление так:
do{
mysql_query("DELETE FROM `table` WHERE `user_id` = 1 LIMIT 100");
}
while(affected_rows());
Нужен ли quick в данном случае? Или иначе, в каких случаях он нужен?
_____________
Обучаю веб-программированию качественно и не дорого: http://school-php.com
Фрилансер, принимаю заказы: PHP, JS, AS (видео-чаты). Писать в ЛС (Личные сообщения на phpforum).
Explain работает только для select. Если надо проверить покрываемость индексами запроса delete, то всего-то надо условие where подставить в select и проEXPLAINить его.
Здесь расположена полная версия этой страницы.