Добрый день!
Использую скрипт Magnum72 для деления таблиц
discount_transactions_all и discount_transactions_iptraffic_all
Таблицы в базе хранятся в таком виде:
discount_transactions_all_1225573200
discount_transactions_all_1228078800
discount_transactions_all_1230757200
discount_transactions_iptraffic_1225573200
discount_transactions_iptraffic_1228078800
discount_transactions_iptraffic_1230757200
Формирование отчета по трафику может доходить до 5 мин.
Что можно потереть, что можно оптимизировать?
Оптимизация по времени вывода отчетов по трафику
-
mikkey finn
- Сообщения: 1612
- Зарегистрирован: Пт ноя 10, 2006 15:23
Это не мой скрипт
Советами возможно помогал, не более На вскидку для ускорения вывода надо сделать следующие веши:
1) Отсортировать данные в таблицах по пользователям, это приведет к тому что данные будут лежать в одном месте на винте.
2) Округлить дату до 10-600 секунд, все равно данные нетфлоу приходят с задержкой, поэтому это мало что изменит в качестве, зато индекс по дате будет весить в 10 раз и более раз меньше и его станет реально загрузить в память целиком. да и выборка по нему будет идти в основном отчете быстрее.
3) Поле comments (версия 006 и выше) необходимо удалить обязательно, это приведет к тому что рад в таблице примет вид фиксированного, и биллинг для перехода к нужному месту в файле сможет это сделать путем перемножения номера строки на размер ряда, а не путем перечитывания всего файла (условно говоря) пока не доберется до нужного места.
4) Дефолтные индексы надо грохнуть обязательно и создать свои, потому как если вы это не сделаете, то просто добавивь свои или те которые появились в 006, не факт что биллинг будет их использовать, мускул штука загадочная иногда пользует не оптимальные индексы для некоторых выборок.
В-общем-то методика на этом форуме уже описывалась, но мне не сложно )
Это всё применительно к MySQL
1. Создаём таблицу в которой будем хранить данные за месяц (для каждого прошлого месяца - своя).
2. Переливаем туда данные за этот месяц из таблицы discount_transactions_iptraffic_all
Собственно всё. Этот процесс занимает минуту-две. Дальше работаем уже с новой таблицей. К примеру чтобы сделать выборку по аккаунту (сколько трафика и какого класса на него накапало) можно задать запрос типа:
Это всё применительно к MySQL
1. Создаём таблицу в которой будем хранить данные за месяц (для каждого прошлого месяца - своя).
Следует заметить, что здесь введён составной индекс KEY `mix` (`account_id`,`discount_period_id`) - поскольку я делаю выборку именно по этим двум параметрам.CREATE TABLE `[месяц]_discount_transactions_iptraffic_all` (
`account_id` smallint(5) unsigned NOT NULL default '0',
`discount` double NOT NULL default '0',
`discount_with_tax` double NOT NULL default '0',
`service_id` smallint(5) unsigned NOT NULL default '0',
`discount_period_id` mediumint unsigned NOT NULL default '0',
`slink_id` mediumint 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` double 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;
2. Переливаем туда данные за этот месяц из таблицы discount_transactions_iptraffic_all
INSERT INTO [месяц]_discount_transactions_iptraffic_all
SELECT account_id,
discount,
discount_with_tax,
service_id,
discount_period_id,
slink_id,
discount_date,
discount_date_hour,
discount_date_day,
discount_date_month,
t_class,
base_cost,
ipid,
bytes,
is_canceled,
cancel_id
FROM discount_transactions_iptraffic_all WHERE discount_period_id=[номер рассчётного периода];
Собственно всё. Этот процесс занимает минуту-две. Дальше работаем уже с новой таблицей. К примеру чтобы сделать выборку по аккаунту (сколько трафика и какого класса на него накапало) можно задать запрос типа:
На моей практике запрос к новой таблице выполняется от 80 до 120 раз быстрее чем к стандартной таблице нетапа. Всё дело в индексах.
SELECT sum(bytes)/1024/1024 as traf, t_class
FROM [месяц]_discount_transactions_iptraffic_all
WHERE account_id=[номер аккаунта] AND discount_period_id=[номер рассчётного периода]
GROUP BY t_class;
У меня это выполняется скриптом раз в месяц, по окончании месяца.
viewtopic.php?t=5853&postdays=0&postorder=asc&start=0
я так понимаю это тоже самое.
Меня интересует, как осуществить данные пункты:
1) Отсортировать данные в таблицах по пользователям, это приведет к тому что данные будут лежать в одном месте на винте.
2) Округлить дату до 10-600 секунд, все равно данные нетфлоу приходят с задержкой, поэтому это мало что изменит в качестве, зато индекс по дате будет весить в 10 раз и более раз меньше и его станет реально загрузить в память целиком. да и выборка по нему будет идти в основном отчете быстрее.
3) Поле comments (версия 006 и выше) необходимо удалить обязательно, это приведет к тому что рад в таблице примет вид фиксированного, и биллинг для перехода к нужному месту в файле сможет это сделать путем перемножения номера строки на размер ряда, а не путем перечитывания всего файла (условно говоря) пока не доберется до нужного места.
4) Дефолтные индексы надо грохнуть обязательно и создать свои, потому как если вы это не сделаете, то просто добавивь свои или те которые появились в 006, не факт что биллинг будет их использовать, мускул штука загадочная иногда пользует не оптимальные индексы для некоторых выборок.
viewtopic.php?t=5853&postdays=0&postorder=asc&start=0
я так понимаю это тоже самое.
Меня интересует, как осуществить данные пункты:
1) Отсортировать данные в таблицах по пользователям, это приведет к тому что данные будут лежать в одном месте на винте.
2) Округлить дату до 10-600 секунд, все равно данные нетфлоу приходят с задержкой, поэтому это мало что изменит в качестве, зато индекс по дате будет весить в 10 раз и более раз меньше и его станет реально загрузить в память целиком. да и выборка по нему будет идти в основном отчете быстрее.
3) Поле comments (версия 006 и выше) необходимо удалить обязательно, это приведет к тому что рад в таблице примет вид фиксированного, и биллинг для перехода к нужному месту в файле сможет это сделать путем перемножения номера строки на размер ряда, а не путем перечитывания всего файла (условно говоря) пока не доберется до нужного места.
4) Дефолтные индексы надо грохнуть обязательно и создать свои, потому как если вы это не сделаете, то просто добавивь свои или те которые появились в 006, не факт что биллинг будет их использовать, мускул штука загадочная иногда пользует не оптимальные индексы для некоторых выборок.
-
mikkey finn
- Сообщения: 1612
- Зарегистрирован: Пт ноя 10, 2006 15:23
1 - man myisamchk
o --sort-index, -S
Sort the index tree blocks in high-low order. This optimizes seeks
and makes table scans that use indexes faster.
o --sort-records=N, -R N
Sort records according to a particular index. This makes your data
much more localized and may speed up range-based SELECT and ORDER BY
operations that use this index. (The first time you use this option
to sort a table, it may be very slow.) To determine a table's index
numbers, use SHOW INDEX, which displays a table's indexes in the
same order that myisamchk sees them. Indexes are numbered beginning
with 1.
If keys are not packed (PACK_KEYS=0), they have the same length, so
when myisamchk sorts and moves records, it just overwrites record
offsets in the index. If keys are packed (PACK_KEYS=1), myisamchk
must unpack key blocks first, then re-create indexes and pack the
key blocks again. (In this case, re-creating indexes is faster than
updating offsets for each index.)
3 - alter table ... drop column comments;
o --sort-index, -S
Sort the index tree blocks in high-low order. This optimizes seeks
and makes table scans that use indexes faster.
o --sort-records=N, -R N
Sort records according to a particular index. This makes your data
much more localized and may speed up range-based SELECT and ORDER BY
operations that use this index. (The first time you use this option
to sort a table, it may be very slow.) To determine a table's index
numbers, use SHOW INDEX, which displays a table's indexes in the
same order that myisamchk sees them. Indexes are numbered beginning
with 1.
If keys are not packed (PACK_KEYS=0), they have the same length, so
when myisamchk sorts and moves records, it just overwrites record
offsets in the index. If keys are packed (PACK_KEYS=1), myisamchk
must unpack key blocks first, then re-create indexes and pack the
key blocks again. (In this case, re-creating indexes is faster than
updating offsets for each index.)
3 - alter table ... drop column comments;