Улучшение #62
На больших данных проседает операция добавление трафика в базу
| Status: | Закрыт | Start date: | 2010-11-01 | |
|---|---|---|---|---|
| Priority: | Высокий | Due date: | 2010-11-24 | |
| Assignee: |  Serg79 - |
% Done: | 100% |
|
| Category: | sql | |||
| Target version: | 0.1-beta |
Description
Когда количество строк в таблице traffic_cur начинает приближаться к 4 миллионам, обновление записей при вставке новой порции данных вызывает катастрофическое падение производительности базы. Что, в свою очередь, косвенно начинает приводить к разрушению истинности данных в базе.
Лог скрипта выгрузки данных:
Oct 17 04:40:03 rm-proxy trafsave.pl[16433]: WARNING: When add data to the database updated more than one record (6). Oct 17 04:40:03 rm-proxy trafsave.pl[12123]: WARNING: When add data to the database updated more than one record (6). Oct 17 04:40:03 rm-proxy trafsave.pl[11923]: WARNING: When add data to the database updated more than one record (6). Oct 17 04:40:04 rm-proxy trafsave.pl[3228]: WARNING: When add data to the database updated more than one record (3). Oct 17 04:40:04 rm-proxy trafsave.pl[7426]: WARNING: When add data to the database updated more than one record (6). Oct 17 04:40:04 rm-proxy trafsave.pl[7720]: WARNING: When add data to the database updated more than one record (3). Oct 17 04:40:05 rm-proxy trafsave.pl[9623]: WARNING: When add data to the database updated more than one record (6). Oct 17 04:40:05 rm-proxy trafsave.pl[14185]: WARNING: When add data to the database updated more than one record (6).
Лог медленных запросов MySQL:
# Time: 101014 23:55:03
# User@Host: kkk[kkk] @ localhost []
# Query_time: 8 Lock_time: 3 Rows_sent: 0 Rows_examined: 0
use trafstat;
update traffic_cur set inBytes = inBytes + 0, outBytes = outBytes + 1296
where date = '2010-10-14 11:30:0' && srcIp = '3232235650' && dstIp = '2915158597' && dstPort = '80' && pId = '1';
# Time: 101015 0:29:38
# User@Host: kkk[kkk] @ localhost []
# Query_time: 10 Lock_time: 4 Rows_sent: 0 Rows_examined: 0
update traffic_cur set inBytes = inBytes + 0, outBytes = outBytes + 972
where date = '2010-10-14 11:30:0' && srcIp = '3232235650' && dstIp = '3161599592' && dstPort = '14445' && pId = '1';
# Time: 101015 0:53:21
# User@Host: kkk[kkk] @ localhost []
# Query_time: 9 Lock_time: 3 Rows_sent: 0 Rows_examined: 0
update traffic_cur set inBytes = inBytes + 0, outBytes = outBytes + 868
where date = '2010-10-14 11:30:0' && srcIp = '3232235650' && dstIp = '1832751900' && dstPort = '15720' && pId = '1';
# Time: 101015 1:22:08
# User@Host: kkk[kkk] @ localhost []
# Query_time: 10 Lock_time: 4 Rows_sent: 0 Rows_examined: 0
update traffic_cur set inBytes = inBytes + 95, outBytes = outBytes + 0
where date = '2010-10-14 15:30:0' && srcIp = '3232235650' && dstIp = '1401841941' && dstPort = '40646' && pId = '2';
# Time: 101015 2:45:23
# User@Host: kkk[kkk] @ localhost []
# Query_time: 8 Lock_time: 3 Rows_sent: 0 Rows_examined: 0
update traffic_cur set inBytes = inBytes + 200, outBytes = outBytes + 0
where date = '2010-10-14 11:30:0' && srcIp = '3232235650' && dstIp = '1296210733' && dstPort = '16465' && pId = '1';
/usr/libexec/mysqld, Version: 5.0.67-log (Source distribution). started with:
Tcp port: 0 Unix socket: /var/run/mysql/mysql.sock
Time Id Command Argument
Количество процессов trafsave.pl начинает возрастать (должен быть один):
root@rm-proxy:/var/log# ps ax | grep trafsave.pl 342 ? S 0:00 sh -c /usr/libexec/trafstat/trafsave.pl --eth=eth1 --oftime=-2 --daemon 2>/dev/null 343 ? S 0:12 /usr/bin/perl /usr/libexec/trafstat/trafsave.pl --eth=eth1 --oftime=-2 --daemon 1451 ? S 0:00 sh -c /usr/libexec/trafstat/trafsave.pl --eth=eth1 --oftime=-2 --daemon 2>/dev/null 1452 ? S 0:10 /usr/bin/perl /usr/libexec/trafstat/trafsave.pl --eth=eth1 --oftime=-2 --daemon 3677 ? S 0:00 sh -c /usr/libexec/trafstat/trafsave.pl --eth=eth1 --oftime=-2 --daemon 2>/dev/null 3678 ? S 0:07 /usr/bin/perl /usr/libexec/trafstat/trafsave.pl --eth=eth1 --oftime=-2 --daemon 5787 ? S 0:00 sh -c /usr/libexec/trafstat/trafsave.pl --eth=eth1 --oftime=-2 --daemon 2>/dev/null 5788 ? S 0:06 /usr/bin/perl /usr/libexec/trafstat/trafsave.pl --eth=eth1 --oftime=-2 --daemon 7953 ? S 0:00 sh -c /usr/libexec/trafstat/trafsave.pl --eth=eth1 --oftime=-2 --daemon 2>/dev/null 7954 ? S 0:04 /usr/bin/perl /usr/libexec/trafstat/trafsave.pl --eth=eth1 --oftime=-2 --daemon 10012 ? S 0:00 sh -c /usr/libexec/trafstat/trafsave.pl --eth=eth1 --oftime=-2 --daemon 2>/dev/null 10013 ? S 0:04 /usr/bin/perl /usr/libexec/trafstat/trafsave.pl --eth=eth1 --oftime=-2 --daemon 12145 ? S 0:00 sh -c /usr/libexec/trafstat/trafsave.pl --eth=eth1 --oftime=-2 --daemon 2>/dev/null 12146 ? S 0:04 /usr/bin/perl /usr/libexec/trafstat/trafsave.pl --eth=eth1 --oftime=-2 --daemon 14254 ? S 0:00 sh -c /usr/libexec/trafstat/trafsave.pl --eth=eth1 --oftime=-2 --daemon 2>/dev/null 14255 ? S 0:02 /usr/bin/perl /usr/libexec/trafstat/trafsave.pl --eth=eth1 --oftime=-2 --daemon 16457 ? S 0:00 sh -c /usr/libexec/trafstat/trafsave.pl --eth=eth1 --oftime=-2 --daemon 2>/dev/null 16458 ? S 0:01 /usr/bin/perl /usr/libexec/trafstat/trafsave.pl --eth=eth1 --oftime=-2 --daemon 18800 ? S 0:00 sh -c /usr/libexec/trafstat/trafsave.pl --eth=eth1 --oftime=-2 --daemon 2>/dev/null 18801 ? S 0:01 /usr/bin/perl /usr/libexec/trafstat/trafsave.pl --eth=eth1 --oftime=-2 --daemon 21189 ? S 0:00 sh -c /usr/libexec/trafstat/trafsave.pl --eth=eth1 --oftime=-2 --daemon 2>/dev/null 21190 ? S 0:01 /usr/bin/perl /usr/libexec/trafstat/trafsave.pl --eth=eth1 --oftime=-2 --daemon 23425 ? S 0:00 sh -c /usr/libexec/trafstat/trafsave.pl --eth=eth1 --oftime=-2 --daemon 2>/dev/null 23426 ? S 0:00 /usr/bin/perl /usr/libexec/trafstat/trafsave.pl --eth=eth1 --oftime=-2 --daemon 25614 ? S 0:00 sh -c /usr/libexec/trafstat/trafsave.pl --eth=eth1 --oftime=-2 --daemon 2>/dev/null 25615 ? S 0:00 /usr/bin/perl /usr/libexec/trafstat/trafsave.pl --eth=eth1 --oftime=-2 --daemon 27959 ? S 0:00 sh -c /usr/libexec/trafstat/trafsave.pl --eth=eth1 --oftime=-2 --daemon 2>/dev/null 27960 ? S 0:00 /usr/bin/perl /usr/libexec/trafstat/trafsave.pl --eth=eth1 --oftime=-2 --daemon 30326 ? S 0:00 sh -c /usr/libexec/trafstat/trafsave.pl --eth=eth1 --oftime=-2 --daemon 2>/dev/null 30327 ? S 0:00 /usr/bin/perl /usr/libexec/trafstat/trafsave.pl --eth=eth1 --oftime=-2 --daemon 31547 pts/7 R+ 0:00 grep trafsave.pl
History
Updated by Роман Чернышов over 1 year ago
- File trafsave_new.pl.bz2 added
Исследования показали, что проблема кроется в индексах: отсутствие PK приводит к возникновению дублированных строк и, как следствие, к снижению производительности запросов UPDATE.
Предлагаю следующие усовершенствования по таблице traffic_cur и скрипту trafsave.pl:
1. Избавится от индексов key1 и key2 (вопрос требует дополнительных исследований).
2. Изменить столбцы:
2.1. date - с DATETIME на TIMESTAMP:
ALTER TABLE traffic_cur MODIFY `date` TIMESTAMP DEFAULT 0;
2.2. dstPort - с INT на SMALLINT UNSIGNED:
ALTER TABLE traffic_cur MODIFY `dstPort` SMALLINT UNSIGNED NOT NULL;
2.3. pId - с TINYINT на TINYINT UNSIGNED - для полноценной поддержки всех протоколов, вкладываемых в IP:
ALTER TABLE traffic_cur MODIFY `pId` TINYINT UNSIGNED NOT NULL;
Но сперва надо менять логику программы: специальное значение -1 заменить на 255!
3. Добавить PK (date, srcIp, dstIp, dstPort, pId):
ALTER TABLE traffic_cur ADD PRIMARY KEY (date, srcIp, dstIp, dstPort, pId);
4. В скрипте изменить обработку строк:
4.1. Производить отсев строк в цикле, т.к. во входном потоке могут встречаться пустые строки и заголовки.
4.2. Заменить проверку портов на константы "client" и "none" с условий if на операторы s///.
4.3. Добавить обработку значения порта "undef" (не знаю, что оно тут значит и потому просто отсеиваю такие строки).
4.4. Группировать строки по PK прямо в скрипте. Соответственно, вынести вывод в базу из цикла ввода.
4.5. Вывод в базу осуществлять оператором INSERT INTO ... ON DUPLICATE KEY UPDATE ... .
Вероятно, предложенные изменения можно отнести и на счет других таблиц базы, сходных с по формату traffic_cur. Это тоже вопрос для исследований.
На тестовых данных (с предварительной нормализацией таблицы traffic_cur - суммирования дублированных строк) получены следующие результаты:- В таблице изначально было 2039300 строк.
- На вводе 863295 строк.
- Строк с принятыми данными - 861420.
- После группировки осталось 271957 строк.
- После записи в базу в таблице 2039302 строк.
- На ввод затрачено 47 секунд.
- На запись в базу затрачено 97 секунд.
- Скорость записи: ~2800 операторов в секунду.
Тесты проводились на машине P4 2.8 под управлением CentOS 5.5 и MySQL 5.0.77.
Переработанный скрипт прилагаю.
Updated by Serg79 - over 1 year ago
- Status changed from Новый to В работе
Updated by Serg79 - over 1 year ago
Провел тесты с использованием выше предложенной структурой таблицы traffic_cur.
Тесты проводились в виртуальной машине на компьютере P3 с 512 Мбайт ОЗУ.
1. Текущее состояние таблицы.
Таблица имеет следующую структуру:
create table `traffic_cur` ( `date` datetime not null, `srcIp` int unsigned not null, `dstIp` int unsigned not null, `dstPort` int not null, `pId` tinyint not null, `inBytes` int unsigned not null, `outBytes` int unsigned not null, key `key1` (`date`), key `key2` (`srcIp`, `date`) ) engine = MyISAM;
В пустую таблицу выгружается дамп трафика который содержит 862075 строк.
Все вставки осуществляются следующим оператором:
insert into traffic_cur (date, srcIp, dstIp, dstPort, pId, inBytes, outBytes) values(?, ?, ?, ?, ?, ?, ?)
Уникальных записей в таблице получилось: 861421
Битых значений в дампе: 654
Выгрузка дампа заняла времени:
real 24m24.862s user 2m32.910s sys 13m19.860s
Чистый результат: 100%
2. Используется модифицированная таблица.
Таблица имеет следующую структуру:
CREATE TABLE `traffic_cur` ( `date` timestamp NOT NULL default '0000-00-00 00:00:00', `srcIp` int(10) unsigned NOT NULL, `dstIp` int(10) unsigned NOT NULL, `dstPort` smallint(5) unsigned NOT NULL, `pId` tinyint(4) NOT NULL, `inBytes` int(10) unsigned NOT NULL, `outBytes` int(10) unsigned NOT NULL, PRIMARY KEY (`date`,`srcIp`,`dstIp`,`dstPort`,`pId`) ) ENGINE=MyISAM;
В пустую таблицу выгружается дамп трафика который содержит 862075 строк.
Все вставки осуществляются следующим оператором:
insert into traffic_cur (date, srcIp, dstIp, dstPort, pId, inBytes, outBytes) values(?, ?, ?, ?, ?, ?, ?) on duplicate key update inBytes = inBytes + ?, outBytes = outBytes + ?
Срабатывал как чистый INSERT, так и DUPLICATE KEY.
Уникальных записей в таблице получилось: 271958
Битых значений в дампе: 654
Выгрузка дампа заняла времени:
real 26m17.528s user 2m49.470s sys 10m13.840s
107% от теста 1.
3. Используется модифицированная таблица.
Таблица имеет следующую структуру:
CREATE TABLE `traffic_cur` ( `date` timestamp NOT NULL default '0000-00-00 00:00:00', `srcIp` int(10) unsigned NOT NULL, `dstIp` int(10) unsigned NOT NULL, `dstPort` smallint(5) unsigned NOT NULL, `pId` tinyint(4) NOT NULL, `inBytes` int(10) unsigned NOT NULL, `outBytes` int(10) unsigned NOT NULL, PRIMARY KEY (`date`,`srcIp`,`dstIp`,`dstPort`,`pId`) ) ENGINE=MyISAM;
Таблица уже содержит строки загруженные в тесте 2.
Все вставки осуществляются следующим оператором:
insert into traffic_cur (date, srcIp, dstIp, dstPort, pId, inBytes, outBytes) values(?, ?, ?, ?, ?, ?, ?) on duplicate key update inBytes = inBytes + ?, outBytes = outBytes + ?
Срабатывал только чистый DUPLICATE KEY.
Уникальных записей в таблице получилось: 271958
Битых значений в дампе: 654
Выгрузка дампа заняла времени:
real 26m49.304s user 2m52.760s sys 9m9.490s
109% от теста 1.
Вывод
Предложенная структура таблицы позволяет осуществлять операции вставки трафика со скоростью ниже всего на 7% чем при использовании чистого INSERT и на порядок быстрее чем при использовании операции UPDATE. И при этом обеспечивается четырех кратное сокращение числа строк в таблице в отличии от чистого INSERT.
Надо переносить данное решение на рабочую ветку.
Updated by Serg79 - over 1 year ago
- % Done changed from 0 to 100
- Due date set to 2010-11-24
- Status changed from В работе to Закрыт
Внесены изменения в структуру базы данных. Для обновления базы используйте следующий скрипт: source:trunk/scripts/tools/updbase.r128
Committed in r128