Есть bigdata, размером:
1. 60 000 000 объектов
2. Порядка 12 gb Data lenght в MySQL (размер индексов не указываю). 

Требования
* У каждого объекта примерно 20 атрибутов типа integer, по которым может производиться выборка (как по одному атрибуту, так и любому количество атрибутов одновременно) т.е. по сути 20 атрибутов достойны стать индексами. 
* Добавлять/обновлять примерно по 50000 записей в сутки - не меньше (больше можно =)
* Скорость любой выборки не более 500 мс
* Поддержка группировки и агрегации в выборках


MySQL быстро делать выборки отказалась почти сразу. В панике прикрутили Sphinx - он вроде как лучше работает, но все равно желаемых 500 мс добиться не вышло. Приглядываемся к ElasticSearche.

Ребят, возможно мы ищем не в том направление? Возможно та же mongoDb, без индексов будет работать шустрее? Или может стоит делать виртуальную реплику этой таблицы? Расскажите, пожалуйста, что вы об этом все думаете?


Это сообщение отредактировал(а) BuShaRt - 7.4.2014, 20:32

Только AND или и другие варианты?


Бред. Неудивительно, что 

Скорее всего только AND



Кто же спорит? Это был промежуточный вариант по большей части для некоторой иследовательской работы. Вопрос то не в этом =)

В этом. На описанной структуре и для описанной задачи разница между различными СУБД будет незначительной.

Так, что же делать? Неужели вычислительная мощность современных программно-аппаратных комплексов (массового производства) не способна справиться с поставленной задачей? Я же не ставлю условия, что данные должны храниться именно в СУБД или вообще использовать б-индексы и т.п. (Выбор на раздел СУБД выпал только по той причине, что это наиболее подходящий раздел в контексте других).

Покажите лучше структуру, которая была создана под описанную задачу, и тот запрос, на котором MySQL "провалился", в форме explain.

Нет ни какой особенной структуры. Есть 1 таблица в которой 1 объект = 1 строка. Можно представить как id (int), param1(int) ... param20(int). Запросы не проваливаются, а выполняются слишком долго (от 5 секунд до бесконечности, а по условию мы хотим чтоб скорость выполнения запроса была менее секунды).

За сегодня мы протестировали ElasticSearche и он показал хорошие результаты, выполнив часть задач (за счет Facet Searche и агрегаций), но в нем нет группировки в том виде, в каком она есть в MySQL, поэтому есть еще черные пятна. Если говорить конкретно, то он не может выполнить запрос типа SELECT distinct param1 FROM table ORDER BY param2, где мы хотим получить список уникальных значений param1, отсортированные по param2. Это конечно расстраивает, но от поискового движка я большего и не ожидал.

Как думаете, CouchDB сможет выполнить задачу описанную в сабже + этом сообщение?

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


Покажите лучше структуру, которая была создана под описанную задачу, и тот запрос, на котором MySQL "провалился" который выполняется более полусекунды, в форме explain.

Возможно я что-то не учел в запрос, поэтому приведу более точный пример SELECT country FROM cities ORDER BY max(population) GROUP BY country; В таблице храниться список городов и мы на основание этой таблицы хотим получить: "Список стран, отсортированный по количеству жителей в самом населенном городе каждой страны".

По поводу инфраструктуры, уточните пожалуйста, что еще я могу уточнить касательно ее? Если абстрактная table (id, param1 (int), ..., paramN (int)) не слишком понятна, я могу привести такой пример. cities (id(int), country(int), region(int), pipulation(int), crimeLevel(int), turistLevel(int), latitude(int), longitude(int), ну и еще дюжина цифровых параметров, которые могут характеризовать город). Все храниться в одной таблице и все поля - индексы. Все это в MySQL на сервере, без реплик, кластеров и т.п. 

К сожалению информации о реальной таблице я предоставить не могу т.к. NDA.

При наличии индекса по (country, population) запрос должен лететь. И лимитирующим звеном будут либо пустой кэш, либо сетевой канал передачи данных от сервера. Ну и для соблюдения синтаксиса надо две последние секции местами поменять, есссно.


Вот-вот... достаточно редко индекс по одиночному полю эффективен - чаще получается так, что составные индексы эффективнее. Но поскольку на каждый чих не наздравствуешься - приходится искать компромисс между расходом дискового пространства на чёртову кучу индексов и быстродействием запросов. Естественно приоритет отдаётся тем наборам индексов, которые повышают эффективность выполнения более часто выполняющихся запросов. Плюс учёт кучи дополнительных факторов - ну там сколько памяти можно отдать на разные нужды, умеет ли СУБД мержить индексы, и т.д., и т.п...
Впрочем, индексы вовсе не единственный способ повышения эффективности.

