Модераторы: skyboy, SamDark

Поиск:

Ответ в темуСоздание новой темы Создание опроса
> Оптимизация запроса, В чем причина Copying to tmp table? 
V
    Опции темы
azesmcar
Дата 27.2.2010, 18:21 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


uploading...
****


Профиль
Группа: Участник Клуба
Сообщений: 6291
Регистрация: 12.11.2004
Где: Армения

Репутация: 6
Всего: 211



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

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

SELECT c.id    
FROM   cdr c
       INNER JOIN customer_accounts ac ON c.customer_account_id  = ac.id
       INNER JOIN vendor_gateways gw ON c.vendor_gateway_id    = gw.id
order by c.id
limit 1,10

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

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

1    SIMPLE    ac    index    PRIMARY    PRIMARY    4  3    Using index; Using temporary; Using filesort
1    SIMPLE    c    ref    customer_account_id,vendor_gateway_id    customer_account_id    4    db.ac.id    69515    
1    SIMPLE    gw    eq_ref    PRIMARY    PRIMARY    4    db.c.vendor_gateway_id    1    Using index


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

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

Заранее спасибо.
PM   Вверх
Kesh
Дата 27.2.2010, 19:27 (ссылка) |    (голосов:2) Загрузка ... Загрузка ... Быстрая цитата Цитата


Эксперт
****


Профиль
Группа: Эксперт
Сообщений: 2488
Регистрация: 31.7.2002
Где: Германия, Saarbrü cken

Репутация: 15
Всего: 54



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


--------------------
user posted image
PM MAIL WWW ICQ Skype   Вверх
azesmcar
Дата 27.2.2010, 19:31 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


uploading...
****


Профиль
Группа: Участник Клуба
Сообщений: 6291
Регистрация: 12.11.2004
Где: Армения

Репутация: 6
Всего: 211



Цитата(Kesh @  27.2.2010,  19:27 Найти цитируемый пост)
имхо слишком много данных для сортировки... Может сначала выбрать 10 id, и загнать их во внутренний select, а потом уже к ним подтянуть остальные таблицы... 

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

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

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

Сработало! smile 

Спасибо! smile
PM   Вверх
azesmcar
Дата 27.2.2010, 21:11 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


uploading...
****


Профиль
Группа: Участник Клуба
Сообщений: 6291
Регистрация: 12.11.2004
Где: Армения

Репутация: 6
Всего: 211



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

Код

SELECT COUNT(*)
FROM   billing_cdr c
       INNER JOIN billing_customer_accounts ac ON c.customer_account_id = ac.id
       INNER JOIN billing_vendor_gateways gw ON c.vendor_gateway_id   = gw.id

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

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

Это он так долго считает?
PM   Вверх
Kesh
Дата 27.2.2010, 21:28 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Эксперт
****


Профиль
Группа: Эксперт
Сообщений: 2488
Регистрация: 31.7.2002
Где: Германия, Saarbrü cken

Репутация: 15
Всего: 54



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


--------------------
user posted image
PM MAIL WWW ICQ Skype   Вверх
azesmcar
Дата 27.2.2010, 21:34 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


uploading...
****


Профиль
Группа: Участник Клуба
Сообщений: 6291
Регистрация: 12.11.2004
Где: Армения

Репутация: 6
Всего: 211



Цитата(Kesh @  27.2.2010,  21:28 Найти цитируемый пост)
Давайте дамп табличек с тестовыми данными - посмотрим... 

В каком смысле? 2 миллиона записей?
PM   Вверх
azesmcar
Дата 27.2.2010, 21:52 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


uploading...
****


Профиль
Группа: Участник Клуба
Сообщений: 6291
Регистрация: 12.11.2004
Где: Армения

Репутация: 6
Всего: 211



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

