Добрый вечер,

Заполнил сегодня базу для теста парой миллионов записей и программа мягко говоря перестала работать, хотя запрос вроде правильный и ничего особо сложного не вытворяет. База данных - MySQL 5.0, тип таблиц InnoDB, сам запрос выглядет вот так

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

explain запроса показывает


профайлер показыет, что основное время запроса выполняется копирование в темповую таблицу (Copying to tmp table?). В чем причина?
customer_account_id и vendor_gateway_id являются внешними ключами (foreign key) и ссылаются на таблицы customer_accounts и vendor_gateways соответственно. Поле ID - primary key. Если убрать сортировку скорость повышается до нормы и копирование таблиц больше не происходит, тоже самое если оставить сортировку, но убрать JOIN-ы.

В чем может быть причина?

Заранее спасибо.

имхо слишком много данных для сортировки... Может сначала выбрать 10 id, и загнать их во внутренний select, а потом уже к ним подтянуть остальные таблицы...

В принципе вариант, сейчас попробую, но по моему нормальная база должна была сама это сделать  
Обыкновенный JOIN с сортировкой по индексам  

Сейчас попробую

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

Сработало!  

Спасибо! 

А с этим что можно сделать?
  


не пойму я ... то ли чего-то не то делаю, то ли MySQL дрянь, либо третье - настроить его надо как следует.

профайлер показывает Sending data - 99,98%

Это он так долго считает?

Давайте дамп табличек с тестовыми данными - посмотрим...

В каком смысле? 2 миллиона записей?

Вот таблица в упрощенном варианте






вот примерно так

Это сообщение отредактировал(а) azesmcar - 27.2.2010, 21:52

afaik, InnoDB, как и все нормальные системы не хранит количество значений в ПК. Если тебе нужно сколь нибудь регулярно получать этот count, позаботься об этом заблаговременно. Напиши триггерки и считай по мере насыщения таблицы данными. Однако в этом случае будь готов к тому что вставка в таблицу сможет производиться единомоментно только одной сессией. Кстати, именно из за этой причины только MyISAM и хранит в индексе количество записей, там и так блокировка захватывается только на таблицу.

Еще. Непонятно зачем тебе джойны в этом запросе. у тебя есть FK, джоинишься ты нотнуллабельными полями в первичные ключи, в результате будет тоже количество что и в billing_cdr. Джойны не нужны. Ты уверен что оптимизатор это понимает и не выполняет их?

Это сообщение отредактировал(а) Zloxa - 27.2.2010, 23:21

Ну это упрощенный вариант запроса, в итоге тут может идти фильтрация по некоторым полям, доступным из других таблиц, таких как customer или vendor. Фильтры задаются извне, это и делает бессмысленным подсчет количества срок, так как мне в любой момент может понадобиться подсчитать количество срок в отфильтрованном запросе. Более того, результат в основном нужен в фильтрованом виде (в отчетах), так он практически не нужен.


Как я написал выше, JOIN был нужен, но вчера я провел несколько изменений в структуре таблиц, сейчас для count -а join убрал. Для 2х миллионах записей запрос подсчета суммы и сам запрос с сортировкой отработал за 0,8 секунд. Хороший результат. Сейчас уже вроде как неплохо, всю ночь поставил на stress test, залил в базу 4,5 миллиона записей, сейчас отрабатывает долго, чистый запрос

работает 14 секунд, но тут уж я так понимаю ничего не поделаешь, или нет?

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

Сейчас вожусь с одним отчетом

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


вот тут начинается .. 1 минута, а мне таких функций штук 10 надо (avg(duration), min(duration) ... ).
  

как вариант - можно сделать materialized view (точнее сымитировать его через cron), но пока не хочется, желательно получать свежие данные. Еще как вариант можно строить отчет на момент вставки, но тогда замедлиться сама вставка, что вызовет замедление не web приложения а ядра системы, в следствии - уменьшается количество максимальных одновременных звонков, показатель нужный. Но в крайнем случае так и поступлю, поддерживать 7000 одновременных звонков и не давать возможность их потом посмотреть - бессмысленно. В любом случае этот вариант считаю нежелательным, не люблю много зависимостей. По сути та же информация будет храниться в двух местах, в разном виде.

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

надо бы одновременно на других базах попробовать

Это сообщение отредактировал(а) azesmcar - 28.2.2010, 10:50

azesmcar, а когда оракл на десятке млн ляжет, куда мигрироваться будешь? Миграция на оракл ошибок проектирования, имхо не устранит. Вполне очевидно что тебе нужна преаггрегация по нужным тебе разрезам.


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

Да, конечно, но разве считать сумму на большой таблице - ошибка проектирования? Я писал примерно тоже самое на оракле несколько лет назад, замедление чувствовалось при 16 миллиардах записей. Я не думаю, что использование агрегат функций на относительно больших таблицах является ошибкой проектирования. Сегодня это 1 отчет, завтра их может быть сотня, и каждый по разному будет формировать данные, преаггрегация данных для каждого отчета ИМХО не лучший выход, но вариант решения.


В MySQL JOB -ов нету к сожалению, придется ставить обычный триггер.

тыж сам упоминал крон.

  
/*а там не было фастрефреш он коммит аггрегирующей матвью и квериреврита?*/
У мну count ~100млн считается весьма долго.
Если хочешь завтра сниму тайминг, думаю будет не менее пяти минут.