Akina, Я правильно понял, что 60 000 000 записей должны летать в MySQL, если операции идут по правильно проиндексированным полям, а база данных, сервер и инфраструктура корректно настроены?

Это сообщение отредактировал(а) BuShaRt - 9.4.2014, 13:56

Да, скорость обработки должна быть высокой. Впрочем, конкретных цифирей в секундах не дам. Как я понимаю, у тебя есть реальная база. Ну так и попробуй прямо на ней скорость обработки своего запроса с указанным индексом и без него. Заодно просмотри скорость работы с пустым и набитым кэшем. Движок лучше MyISAM - судя по описанию таблицы, транзакционности на ней не требуется.

Провел эксперимент на свое личном компьютере (Fedora 20, 16gb, i5, количество процессов стремиться к нулю). Как видим из 47117231 миллионов записей, используя индекс, самый простой запрос выполнялся почти 7 секунд. Если добавить в запрос еще GROUP BY и WHERE то может на минуту "потеряться".

Это сообщение отредактировал(а) BuShaRt - 10.4.2014, 19:24

Учитывая, что всегда можно придумать логичный запрос, который ответит полным набором данных, а полный набор данных у нас 12GB -- можно сделать вывод, что нужна производительность как минимум 24GB/s. Это скорость памяти на средненькой машыне. Из этого следует, в первую очередь, что все данные должны быть в RAM. Для ультра-параллельной обработки можно было бы ещё подумать о том, чтобы расшардить их на 500 компьютэров, и брать у каждого с диска но, думаю, у Вас такого параллелизма всё равно нет, чтобы это развлечение окупилось.

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

Ну, и в третью, что, похожэ, автор перемудрил с условиями и на самом деле ему это нафиг не нужно.

На предложенной в качестве образца структуре (страны, города, население) требование в полсекунды действительно выглядит вакханалией абсурда...

А, ну и да, в случае SELECT count(entity_id) поможэт, разумеется, создание индэкса на entity_id. Но это мелочи.

Добавлено через 3 минуты и 48 секунд
И ещё в случае MySQL можэт помочь вместо SELECT count(entity_id) сделать SELECT count(*) - SELECT count(*) WHERE entity_id IS NULL;
При условии существования индэкса, конечно.
И если на самом деле entity_id мало где NULL.
Но это ужэ надо проверять и тонкости.

Добавлено через 11 минут и 42 секунды


Честно говоря, абсурдом тут выглядит скорее 50 миллионов записей и 12GB данных. Учитывая, что в РФ менее 10 тысяч населённых пунктов, страна у нас сравнительно большая, а стран -- менее 200.

Но автор сказал жэ, что это он выдумал пример. Так что что докапываться.

Да, некоторые банальности.

Во-первых, если по произвольному набору из 20 индэксов -- то можно ещё добавить композитных индэксов, по тем парам/тройкам полей, у которых сравнительно много данных.

Во-вторых, 50000 записей в сутки, дажэ если on-line добавление, дажэ с учётом 3-х кратных пиков -- ну, это 3 записи в секунду. В общем, нагрузка на запись маленькая, индэксов можно добавлять очень много. А если сделать группированное потоковое добавление -- то и ещё большэ.


В-третьих, тут буквально вчера один человек вывел чеканную формулировку: BigData - это когда продавец втирает менеджерам, что специалисты по базам не нужны, а можно просто купить их чудесные тулы. (А рядом стоятя продавцы железа и радостно кивают головами)

В общем, не старайтесь компенсировать мало количество своих знаний крутыми утилитами. Дажэ если и поможэт -- то очень ненадолго. Тем более, что например mongo по сравнению дажэ с MySQL -- очень как-то глупо сделанное барахло.

Не понял. Что значит нафиг не нужно? У меня есть реальная база с 60 000 000 записями из которой мне надо делать выборки для веб-сервиса. Выборка подразумевает возможность фильтрации по 7 полям и сортировки по еще 5. Кроме того, результат вычисления группируется еще по 1 полю. Учитывая, что мы работаем с веб-сирвисом и у него хватает забот, кроме этой выборки, то для болие-менее адекватной суммарной скорости работы (1-2 секунды), мы должны тратить на эту конкретную задачу не более 500 мс. 

