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

если у вас XML во все поля, и он вам хорошо знаком, то почему-бы не https://basex.org/ ?

если есть далёкая база, то наверное её можно реплицировать в поближе и работать с более близким экземпляром. По крайней мере тем-же самым кодом, не впердячивая в программу «ещё одну СУБД»

и ещё: необходимость частой работы с полной выгрузкой базы, говорит что у вас что-то не то…или не так с алгоритмами, или база нужна совсем другая.

если есть далёкая база, то наверное её можно реплицировать в поближе и работать с более близким экземпляром. По крайней мере тем-же самым кодом, не впердячивая в программу «ещё одну СУБД»

К самой БД доступ только по API и она находится «не в моей власти» и меня просто пошлют если попрошу ее реплицировать

и ещё: необходимость частой работы с полной выгрузкой базы, говорит что у вас что-то не то…или не так с алгоритмами, или база нужна совсем другая.

не, полную выгрузку можно сделать один раз а потом инкременты подгружать (там могут быть как новые записи так и удаление старых), а вот текстовый файлик надо формировать из суммарной (полная+инкременты).

если у вас XML во все поля, и он вам хорошо знаком, то почему-бы не https://basex.org/ ?

можно посмотреть, но в силу странного api без стороннего скрипта не обойтись

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

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

В итоге получите очень быструю вставку нативным клиентом данных из csv, ну и анализ соответствующий. Придётся написать выгрузку или конвертер в csv и разобраться, а подходить ли под задачу колоночная БД.

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

С Clickhouse дела вообще не имел, данные в xml полях строковые, так что во временные ряды не укладывается (так бы мог в influx загнать, с ним недавно работал)

пригодного к хранению в колоночной БД

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

Ты код покажи, вангую элементарная проблема с транзакциями, 800 тысяч это немного, ни о каких часах речи быть не может.

SQLite или DuckDB

Код не могу, по коммерческим соображениям, но там просто pymysql в цикле записи добавляет, потом переделал чтобы он формировал sql файл (пару минут занимает) и уже его через mysql грузил в БД (и вот тут уже висит). Если подскажешь что почитать по теме буду благодарен.

с каких пор милион записей это большая бд? Удивительно что мускуль не вывозит, какая примерно структура таблиц, какие запросы, какой тип базы mysql был выбран, нужен полнотекстовый поиск?

с каких пор милион записей это большая бд?

ну я с таким впервые столкнулся, пытаюсь понять что и где не так делаю чтобы скилы поднять

4 таблице, в первой поля числовой id и еще шесть тестовых полей, в остальных трех одно поле id (соотносится с id первой таблицы) и пара текстовых.

Запросы, ну пока только INSERT, вообще будет SELECT перебор по первой таблице, берется id если такой id есть во второй таблице то берется тестовое поле из нее, если нет то проверяется вторая и треться, если нигде нет то переход к следующей записи.

И для инкрементов, если из API пришло что запись надо удалить то поиск по одному из текстовых полей в первой таблице, брать id и удалять этот id из всех (ну или отмечать как удаленное чтобы для истории сохранялось)

innodb

полнотекстовый не нужен, думаю

Чёт я нее распарсил, у тебя всё встаёт колом при попытке влить разом много данных в mysql? Попробуй удалить индекс в таблице, влить данные, потом снова добавить индекс. Иначе индекс индекс обновляется на каждую добавляемую строку, и это получается не быстро. Вроде была какая-то фича для массового инсерта без обновления индекса, но это не точно

Иногда полезно если таблица с индексами - на время массовой вставки индексы отключить и пересоздать после вставки

Так я в пустую таблицу лью, оно встает колом

Ок, попробую

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

С точностью до наоборот.

Есть удаленный сервер на котором хранится некая БД

?

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

полную выгрузку...прождал 2 часа

Дело в том, что для этого никогда не используются insert. Для этого используется импорт в залоченную таблицу из csv файла. В разных СУБД это делается немного по-разному.

Так ну импорт из csv я могу, а как базу залоченной сделать?

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

This.

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

Так читать док на MySQL - импорт csv подразумевает лок.

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

Есть такое понятие «репликация», когда признаетесь какая на сервере СУБД используется, поможем.

