Как устранить проблему пропусков при записи в myisam таблицу?
Здравствуйте все, кто еще не спит. У меня проблема при вставке в таблицу данных. Данные храню в myisam таблице. Читал, что она, якобы, не такая отказоустойчивая, как дефолтная, но не может же она давать сбой при 100000 записях? В общем, получаю данные со стороннего сервера в виде json. Затем парсю в массив и добавляю бд. Думал, что может быть не все запросы прошли успешно, но нет, все нормально. В пределах одного массива(2000 записей) часть записывается, часть нет. Не знаю что и делать. Хотел потестить innodb, но не рискнул) Уж очень он медленный. На 40.000 записей innodb справляется на ура, больше - начинает пропускать. Прошу не судить строго, только пару дней назад узнал, что в myisam insert и select работает значительно быстрее, нежели в innodb. Никогда не работал с большим объемом данных.
Да, запросы на вставку. Получаю данные с другого сайта, пачками. Потом конвертирую в массив. В пределах массива(2000 элементов), примерно 1000 вставляется. Нет никакой зависимости, что например вставились только первые 1000 или последние. Выходит в бд записи 1, 2, 5, 6, 10...
По поводу скорости: 100.000 записей вставляется за пару секунд. В innodb за тоже время вставляется где-то около 100.
InnoDB - транзакционное хранилище, и если вы явно не стартуете транзакцию, каждый ваш запрос неявно оборачивается в транзакцию (если не настраивать это иначе, в вашем случае, думаю такого не было). Если вы начнете явно открывать транзакцию перед вставкой ваших многих тысяч записей, и в конце коммитить, прирост по скорости будет раз в 100. Попробуйте так для начала, а про пропуски звучит, если честно, бредово как-то.
@Assargin Ясно, просто никогда не изучал mysql. Всё начиналось с необходимости вставки, редактирования записей, ну а для этого было достаточно знать несколько строк sql кода. Пропуски были потому, что данных было очень много и как оказалось в них были повторяющиеся значения, которые использовались для unique поля. Собственно и происходила коллизия, причину который я не могу понять.
@Assargin Забавная штука эти transaction-comit, спасибо) Один вопрос: огда транзакция стартует, она не блокирует бд? С другой страницы я смогу обращаться к базе?
Ну и как, получше с транзакциями стало?
Блокируется не вся БД, а то ли таблица, то ли даже строки (скорее всего, точно сейчас не могу сказать). На эту тему можно почитать в инете, и еще можно почитать про уровни изоляций транзакций.
Если слишком большой импорт, можете не весь импорт оборачивать в транзакцию, а, скажем, строк 1000. Или использовать вставку сразу нескольких строк (insert into tt (...) values (...),(...),(...))
@Assargin
Да, заметно) Добавил 200.000 записей с одним значением. В обеих случаях ушло по 4 секунды. Плюс, теперь я знаю как можно обрабатывать сложные запросы, вроде банковских операций и прочих, где в случае ошибки одного запроса нужно завершать все. Насколько помню, в myisam - вся таблица блокируется, а в innodb - отдельные поля.
Ну всегда же можно разбить что-то большое на части. В любом случае, ограничения памяти мне не позволит сделать слишком много запросов за один раз, верно? Точнее не памяти, а настройки среды.
Да ну как сказать, я делал тысячи запросов в пределах одной транзакции, в память так и не уперся, но было требование слишком долго не удерживать блокировку и открытую транзакцию. Поэтому разбил одну большую операцию на мелкие, не потеряв существенно в производительности.
Один раз даже пришлось реально использовать уровни изоляций транзакций. В системе перелинковки, при длительной операции привязки тысяч ссылок на страницы необходимо было в веб-морде сделать так, чтобы уже занятые страницы (к которым я в БД в таблице ссылок привязал ссылки) не учитывались при поиске свободных страниц. И так как транзакция размещения ссылок еще была открыта, использовал при выборке данных для веб-морды уровень read uncommitted - чтение "грязных" данных, тогда как по умолчанию уровень read committed - пока транзакция открыта (не закоммичена и не откачена), её изменения другие транзакции не видят.
@Assargin И все же, если запросы типа update редко используются, а delete не используется вобще + высокий аптайм не нужен, но при этом нужна хорошая скорость выборки при немалых размерах бд - лучше использовать myIsam?
Тут не подскажу, сам давно использую иннодб, а когда мне в прошлом году встала задача справляться с нагрузками по выборкам (поиску) - я начал использовать sphinx для этого, и все
Как именно вставляете? Есть ли ошибки в логах?
Проблема может быть где угодно, вплоть до лимита на время выполнения скрипта.
Попробуйте для разнообразия сохранить данные в локальный файл и импортировать его из консоли.
@metamorph
1. Вставляю из php, драйвер pdo. Ошибок нету, смотрел в /var/log/mysql/error.log
2. С этим точно проблем нет. Допустим массивов 100, выходит 100 * 2000 элементов. При этом в бд могут быть записи с id 200000, но может отсутствовать 5, 15. id не инкрементируется автоматически, а высчитывается в коде.
У меня есть подозрение что из за большого количества инсертов очередь выполнения сервера набивается запросами и они банально долго выполняются, даже тогда когда вы думаете что все данные обработаны, но она на самом деле еще в только собираются вставится.
Одним инсертом можно ставить несколько строк. Тоесть все тоже INSERT INTO tt(id, tt_id, preview, category, type) VALUES (?,?,?,?,?), только после values через запятую скобки с данными и таким образом зараз по 500-1000 записей, это отработает быcтрей чем 500 одиночных инсертов.
И всетаки рекомендовал перейти таки на InnoDB он понадежней и быстрей выполняет сложные выборки.
Вот моя заляпуха для PDO, которая содержит две функции:
подстановка массива в placeholder, чтобы можно было использовать условие вида IN(:placeholder:)
и вставку сразу нескольких строк (insert into tt (...) values (...),(...),(...)) pastebin.com/BYwSW2GZ
оба варианта предполагают именованные плейсхолдеры.
с помощью нее проблема скорости вставки может быть решена, так как вставка одним запросом 100-1000 строк намного эффективнее. Количество строк зависит от максимального размера запроса в конфиге мускуля.
Средний
