Помогите оптимизировать запросы!
 

Здравствуйте!

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

В числе прочего, в базе есть таблица, имеющая тенденцию сильно "распухать". Сейчас в нее добавляется порядка 2000-3000 новых записей в день, и это по идеологии проекта еще очень мало. Не исключено возрастание наполняемости до 100000-1000000 новых записей в день. Очень бы хотелось хранить эту таблицу (это полный ЛОГ событий и транзакций системы) в полном объеме.

1. Подскажите - не маразм ли это идеологически? Одна таблица с сотнями миллиардов записей и больше?
2. Встает очевидная проблема - уже сейчас. Время запроса к такой таблице. Сейчас у меня сделано любительски (я только начинаю осваивать ЭТО) - внутри цикла WHILE крутится сначала SELECT-запрос - это проверка уникальности новой записи. Если SELECT возвращает нулевой результат - происходит INSERT INTO. Мне подсказали, что делать внутри внешнего цикла каскад подобных запросов - дурь, что можно делать один запрос, а весь каскад проверок может сделать сама СУБД, за время в сотню раз меньше...
Подскажите, реально ли это? Посоветуйте про какие операторы мануалы изучать?)) Или книжку хорошую... Использую РНР и чистый С, с акцентом на второй.

3. Какое типичное время одиночного запроса следует ожидать при размере таблицы допустим, в сотню миллиардов строк? При таком раскладе можно будет только под БД выделить отдельный XEON-сервер.

С уважением, Евгений

Если это лог — зачем проверка на уникальность, причём всей записи? Почему не использовать в качестве ключа просто автоинкрементный идентификатор-счётчик?

описание действительно звучит подозрительно. Т.к. принципы работы СУБД отличаются от принципов работы с массивом в его каноническом понимании.
Задавать операции принудительного перебора строк таблицы - ведет к катастрофическому падению производительности системы.
Не ради рекламы будет сказано: сегодня-завтра проходит выставка российских интернет технологий, в частности представлена секция HighLoad. Насколько будет оправдан входной билет стоиомстью в 21 рубль - не знаю. Но жадный хоботок можно опустить в мозги тем людям.

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

КАк вариант "за что купил".
Есть системы СУБД, которые размещаются в оперативной памяти. По быстродействию, они, разумеется впереди планеты всей. Соотв. выделить n'цать Гб под "оперативный" лог, остальное в архив.

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

Как вариант, посмотрите диагонально протоколы FIX, FAST используемые на фондовых биржах. Это может быть не совсем "лог", но возможно идеи будут полезны.

Столь стремительное возрастание запросов - а сеть выдержит данный поток?

P.S.
Это всего лишь поток сознания! Соотв. относиться к нему надо с должным пониманием и опаской ;)

>>зачем проверка на уникальность, причём всей записи?

Прежде чем событие будет занесено в базу - оно поступает с удаленного устройства. По протоколу, который из соображений целостности имеет избыточность - т.е комбинация "место-время-человек" являющаяся уникальной, может поступить несколько раз, или из одного, или из нескольких физических каналов, но в базу одно и то же событие должно заноситься один раз, в данном случае с проверкой по всем трем полям, так как только их комбинация является уникальной. Для этого, перед INSERT INTO делаю SELECT, если последний не равен 0 - текущие данные отбрасываются, и далее по циклу. При INSERT INTO разумеется, событие обрастает уникальным ID, и флагом того, что оно только занесено - но еще не обработано, и уже из базы извлекается под уникальным номером.
А ПОТОМ - уже другое приложение извлекает запись с пометкой "не обработано", обрабатывает, и производит UPDATE всей строки, в которой помимо полей "id-место-время-человек-флаг" есть еще поля, типа "место события на человеческом языке", "имя на человеческом языке", "списанная сумма" и.т.д.

А потом - человек сам, на сайте вводит содержимое поля "человек" (цифровой аккаунт), и лимит вывода - и получает ответ (детализацию) событий по своему лицевому счету, на человеческом языке. Т.е крохотную выборку из потенциально многомиллиардной общей БД. Все это уже создано, и работает. Но пока не поздно надо переделать - т.к обработка суточных 3000 событий занимает уже 8-10 минут при кол-ве строк ~120000. Я не прошу решить мне задачу - подскажите где копать!))

>>Столь стремительное возрастание запросов - а сеть выдержит данный поток?

Под такой поток - можно сервер в датацентр загнать. Устройство цепляется по ТСР с использованием accept() т.е ожидается нагрузочная способность не хуже чем у apache. 1000000 соединений в сутки это около 11 в секунду... Нормальный сервер должен выдержать... и БД сервер в.т.ч. Или accept-сервер расщепить на несколько по слабее, наибольший упор сделав на БД. Но это мечты, но мечтаю я по полной...

