Оптимизация SQL как обещал

[Magnum72]

Дано:
Таблица discount_transactions_iptraffic_all
Количество записей более 37 миллионов.
После каждого этапа ребутим mySQL
SQL запрос: реальный грепнут из двухдневного slow-log MySQL по ключевым словам SELECT и discount_transactions_iptraffic_all

Тестовый запрос: SELECT SUM(discount),SUM(bytes),t_class,base_cost,account_id,slink_id, SUM(discount_with_tax) FROM z_discount_transactions_iptraffic_all WHERE discount_date>='1143828000' AND discount_date <='1144325617' AND account_id='6728' GROUP BY t_class,base_cost,slink_id,account_id;

Стадия первая:
Удаляем индексы из таблицы и запускаем выборку.
8 rows in set (2 min 10.70 sec)

Стадия вторая:
Создаем дефолтные индексы из поставки UTM5 билд 5.2.02
8 rows in set (25.76 sec)

Стадия третья:
Удаляем автоинкремент из столбца ID, удаляем PRIMARY индекс, значения всех ID приводим к 0, значение по умолчанию ставим 0,
Удаляем все индексы и создаем общий индекс на account_id, discount_period_id
8 rows in set (2.23 sec)

Стадия четвертая для интереса:
(просто посмотреть что будет если заменить в запросе дату начала и конца учетного периода на его id, ну например чтобы получить отчет по трафику у пользователя
за прошлый учетный период)
8 rows in set (0.38 sec)

2 aospan
Интересно ваше мнение по поводу вышесказанного и рекомендации делать так или нет, так сказать последнее решающее мнение непосредственно от разработчика:

Удаляем автоинкремент из столбца ID, удаляем PRIMARY индекс, значения всех ID приводим к 0, значение по умолчанию ставим 0,
Удаляем все индексы и создаем общий индекс на account_id, discount_period_id

[cjcrazy]

2Magnum72, а можно несколько подробнее?
сами запросы к базе на все стадии?

[Magnum72]

До кучи оптимизировали длинну ряда

До
-rw-rw---- 1 mysql mysql 2,8G 5 апр 20:42 /netup/tmp/UTM5/discount_transactions_all.MYD
-rw-rw---- 1 mysql mysql 1,3G 5 апр 20:42 /netup/tmp/UTM5/discount_transactions_all.MYI
-rw-rw---- 1 mysql mysql 9,0K 4 апр 21:56 /netup/tmp/UTM5/discount_transactions_all.frm

CREATE TABLE `discount_transactions_iptraffic_all` (
`id` int(11) NOT NULL auto_increment,
`account_id` int(11) default NULL,
`discount` double default NULL,
`discount_with_tax` double default NULL,
`service_id` int(11) default NULL,
`discount_period_id` int(11) default NULL,
`slink_id` int(11) default NULL,
`discount_date` int(11) default NULL,
`discount_date_hour` int(11) default NULL,
`discount_date_day` int(11) default NULL,
`discount_date_month` int(11) default NULL,
`t_class` int(11) default '0',
`base_cost` double default '0',
`ipid` int(11) default '0',
`bytes` bigint(20) default NULL,
`is_canceled` int(11) default '0',
`cancel_id` int(11) default '0',
PRIMARY KEY (`id`),
KEY `first_discount` (`discount_date`),
KEY `first_account_id` (`account_id`),
KEY `first_slink_id` (`slink_id`)
) TYPE=MyISAM AUTO_INCREMENT=152490799 ;

Формат фиксированный
Размер ряда o 126 Bytes

________________________________________________________
После

-rw-rw---- 1 mysql mysql 1,8G 11 апр 14:12 z_discount_transactions_iptraffic_all.MYD
-rw-rw---- 1 mysql mysql 307M 11 апр 14:15 z_discount_transactions_iptraffic_all.MYI
-rw-rw---- 1 mysql mysql 8,9K 11 апр 14:09 z_discount_transactions_iptraffic_all.frm


CREATE TABLE `z_discount_transactions_iptraffic_all` (
`account_id` smallint(5) unsigned NOT NULL default '0',
`discount` float NOT NULL default '0',
`discount_with_tax` float NOT NULL default '0',
`service_id` smallint(5) unsigned NOT NULL default '0',
`discount_period_id` mediumint(8) unsigned NOT NULL default '0',
`slink_id` mediumint(8) unsigned NOT NULL default '0',
`discount_date` int(10) unsigned NOT NULL default '0',
`discount_date_hour` int(10) unsigned NOT NULL default '0',
`discount_date_day` int(10) unsigned NOT NULL default '0',
`discount_date_month` int(10) unsigned NOT NULL default '0',
`t_class` smallint(5) unsigned NOT NULL default '0',
`base_cost` float NOT NULL default '0',
`ipid` int(11) NOT NULL default '0',
`bytes` bigint(11) unsigned NOT NULL default '0',
`is_canceled` enum('0','1') NOT NULL default '0',
`cancel_id` enum('0','1') default NULL,
KEY `mix` (`account_id`,`discount_period_id`)
) TYPE=MyISAM;

