Версия для печати темы
Нажмите сюда для просмотра этой темы в обычном формате
MyDC.ru _ Технические вопросы по PtokaX _ Эффективность mysql
Автор: Iskandark 13.12.2011, 23:45
Всем привет!
Ещё очень давно интересует один вопрос. Насколько эффективно использовать mysql по сравнению со стандартной реализацией таблиц (в большинстве скриптах lua) в обычном текстовом файле?
Есть ли проигрышь в скорости доступа к информации и загрузке процессора, используя mysql?
Возможно преимущества от использования mysql в скриптах появляются, когда большая БД (сотни МБ или ГБ, когда вся БД в оперативку не помещается) и много записей?
Автор: Saymon21 14.12.2011, 0:25
1) Есть.
2) Если правильно писать запросы, мб чуть поработать над оптимизацией самого сервера - да.
Автор: Alexey 14.12.2011, 0:58
Хороший вопрос поднят. Предлагаю включить в сравнение ещё и SQLite.
С удовольствием почитал-бы развёрнутый ответ.
Автор: mariner 14.12.2011, 8:37
Ну это же очевидно.
1. нету парсинга со стороны хаба - быстрее запуск скрипта
2. унифицированный формат. С базой скрипта удобно работать скриптом, если ндо еще откуда-то то начинается гемор. А SQL-хранилища хороши тем, что протокол унифицирован
3.Поиск в sql обычно идет по индексу( а там тебе и деревья и прочее) + таблица хэшируется для ускорения поиска. Кроме того в sql есть удобные средства, чтобы избегать дублей и прочих ненужных вещей.
Возможно преимущества от использования mysql в скриптах появляются, когда большая БД (сотни МБ или ГБ, когда вся БД в оперативку не помещается) и много записей?
1. sql сервер конечно много будет есть при огромных базах, но ничто не мешает его ограничить, в птоке таких настроек естественно нет. При этом из-за хэширования и прочих плюшек (индексы и кэши) он будет быстр при поиске.
2. выйгрыша в скорости может и не быть, но тут все зависит от операций. если вы ищите в таблице, то поиск на sql-хранилище может быть шустрее, чем в таблице, а вот если изменяете - то тут уже надо думать. В данном случае на первый план выходит вопрос проектирования базы и запросов к ней.
Примеры:
1. нам надо будет искать в базе и чтобы не было дублей. Мы делаем базу с уникальным ключом и индексом. При этом у нас будет относительно долгое добавление, но быстрый поиск. Тогда, чтобы избежать тормозов с добавлением надо проектировать запросы к базе так, чтобы он добавлял за раз сразу несколько значений. Тогда мы будем эффективней перестраивать индекс.
2. У нас есть, допустим, база регистрированных юзеров и база рекордов скриптов. Юзер, допустим, решил удалить себя. Логично, что нам надо очистить рекорды! В mysql это можно произвести просто указав внешний ключ из таблицы рекордов на таблицу юзеров, а в таблицах прийдется пройтись везде "ручками"
Автор: Ksan 14.12.2011, 14:24
Как-то раньше Сетапер высказывался (вроде), что мускульная база выгоднее при записях больше 10 тысяч (могу ошибиться в порядке числа).
Надо его подключить к этой теме.
Автор: Iskandark 14.12.2011, 23:24
Как понял из сообщения mariner, что использование mysql оправдано при наличие нескольких приложений, которые работают с этой базой (например веб-сервер или другие хабы коннектятся к одной общей базе).
Приведу пример (вымышленный, только для примера объема записей и размера базы). Есть хаб с пиком юзеров 10000. Требуется вести лог по следующей информации:
1) ники всех посетителей,
2) 25 последних неодинаковых IP каждого пользователя (считаем, что все 25 полей каждого пользователя заняты),
3) дата последнего прихода или ухода.
За год работы хаба у нас накопилось 25000 записей с никами, IP и датами посещения пользователей. Итого у нас 25000*(25+1)=650k полей. Предположительно, такая база будет весить в текстовом формате 15-30Мб.
Насколько эффективно использовать в этом случае mysql или базу в текстовом файле? Как сказал mariner, что ограничить потребляемые ресурсы на обработку запросов в lua нельзя.... Как понимаю, остается только один параметр, который стоит рассматривать - это помещается ли вся база в ОЗУ компьютера? Но 15-30Мб не так уж и много для того, чтобы держать всю эту память в ОЗУ. Спасибо за уже высказанные мысли, и которые ещё будут высказаны, надеюсь.
Автор: mariner 14.12.2011, 23:28
А как ты по этой базе ищешь то? Mysql будет искать эффективными алгоритмами, а вот используешь ли их ты в своем скрипте?
Автор: Iskandark 15.12.2011, 0:24
Например необходимо реализовать следующий функционал:
(1) просмотр по нику последние IP - это просто ищется, так как ник типа первичного ключа записи в нашей текстовой базе;
(2) найти все ники, которые принадлежали диапозону IP, например диапозону 123.45.0.0-123.45.255.255
Если требуется часто вызывать функцию (2), то необходимо сделать вторую базу, которая будет обновляться по данным из первой базы каждые 2 часа, например. Вторая база будет иметь группировку уже по диапозонам IP, вот пример записи такой второй допольнительной и обновляемой каждые 2 часа базы:
[123.34.] = {
[123.34.45.67]={nick1,nick2,nick3,},
[123.34.45.68]={nick3,nick4,nick5,},
[123.34.45.69]={nick6,nick2,nick5,},
...
},
[123.35.] = {
[123.35.45.67]={nick11,nick12,nick13,},
[123.35.45.68]={nick13,nick14,nick15,},
[123.35.45.69]={nick16,nick12,nick15,},
...
},
...
...
Вот использовать такую вторую дополнительную базу, которая обновляется регулярно по данным первой основной базы, более эффективно для реализации функции (2).
Такой подход будет считаться эффективным для выполнения этих двух функций или всё-таки использование mysql более предпочтительно будет? Я, понимаю, что возможно нельзя точно завить, что использование одного метода более эффективно, но возможно у кого-то есть соображения по этому поводу или опыт.
Автор: Enyby 15.12.2011, 9:07
Мускуль писали профессионалы.Причем на Си. Весьма сомнительно что вам удастся перепрыгнуть его по быстродействию, написав аналог на lua.
Это общее замечание.
На деле есть порог вхождения. База с 3 записями не эффективна. Лучше текстовый файл. Но уже на тысячу записей эффект будет очевидным.
Правда тут надо еще и задизайнить базу нормально. При плохом дизайне будет проигрышь в скорости.
Мускуль тоже будет всю базу в памяти держать, если ее объем 15-30 мб, только он еще будет строить индексы на основе бинарных деревье и прочие вкусности, причем от вас не требуется ничего сложного для реализации, в отличие от написания своего варианта DBMS на lua.
Автор: Setuper 15.12.2011, 11:47
А с чего вы взяли, что базы данных держать всё в ОЗУ? По всей видимости это какой-то частный случай, когда бд держит всё в памяти, а вообще бд подкачивает нужные данные по необходимости, в противном случае на базы данных в несколько Тб никакой оперативы не напасёшься.
По поводу работы с бд, то тут можно смотреть с разных сторон.
Конечно, если ты профессионал, то ты обязательно используешь бд. БД обеспечивает лучшую сохранность данных, чем файлы.
Однако существуют пользователи, которые по той или иной причине не хотят устанавливать какие-то бд, тогда на помощь может приходит sqlite 
Тем не менее, самый приемлемый вариант - это 2 машины: на одной хаб, на другой бд.
Что касается оптимальности, то тут основную роль конечно же играет проектирование бд. Хорошо спроектированная бд и быстро работает, и масштабируема для проекта в целом. Параллельно с проектированием бд нужно продумывать эффективные алгоритмы взаимодействия с этой бд.
Как привило, обычно возникает палка с двумя концами: быстрой поиск или быстрая вставка.
Автор: Enyby 15.12.2011, 11:51
Если объем базы составляет 15-30 мб и кэш по дефолту порядка 200 мб, то все таблицы находятся в кеше и ни разу не вытесняются на диск. Именно поэтому можно говорить, что БД в памяти. Хотя это происходит не сразу. С момента старта проходит некоторое время до полного кеширования базы.
Насчет сохранности - это очень важный момент. Если что-то случится, например, закончится место на диске, то все файлы пойдут лесом, в то время как БД, особенно транзакционные останутся в нормальном состоянии, просто произойдет утеря последних данных.
Автор: mariner 15.12.2011, 12:17
А вот это сильно зависит от конфига.
Автор: dimajak 15.12.2011, 22:20
Здесь палка о двух концах, объясню почему.
Кто-нибудь, когда-нибудь задумывался о том, что происходит с SQL-запросом после передачи MySQL-серверу?
Вы задумывались, что после передачи строки запроса нужно совершить множество операций перед тем как выдать вам результат?
1) Лексический анализ;
2) Парсинг;
3) Обращение к схеме (узнать индексы, поля);
4) Работа с кэшем запросов;
5) Оптимизатор;
6) Анализ связанных индексов;
7) Выборка стратегии запроса;
8) Открытие и блокировка таблиц;
9) Запрос к движку;
10) Обслуживание;
11) Форматирование данных;
12) Закрытие и разблокировка таблиц.
Если еще сюда добавить на время на установление соединения с MySQL-сервером, то время на обработку вашего запроса еще увеличится. Умножьте на количество запросов в единицу времени. Можно, конечно, съэкономить на однотипных запросах, но не намного при большом количестве разнородных запросов.
Теперь стало понятнее?
Хаб не заморачиваясь и не напрягаясь передал строку запроса MySQL-серверу, не занял много памяти и не занял много процессорного времени. Но при этом мы видим резкий скачок в потреблении памяти и ресурсах процессора.
Если хотите, то почитайте о NoSQL решениях, даже с использованием MySQL, производительность выше в 3-5 раз.
Автор: Enyby 15.12.2011, 22:25
Без конкретики это пустой разговор. Иной раз лучше весь этот разбор + поиск по индексу чем тупой перебор пары миллионов записей из файла. А когда объем базы превышает обем доступной памяти то без SQL становится туго.
Автор: mariner 16.12.2011, 6:53
А то, но dimajak, у тебя пара пунктов лишние и пары не хватает.
Автор: dimajak 22.12.2011, 4:47
Автор: mariner 22.12.2011, 10:34
Открытие и блокировка таблиц и соответсвенно закрытие и разблокировка. Это происходит отнюдь не всегда. При чтении такого нет.
А не хватает у вас - проверка прав доступа в ACL
Автор: dimajak 27.12.2011, 1:26
Enyby, т.е. теперь мы готовы разобрать конкретные случаи? Ну что ж, давайте. Приведите примеры, желательно применительно к PtokaX, чтобы не было пустым разговором (заодно перечитайте свое сообщение).
mariner, а всегда это надо?
Я так понимаю, что из приведенных, навскидку, 12 операций, (я многие мог и забыть, т.к. я не специалист в SQL-серверах) знакомыми вам оказались лишь пара? 
Еще раз повторюсь, если было не понятно - каждая БД требует своего внятного применения, скорость доступа к данным в этой БД зависит от скорости доступа к результатам итоговых данных.
Автор: Enyby 27.12.2011, 3:02
Конкретики в том смысле, что конкретный объем пользователей, конкретный набор железа и т. п. Если у вас 100 юзеров, это один разговор, а когда их 20 к - совершенно другой.
Серебряной пули нет. Это я имел в виду. В каком-то конкретном случае будут лучше файлы, а в каком-то - реляционная СУБД.
Автор: mariner 27.12.2011, 8:59
я не специалист в SQL-серверах
Тогда может помолчишь? По поводу 12 операций - ты очень близко все описал, ага. Но кое-что важное забыл. То, что ВСЕГДА надо.
У нас вообще в институте есть такая штука как курс баз данных, который обновляется ежегодно. Соответственно курс несет в себе текущую информацию на счет БД. Суть в том, что я его как раз в этом семестре слушал и даже немного, в результате, в теме. А еще этот курс доступен всем, могу дать ссылку - прочтешь.
Автор: Alexey 27.12.2011, 23:16
Давай свою ссылку, почитаю ;)
Автор: mariner 27.12.2011, 23:24
- вот текущая "dev" версия, которую ныне обновляем.
- то, что читали нам
Автор: dimajak 29.12.2011, 0:56
У нас вообще в институте есть такая штука как курс баз данных, который обновляется ежегодно. Соответственно курс несет в себе текущую информацию на счет БД. Суть в том, что я его как раз в этом семестре слушал и даже немного, в результате, в теме. А еще этот курс доступен всем, могу дать ссылку - прочтешь.
К своему сожалению знаю систему обучения в наших ВУЗах :(
Огромная пропасть между тем, что преподают в так называемых универах и тем, чему учил нас, "желторотых цыплят", но как нам тогда казалось "всё умеющих и всё знающих монстров" удачно для нас принятым программистом Сергеем (фамилию, к сожалению, уже не помню). Всё это эмоции и воспоминания. Все это вода.
По теме:
Что я пропустил?
Что я указал неправильно, относительно MySQL ?
Автор: mariner 29.12.2011, 9:47
Ты можешь глянуть тему, освещенный в нашем курсе. Ссылки выше по теме.
Я написал что. Проверка прав доступа есть всегда, это раз. Блокировка есть не всего - это 2.