Так в отсутствии конкретики, мы этим и занимаемся :)
Возможно имеет смысл провести еще одну нормализацию и выделить еще пару таблиц для работы с "человеческим описанием"?
+ Введение максимального количества цифр. Т.к. операции численного сравнения будут быстрее, чем символьного.
Получится что лог - будет сборником идентификаторов.
Это перебор.

Кстати, а как именно выглядит процедура?
На каждую из 3000 строк происходит поиск SELECT по все таблице?
Т.е.
?

evpu, примерно так вижу:

1. Выделить под сбор отдельную таблицу с полями: ID, и первичным ключом из трёх полей «место-время-человек». Всё прочее вынести в отдельную таблицу, имеющую по ID целостную связь 1:1 с первой.
2. Как уже писал выше, в первой таблице сделать первичный ключ из трёх полей «место-время-человек». Никаких проверок select'ом перед вставкой не производить вовсе — это задача базы данных проверить и отвергнуть операцию добавления записи с дублирующим первичным ключом (надеюсь, MySQL умеет сие, не работал с ним программно).

P.S. Как Вы понимаете, это примерная схема. Не зная предметной области, не зная деталей трудно советовать что-либо конкретное. Например, тот же флаг обработки — держать в первой или во второй таблице?!

>>While 1 - 3000
>> Select * FROM Table
>>If 0 Then INSERT

Именно. Типа того.

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

>>Выделить под сбор отдельную таблицу с полями: ID, и первичным ключом из трёх полей «место-время-человек». Всё >>прочее вынести в отдельную таблицу, имеющую по ID целостную связь 1:1 с первой.

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

Т.е насколько я понял, сочетание полей «место-время-человек» может быть уникальным 3-мя полями отдельно (а точнее 4 - место состоит из 2-х частей)?? Тогда, можно проще - я сырую строку прилетающую из аппаратуры включающую в себя эти 3 компонента для простоты разбиваю на 3 поля, а могу ведь этого не делать - загонять в базу одним общим полем "raw_string" - которое и должно быть уникальным...
И что - при 2-х кратном INSERT одного и того же, дубликат может быть отвергнут самой БД??? Круто... Попробую.
На эту тему последнее - а можно в одном запросе послать не 1 INSERT а сразу кучу (15-20 и более)? А уж БД в процессе сама разберется с уникальностью...

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

Э… Откуда ж мне знать Вашу предметную область? Так именно Вы выше написали, и именно так я и понял.

Не уверен, что сие будет правильным. Впрочем, Вам на месте виднее будет.

Вот Вам для примера база Microsoft Access: Файл 125362. Для простоты взяты «место» и «человек» как простой текст (если могут повторяться, то они обязаны содержаться в отдельных таблицах, куда из записи основной таблицы должны вести ссылки). Первичным ключом таблицы «Таблица1» является тройка полей «место-время-человек».

При исполнении кода процедуры из «Модуль1» производится попытка вставки четырёх записей поочерёдно отдельными запросами «INSERT INTO … VALUES», причём третий и четвёртый запросы — дублируют по VALUES первый и второй.

Так вот, будет вставлено только две записи по первому и второму запросам. А по третьему и четвёртому — вставка записи будет отвергнута, поскольку недопустимо дублирование первичного ключа.

Разумеется, это Jet и Access. Как там обстоят дела в MySQL, я не знаю, не пробовал. Но общие принципы построения реляционных баз данных должны быть одинаковы для всех.

Можно, «INSERT INTO … SELECT … FROM …». Но, как я понимаю, Вам это не подойдёт.

Подобный подход действительно роняет производительность СУБД в ноль
СУБД работает не с массивами, СУБД работает с множествами.
Запомните как мантру! (искренне не в обиду)
Любой итерационный подход - табу!

У вас есть таблица лога, есть основная таблица.
Вы делаете пересечение таблиц (Join_(SQL)) с необходимыми параметрами.
Получаете дельту и уже ее добавляете к основной таблице.
Кстати!
Merge
Объединение двух таблиц с условием. Тут отдельно стоит уточнить вопросы блокировки таблиц. Краем глаза видел слова в описаниях, но досконально не выяснял.

>>а можно в одном запросе послать не 1 INSERT а сразу кучу (15-20 и более)? »
>Можно, «INSERT INTO … SELECT … FROM …». Но, как я понимаю, Вам это не подойдёт.

Кое в чем подойдет. Перспектива тратить на 1 вставку (или что угодно другое) аж целый запрос - ИМХО жирно. Полагаю, что СУБД прекрасно в состоянии отработать одновременно 20-30 фраз, которые можно впихать в один запрос. Поправьте, если я не прав... буду пробовать.

>>Так вот, будет вставлено только две записи по первому и второму запросам. А по третьему и четвёртому — вставка записи будет отвергнута, поскольку недопустимо дублирование первичного ключа.

Это проверил - работает на MySQL! Уже хорошо!

Подскажите еще по одному - так таки если таблица будет иметь указанный чудовищный размер (сотни миллиардов строк) - будет ли разумным время ответа при формировании из нее небольшой выборки?