CREATE TABLE cdr
(
    id INT UNSIGNED NOT NULL auto_increment,
    customer_account_id INT UNSIGNED NOT NULL,
    vendor_gateway_id INT UNSIGNED NOT NULL,
    called_station_id VARCHAR(30) NOT NULL,
    calling_station_id VARCHAR(30) NOT NULL,
    duration INT UNSIGNED NOT NULL DEFAULT 0,
    FOREIGN KEY (customer_account_id) REFERENCES billing_customer_accounts(id)
        ON DELETE RESTRICT,
    FOREIGN KEY (vendor_gateway_id) REFERENCES billing_vendor_gateways(id)
        ON DELETE RESTRICT,
    PRIMARY KEY(id)
) ENGINE = InnoDB, DEFAULT CHARSET=utf8;


Код

CREATE TABLE vendor_gateways
(
    id INT UNSIGNED NOT NULL auto_increment,
    vendor_id INT UNSIGNED NOT NULL,
    name VARCHAR(20) NOT NULL,
    PRIMARY KEY(id)
) ENGINE = InnoDB, DEFAULT CHARSET=utf8;


Код

CREATE TABLE customer_accounts
(
    id INT UNSIGNED NOT NULL auto_increment,
    customer_id INT UNSIGNED NOT NULL,
    ip_address VARCHAR(15),
    PRIMARY KEY(id),
    UNIQUE KEY ip_address(ip_address)
) ENGINE = InnoDB, DEFAULT CHARSET=utf8;


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

Это сообщение отредактировал(а) azesmcar - 27.2.2010, 21:52
PM   Вверх
Zloxa
Дата 27.2.2010, 23:09 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Чо?
****


Профиль
Группа: Завсегдатай
Сообщений: 3470
Регистрация: 12.9.2008

Репутация: 33
Всего: 161



Цитата(azesmcar @  27.2.2010,  21:11 Найти цитируемый пост)
чего-то не то делаю

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

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

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


--------------------
Достоверно известно, что 89% людей доверяют статистике взятой с потолка smile
PM   Вверх
azesmcar
Дата 28.2.2010, 08:17 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


uploading...
****


Профиль
Группа: Участник Клуба
Сообщений: 6291
Регистрация: 12.11.2004
Где: Армения

Репутация: 6
Всего: 211



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

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

Цитата(Zloxa @  27.2.2010,  23:09 Найти цитируемый пост)
Еще. Непонятно зачем тебе джойны в этом запросе. у тебя есть FK, джоинишься ты нотнуллабельными полями в первичные ключи, в результате будет тоже количество что и в billing_cdr. Джойны не нужны. Ты уверен что оптимизатор это понимает и не выполняет их?

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

select count(*) from cdr

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

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

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

SELECT  c.vendor_id,
             COUNT(c.id) total_calls
       FROM     cdr c
GROUP BY c.vendor_id

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

SELECT  c.vendor_id,
             COUNT(c.id) total_calls,
        MAX(c.duration)
       FROM     cdr c
GROUP BY c.vendor_id


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

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

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

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

Это сообщение отредактировал(а) azesmcar - 28.2.2010, 10:50
PM   Вверх
Zloxa
Дата 28.2.2010, 12:06 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Чо?
****


Профиль
Группа: Завсегдатай
Сообщений: 3470
Регистрация: 12.9.2008

Репутация: 33
Всего: 161



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

Цитата(azesmcar @  28.2.2010,  08:17 Найти цитируемый пост)
 можно сделать materialized view (точнее сымитировать его через cron), но пока не хочется, желательно получать свежие данные. Еще как вариант можно строить отчет на момент вставки, но тогда замедлиться сама вставка, что вызовет замедление не web приложения а ядра системы, в следствии - уменьшается количество максимальных одновременных звонков, показатель нужный

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


--------------------
Достоверно известно, что 89% людей доверяют статистике взятой с потолка smile
PM   Вверх
azesmcar
Дата 28.2.2010, 12:35 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


uploading...
****


Профиль
Группа: Участник Клуба
Сообщений: 6291
Регистрация: 12.11.2004
Где: Армения

Репутация: 6
Всего: 211



