Удалить дубли строк mysql 100 Гб, 1 миллиард строк, 30% повторов

[obkrov]

Добрый день!
Есть таблица nTable (myIsam) с одним полем nKey (Text), индексы отсутствуют, длина строки 70-80 байт.
Таблица имеет 1 миллиард строк (это ключи для СЕО) около 30% это дубли!
Как грамотно удалить эти дубли и сделать все строки уникальными?
Комп имеет 2 диска (SSD и HDD, 8 Гб ОЗУ).
Помогите, пожалуйста, третий день уже бьюсь, и безрезультатно

 

[ykpon]

Для просмотра ссылки Войди или Зарегистрируйся
Конкретный пример под твои задачи. Сервер MySQL предварительно перенеси на SSD либо его базу туда.

 

[obkrov]

Для просмотра ссылки Войди или Зарегистрируйся
Конкретный пример под твои задачи. Сервер MySQL предварительно перенеси на SSD либо его базу туда.

Там во всех примерах, по моему, перебор по циклу - для маленькой базы нормально, а для базы в миллиард строк это вечность, или я не прав?
М.б. есть способ создать новую базу записывая в неё значения из исходной с проверкой на уникальность (дубли игнорировать)? Так быстрей не выйдет?

 

[javx]

Там во всех примерах, по моему, перебор по циклу - для маленькой базы нормально, а для базы в миллиард строк это вечность, или я не прав?
М.б. есть способ создать новую базу записывая в неё значения из исходной с проверкой на уникальность (дубли игнорировать)? Так быстрей не выйдет?

Если ты укажешь столбцу значение "unique" то в данном столбце будут только уникальные значения. И при записи укажи INSERT IGNORE что бы ошибки не прерывали работу, а просто игнорировались. Таким образом ты получишь только уникальные значения, способом переноса из другой таблицы.

 

[Bezhev]

Добрый день!
Есть таблица nTable (myIsam) с одним полем nKey (Text), индексы отсутствуют, длина строки 70-80 байт.
Таблица имеет 1 миллиард строк (это ключи для СЕО) около 30% это дубли!
Как грамотно удалить эти дубли и сделать все строки уникальными?
Комп имеет 2 диска (SSD и HDD, 8 Гб ОЗУ).
Помогите, пожалуйста, третий день уже бьюсь, и безрезультатно

Всё делается ср-вами SQL.

Для просмотра ссылки Войди или Зарегистрируйся

 

[obkrov]

Для просмотра ссылки Войди или Зарегистрируйся
Конкретный пример под твои задачи. Сервер MySQL предварительно перенеси на SSD либо его базу туда.

Попробовал сделать так:

INSERT INTO Ttemp SELECT DISTINCT * FROM T;

Сервер создаёт временную таблицу и индекс к ней и начинает искать уникальные значения.
Так вот, пока таблица индекса в ОЗУ - скорость проверки нормальная (примерно 5-7 МБ/с на SSD диске), но как только индексный файл становится больше 2 ГБ его сервер перекидывает из ОЗУ на SSD и скорость перебора падает до 200 КБ/с, а при весе 100 гигов это 5 дней работы.
На каждые 7 гигов MYD данных выходит 1 гиг MYI, что на компе с 16-20 ГБ ОЗУ можно держать весь индекс в ней и выполнить перебор за разумное время.

Проблема в том, что MYSQL не даёт запускать сервер с конфигом read_buffer_size больше 2 ГБ, м.б. кто знает как весь индексный файл держать в ОЗУ?

160305 23:59:09 [Warning] option 'read_buffer_size': unsigned value 6291456000 adjusted to 2147479552
160305 23:59:09 [Warning] option 'read_rnd_buffer_size': unsigned value 6291456000 adjusted to 2147483647

последнее решение реально рабочее, но есть одна фишка время работы скрипта, на такое огромное количество может и не хватить стандартного времени

А причём тут время работы скрипта?
Запрос сразу на сервер шлётся через консоль, все ограничения по времени сняты т.к. счёт идёт на часы.
Остался вопрос с ОЗУ: разница 5-7 часов если индекс в ОЗУ и 5 дней если индекс на SSD.

