На больших данных проседает операция 'update' в MySQL

[Serg79]

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

Вот структура таблицы:

Код:

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;

Вот лог медленных запросов 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 

Подскажите как быть? Может можно как-то разбить таблицу на более мелкие? Или переходить на PostgreSQL?

[RXL]

Как часто ты делаешь update?

1. Увеличь буфер ключей MYISAM.

2. Попробуй блочное обновление ключей - запрещай ключи, проводи серию update и разрешай ключи вновь.

3. Переходи на Innodb.

[McZim]

Есть индексы? Может, для эксперимента, их удалить? Кстате, а в mysql есть какаянибудь трассировка, что бы можно было понять какие именно ресурсы, на каких именно объектах тратятся?

[Serg79]

Как часто ты делаешь update?

1. Увеличь буфер ключей MYISAM.

2. Попробуй блочное обновление ключей - запрещай ключи, проводи серию update и разрешай ключи вновь.

3. Переходи на Innodb.

update выполняется каждые 5 минут для ~1000 записей, т.е. каждые 5 минут вызывается ~1000 update.

1. Использовал следующие параметры для запуска MySQL: my-huge.cnf,  my-large.cnf,  my-medium.cnf,  my-small.cnf (что в свою очередь изменяет размер буфера под ключи), принципиальной разницы никакой. Да и это не выход, надо пересматривать алгоритм работы с данными, а не повышать производительность железа.

2. А это что такое?

3. Вызвало даже падение производительности еще где то на 10%.

Есть индексы? Может, для эксперимента, их удалить? Кстате, а в mysql есть какаянибудь трассировка, что бы можно было понять какие именно ресурсы, на каких именно объектах тратятся?

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

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


Думаю разбить вышеприведенную таблицу на две под таблицы:

Код:

create table `traffic_cur_key`
(
        `id` int not null auto_increment,
        `date` datetime not null,
        `srcIp` int unsigned not null,
        `dstIp` int unsigned not null,
        `dstPort` int not null,
        `pId` tinyint not null,
        primary key (`id`),
        key (`date`),
        key (`srcIp`, `date`)
) engine = MyISAM;

create table `traffic_cur_data`
(
        `id` int not null,
        `inBytes` int unsigned not null,
        `outBytes` int unsigned not null,
        primary key (`id`),
) engine = MyISAM;

Тогда в update будет выглядеть следующим образом:

Код:

update
  traffic_cur_data
set
  inBytes = inBytes + 100, outBytes = outBytes + 20
where
  id =
    (
      select id from traffic_cur_key
      where date = '2010-10-14 11:30:0' && srcIp = '3232235650' && dstIp = '2915158597' && dstPort = '80' && pId = '1'
    );

В этом случае обновляться (update) будет только маленькая таблица 'traffic_cur_data', а в таблицу 'traffic_cur_key' будут производиться только 'insert' по мере необходимости. Думаю это должно позволить на 30% - 40% увеличить производительность. Ну а если и это не поможет, то надо будет переходить на 'insert' в маленькую таблицу (traffic_cur_data) и отказаться от агрегирования данных.

Мне говорят, что в PostgreSQL довольно оптимизирован оператор 'update' и советуют с начало попробовать его.

[McZim]

Если проводить аналогию с Oracle, то update обновляет записи как в таблице так и в индексах, не рассматриваем специфические задачи.

Скажи, а есть возможность спроектироваться код так, что бы update делать для "пачек" строк, а не для каждой!? Я думаю что Рома, что то похожее имеет ввиду.

Я не знаю MySQL, поэтому могу говорить непонятно.

[RXL]

2.

Код: (SQL)

ALTER TABLE tab DISABLE KEYS;
UPDATE tab ...;
UPDATE tab ...;
UPDATE tab ...;
UPDATE tab ...;
UPDATE tab ...;
ALTER TABLE tab ENABLE KEYS;

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

3.
Формат MYISAM не транзакционный и не поддерживает частичные блокировки. Для операций изменения таблицы он блокирует всю таблицу. Массовые UPDATE приводят к массовому блокированию таблицы на время операции (во время которой производятся еще и обновления индексов). Исключение - INSERT в конец таблицы (когда нет пропусков в данных) - это не приводит к блокировке. Этот формат подходит под преобладающую выборку.
Формат INNODB транзакционный и поддерживает блокировку как отдельных строк, так диапазонов (используя блокировку PK). Вставка и модификация строки не мешают параллельным не пересекающимся операциям. У данного типа таблиц свой, отдельный от MYISAM кеш - его тоже надо настраивать. При правильной настройке производительность не только не должна проседать, но и может повышаться за счет параллелизма конкурирующих запросов.

