Что выбрать для большой БД (порядка 1000000 записей в 4 таблицах)

Спасибо, почитаю

У меян число расработческих задач крайне мало, обычно с БД сталкиваюсь когда нужно какой-то сервис (вместе с БД) смигрировать в другое мето, а перенос всей системы невозможен

но там просто pymysql в цикле записи добавляе

так в этом и беда. в цикле надо формировать пакеты данных и собачить по 10 тысяч за проход.

большой БД

порядка 1000000 записей в 4 таблицах

/0

но захотелось сделать «покрасивше» и добавить локальную БД в которой можно производить поиск по полям и простенькую аналитику (сколько записей добавилось/удалилось после инкрементного обновления, статистику по значению полей и пр.)

SQLite

А как узнать оптимальное количество строк для одного подхода?

можно без написаний всякого кода. в гуглодоках есть возможность прям из апей данные выгружать в таблицу. а там уже обычными фильтрами делать с данными что нужно. вот наглядно как это делать https://www.youtube.com/watch?v=9VBxRZgY8_4

зы 1млн записей - это мелкая база. даже в Ёксел таблицу поместится

наощупь, стакаешь 100 инсертов в один и 100 таких стаков в одну транзакцию, коммитишь, следующая пачка. цифры по наитию подбирай))

Еще почитай про load infile если кодить нехочется - это самый быстрый способ.

Вопрос - а зачем тебе база? Ты не можешь просто в память грузануть данные и обработать их там?

А если надо конкретно эти данные сохранить - через pickle сохранить и потом обратно загрузить. 4 таблицы по миллиону, вроде, должны в память лезть или там что-то развесистое совсем?

Нет, создавать в памяти буфер с CSV и грузить его как файл в таблицу. В документации на pandas даже есть пример для перегрузки метода для постгреса.

Советую postgres. По нему есть куча литературы и ответов на StackOverflow.

Это пост для ТС.

Да, именно так.

Сколько записей в генерируемый insert помещать, пожалуй нужно экспериментально подбирать или погуглить (ИМХО лучше не более тысячи записей).

базу можно загрузить в базу без питона. сначала экспорт в .sql и потом импорт из него средствами самой бд

как? - такие вопросы можно задавать gpt чату poe.com

Код не могу, по коммерческим соображениям

Так напиши руками минимально воспроизводимый пример, нам ваши бизнес-секреты не интересны.

В любой ситуации выбирай постгрес.

Но в твоём случае тормоза не из-за мускуля. Индексы везде одинаково работают.

Для встраиваемых решений можно использовать sqlite, если не хочется раскочегаривать полноценный сервер СУБД, а хватает БД в процессе.

База в целом не нужна, основную задачу скрипт и так делает (формирует текстовик из xml), я больше для саморазвития копаюсь

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

База хороша в случаях когда:

1. У тебя много сложных обращений к одним и тем же данным
2. Много перезаписей конкретных данных (да, если в лоб работать с файлами и часто перезаписывать, то есть подводные камни)
3. Тебе нужны гарантии по порядку записи данных
4. Тебе нужны гарантии консистентности данных (связность, целостность и следующие отсюда транзакции)
5. У тебя много данных (много == не помещается в ~50-70% памяти машин, где тебе эти данные нужно обрабатывать)
6. Тебе нужна разнесенность данных по географии/машинам (по каким-либо причинам)
7. Требования ТЗ - данные нужно положить именно в БД по каким-либо причинам

ИМХО, в остальных случаях, обычные файлы, очень часто, будут проще и удобнее для решения задач.

Вангую дело в том что вы вставляете этот миллион записей в режиме автокоммита, что порождает 1млн транзакций, каждая из которых как минимум пишет в журнал (1 fsync). А в коде выше вставка идет пачками по 100 записей. Получается общий объем записи на 2 порядка меньше. А монго по дефолту работает без транзакций и там работает и с дефолтной логикой вставки

А если очень хочется загрузить много данных локально, то вместо загрузки через коннектор python, я бы сформировал голый SQL файл и потом его накатил через psql. Структуру базы отдельно положил бы и накатывал при необходимости отдельно.

Код не могу, по коммерческим соображениям

?

Мильен записей по нынешним понятиям это совсем не много.

1. Покрути настройки [mysqld] всякие размеры буферов и т.п.

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

3. Возможно у тебя пипон работает в один поток и от того выходит мееедленно и можно как-то распараллелить эту задачку ускорив в разы.

4. В норме миллионы записей могут заехать за пару минут (или даже сколько-то секунд). Может быть будет эффективнее сформировать sql файл (или распараллелив несколько) и сунуть это все консольному клиенту. Выключать индексирование уже советовали. Посмотри как разные утилиты формируют опции импорта в дампы.

800000 записей это не много. Тут 2 варианта:

1. Для каждой строки используешь команду INSERT. Как вариант, делать 1 INSERT для 1000 записей;
2. Много индексов. Можно удалять индексы перед вставкой, а потом их заново создавать.

Могу предложить отказаться от базы данных и использовать pandas для аналитики. Загрузка данных требует некоторое время, зато с пандасом можно делать то, что через SQL невозможно.

P.S. Мусколь по умолчанию настроен так, чтобы мог запуститься хоть на утюге. Если хочешь использовать SQL, то посмотри настройки.

А не рассматривал вариант с формированием(уже после правок данных), допустим, xml, и вот этот xml передавать в процедуру, которая средствами SQL разберет его на стороне БД и сделает INSERT? Я такое делал на MS SQL, но думаю, что в MySQL тоже есть средства для работы с Xml.

Советую PostgreSQL. ОН быстрее чем MySQL. А данные советую загружать через дамп. Возможно что придётся сконвертировать дамп из формата Вашей БД в формат PostgreSQL с помощью пайтона.

Я не знаю, посоветовали может уже.

Делал три года назад так, с постгресом.

Заливал bulk copy csv -> db в новую таблицу, генерил индекс, затем в одной транзакции подменял, и удалял старую.

Попробуй, может быть подойдёт для твоего случая.

У меня хоть нагрузка и была слабой, делал в ночное время по расписанию в случае доступного «окна импорта».

p.s.хотя, вроде даже хитрее надо было, предобработку данных делать - и не получалось из csv из-за кривых исходных дат, а пришлось делать запуск внешнего .sql с данными (INSERT...).

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

1М записей - это маленькая бд. Любая нормальная реляционная бд это прожуёт (ну кроме sqlite, мб).

Попробовал mysql, сделал таблицы, сделал скрипт импорта, на небольшом куске данных все ок, запустил полную выгрузку...прождал 2 часа и понял что че-то долго,

Без схемы бд говорить не о чём.

Уже все решил, в итоге сделал импорт разбивкой по 10000 записей через executemany().

Может через пол года вернусь к этой задаче и говнокод в более приличный вид приведу

Там бывают импорты всякие из csv, встроенные в бд. Не обязательно портянки тащить инсерт-запросами. В оракле точно была такая встроенная тулза, у остальных тоже что-то должно быть. Так на порядки быстрее это делается.

Да, через csv тоже пробовал (даже тут отписывал), так еще быстрее но мне опыта не хватило сделать это в рамках python кода без создания временных csv файлов