Это сообщение отредактировал(а) Zloxa - 28.2.2010, 13:08

а .. ну да  


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

Добавлено через 5 минут и 17 секунд
кстати, я так понял MySQL кэширует, первый запрос на count длиться долго, второй почти мгновенно.

Это сообщение отредактировал(а) azesmcar - 28.2.2010, 13:14

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

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


Точно!

Это сообщение отредактировал(а) azesmcar - 28.2.2010, 13:24

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

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

Да, у нас это редкость, спроса мало.

Сейчас попробую сделать job-ы, спасибо  

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

что делать?

Это сообщение отредактировал(а) azesmcar - 28.2.2010, 18:21

Выбирать правильный разрез преаггрегации 
/*под разрезом представления статистических данных обычно имеют в виду поля, перечисленные в group by*/
У тебя уже один из разрезов выявлен - год.  Возможно месяц или день.
Второй, по всей видимости - вендор, т.к. по нем группируешься.
Можно иметь несклько разрезов преаггрегации, для разного рода отчетов.

Вообще сложно тебе советовать, не понимая твоей специфики. Я с задачами биллинга, увы, никогда не сталкивался. И, кстати, на приведенной тобой структуре вообще не фегурирует ни одной даты, потому как реализовывать требование "за последний год"- вообще не ясно.


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


Это сообщение отредактировал(а) Zloxa - 28.2.2010, 18:52

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


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

По сути CDR (Call Duration Records) - таблица, которая хранить данные о всех звонках (даже не прошедших), данные об ошибке, коды разные, айпи адреса и тому подобное .. в общем всю ту фигню, которую посылает свитч. Там же хранится дата. Отчет, о котором мы говорим - суммарный, его смысл - посмотреть суммарные данные за некий промежуток времени, т.е. сумму длительностей всех звонков, среднюю длительность звонка, сколько всего звонков было, сколько из них не прошло и все это для каждого customer-а и vendor-а. 

Еще как идея, хранить преаггрегацию, скажем по месяцу.  
А аггрегацию по произвольному интервалу делать аггрегацией по объединению трех запросов:


Добавлено @ 20:02
ps сорри за ораклиный синтаксис в ветке по MySQL. думаю о чем речь понятно, а иначе выразить мысль не смог.

Это сообщение отредактировал(а) Zloxa - 28.2.2010, 20:07

Zloxa

А если пользователь захочет данные за последние 2 дня например? Или я не так понял?
Сейчас убрал поиск по времени, все равно он особо не нужен, создал таблицу

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

Вроде работает, запустил автомат-тест, завтра утром посмотрю, что там произошло.


Zloxa
Спасибо за помощь.

azesmcar, а прокэшировать запрос нельзя чтоли?

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

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

Это сообщение отредактировал(а) gcc - 28.2.2010, 21:05

Тогда второй и третьи подзапросы ничего не отберут, если я не накосячил.

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

Добавлено через 1 минуту и 7 секунд

информация в кэше актуальна до ближайшего инсерта.
нет?

инсертит одна сессия - ядро системы, читают пользователи через веб. Я видимо слишком долго сидел за компьютером, совсем не соображаю, в общем проблема с отчетами решилась, все в порядке, но первая проблема осталась, я только допер  

В общем дела обстоят так:

Есть веб страница, на ней показываются последние звонки, все, для всех пользователей, эта страница существует для администратора. Он имеет возможность фильтровать данные, например: показать все звонки на номер 37410%, или все звонка от конкретного customer-а или и то и другое (все звонки от customer-а на номер xxxx). Совет

сперва мне показался удачным, но сейчас понимаю что тут есть проблемы, а именно
выбираем 10 id (т.е. первую страницу показа) потом подключаем к ней таблицу customers и vendors, а уж потом фильтруем. Что получаем в итоге? Отфильтрованные строки из 10-и строк а не 10 отфильтрованных. Да и count считать по любому нужно на фильтрованном запросе.

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

Есть идеи?


да 

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

ну так мне и нужно чтобы он все время вызывался.


  это по поводу отчета или первого вопроса?

Это сообщение отредактировал(а) azesmcar - 28.2.2010, 21:33

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

чуть не забыл, мне вот рекомендовали когда-то посмотреть на Атомарность операций и счетчики в memcached (см. Счетчик просмотров).



Это сообщение отредактировал(а) gcc - 28.2.2010, 22:11

Тогда пляс с бубном вокруг иммитации жобы  не уместен ;)

КО говорит что фильтровать таки надо сначала    
Не совсем понятно что тому препятствует

Некоторые поля появляются только после JOIN-а, пришлось перенести.  

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


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

Действительно странно, причем записей меньше.

Спасибо за информацию  

а то на нашей базе я select * from dual боюсь запустить, боюсь база не выдержит (это у нас админы такие "хорошие"), не то, что туда 5 миллионов залить (особенно со своего частного проекта)  

Это сообщение отредактировал(а) azesmcar - 1.3.2010, 10:36

они лежать могут менее "кучно".
на первом серваке мы exp-imp сравнительно недавно делали.

и то верно, фрагментация сильно влияет.

Это сообщение отредактировал(а) azesmcar - 1.3.2010, 10:37