Нет у меня доступа к исходной БД, вообще никакого ее предоставляет отдельная организация к тому же государственная, есть api по которому можно получить полную выгрузку и инкременты.

там просто pymysql в цикле записи добавляет, потом переделал чтобы он формировал sql файл (пару минут занимает) и уже его через mysql грузил в БД (и вот тут уже висит).

вставки/обновления должны быть обёрнуты транзакцией и помещены в отдельную функцию. Использование функции разобьёт действо на две части: 1) передача данных в сервер, всякие проверки и байт-код; 2) непосредсвенные манипуляции данными

Про индексы уже выше сказали.

есть api по которому можно получить полную выгрузку и инкременты

Какой в нём функционал реализован (лучше мануал)?
Раз Python используете, то биндинги к какой базе?

Первые два вопроса скорее всего лишние, так как к базе вас не допускают.
Так?

Очень неопределенная постановка задачи. 1,000,000 строк — это немного для любой СУБД. Если тормозит, значит что-то неправильно в установке бд, либо настройках, либо ещё что-то. М.б. железо слишком слабое?

В том же Postgres для быстрой загрузки и выгрузки существует команда COPY.

Если «государственная БД» - это ФИАС, то существует множество статей о том, как загружать данные в различные СУБД и работать с ними.

Если речь именно об импорте XML -> БД, то начать надо с методов импорта.

Разницу между DOM и SAX представляешь? Так вот, для БД уровня того же ФИАС (40-гиговый XML, помню) это однозначно SAX. DOM на таких объёмах вешается гарантировано. А через SAX я выборку по адресам для своей области делал за вполне приемлемое время.

Как эти методы реализованы конкретно в питоне, я не в курсе. Но вроде бы в разных ЯПах и фреймворках обычно есть библиотеки и для первого, и для второго.

Мы ТС помочь хотим, а он «не признаётся» (Впрочем может быть для него вопросы не понятны?).

Да я уже нашел много интересного в этой теме, сейчас занимаюсь правками. Мне главное было понять, действительно такое число записей «много» для mysql или я что-то делаю неправильно, выходит что второе.

Попробуйте всё же понять о чём вопросы и ответить на них и уверяю вас узнаете ещё больше.

Да я понял о чем вопросы, и даже искал когда-то готовые решения под свой вопрос на github, но там было аналогично: парсим xml, что за БД говорить не хочу, ну мое право) Могу сказать что там SOAP api который возвращает xml в одном из тегов которого base64 который надо преобразовать в zip, распоковать этот zip и в нем уже будет итоговый xml. Вот такая матрешка.

А и не нужно «1С пахнет».
Они любители SOAP, впрочем это может быть и какой-то сервис к базе.

Т.е. API предоставляют лишь выгрузку в XML и вам нужно обеспечить подгрузку данных в свою базу?

Миллион записей это ни по каким меркам не большая база, в любую СУБД это загружается за секунды (если только там не мегабайтные записи и в базе не накручено неадекватное количество индексов и триггеров). Смотри прежде всего как ты работаешь с базой - например, не нужно каждую запись писать отдельным запросом, да ещё и каждый такой запрос в собственной неявной транзакции. По производительности подойдёт всё что угодно вплоть до ублюдка sqlite, поэтому базу можешь выбирать любую удобную тебе.

Не хвалю sqlite, но 1000000 записей в таблице для неё не проблема.

Из интереса, сделал нечто подобное твоей дб локально и заполнил её рандомными данными, скриптом, всё выполнение занимает несколько минут

import mysql.connector
import random
import string
import uuid
import time

def generate_string(length):
	alphabet = string.ascii_lowercase + string.ascii_uppercase
	return ''.join(random.choice(alphabet) for i in range(length))

def generate_table1_data(mysql_client, amount):
	cursor = mysql_client.cursor()

	add_record_query = "insert into tblMain (name, anothername, testc, uuid, sha2hash) values (%s, %s, %s, %s, sha2(name, 256))"
	
	for i in range(amount):
		new_record = (generate_string(40), generate_string(50), generate_string(10), str(uuid.uuid4()))
		cursor.execute(add_record_query, new_record)
		if (i%(amount/100) == 0):
			mysql_client.commit()

	mysql_client.commit()
	cursor.close()