Что касается приведенного примера, да он придуман, но тоже имеет право на жизнь. Город в абстрактном понимание - это любой насиленный пункт. Их легко может быть 60 000 000 по всему миру. И все они храняться у нас в базе. А мы предоставляем нашим клиентам статистику по этим городам. И вот одному клиенту стало интересно, какие самые заселенные города в каждой из стран. На выходе он хочет список
Россия Москва 11 500 000
Китай Пекин 11 500 000
ФРГ Берлин 3 500 000
и т.п.

И этот список он хочет получить быстро, а не спустя 20 секунд. не пойму в чем абсурдность желания предоставить такой функционал.

Ну, вот видите, ужэ не любая выборка.

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

И так далее. Потому я и говорю, что с требованием "любая выборка 0.5с" Вы явно перемудрили.

Сейчас я пытаюсь компенсировать недостаток знаний этим форумом. 

Я, вероятно, подтупливаю, но из всего, что уже написано в этом треде я так и не сделал окончательного вывода. Может поможите подытожить? Что мне нужно сейчас сделать (допилить, почитать, изучить), чтоб соорудить систему, способную справиться с моими требованиями?

Добавлено @ 14:22

Уговорили. Допустим у меня есть конкретная выборка. Как я уже описал выше. В ней может быть условие WHERE по 7 полям таблицы, сортировка по одному из 5 полей и обязательно группировка. Результат я должен получать партиями по 10 записей. Вот пример: 1 из 7 возможных условия - выборка по стране, сортировка + группировка. База "уходит" почти на минуту.



Попробую предугадать шквал обвинения, мол чего я ожидал, ведь у меня "Using where; Using temporary; Using filesort", но изначально условие задачи подразумевало "любые запросы" т.е. в том числе запросы которые ну не могут без временной таблицы обойтись или должны группировать по 1 полю, а сортировать по другому.

Это сообщение отредактировал(а) BuShaRt - 11.4.2014, 14:25

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

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



Чего ты ожыдал -- у тебя всё остальное (ну, contry_id=2) занимает треть базы, а реальное поле, по значению которого  фильтруется --  pamam_for_sorting -- похожэ, вообще без индэкса. Кстати, если у каждого pamam_for_sorting этих entity_id более сотни -- то как раз место для композитного индэкса.

Добавлено @ 14:42
PS Да, и можэшь сказать, что тебе муторно, mysql тупой, ты хочешь все запросы и не хочешь заниматься выяснением, какие нужны. Ну, тогда вперёд -- сервер посвежэй, и 30GB RAM, всё в памяти, допиленные движки баз данных под zero-copy, в общем оно дажэ можэт получиться.

Это сообщение отредактировал(а) tzirechnoy - 11.4.2014, 14:46

1. Ну да, занимает треть базы. Что я могут в данном случае оптимизировать?
2. pamam_for_sorting - это индекс.




Все уже сделано. Перед вами пример одного из самых простых запрос к 1 таблице (речь все время идет об 1 таблице, без джоинов и т.п.) работающий с 3 полями, каждый из которых является индексом. Проще уже не куда. Что я упустил в оптимизации?


Давайте будем или обсуждать конкретные вещи или вообще ничего не будем обсуждать. Если вы знаете как помочь и, что именно подсказать - я буду очень благодарен, если же хочется просто потрепаться, а задача сама по себе вам не по зубам, то не надо наводить смуту в треде - лучше пообщайтесь в курилке. Что я подразумевают под конкретными вещами? Я привел пример конкретного запроса к которому не применимы все ваши рекомендации по той простой причине, что запрос уже выведен на основание аналогичного списка рекомендаций. Давайте обсуждать этот запрос и пути его оптимизации, а не абстрактные вещи.

Это сообщение отредактировал(а) BuShaRt - 11.4.2014, 15:04

В таком случае можно, очевидно, как-то оптимизировать, чтобы он использовался. И начать такую оптимизацыю -- с вычисления, с авторучкой в руках, как Вы бы максимально эффективно выбрали нужные Вам данные из Вашэй базы. Подсказка: в данном конкретном случае на логичных примерах данных это должно было бы занять один index scan по одному индэксу где-то на десяток, или максимум первые сотни значений, и index access по другому индэксу.

Впрочем, я посмотрел, мысклевый оптимизатор действительно вполне ошыбается в таких случаях. Более того, по-моему у него вообще нет необходимого двухпроходного варианта для случая GROUP BY  -- потому чтобы впрямую такой запрос заработал, кажэтся, необходим композитный индэкс на (pamam_for_sorting, entity_id).

Ну, или конкретно этот запрос -- переделать на SELECT DISTINCT entity_id ... без GROUP BY.

