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

Заполнил сегодня базу для теста парой миллионов записей и программа мягко говоря перестала работать, хотя запрос вроде правильный и ничего особо сложного не вытворяет. База данных - 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