Формат фиксированный
Ряды 35,069,173
Размер ряда 64 Bytes

[aospan]

z_discount_transactions_iptraffic_all это копия discount_transactions_iptraffic_all с изменениями ?
Сделайте пожалуйста show index from z_discount_transactions_iptraffic_all в итоге после ускорений ?

По хорошему "Удаляем автоинкремент из столбца ID" для таблицы discount_transactions_iptraffic_all относительно безопасно. Для discount_transactions_all не получится ...

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

"значения всех ID приводим к 0" - делаете update id = 0 ? по этому id идет связка с таблицей discount_transactions_all. По хорошему после такого могут не работать отчеты и оптимизация из админки 100% не будет работать.

"значение по умолчанию ставим 0" - для каких полей ?

[Magnum72]

z_discount_transactions_iptraffic_all это копия discount_transactions_iptraffic_all с изменениями ?

именно так

Сделайте пожалуйста show index from z_discount_transactions_iptraffic_all в итоге после ускорений ?

z_discount_transactions_iptraffic_all 1 mix 1 account_id A 6758 NULL NULL BTREE
z_discount_transactions_iptraffic_all 1 mix 2 discount_period_id A 34147 NULL NULL BTREE


По хорошему "Удаляем автоинкремент из столбца ID" для таблицы discount_transactions_iptraffic_all относительно безопасно.
"удаляем PRIMARY индекс" - в принципе должно быть безразлично.
"значение по умолчанию ставим 0" - для каких полей ?
"значения всех ID приводим к 0" - делаете update id = 0 ?

Это все делается для поля ID у таблице discount_transactions_iptraffic_all для того чтобы не нарушать структуру таблицы и избавится от автоинкремента, автоинкремент без индекса работать не может.

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

Не не может этого быть


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

[hmepas]

Во! Вот это предметный разговор. Спасибо

[Fenix]

Стадия третья:
Удаляем автоинкремент из столбца ID, удаляем PRIMARY индекс, значения всех ID приводим к 0, значение по умолчанию ставим 0,
Удаляем все индексы и создаем общий индекс на account_id, discount_period_id

интересно узнать про 3-ю стадию, а точнее про приведение всех значений id к 0

точно не нарушатся связи? если для таблицы discount_transactions_iptraffic_all сделать:

update discount_transactions_iptraffic_all set id = 0;

по каким данным вообще информация выбирается из discount_transactions_iptraffic_all в запросах UTM?

ps:

SELECT account_id, slink_id, t_class, SUM(bytes), base_cost, SUM(discount), SUM(discount_with_tax) FROM discount_transactions_iptraffic_all WHERE discount_date>='1228078800' AND discount_date <='1230757199' GROUP BY t_class,base_cost,account_id,slink_id ORDER BY account_id, t_class, base_cost

ну в отчете по трафику допустим используется discount_date =)
мож действительно обнулить id =)

[banec]

CREATE TABLE `z_discount_transactions_iptraffic_all` (
`account_id` smallint(5) unsigned NOT NULL default '0',
`discount` float NOT NULL default '0',
`discount_with_tax` float NOT NULL default '0',
`service_id` smallint(5) unsigned NOT NULL default '0',
`discount_period_id` mediumint(8) unsigned NOT NULL default '0',
`slink_id` mediumint(8) unsigned NOT NULL default '0',
`discount_date` int(10) unsigned NOT NULL default '0',
`discount_date_hour` int(10) unsigned NOT NULL default '0',
`discount_date_day` int(10) unsigned NOT NULL default '0',
`discount_date_month` int(10) unsigned NOT NULL default '0',
`t_class` smallint(5) unsigned NOT NULL default '0',
`base_cost` float NOT NULL default '0',
`ipid` int(11) NOT NULL default '0',
`bytes` bigint(11) unsigned NOT NULL default '0',
`is_canceled` enum('0','1') NOT NULL default '0',
`cancel_id` enum('0','1') default NULL,
KEY `mix` (`account_id`,`discount_period_id`)
) TYPE=MyISAM;