Кстати, у тебя нет PK - с ним поиск и обновление было бы существенно быстрее.

Синтаксис UPDATE и SELECT схож и EXPLAIN SELECT отражает ту же картину, которая будет при UPDATE - что и как ищется.

[Serg79]

2. Надо будет попробовать. Но сильно сомневаюсь что это даст существенный прирост производительности.

3. По этому пункту, что бы не было каких либо гаданий на тему, что во время 'update' еще кто то обращается к таблице и происходят где то там блокировки какие то... Эта же проблема воспроизводиться точно так же и на полностью свободном сервере MySQL от каких либо запросов. Т.е. каждые 5 минут запускается скрипт который через 'update' производит агрегацию данных, при этом к базе больше ни кто кроме него не обращается. Теперь вопрос по InnoDB, первым делом при решении данной проблемы Google сказал, что надо использовать InnoDB и это даст прирост производительности вот на таких операциях 'update t set count = count + 1'. Если честно, InnoDB ни дало никакого прироста производительности и его преимущество на уровне блокировки строк только добавило тормозов (не забываем, что процесс выполняющий update имеет монопольный доступ к базе), т.к. заблокировать всю таблицу намного быстрее чем выполнять поиск строки перед ее блокировкой. Да и сомневаюсь я, что если запрос update на таблице MyISAM (заметим, что MyISAM очень простой формат хранения данных и не поддерживает транзакции, что в свою очередь позволяет в плане производительности отдельных, не конкурирующих запросах, обгонять те же операции на таблице InnoDB) выполняющийся 54 сек, будет на таблице InnoDB выполняться за микросекунды. Одним словом RXL, не в ту сторону копаешь.

Кстати, у тебя нет PK - с ним поиск и обновление было бы существенно быстрее.

Для этой таблицы PK надо делать либо таким:

Код:

primary key (`date`, `srcIp`, `dstIp`, `dstPort`, `pId`)

либо не заводить его совсем, так как пользы от одного 'primary key (`id`)' не будет вообще. Но такой PK будет занимать места на диске в 7 раз больше чем сама таблица хранящая данные, и его обновление будет занимать еще больше времени.

Синтаксис UPDATE и SELECT схож и EXPLAIN SELECT отражает ту же картину, которая будет при UPDATE - что и как ищется.

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

Скажи, а есть возможность спроектироваться код так, что бы update делать для "пачек" строк, а не для каждой!? Я думаю что Рома, что то похожее имеет ввиду.

Я тоже думал об этом, но как я выше сказал, уникальность каждой записи определяется следующим видом 'primary key (`date`, `srcIp`, `dstIp`, `dstPort`, `pId`)', т.е. в таблице не встречаются две строки, которые имели бы одинаковые значения этих параметров. Мне ничего не приходит в голову такое, которое позволит в одном UPDATE обновление нескольких строк.


Короче тупик, уперся в производительность UPDATE в MySQL на большом количестве данных. Надо пробовать разбивать таблицу которую требуется обновлять на более мелкие под таблицы и обновлять уже их, или полностью исключать UPDATE и использовать только INSERT. С INSERT данные просто влетают в таблицу, но ее размер начинает расти очень быстро.

[McZim]

Serg79, аггрегация строк до вставки не подойдет? Ненужно делать update, и колличество insert сократиться.

[Serg79]

Serg79, аггрегация строк до вставки не подойдет? Ненужно делать update, и колличество insert сократиться.

Думал об этом, но данные поступающие от приложения каждые 5 минут уже агрегированы по этому времени. А в базе данные агрегируются в диапазоне 1 час. Если копить статистику в течении одного часа и потом INSERT-ить ее в таблицу, то информация о трафике в таблице, будет очень старой под конец часа.

Можно конечно вводить избыточность в базу и завести отдельную таблицу в которой данные будут INSERT-иться в течении часа, после чего они агрегируются и INSERT-ятся уже в основную таблицу, а часовая таблица очищается. Это конечно будет на порядок быстрее.

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

[McZim]

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

Тот способ, кторый затрагивает изменения наименьшего колличества объектов бд.

[RXL]

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

[Serg79]

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

Я так и сделал RXL, пока отложил этот вопрос. Да же и не хочу о этих базах думать.))))

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