Цитата(Zloxa @  28.2.2010,  12:06 Найти цитируемый пост)
azesmcar, а когда оракл на десятке млн ляжет, куда мигрироваться будешь? Миграция на оракл ошибок проектирования, имхо не устранит. Вполне очевидно что тебе нужна преаггрегация по нужным тебе разрезам.

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

Цитата(Zloxa @  28.2.2010,  12:06 Найти цитируемый пост)
Можно сделать чтобы в момент вставки формировалось задание на пересчет преаггрегации, которое выполнялось бы  в другой сессии. 

В MySQL JOB -ов нету к сожалению, придется ставить обычный триггер.
PM   Вверх
Zloxa
Дата 28.2.2010, 12:47 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Чо?
****


Профиль
Группа: Завсегдатай
Сообщений: 3470
Регистрация: 12.9.2008

Репутация: 33
Всего: 161



Цитата(azesmcar @  28.2.2010,  12:35 Найти цитируемый пост)
В MySQL JOB -ов нету 

тыж сам упоминал крон.
Цитата(azesmcar @  28.2.2010,  12:35 Найти цитируемый пост)
ри 16 миллиардах записей

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


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


--------------------
Достоверно известно, что 89% людей доверяют статистике взятой с потолка smile
PM   Вверх
azesmcar
Дата 28.2.2010, 13:12 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


uploading...
****


Профиль
Группа: Участник Клуба
Сообщений: 6291
Регистрация: 12.11.2004
Где: Армения

Репутация: 6
Всего: 211



Цитата(Zloxa @  28.2.2010,  12:47 Найти цитируемый пост)
тыж сам упоминал крон.

а .. ну да smile 

Цитата(Zloxa @  28.2.2010,  12:47 Найти цитируемый пост)
/*а там не было фастрефреш матвью и квериреврита?*/
У мну count ~100млн считается весьма долго.
Если хочешь завтра сниму тайминг, думаю будет не менее пяти минут.

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

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

Это сообщение отредактировал(а) azesmcar - 28.2.2010, 13:14
PM   Вверх
Zloxa
Дата 28.2.2010, 13:19 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Чо?
****


Профиль
Группа: Завсегдатай
Сообщений: 3470
Регистрация: 12.9.2008

Репутация: 33
Всего: 161



Цитата(azesmcar @  28.2.2010,  12:35 Найти цитируемый пост)
В MySQL JOB -ов

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




--------------------
Достоверно известно, что 89% людей доверяют статистике взятой с потолка smile
PM   Вверх
azesmcar
Дата 28.2.2010, 13:23 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


uploading...
****


Профиль
Группа: Участник Клуба
Сообщений: 6291
Регистрация: 12.11.2004
Где: Армения

Репутация: 6
Всего: 211



Цитата(Zloxa @  28.2.2010,  13:19 Найти цитируемый пост)
Из триггера на целевую таблицу ты выполняешь инсерт в табличку заданий, сохраняешь id записей, которые должны влиять на пересчет.

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

Цитата(Zloxa @  28.2.2010,  13:19 Найти цитируемый пост)
Я так понял у тебя идут интенсивные инсерты, нет апдейтов и делитов, порядок обработки проинсерченных записей - не критичен. 

Точно!

Это сообщение отредактировал(а) azesmcar - 28.2.2010, 13:24
PM   Вверх
Google
  Дата 28.2.2020, 12:57 (ссылка)  





  Вверх
Страницы: (3) Все [1] 2 3 
Ответ в темуСоздание новой темы Создание опроса
1 Пользователей читают эту тему (1 Гостей и 0 Скрытых Пользователей)
0 Пользователей:
« Предыдущая тема | MySQL | Следующая тема »


 




[ Время генерации скрипта: 0.1179 ]   [ Использовано запросов: 21 ]   [ GZIP включён ]


Реклама на сайте     Информационное спонсорство

 
По вопросам размещения рекламы пишите на vladimir(sobaka)vingrad.ru
Отказ от ответственности     Powered by Invision Power Board(R) 1.3 © 2003  IPS, Inc.