Короче... Всем спасибо, я еще поизучаю вопрос, но в крайнем случае, буду использовать временную таблицу, размером до 100000, а при превышении этого размера данные будут переноситься в архивную таблицу, а временная очищаться...

evpu, ну так, рекламы для facebook-presto
Чисто для покурить поискать слова и спрятанные за ними технологии.
Beeline AFAIK хранит информацию до полугода/года. Дальнейший запрос на архивные данные - через заявку.
Т.ч. тут как и везде - сперва определить потребность, потом смотреть, как этого крякозябла воплощать к жизни.
Если эта таблица поместиться на быстродействующую память, то будет. А нет-нет.
Исхожу из статьи, посвященному обработке гигабайтных баз данных под MapReduce что ли.
Вывод был таков: если БД помещается в оперативку, или на SSD накопитель, то пользуйте PostgreSQL все нормально. А вот если оно туда не влазит, то это уже можно попробовать назвать BigData и думать над соотв. инструментом.
Т.ч. если этим сотни миллиардов строк уложатся пусть в пусть 100Gb пространства, то SSD - снивилирует эту проблему.
Бекап понятно ленты, диски. Но это уже другой вопрос.

Тот синтаксис предназначен для добавления записей в таблицу, извлечённых из другой таблицы. И диалект я привёл, разумеется, для Jet.

«Перспектива тратить на 1 вставку…» — это вполне нормально. Вспомните, что ядро базы должно при этом отработать все триггеры: каждого поля, всей записи, всех ключей, целостности базы.

Сейчас у Вас сколько примерно строк?

lxa85, небольшая выборка — это и значит небольшая выборка. Поиск должен будет вестись только по индексам, а не по:
И извлекаться будут не все эти объёмы. Хотя, как я понимаю, даже три longint (Jet) или bigint (MS SQL) плюс один double — размер строки основной таблицы, если действительно «место» и «человек» могут повторяться и обязаны быть вынесены в отдельные таблицы, займёт несколько больше 100 Gb.

>>Сейчас у Вас сколько примерно строк?

За 100000 перевалило... Время запроса произвольной строки около 10-20мс.


Извиняюсь за офтоп... Тут задачка для первокласника... Вот есть функция sscanf() производящая форматированный ввод из строки. Конкретнее пример:
sscanf("192.168.1.1 root jgjFGHjhj 20000 0", "%s %s %s %s %s", ip, login, pass, listen, sqlport );

Приведенаня строка на самом деле - читается из файла.... Но "192.168.1.1 root jgjFGHjhj 20000 0" это неудобно, хотелось бы настроить форматированный ввод типа:

"<ip>192.168.1.1</ip> <login>root</login> <pass>jgjFGHjhj</pass> <listen>20000</listen> <sqlport>0</sqlport>".

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

Добрый день!

По данной теме, пришел к решению, писать данные во временную таблицу temp_log, с последующим переносом в основную - stat_tab, именно она будет распухать.

Для переноса соорудил конструкцию:

INSERT INTO stat_tab SELECT * FROM temp_log WHERE `flag` = '1' ;

(флаг=1 - копируются записи, прошедшие транзакционную обработку.)

Вопрос - как соорудить команду, чтобы в процессе запроса помимо всего прочего, строка после вставки удалялась из temp_log?

Доброго времени суток. Вопрос в к Вам как раз по поводу оптимизации текст по ссылке http://forum.oszone.net/thread-304322.html. И вообще где можно прочитать о зависимости аппаратного обеспечения на показатели тестирования какие будут идеальны. А какие уж очень ужасны.

Да, тут речь идет о хранимых процедурах, как раз оно! На таком решении и сделал!

Это вообще БОМБА! Когда создавал эту тему - и предполагал об этом подсознательно... Там и внутрисерверные циклы, и все что угодно.


Только модифицировать пришлось:
INSERT INTO stat_tab SELECT `id`=0,``...()весь длиннющий перечень...`` FROM temp_log WHERE `flag` = '1' ;
TRUNCATE temp_log;
И далее всякие COMMIT и т.п.

Модификация потому, чтобы idшники пересчитывались в новую таблицу.

Вроде, про профилировку никто еще не сказал. Сюда и далее по поиску.

Кстати да... Спасибо, поюзаю!

>>какие будут идеальны. А какие уж очень ужасны.
Тут сказать сложно... Но например - я взял SSD, на его базе поднял linux-apache-mysql-php (LAMP), и там же исполняется бинарник написанный на С.
Так скажем - отличие РАДИКАЛЬНОЕ от Денверов и девелоперских конфигураций - на порядок. Я как будто только успеваю отправить запрос - как мне выплевывает ответ, несмотря на кучу запросов. А конфигурация тем не менее проста и бесхитростна - core2duo 8400 3GHz, RAM 3GB, SSD накопитель.