def generate_table2_data(mysql_client, amount):
	cursor = mysql_client.cursor()

	cursor.execute("select id from tblMain order by rand() limit {size}".format(size=amount))

	id_set = [record[0] for record in cursor.fetchall()]

	add_record_query = "insert into tblSubtable1 (id, subname) values (%s, %s)"

	for i in range(amount):
		new_record = (random.choice(id_set), generate_string(40))
		print(new_record)
		cursor.execute(add_record_query, new_record)
		if (i%(amount/100) == 0):
			mysql_client.commit()

	mysql_client.commit()
	cursor.close()	

def generate_table3_data(mysql_client, amount):
	cursor = mysql_client.cursor()

	cursor.execute("select id from tblMain order by rand() limit {size}".format(size=amount))

	id_set = [record[0] for record in cursor.fetchall()]

	add_record_query = "insert into tblSubuuid (id, subname) values (%s, %s)"

	for i in range(amount):
		new_record = (random.choice(id_set), str(uuid.uuid4()))
		cursor.execute(add_record_query, new_record)
		if (i%(amount/100) == 0):
			mysql_client.commit()

	mysql_client.commit()
	cursor.close()		

try:
	mysql_client = mysql.connector.connect(user='root', password='root', host='localhost', database='perf_test')

	start = time.time()
	print("Filling main table...")
	generate_table1_data(mysql_client, 1000000)
	print("Done, time taken ", time.time() - start)

	start = time.time()
	print("Filling second table...")
	generate_table2_data(mysql_client, 1000000)
	print("Done, time taken ", time.time() - start)

	start = time.time()
	print("Filling third table...")
	generate_table3_data(mysql_client, 1000000)
	print("Done, time taken ", time.time() - start)

	mysql_client.close()
except mysql.connector.Error as e:
	print(e)
finally:
	print("We've done here")

import mysql.connector

Оно реально лучше pymysql или просто привычка? Я pymysql по привычке взял, может не лучший выбор был

Если честно не знаю, я больше по с/с++, дефолтный коннектор взял по привычке, чисто гипотетически разницы особо быть не должно, т.к. и то и то просто обёртка над Сишной библиотекой клиента

Понял, спасибо за код поизучаю

https://dev.mysql.com/doc/refman/8.0/en/load-xml.html

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

Код скрипта можете запостить?

Поиск типа «python скрипт для загрузки xml в mysql» вам поможет 100%.

Да, с csv стало за несколько секунд загружать

Не, напрямую грузить xml не вариант, я его в порядок привожу (убираю глубокую вложенность) и на таблицы разбиваю

Безусловно, но так как (к сожалению) на вопросы ответов не было, то и не советовал csv.

Проблема решена?

В целом да, но через пол года опять вспомню про это может чего перепишу

Заметил отличие от своего кода, я беру исходную запись разбиваю на 4 запроса и отправляю запросы в разные таблицы (и после этого commit), перехожу к следующей записи.

А в твоем варианте сначала одна таблица заполняется (потом commit), потом вторая (потом commit), потом третья (потом commit).

Может в этом дело быть?

На самом деле нет, но если есть возможность аргрегации нескольких запросов перед commit, то это будет более эффективно

а чем обычный текстовый файл плох
имею примеры по 3млн записей, работа с ним в с++ занимает пренебрежительно малое количество времени

mysql перестаёт вывозить на ~100M, дальше время поиска по индексу начинает расти совсем уж погаными темпами.

Возможно в том что ТС не разработчик, у меня есть агрегатор логов, для подсчёта статистики, который довольно неплохо 6млрд записей ворочает, но подтягивает из текстовых файлов данные оч медленно

Выполнение commit после каждой записи не эффективно.

Поисковая строка «insert mysql commits»

https://webistore.ru/sql/tranzakcii-v-mysql/ Введение в транзакции в MySQL

https://habr.com/ru/articles/537594/ Что такое транзакция

Один миллион записей это очень маленькая база, спокойненько в оперативу влезет (а то и в кэш некоторых процов размером с блюдце). Пока данных до нескольких, нескольких десятков террабайт, то любая обычная реляционная база это вывезет. Дальше уже нужно будет либо руками дорабатывать, либо переходить на Clickhouse. Работает плохо возможно потому, что нужно не забывать про батчи.