Я новичок в работе с базами данных и для их изучения (а заодно для изучения Питона) начал небольшой пет-проект. В процессе оказалось, что в моей многопользовательской системе запросы к БД всегда касаются только одного пользователя. «Отлично!» подумал я, «раздам каждому пользователю по табличке и сэкономлю ресурсы сервера на индексации!». Конечно сначала я почитал литературу и подобных решений в ней не нашел. Потом сходил на один из популярных ресурсов и там мне объяснили, что так делать нельзя. Но почему – никто объяснить не смог (или возможно не захотел). Поэтому я решил сделать небольшой тест на самых популярных БД – проверить что будет на самом деле.
Методика тестирования
Конечно этот тест не особо репрезентативный, поскольку результаты будут зависеть от конфигурации сервера и настроек БД, но надеюсь почитать будет интересно, а кому-то возможно окажется и полезно. В тестовой системе использовался Ryzen 5 5600 (6 ядер – 12 потоков), 32Гб оперативной памяти и NVMe SSD диск, под управлением Windows 10. Версии БД последние на весну 2023г. Тестовый скрипт работал на той-же машине, т.е. затрат на пересылку по сети небыло. В процессе отладки и налаживания методики скрипт запускался много раз, с небольшими изменениями, результат всегда приблизительно один и тот-же.
Тестовая задача, более- менее приближенная к моей реальности, выглядела так:
периодически пользователи добавляют в БД записи в которой есть ID пользоватея, время записи, текстовая метка (комментарий) и какой-то параметр (число)
INSERT INTO mega_table (id, dt, txt, dat_stat) VALUES ( %s, %s, %s, %s )По ходу наполнения базы пользователем иногда запрашивается статистика по тому самому числовому параметру за какой-то период времени и с определенным комментарием
SELECT SUM( dat_stat ) FROM mega_table WHERE id=%s AND dt < %s AND dt > %s AND txt = %sПроверить решил 3 самые популярные БД: MySQL с движками InnoDB и MyISAM, SQLite и Postgres. Написал скрипт, который эмулирует сначала случайное заполнение БД пользователями, потом рандомные запросы, и измеряет сколько времени в среднем ушло на добавление записи в БД и на выполнение запроса в зависимости от количества пользователей и количества записей (строк) у каждого пользователя. Часть скрипта для одной из БД лежит под спойлером, для остальных БД всё аналогично. Скрипт сугубо тестовый, т.е. некрасив и без обработки ошибок, увы.
Кусочек кода с процедурой тестирования
date_start = datetime(2023, 1, 1, 0, 0, 0, 0)
d_step = timedelta( minutes= 10)
days_max = int(user_rows_num / 144) - 1
users_list = range( users_num )
texsts_list = [""]*texts_num
for ii in range( texts_num ):
texsts_list[ii] = str( randrange(1, max_data) ) + "sometext" + str( ii )
#==================================================================================================================================
# MySQL InnoDB, все данные всех пользователей лежат в одной таблице, есть Primary key по столбцам id и dt
conn = get_conn_mysql('test_db1a')
cursor = conn.cursor()
create_megatable = "CREATE TABLE IF NOT EXISTS mega_table( id INTEGER, dt DATETIME, txt TEXT, dat_stat INTEGER, PRIMARY KEY(id, dt) );"
cursor.execute( create_megatable )
conn.commit()
current_date = date_start
start_time = datetime.now()
for ii in range( user_rows_num ): # эмуляция наполнения таблицы данными
cmd = "INSERT INTO mega_table (id, dt, txt, dat_stat) VALUES ( %s, %s, %s, %s )"
curr_dt = current_date.strftime('%Y-%m-%d %H:%M:%S')
current_date += d_step
users_list = sample(users_list, users_num)
for jj in range( users_num ):
cursor.execute(cmd , ( users_list[jj], curr_dt, texsts_list[randrange(texts_num)], randrange(1, max_data)))
conn.commit()
end_time = datetime.now() - start_time
start_time = datetime.now()
for ii in range(tests_num): # эмуляция запросов статистики по одному из текстов для рандомного пользователя
cmd = "SELECT SUM( dat_stat ) FROM mega_table WHERE id=%s AND dt < %s AND dt > %s AND txt = %s"
select_date_start = current_date - timedelta( days = randrange( days_max ) )
select_date_end = select_date_start- timedelta(days = 1 )
cursor.execute(cmd , (users_list[ randrange(users_num) ], select_date_start.strftime('%Y-%m-%d %H:%M:%S'), select_date_end.strftime('%Y-%m-%d %H:%M:%S'), texsts_list[ randrange(texts_num) ] ))
res = cursor.fetchall()
print("InnoDB one table. Create :", end_time *1000 / (users_num * user_rows_num)," | select :", (datetime.now() - start_time)*1000 / tests_num )
#cursor.execute("DROP TABLE mega_table") # зачищаем всё если хотим следующей итерации теста
#cursor.execute( "VACUUM" )
conn.commit()
conn.close()Скачать полный скрипт можно отсюда.
Кроме измерения времени после выполнения скрипта посмотрел сколько полученные базы данных занимают места на диске. И в результате получились такие таблички.
Результаты
Выводы
Какие выводы новичка я для себя сделал.
Все БД достаточно надежно обеспечивают одинаковое время создания записи и выполнения однотипных запросов, вне зависимости от внутреннего наполнения базы. Особенно хорошо это работает для стандартного сценария использования - одной большой таблицы с данными.
В некоторых случаях имеет смысл давать каждому пользователю по табличке, но для этого нужно либо хорошо разбираться в БД, либо хорошо проверять гипотезу. Для себя, как для новичка, я выбрал использование одной таблицы для всех пользователей как более предсказуемый вариант, а далее возможно использовать другие механизмы БД для улучшения производительности.
Если мне будет важнее скорость добавления записей (т.е. экономить вычислительные ресурсы) то нужно пользовать Postgres, если важнее экономить место на диске – то MySQL с движком MyISAM. MySQL с движком InnoDB где-то посередине, а SQLite я рассматривал из любопытства, для моего проекта он не очень подходит.
Нужно поискать более углубленных книг по программированию баз данных ( возможно кто-то что-то посоветует ).
Комментарии (38)
boldMahoney
28.09.2023 15:57Как вам выше справедливо заметили без подробностей настроек БД смысла говорить о результатах мало. На таких малых объемах и с такими малыми длинами записей и тривиальными индексами во взрослых RDBMS вся ваша БД будет держаться в памяти, а не на диске. А подчас даже данные вероятно будут лежат в самом индексе, а не в таблицах. И вы своими тестами скорее будете измерять скорость доступа к RAM и производительность CPU, нежели эффективность механизмов самой БД. Чтобы проводить подобные осмысленные тесты, надо сначала почитать как организовано хранение данных в той или иной БД. Потом почитать как организован поиск этих данных. Потом возможно взглянуть какие оптимизации используются для чтения\записи. И уже с учетом этих знаний пытаться что-то измерять на данных приближенных к боевым с боевыми запросами, а не на синтетически сгенерированных на коленке с тривиальными запросами.
yureesk Автор
28.09.2023 15:57-3ну это "так и не так".
Изначально у меня вопрос был не с ускорением запросов вцелом, а более конкретно: можно-ли выкинуть ненужную процедуру индексации по пользователям за счет использования многих таблиц. Для моего проекта это было полезно, т.к. мои "боевые данные" выглядят в точности так как в тесте, и сценарий использования БД очень похож. Ответа в литературе я сходу не нашел, подробное изучение внутренней механики каждой из БД это не одна неделя, поэтому я просто взял и померил.
По поводу результатов - тестовая система для всех БД одинаковая, с одинаковым временем доступа к RAM и диску - а время выполнения разное, мне кажется эффективность БД таки поучаствовала в тестировании. Единственное, о чем я не подумал - о том что нужно было это всё развернуть на виртуалке и проверить с небольшим количеством RAM и одним-двумя ядрами, как на настоящем сервере небольшого проекта.
lair
28.09.2023 15:57раздам каждому пользователю по табличке [...] Потом сходил на один из популярных ресурсов и там мне объяснили, что так делать нельзя. Но почему – никто объяснить не смог (или возможно не захотел).
Потому что никто не будет создавать новую таблицу при регистрации пользователя.
yureesk Автор
28.09.2023 15:57-2ну вот эта концепция ответа на вопрос "почему?" - "потому что." и привела меня к идее тестирования. Например "потому что появляются большие накладки на работу с файлами" - это понятно, а "потому что никто так не делает" - ну не особо понятно, может у людей просто сценарии использования не такие как у меня.
FanatPHP
28.09.2023 15:57+6Ага, "я такой особенный, у меня уникальные сценарии" :)))
А может быть объяснение немного проще? "Я двоечник, который не удосужился узнать, что такое реляционная база данных и выдумал от невежества такой сценарий?"Вам следовало написать это не на хабр, а на https://qna.habr.com и попросить объяснить полученные результаты. А здесь вы только получите минусов в карму.
yureesk Автор
28.09.2023 15:57Сильно этот сценарий уникальный или нет - я не знаю, в той пачке книжек по БД, которую я прочел в поисках ответа такого сценария небыло. Я обычно ищу инструмент для решения определенной задачи, а не подгоняю задачи под известные инструменты. Моя задача вот такая.
Минусы в карму это наверное печально, но я пост сделал не для кармы, а потому что в ходе решения своей задачи сделал что-то, чего в популярных книжках и статьях из интернета - небыло, поэтому поделился с сообществом. Просто потому что мне было интересно.FanatPHP
28.09.2023 15:57+1Вы подгоняете инструмент не под задачу, а под свою чудовищную неграмотность.
И это ещё пол-беды, потому что все тупят поначалу.
Куда хуже то, что даже после всех объяснений, вы все так же считаете свое решение гениальнымyureesk Автор
28.09.2023 15:57эм. Вижу прям недопонимание.
Я считаю что объяснения в стиле "ты ничего не зниешь" и "так никто не делает" - это недостаточный аргумент. Ну да, я многого не знаю и действительно так никто не делает. Для меня объяснение - это когда указывают причину, почему это не будет работать. Если причину в литературе (и в каментах) найти не удалось - значит нужно проверить - будет работать или нет, потому что возможно вот с этим конкретным случаем люди просто не сталкивались, именно поэтому так никто не делает - им было не нужно. А мне было нужно и я проверил идею.
Вот проверил и убедился что да, в большинстве случаев - так не работает, о чем написал. Потому что раньше про такой сценарий никто не писал; вдруг кому-то понадобится или будет интересно.
Про "гениальность идеи", ну хз, у меня про это нигде не написано. Выводы у меня достаточно чёткие - чаще всего не работает, я этим пользоваться не буду.lair
28.09.2023 15:57Вот проверил и убедился что да, в большинстве случаев - так не работает, о чем написал.
...даже после нескольких комментариев вы все еще не поняли, что дело не в производительности?
yureesk Автор
28.09.2023 15:57ну блин, в ней, родимой. Вот есть прекрасный ответ, почему оно так не работает: https://habr.com/ru/articles/764224/#comment_26013286
lair
28.09.2023 15:57Я и говорю: вы, к сожалению, все еще не понимаете. Да, прозводительность такого решения может быть не восторг (хотя реальные цифры надо мерять, и они могут радикально отличаться в зависимости от множества параметров); но первое, что останавливает от его применения - это безопасность и поддерживаемость. Просто это те качества, которые даже не пришли вам в голову, пока вы не попробуете это провести через код-ревью.
yureesk Автор
28.09.2023 15:57давайте обсудим проблемы безопасности и поддерживаемости? =)
Очень интересно, какие проблемы вы видите как специалист.
Для этого наверное нужно рассказать не только о базе, но и о том, как она используется в проекте.
У меня есть условный "фронт-энд", который формирует запросы от пользователей и отсылает их на "бэк-энд" (программа на сервере). Бэк проверяет поля запроса на соответствие, формирует SQL запрос и закидывает в БД, ответ отсылает обратно во фронт. Пользователь и программист фронта с базой не контактируют вообще. Для программиста бэка ID пользователя идет либо как одно из полей данных, либо как название таблички, вся остальная разница спрятана внутри БД. Какие могут возникнуть проблемы с поддерживаемостью или безопасностью, если у нас ID пользователя переедет из данных в название таблицы?yureesk Автор
28.09.2023 15:57прога-бэкенд.
Когда приходит запрос от фронта на регистрацию нового пользователя.
lair
28.09.2023 15:57Вот в этот момент у вашего бэкенда слишком много прав. У него не должно быть прав на модиификацию структуры БД.
lair
28.09.2023 15:57ну вот эта концепция ответа на вопрос "почему?" - "потому что."
Ответ на больщую часть вопросов "почему" начинается с "потому что". В вашем случае ответ на вопрос "почему нельзя разделить юзеров по таблицам" начинается с "потому что никто не будет создавать по таблице на юзера". Это разные вещи. Вас интересует, почему нельзя создавать по таблице на юзера? Например, потому что это требует от приложения слишком много прав, которые в норме приложению стараются не давать.
pfffffffffffff
28.09.2023 15:57Добавлю что однотипные сущности должны лежать в своих таблицах. Для этого не нужно плодить таблицы, нужно только добавить новый кортеж в таблицу.
b237
28.09.2023 15:57Ну их же не руками создавать
А вот то, что запросы для работы с данными пользователей будет менее удобно писать уже похоже на правду. Запросы вроде "Выбрать всех пользователей старше 20", которые в одной таблице пишутся в одну строчку, теперь надо как-то костылить проходясь по всем таблицам с подходящей схемой
yureesk Автор
28.09.2023 15:57задача не подразумевала запросов типа "выбрать каких-то пользователей". Всегда каждый пользователь работает только со своими данными, и всё. Пользователи не взаимодействуют между собой вообще никак.
Соответственно никаких костылей и никаких прав для пользователя не нужно - пользователь обращается к бэкенду, бэк на основе логина обращается к нужной табличке и возвращает пользователю ответ.lair
28.09.2023 15:57Всегда каждый пользователь работает только со своими данными, и всё. Пользователи не взаимодействуют между собой вообще никак.
Я, кстати, хочу заметить, что описанная вами ситуация называется multitenancy, где то, что вы называете "пользователем" - это тенант (tenant), и для нее существуют описанные архитектурные паттерны, вплоть до "изолированное окружение на тенанта". Вопрос только в том, зачем конкретно это делается.
Batalmv
28.09.2023 15:57Экономия на индексации - это даже не копейки
Новая таблица ... ну, к примеру DDL автоматом делает commit (fixme), что крайне неудобно, если в рамках транзакции. Накладные расходы на создание таблицы и ведение словаря такого размера тоже как бы наверняка не бесплатны, но честно говоря, никогда не задумывался
alexmib
28.09.2023 15:57Идея чудовищная.
Во-первых, это раздувание словаря, что не есть хорошо.
Во-вторых, на каждый запрос будут выполнятся все шаги Parse -> Rewrite -> Plan ->Execute.
И чем больше раздувается первое, тем медленнее работает оптимизатор (Plan) во втором.
Нормально - когда первые три фазы делаются один раз, а потом только выполняется Execute столько раз, сколько потребуется.
yureesk Автор
28.09.2023 15:57А вы уверены, что всё так однозначно?
Вот выдержка из документации к Postgres: "Сначала планировщик/оптимизатор вырабатывает планы для сканирования каждого отдельного отношения (таблицы), используемого в запросе." Выглядит так, как будто работа оптимизатора наоборот должна ускориться, потому что ему не нужно по индексу собирать данные пользователя, я ему сразу указываю таблицу, в которой лежат все нужные данные, причем лежат упорядоченно.alexmib
28.09.2023 15:57+1А вы уверены, что всё так однозначно?
Я не уверен, я знаю. :-) На самом деле всё еще хуже чем я писал.
я ему сразу указываю таблицу
Вот в этом и проблема.
Вам знакомы термины Hard Parse, Soft Parse, Library Cache ?
Вы в каждом запросе указываете СУБД разную таблицу, из-за чего ей приходится каждый раз делать hard parse и строить новый план.
На этапе hard-парсинга:
проверяется синтаксис запроса.
проверяется существование объектов запроса (select-ы к словарю)
проверяются права пользователя к этим объектам (select-ы к словарю)
проверяется наличие и тип колонок в таблицах (select-ы к словарю)
проверяются настройки безопасности любого уровня (select-ы к словарю)
На этапе построения плана запроса:
выбираются статистики по таблице (select-ы к словарю)
выбираются статистики по колонкам (select-ы к словарю)
выбираются наличие и состав индексов на таблицах (select-ы к словарю)
перебираются варианты доступа и соединения с оценкой стоимости
Это упрощенно, но надо ли вот это всё проделывать для каждого запроса? Или все-таки стоит не убивать СУБД, а проделать это один раз, а потом только переиспользовать проделанное и вызывать только фазу exec?
Достаточно один раз посмотреть трейс 10046 (это оракл), чтобы понять какую чудовищную работу приходится делать чтобы сделать hard parse.
Я ответил на ваш вопрос - "все говорят что не надо так делать, но не могут объяснить почему" ?
alexmib
28.09.2023 15:57Добавлю, если это не очевидно.
Нормальная СУБД закэширует все этапы подготовки к исполнению в Library Cache и сама будет переиспользовать результаты парсинга и планирования.
Только не надо ей мешать и каждый раз подсовывать разные таблицы.
yureesk Автор
28.09.2023 15:57божечки, спасибо!!!
Вот без всякого сарказма - спасибо огромное!!
не могли-бы посоветовать хорошую книжку, где про это прочитать можно?alexmib
28.09.2023 15:57Вот по части литературы сложно что-то посоветовать. Хороших современных книг по базам данных так сходу на ум не приходит.
Можно почитать старого доброго Тома Кайта. Книжка, конечно, старая, написана про Оракл, но базовые принципы всех РСУБД одинаковы и там объяснены. Читается легко, я бы даже сказал увлекательно.
Льюис Адамс... ну тяжеловатое чтиво, тем более что оптимизаторы с тех пор сильно продвинулись вперед. Но если есть цель немного "влезть под кожу" оптимайзера - то можно.
Совсем лайтовый вариант - курс QPT от PostgresPro, он свободно выложен у них на сайте. Толком ничего не объясняет, но "по вершкам", в принципе, пробегает.
Tzimie
28.09.2023 15:57Дело не только в производительности. Представьте что у вас банк. И счета каждого клиента хранятся в отдельной таблице. Вам надо сосчитать общую сумму. Ваши действия?
orefkov
28.09.2023 15:57раздам каждому пользователю по табличке
Собственно партиционирование и шардирование примерно по этому принципу "унутре" и работают. Только без "закатывания солнца вручную".
edogs
Без настроек БД говорить о чем-то смысла нет, тем более на таких небольших данных. Почти наверняка у innoDB все попало в кэш, sqllite традиционно тупо пишет на диск, а myisam где-то посредине. Что собственно и видим в результатах, но это ничего не говорит о самих БД как таковых.
yureesk Автор
настройки были "по умолчанию" и это, насколько я знаю, достаточно распространенный случай. Я прочел десяток поверхностных статей по настройкам, и для InnoDB даже поувеличивал разные части кэша, но на результатах это особо не сказалось, поэтому оставил как есть. Видимо при моих размерах данных всё что нужно влезало в кэш и так.
В результатах видно, что разница между "одна таблица + индекс" и "по таблице каждому юзеру" определенно есть, но не в ту сторону, на которую я расчитывал (ну кроме SQlite). Связываю это с тем, что все протестированные БД заводят для каждой таблицы отдельные файлы, а SQlite всё пишет в один файл и видимо с некоторого количества таблиц переключение между файлами становится более накладным, чем индексация.