ну мои первые мысли запрос sql в phpmyadmin, а процесс работы скрипта может превысить максимальное время установленного в настройках (вроде 30 сек по памяти - дефолтовские настройки) на 1 лярд конечно не хватит но и в настройках могут быть разные размеры.
Через какие вы там консоли отправляете запросы где нет ограничений так и не понял, да и врятли пойму

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

Ну это всё выполняется на MYSQL сервере запущенном на локальном компе.
Нагрузка, кстати, небольшая проц Core i5 2,3 ГГц процентов на 30 загружен, а вот с оперативкой затык.

 

[latteo]

Попробовал сделать так:

INSERT INTO Ttemp SELECT DISTINCT * FROM T;

Странно что мускуль не ругнулся на дублирующую запись и не стопнулся. Я использую INSERT IGNORE INTO для похоже задачи

На каждые 7 гигов MYD данных выходит 1 гиг MYI, что на компе с 16-20 ГБ ОЗУ можно держать весь индекс в ней и выполнить перебор за разумное время.

Проблема в том, что MYSQL не даёт запускать сервер с конфигом read_buffer_size больше 2 ГБ, м.б. кто знает как весь индексный файл держать в ОЗУ?

Есть рецепты, которые позволяют вынести MYI на другой диск, а диск можно создать в оперативке (RAM Disk).
Для просмотра ссылки Войди или Зарегистрируйся
У меня под 7 виндой SoftPerfect RAM Disk - глючит с запуском (удаляй диск из настроек перед выходом из винды), но для редких экспериментов нормально работает.

Второй момент - можно уменьшить индекс через хеши, создав временное поле для уникального индекса, в котором помешать MD5(text) varchar(32)- коллизии мало вероятны или CRC32(text) bigint - вот тут с коллизиями похуже, можно потерять часть разных по тексту данных с одинаковым CRC32, но зато всего лишь 8 байт. Временное поле раздует MYD, но за счёт уменьшения количества символов, может ускорить построение индекса.

Экспериментировать с выбором наилучшего алгоритма нет возможности, так что отпишись о том, что попробовал и какой подход оказался оптимальным.

 

[obkrov]

Странно что мускуль не ругнулся на дублирующую запись и не стопнулся. Я использую INSERT IGNORE INTO для похоже задачи

Таблица с одним полем nKey (Text), соответственно атрибут уникальности отсутствует - ошибки на дублирующую запись не возникает.

Есть рецепты, которые позволяют вынести MYI на другой диск, а диск можно создать в оперативке (RAM Disk).

Спасибо за ссылку классный способ!!!

CRC32(text) bigint - вот тут с коллизиями похуже

Тут с коллизиями совсем плохо! 16^8=4 294 967 296 после 400 000 000 строк вероятность коллизии на каждую последующую строку 10%, а на 1 000 000 000 вероятность уже 25% у меня 1,2ккк строк.

MD5(text) varchar(32)

А тут выходит разрастание 32*1 200 000 000 байт = 35 Гб
После добавления md5 столбца база на практике выросла на 40Гб
Самый прикол, что создав индекс по md5 столбцу размер индекса был примерно 40 Гб, а его я уже в RAM запихнуть не могу.
Короче я в тупике, все эксперименты длятся часами а результата 0.
Буду бить в лоб

 INSERT INTO Ttemp SELECT DISTINCT * FROM T;

и ждать сутками

 

[mrakobes0001]

так я не понял "ALTER IGNORE TABLE nTable ADD UNIQUE (nKey)" - не подойдет? все равно будет вешаться?

 

[latteo]

Буду бить в лоб

 INSERT INTO Ttemp SELECT DISTINCT * FROM T;

и ждать сутками

Оппа, а вот DISTINCT тут лишний, возможно именно он и даёт такое дикое время работы. На моей скромной табличке в 0,5 кк он почти в 4 раза увеличивает время выборки. Либо лишним является уникальный ключ в новой таблице (лучше его будет создать после полного заполнения).

Глобально рецепт выглядит так:
создать новую таблицу аналогичную предыдущей;
добавить уникальный ключ;
заполнить через INSERT IGNORE INTO SELECT * FROM T;

PS поэксперементируй с другими типами таблиц в частности archive и InnoDB - иногда это даёт значительный прирост.
С учётом единственного поля есть смысл глянуть в сторону NoSQL решений, благо они сейчас входят в популярные LAMP сборки, а скорости работы может в 10 раз превышать MySQL, если повезёт...