Опять жэ впрочем, группировка таки нередко никак не работает эффективно -- и потому приходится делать суммарные статистические базы. Т.е., фактически, та жэ группировка, только сделанная один раз. Благодаря триггерам это относительно несложно делать и поддержывать в актуальном состоянии (а в постгрэссе ещё есть печеньки в виде правил переписывания запросов, позволяющие автоматически подставлять нужное в запросы к определённой таблицэвсё равно этим нереально нормально пользоваться). Ещё раз впрочем -- это не для этого конкретного запроса, его реально и так запинать.



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

Добавлено через 2 минуты и 43 секунды
PPS Ещё банальность: базу -- на SSD. Да, они ненадёжны, потому бэкапы должны быть чёткими, и в ответственных случаях надо что-то придумывать с очень горячим стэндбаем -- но average seek time HDD добавляет столько проблем, что лучшэ с ними в ненужных ситуцацыях не связываться.

Сам спросил - сам отвечу. CouchDB тут не подойдет т.к. она умеет работать только с одним индексом (он может быть составной, но к нему применимы все ограничения составного MySQL индекса)... 

А в MongoDB похоже нету возможности делать группировку и сортировку одним запросом...

А sphinx каким-то чудом делает запросы с группировками и сортировками за считанные доли секунды... Why?

Сфинкс использует свои индексы для ускорения обработки. Нацеленные именно на ускорение операций, и в первую очередь текстового поиска.

Akina, 
Я понимаю, как Sphinx может обойти базы данных в вопросе полнотекстового поиска. Но за счет каких таких индексов он опережает их в вопросе поиска и группировки по целочисленным значениям? Какие такие хитроумные индексы он использует (ведь не btree же)? И почему их не используют базы данных, разве им не интересно работать шутрее?

* з.ы. Акцентирую внимание, что о полнотекстовом поиске я не говорю. Речь идет только о работе с целочисленными значениями. 

Если кому интересно, то на данный момент мы остановились на использование связки ElasticSearch + SphinxSearche, разделив между ними запросы т.к. они с разной эфективностью вытягивают запросы разных видов. Я не знаю, как это происходит, но эти Ребята, справляются за доли секунды со всеми запросами, в то время как самые простые запросы в MySQL и MongoDB по индексам(!) занимали у нас порядка 10 секунд, а некоторые сложные уходили в глубокие раздумья минут на 30 (в них индесы уже не использовались т.к. нужны были составные, а предсказать все комбинации просто не возможно).

исходя из примера, который вы привели по городам - то 60 000 000 строк выглядит действительно довольно сомнительно. Более того таблица с 20 параметрами, пусть даже целочсиленными.... Может имеет смысл перепроектировать саму таблицу/БД, а не пихать все и вся в одну таблицу?? Просто мысль, так как конкретно что по смыслу представляют собой все эти 20 параметров, как и вся таблица - осталось за темной завесой!

И еще как вариант-хитрость, на каждый запрос формируется своя таблица, более меньшего объема, соответственно более эффективная для запросов с выборокй! Эти таблицы периодически обновляются скриптами-демонами, в которых акутальные запросы могут исполнятся не 1-2 секунды, а секунд 10 к примеру, периодичность исполнения этих скриптов размазывается последовательно(каждому скрипту выделяется допустим 2 минуты на исполнеие - достаточный запас), с определенной периодичностью(как только отработал каждый скрипт - вся группа проходит новый цикл). Акутальность данных будет отставать от нескольких минут, до пары десятков, ну полчаса максимум. Но учитывая результат запроса по городам, пример который вы привели - это вообще никак не критично!   

При таком варианте можно достаточно легко оптимизоровать как и MySQL так и любую другую субд, без "крайней экзотики"   

Это сообщение отредактировал(а) Bulat - 22.4.2014, 10:44

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

BuShaRt, я это все к тому, что сложную задачу все же легче разбивать на несколько более простых и решать каждую отдельно, а потом синхронизировать! Или по правилу Unix - пусть твоя программа делает что-то одно, но делает это хорошо!   

=) Если кто-нибудь найдет этот топик и отважиться дочитать до конца, пусть знает, что  мы нашли нужный функционал в ElasticSearche и он просто летает. Добиться желаемых 500 мс, применимо ко всем запросам,  конечно не получилось, но после всех иследований и замеров других продуктов, было принято решение, что 2-3 секунды на особотяжелых запросах при нашей посещяемости допустимы в конечном счете наиболее популярные запросы примерно в 500 у нас укладываются.

facepalm.jpg