Формат фиксированный
Ряды 35,069,173
Размер ряда 64 Bytes

А у Вас сейчас так и осталось?
и хотелось бы видеть ваши структуры
и discount_transactions_all
Надеюсь на ответ и заранее Спасибо!

[banec]

Magnum!!
При 68000 лицевых ну точно должна быть с вашей стороны
оптимизация структуры и индексов.

Поделитесь плиз с общественностью!!!!

[banec]

Кто пробывал юзать
http://www.mysqlperformanceblog.com/201 ... e-1-0-6-9/
?

[Magnum72]

Поделитесь плиз с общественностью!!!!

У меня тут еще идейка созрела:
создаем на основном сервере базу UTM5H, и реплицируем эту базу на слейв, далее на основном сервере создаем базу UTM5F и в ней создаем таблицы типа федератед, котторые ссылаются на базу UTM5H которая расположена на слейве. Далее настраиваем архивирование таблиц в базу UTM5H и в итоге получаем эффект того что все запросы реально будут выполнятся на слейве, который у всех обычно все равно стоит без дела.

ЗЫ В индексах ничего практически не поменялось..

[banec]

в таблице archives наверное нужно ссылаться на UTМ5F?


PS Может все таки структуру таблиц выложите, плиз? Ну очень хочется увидеть.

[tesla]

У меня тут еще идейка созрела:
создаем на основном сервере базу UTM5H, и реплицируем эту базу на слейв, далее на основном сервере создаем базу UTM5F и в ней создаем таблицы типа федератед, котторые ссылаются на базу UTM5H которая расположена на слейве. Далее настраиваем архивирование таблиц в базу UTM5H и в итоге получаем эффект того что все запросы реально будут выполнятся на слейве, который у всех обычно все равно стоит без дела.

ЗЫ В индексах ничего практически не поменялось..

Я попытался сделать так. На отдельный(не биллинг) сервер слил архивные таблицы. На основном создал базу UTM5ARCH и попытался сделать federated табличку с индексами.

Код: Выделить всё

CREATE TABLE discount_transactions_all_31Oct2011 ( id int(11) NOT NULL auto_increment, account_id int(11) default NULL, incoming_rest double default NULL, outgoing_rest double default NULL, discount double default NULL, discount_with_tax double default NULL, service_id int(11) default NULL, service_type int(11) default NULL, discount_period_id int(11) default NULL, slink_id int(11) default NULL, discount_date int(11) default NULL, charge_type int(11) NOT NULL default '0', PRIMARY KEY  (id)  ENGINE=FEDERATED CONNECTION='mysql://xxx:xxxxx@10.27.65.78:3306/UTM5ARCH/discount_transactions_all_31Oct2011';

Получил отказ т.к federated не умеет оказывается работать с какими-то индексами судя по докам.

There are issues regarding performance/stability that should be considered prior to the usage of the federated engine in a production environment, for example -
It's 100 percent a storage engine. For queries that need full table scans (because there isn't an index available), the server will ask for all table rows from the storage engine, and filter the relevant ones in the server level. With federated, this means the whole table will be passed over the network to the requesting server, and only then the relevant rows will be filtered out. Besides bad performance and network overload, this could easily lead to a server crash if the table is big, as it stores all this data in memory, in some buffer of it's own. For example, if you retrieve a table that's 4 gigs of data, and the requesting server has 2 or 4 gigs of RAM, in a very short while you'll start swapping like crazy, and if you run out of swap, OS will hang.
Also note it doesn't respect a LIMIT clause. It will pass the whole table over the network even if you request only 1 row.

В итоге пришлось отказаться от затеи. Посему вопрос. Есть желание разгрузить основной сервер билинга от запросов.
Чтобы запросы по клиентам делать в удаленной БД. Есть ли еще решения подобного плана?

[gravis]

В итоге пришлось отказаться от затеи. Посему вопрос. Есть желание разгрузить основной сервер билинга от запросов.
Чтобы запросы по клиентам делать в удаленной БД. Есть ли еще решения подобного плана?

Если это только запросы на получение информации то:
репликация на другой сервер + второе ядро в режиме только для чтения к этому серверу. Но это годится только для отчетов. Текущие данные, например баланс, будут не корректны. В общем это получается какой-то лабораторный вариант, в реальной работе видимо применения не найдет

[hellard]

Кто подскажет какие индексы лучше оставить в таблице: discount_transactions_all

А то количество индексов превышает размер самих данных в таблице, а на скорость как я понял никак не влияет.