Знатокам MySql discount_transactions_all и select

NShut · Сообщение **NShut** » Вс янв 24, 2010 08:56

не могу вывести ни одного отчета по услугам.
после анализа выяснилось что проблема не в утм а в базе данных.

SELECT discount,slink_id,discount_date,discount_period_id,account_id,charge_type,discount_with_tax FROM discount_transactions_all WHERE discount_date>='1262304000' AND discount_date <='1262390400' AND account_id='11' order by discount_date desc

данный запрос создает загрузку проца 100%, результат появляется минут через 15, если стереть order by discount_date desc, то запрос выполняется 15 секунд.

количество записей в таблице 6068808.
но есть прикол. имеется еще одна база другой компании, там записей в таблице 18373889, т.е. в 3 раза больше. Заливаю таблицу в свою, запрос выполняется 6 сек (комп тестовый слабый). Заливаю старую таблицу опять тормоза.
причем аккаунт id можно указать несуществующий, результат такой же долгий.

дропал и пересоздавал индексы: не помогло

show create table

CREATE TABLE `discount_transactions_all` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`account_id` int(11) NOT NULL DEFAULT '0',
`incoming_rest` double NOT NULL DEFAULT '0',
`outgoing_rest` double NOT NULL DEFAULT '0',
`discount` double NOT NULL DEFAULT '0',
`discount_with_tax` double NOT NULL DEFAULT '0',
`service_id` int(11) NOT NULL DEFAULT '0',
`service_type` int(11) NOT NULL DEFAULT '0',
`discount_period_id` int(11) NOT NULL DEFAULT '0',
`slink_id` int(11) NOT NULL DEFAULT '0',
`discount_date` int(11) NOT NULL DEFAULT '0',
`charge_type` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `index_b3e12acadb1bbd7f8eb9b8a9ac479e6c` (`discount_date`,`account_id`,`service_type`,`discount`,`discount_with_tax`),
KEY `index_92e08f5e788b4bcd2bd08d642d1f16f2` (`service_id`,`discount_date`,`account_id`,`service_type`,`charge_type`,`discount`,`slink_id`,`discount_period_id`,`discount_with_tax`)
) ENGINE=MyISAM AUTO_INCREMENT=47269190 DEFAULT CHARSET=utf8

не пойму где может быть проблема. что можно еще проверить.

игрался с параметрами sort_buffer_size и read_rnd_size, результат нулевой

dwemer · Сообщение **dwemer** » Пн янв 25, 2010 06:42

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

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

NShut · Сообщение **NShut** » Пн янв 25, 2010 08:22

да делал все идентично. Пробовал все. даже скул сервер другой версии ставил.
Подозреваю что весь геморой в расположении данных и индексов, т.к. индексы на кучу полей.
после двух дней мучений единственное улучшение проявилось на
alter table discou...all type=innodb

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

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

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

Magnum72

NShut писал(а):да делал все идентично. Пробовал все. даже скул сервер другой версии ставил.
Подозреваю что весь геморой в расположении данных и индексов, т.к. индексы на кучу полей.
после двух дней мучений единственное улучшение проявилось на
alter table discou...all type=innodb

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

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

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

Сделайте проще, удалите индексы и создайте:
PRIMARY KEY (`id`),
KEY `discount_date` (`discount_date`),
KEY `account_id` (`account_id`)

NShut · Сообщение **NShut** » Пн янв 25, 2010 13:45

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

наблюдаю как раз за темой отимизация скул, как обещал. думаю действительно стандартные индексы не айс

Magnum72

NShut писал(а): сделал, все запросы выполняются меньше секунды. еще погляжу как утм на это среагирует. спасибо.

наблюдаю как раз за темой отимизация скул, как обещал. думаю действительно стандартные индексы не айс

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

dwemer · Сообщение **dwemer** » Вт янв 26, 2010 05:14

Magnum72 писал(а): а они и не айс, такие составные индексы годятся только если в запросе используется все поля из индекса, притом в том же порядке в каком они идут в запросе, + я не уверен что мускул будет их используовать, сполне возможно он решит что индексов дофига, проще без них выборку сделать.

Хм, поидее все поля не обязательно .
То есть при составном индексе (`discount_date`,`account_id`,`service_type`,`discount`,`discount_with_tax`),
и запросе c "where discount_date .. and account_id ..) должен использоваться этот индекс.
explain на вышеприведенный запрос это подтверждает.
В другом порядке аналогично.
У меня на 18 миллионах строк этот запрос отрабатывает за пару секунд.

Правда размер индексов больше чем данных - это нормально?

NShut · Сообщение **NShut** » Вт янв 26, 2010 06:13

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

В архивных таблицах вообще грохнул id, с основным отчетом проблема, т.е. incoming, outgoingrest выбирается по айди. но за прошлые года эти отчеты мне ненужны

Magnum72

NShut писал(а):В архивных таблицах вообще грохнул id, с основным отчетом проблема, т.е. incoming, outgoingrest выбирается по айди. но за прошлые года эти отчеты мне ненужны

Ну это зря.

Magnum72

dwemer писал(а):
Magnum72 писал(а): а они и не айс, такие составные индексы годятся только если в запросе используется все поля из индекса, притом в том же порядке в каком они идут в запросе, + я не уверен что мускул будет их используовать, сполне возможно он решит что индексов дофига, проще без них выборку сделать.
Хм, поидее все поля не обязательно .
То есть при составном индексе (`discount_date`,`account_id`,`service_type`,`discount`,`discount_with_tax`),
и запросе c "where discount_date .. and account_id ..) должен использоваться этот индекс.
explain на вышеприведенный запрос это подтверждает.
В другом порядке аналогично.
У меня на 18 миллионах строк этот запрос отрабатывает за пару секунд.

Правда размер индексов больше чем данных - это нормально?

Странно конечно, но возможно

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

postfix

А можно ли каким нибудь образом из sql базы получить детальную статистику по абонентам, интересует id пользователя, его ip, адрес получателя, время. Детальный отчет делается только за последние два дня (аварийный ребут всей стойки по питанию) в логах ошибок нет, в /netup/utm5/db есть несколько файлов как раз за последние дни. freebsd, mysql5, utm 5.2.1–007 update 10

drag0mir

Magnum72 писал(а): Сделайте проще, удалите индексы и создайте:
PRIMARY KEY (`id`),
KEY `discount_date` (`discount_date`),
KEY `account_id` (`account_id`)

Ребята, а можно специально для дураков расписать команды полностью? )))
Я в БД, как свинья в апельсинах ))) буду очень признателен.

mikkey finn

http://lmgtfy.com/?q=mysql+create+index+syntax