[RXL]

Одно точно могу сказать, что не надо биться головой - все объяснимо, если разобраться в деталях. MySQL версии 5.1 хорошо поддается оптимизации.

[Serg79]

Одно точно могу сказать, что не надо биться головой - все объяснимо, если разобраться в деталях. MySQL версии 5.1 хорошо поддается оптимизации.

То же в планах попробовать обновиться до версии 5.1 и посмотреть что там будет, сейчас использую 5.0.91. Но при обновлении придется все окружение пересобирать, это PHP, Perl и Apache. За одно тогда можно будет и PostgreSQL попробовать.


RXL, если есть большое желание попробовать объяснить объяснимое, то могу скинуть тебе все необходимые дампы базы и скрипты выгрузок. Может у тебя оператор UPDATE летать будет.)))

[RXL]

Пересобирать не придется - клиентская библиотека та же.

Давай. За одно попробую оптимизацию. Какой размер дампа?

[Serg79]

Пересобирать не придется - клиентская библиотека та же.

Давай. За одно попробую оптимизацию. Какой размер дампа?

Хорошо, подготовлю все и залью на ftp.shelek. Может я действительно чего не понимаю, все таки с MySQL работаю еще мало.

[RXL]

Апгрейд 5.0 -> 5.1: с самой базой толком ничего делать не надо (по крайней мере с MYISAM) (бакапы - по любому ). Но надо проапгрейдить системную базу "mysql" и сделать это до апгрейда софта (!), т.к. при отсутствии новых таблиц и полей сервер не стартует. Лучше перед апгрейдом прочесть детали в мануале.

[Serg79]

Все необходимое залил на ftp.

[RXL]

На всякий случай цитирую, что написал в аську поле твоего отключения. Немного подправил.

-------------------------------
alter table traffic_cur modify date timestamp default 0, modify dstPort smallint unsigned not null;

Размер строки сократился с 30 до 24 байт.
Попробовал создать PK:

mysql> alter table traffic_cur add primary key (date, srcIp, dstIp, dstPort, pId);
ERROR 1062 (23000): Duplicate entry '2010-10-14 09:30:00-3232235650-1434095645-29227-2' for key 1

У тебя неуникальность в дампе traffic_cur.sql.

-------------------------------
mysql> select * from traffic_cur where date='2010-10-14 09:30:00' and srcIp=3232235650 and dstIp=1434095645 and dstPort=29227 and pId=2;
+---------------------+------------+------------+---------+-----+---------+----------+
| date                | srcIp      | dstIp      | dstPort | pId | inBytes | outBytes |
+---------------------+------------+------------+---------+-----+---------+----------+
| 2010-10-14 09:30:00 | 3232235650 | 1434095645 |   29227 |   2 |     625 |      318 |
| 2010-10-14 09:30:00 | 3232235650 | 1434095645 |   29227 |   2 |     625 |      318 |
+---------------------+------------+------------+---------+-----+---------+----------+
2 rows in set (0.90 sec)

(Меденно потому, что я удалил ключи)

-------------------------------
mysql> select date, srcIp, dstIp, dstPort, pId, count(*) from traffic_cur group by date, srcIp, dstIp, dstPort, pId having count(*) > 1;
..........................
389702 rows in set (3 min 48.43 sec)

mysql> select count(*) from traffic_cur;
+----------+
| count(*) |
+----------+
|  3713141 |
+----------+
1 row in set (0.00 sec)

-------------------------------
У тебя 10% строк составляют повторы!

-------------------------------
Вру - более 10%, т.к. там не только count(*) = 2, но и 5, и 6.

-------------------------------
Сравнил по всем столбцам:

mysql> select count(*), sum(t.cnt) from (select date, srcIp, dstIp, dstPort, pId, inBytes, outBytes, count(*) cnt from traffic_cur group by date, srcIp, dstIp, dstPort, pId, inBytes, outBytes having count(*) > 1) t;
+----------+------------+
| count(*) | sum(t.cnt) |
+----------+------------+
|   389963 |    2058893 |
+----------+------------+
1 row in set (3 min 42.69 sec)

Колонка 1 - сколько вариантов повторов.
Колонка 2 - столько строк в повторах.
Итого 389963 строк лишних.

-------------------------------
Ошибка: итог - 1668930.

Теперь по предполагаемому PK:

mysql> select count(*), sum(t.cnt) from (select date, srcIp, dstIp, dstPort, pId, count(*) cnt from traffic_cur group by date, srcIp, dstIp, dstPort, pId having count(*) > 1) t;
+----------+------------+
| count(*) | sum(t.cnt) |
+----------+------------+
|   389702 |    2063543 |
+----------+------------+
1 row in set (4 min 3.97 sec)

Лишних - 1673841 строк или 45%.
Таки надо избавляться от мусора...

-------------------------------
Просуммирую через временную таблицу.

mysql> CREATE TABLE `tmp` (
    ->   `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
    -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8
    -> ;
Query OK, 0 rows affected (0.03 sec)

mysql> insert into tmp select date, srcIp, dstIp, dstPort, pId, sum(inBytes), sum(outBytes) from traffic_cur group by date, srcIp, dstIp, dstPort, pId;
Query OK, 2039300 rows affected (4 min 7.32 sec)
Records: 2039300  Duplicates: 0  Warnings: 0

Сравни размеры:
-rw-rw---- 1 mysql mysql 48943200 Nov  3 05:14 tmp.MYD
-rw-rw---- 1 mysql mysql 89115408 Nov  3 04:41 traffic_cur.MYD
Исходный дамп:
-rw-rw---- 1 mysql mysql 111394260 Nov  3 05:17 traffic_cur.MYD
Получилось 44% от исходного!

Замещаем таблицу:

mysql> drop table traffic_cur;
Query OK, 0 rows affected (0.07 sec)

mysql> rename table `tmp` to `traffic_cur`;
Query OK, 0 rows affected (0.00 sec)

Создаем PK:

mysql> alter table traffic_cur add primary key (date, srcIp, dstIp, dstPort, pId);
Query OK, 2039300 rows affected (15.97 sec)
Records: 2039300  Duplicates: 0  Warnings: 0

Место на диске:
-rw-rw---- 1 mysql mysql 45179904 Nov  3 05:19 traffic_cur.MYI
Исходный индекс:
-rw-rw---- 1 mysql mysql 122568704 Nov  3 05:17 traffic_cur.MYI

Т.к. в PK первый столбец date, то он перекрывает старый индекс key1. Вместо key2 вероятно будет частичная выборка по (date, srcIp) через временную таблицу, но это нужно смотреть конкретные звпросы.

-------------------------------
Сбоит ip2long().

Use of uninitialized value in subroutine entry at ./trafsave_.pl line 164, <TRAFFIC> line 57791.
Use of uninitialized value in unpack at ./trafsave_.pl line 164, <TRAFFIC> line 57791.
Use of uninitialized value in subroutine entry at ./trafsave_.pl line 164, <TRAFFIC> line 57791.
Use of uninitialized value in unpack at ./trafsave_.pl line 164, <TRAFFIC> line 57791.

И далее по цепочке.

-------------------------------
Номера строк у меня другие - я закомментировал часть кода и вывожу данные в STDOUT.

-------------------------------
Понял. Начальный заголовок пстречается еще не раз.

-------------------------------
Вот такой тест позволит убрать их:

        next if m/(^\s|^\s*$)/;

-------------------------------
В выходном файле 861421 команд.

-------------------------------
# time mysql trafstat < a.sql

real    2m56.157s
user    0m26.225s
sys     0m24.390s

Все
Скорость соcтавила 4894 строк в секунду. Это показывает возможности MySQL на типичной старой офисной машине при должной оптимизации.

Провел тест с непосредственной работой с базой через программу:  6m14.910s или 2297 строк в секунду.
В базе сейчас 2310736 строк, объем данных 55457664, объем ключей 52185088.

Проблема была, прежде всего, в мусоре в таблице - следствие отсутствия контроля со стороны базы (не было PK) и в двойной работе UPDATE+INSERT.
Решение: чистка таблицы, добавление PK и смена на INSERT ... ON DUPLICATE KEY UPDATE.

[RXL]

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

Добавлено через 4 часа, 56 минут и 5 секунд:
Программу, однозначно, нужно подправлять. В ней есть баги.

1. Обработка заголовка. Приводит к массовым ошибкам run-time.

2. Оператор next. Ведь он не как в си работает - с ближайшим циклом, а по своему - с ближайшим блоком! По этому нужно либо использовать метку цикла, либо, если позволяют условия, делать обратную краткую запись.
Бага приводит к тому, что next не пропускает строку, а все равно ее обрабатывает.

Варианты:

Код:

    loop_by_file: while (<>)
    {
# .........
        if ($a > $b)
        {
# ...........
            next loop_by_file;
        }
    }

Код:

        next if $a > $b;

Код:

    while (<>)
    {
        $skip = 0;
# .......
        if ($a > $b)
        {
# .......
            $skip = 1;
        }

        next if $skip;

Насчет next  я был неправ. То ли моя книга устарела, то ли что-то запамятовал.

[Антон (LogRus)]

Дураций вопрос (ну не знаю я мускуль), а так положено числовые данные в кавычки оформлять в update или как? насколько я знаю, это приводит к лишним преобразованиям "строка <--> число"

и я бы добавил уникальный индекс по 4-ём полям, ну опять же я не спец, но мне кажется будет полезно.

Да и вообще сделал бы обёртку ввиде In Memory Db можно было бы прокачивать десятки  сотни тысяч записей в секунду. Ну это так поманьячить.

[RXL]

Антон (LogRus), нет, не положено, но преобразование типов проходит успешно.
Вообще-то, SQL - текстовый язык и числа все равно представляются как текст и преобразование неизбежно. И скажу более: тысяча таких преобразований не стоит одного обращения к диску.

Тут уникальность по пяти полям. Какие четыре?

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

[Serg79]

На счет дублирования записей да, это есть. Но это не дублирование в прямом смысле слова, просто совпадают значения (date, srcIp, dstIp, dstPort, pId), но если просуммировать значения inBytes и outBytes то получится корректное значение трафика. Так что надо рассматривать и такие случаи...

А ошибка эта накапливается за счет того, что за пять минут весь дамп не успевает выгрузиться и получается что то подобное:

Код:

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

[RXL]

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

Думаю, что нужна утилитка для лечения - аналогично проведенной мной работе. Т.е.:
1. Изменение типов столбцов.
2. Суммирование через дополнительную таблицу.
3. Создание PK.

На время обновления программу надо останавливать, либо лочить таблицу на весь период лечения.

И надо еще проверить запросы, ориентированные на ключ key2 - как они будут работать.

[Антон (LogRus)]

Антон (LogRus), нет, не положено, но преобразование типов проходит успешно.
Вообще-то, SQL - текстовый язык и числа все равно представляются как текст и преобразование неизбежно. И скажу более: тысяча таких преобразований не стоит одного обращения к диску.

Это я к OCI привык, биндинг, пакетная обработка и т.д. и т.п.
а настройки NLS не могут повлиять на формат отображения числа, я так понимаю в этом случае, есть шанс, что запрос не отработает

Тут уникальность по пяти полям. Какие четыре?

Считай опечаткой.

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

Ну собственно IMDB это не обязательно прям крутая БД, это может быть прикладная прослойка, которая занимается схлопыванием данных по ключу, накруткой счётчиков и прочим, потом производит обновление максимально быстрым способом в идеале это некий аналог ROWID из Oracle

Ну это собственно, то что ты и предлагаешь.

[RXL]

Форматы ввода даты (DATE и DATETIME) строго описаны: "YYYY-MM-DD[ HH:MM:SS]" или "YYYYMMDD[HHMMSS]". Лучше уточнить в мануале. TIMESTAMP соответствует по возможностям DATETIME, ограничиваясь только диапазоном Unix timestamp.



Добавлено через 9 часов, 40 минут и 2 секунды:
Объединение строк в программе позволило сократить количество запросов и уменьшило время непосредственной загрузки в базу до 97 секунд (без объединения - 176), а всей программы - до примерно 150 секунд.


Добавлено через 9 минут и 17 секунд:
Исходный вариант (вся программа) отработал за 394 секунды.

Нет, он не отработал — он упал через 394 секунды.
Исправил ошибку. Запустил. Такое ощущение, что будет выполняться целую вечность...
Не хватило терпения дожидаться - остановил через 40 минут.

Исправленный вариант показал на 10 млн. строк - 100 секунд, на 30 млн. - 102 секунды. Думаю, что и на 200 млн. он не выйдет за пределы положенных пяти минут.

Добавлено через 9 часов, 57 минут и 43 секунды:
Один минус объединения в программе - расход памяти. У меня на тесте процесс распухает до 80 МБ. Если объем памяти достаточный, можно не обращать на это внимание.

[Антон (LogRus)]

Ну 80 мег это фигня. Вот пара тройка гигов это проблема

[RXL]

Антон (LogRus), аршины бывают разными.

[Serg79]

Да, от UPDATE пришлось отказаться. Помог INSERT ... ON DUPLICATE KEY.