Denveroid1
Новичок
Работа с очень большими таблицами (MySQL)
Работаю с MySQL 5.0.19. Машина: P4 Xeon 2.4 X 2, 2ГБ RAM - это мин. конфигурация.
Имеется следующая таблица:
По названию таблицы не трудно догадаться, что речь идет о логгировании всего траффика, включая реквесты и респонсы, и всякую доп. информацию. Записью в таблицу занимается модуль, написанный на C. Он выполняет LAZY INSERTS со скоростью до 5000 реквестов в секунду.
В таблице могут находится миллионы записей. Для примера, скажем, до 3 миллионов максимум. Для статистику скажу, что при 2 миллионах записей со стандартными, не огромными реквестам+респонсами, файлы весят: PROXY_LOG.MYD около 2ГБ, а индексный файл PROXY_LOG.MYI около 250МБ.
Пользователю нужно предоставить интерфейс для поиска по этой таблице по куче различных фильтров и показать ему результаты поиска c возможностью сортировки результатов по различным полям (log_time, uri, ip, response_code). По умолчании сортировка идет по ORDER BY log_time DESC - то есть времени.
Также нужен, естесственно, пэйджинг. То есть придется использовать оффсеты: LIMIT <offset>, <perpage>
Основной критерий поиска - это account_id. Пользователя обычно интересует траффик для определенного account_id (или нескольких определенных account_id, так называемая группа аккаунтов). То есть, всегда в селекте будет присутствовать WHERE account_id = <account_id> или WHERE account_id IN (<account_id_list>).
Дополнительные критерии поиска:
При данной структуре и при 2 лямах записях, простые запросы:
mysql> SELECT id FROM PROXY_LOG WHERE account_id='1' AND status!=0 LIMIT 100000,10;
+--------+
| id |
+--------+
| 930511 |
| 930514 |
| 930529 |
| 930533 |
| 930534 |
| 930537 |
| 930544 |
| 930551 |
| 930566 |
| 930572 |
+--------+
10 rows in set (11.13 sec)
mysql> SELECT id FROM PROXY_LOG WHERE account_id='1' LIMIT 100000,10;
+--------+
| id |
+--------+
| 328696 |
| 328698 |
| 328700 |
| 328701 |
| 328703 |
| 328704 |
| 328708 |
| 328709 |
| 328711 |
| 328712 |
+--------+
10 rows in set (2.41 sec)
mysql> SELECT id FROM PROXY_LOG WHERE account_id='1' AND 8589934592&status=8589934592 LIMIT 0,10;
+---------+
| id |
+---------+
| 2073158 |
| 2073159 |
| 2073160 |
| 2073161 |
| 2073162 |
| 2073163 |
| 2073164 |
| 2073165 |
| 2073166 |
| 2073167 |
+---------+
10 rows in set (32.58 sec)
mysql> SELECT id FROM PROXY_LOG WHERE account_id='1' AND log_time>'1144548120' LIMIT 100000,10;
+---------+
| id |
+---------+
| 1299558 |
| 1299560 |
| 1299561 |
| 1299562 |
| 1299564 |
| 1299565 |
| 1299566 |
| 1299567 |
| 1299568 |
| 1299569 |
+---------+
10 rows in set (15.87 sec)
mysql> SELECT id FROM PROXY_LOG WHERE account_id='1' AND flg_req_blocked='1' AND flg_req_truncated='1' AND src_ip='-1062703303' AND response_code=200 AND uri LIKE '%php%' AND log_time>'1144288920' AND log_time<'1144720980' AND 8589934592&status=8589934592 LIMIT 0,10;
Empty set (0.04 sec)
Задача: Этот самолет должен быстро летать. До 5 секунд считается быстро
У меня есть 2 решения с ограничением по кол-ву записей, в которых нужно искать. Например, я могу дать пользователю опцию искать в последних 1000, 5000, 10000 или 50000. Больше начинает тормозить.
Есть мнения?
Работаю с MySQL 5.0.19. Машина: P4 Xeon 2.4 X 2, 2ГБ RAM - это мин. конфигурация.
Имеется следующая таблица:
Код:
CREATE TABLE PROXY_LOG (
id bigint(20) unsigned NOT NULL auto_increment,
support_id bigint(20) unsigned NOT NULL default '0',
account_id int(10) NOT NULL default '0',
src_ip int(12) default NULL,
app_id int(10) NOT NULL default '0',
log_time int(10) NOT NULL default '0',
method_code int(10) NOT NULL default '0',
uri varchar(255) NOT NULL default '',
obj_crc bigint(20) unsigned NOT NULL default '0',
core_ref_crc bigint(20) unsigned NOT NULL default '0',
response_code int(4) NOT NULL default '0',
ts_cookie_name varchar(255) NOT NULL default '',
`status` bigint(20) unsigned NOT NULL default '0',
learning_mask bigint(20) unsigned NOT NULL default '0',
alert_mask bigint(20) unsigned NOT NULL default '0',
blocking_mask bigint(20) unsigned NOT NULL default '0',
flg_sampling int(2) NOT NULL default '0',
request longblob,
flg_has_request int(2) NOT NULL default '0',
flg_req_truncated int(2) NOT NULL default '0',
flg_req_blocked int(2) NOT NULL default '0',
actual_request_length int(10) NOT NULL default '0',
response longblob,
flg_has_response int(2) NOT NULL default '0',
flg_resp_truncated int(2) NOT NULL default '0',
flg_resp_blocked int(2) NOT NULL default '0',
actual_response_length int(10) NOT NULL default '0',
violations blob,
PRIMARY KEY (id),
KEY support_id (support_id),
KEY account_id (account_id),
KEY src_ip (src_ip),
KEY log_time (log_time),
KEY learning_mask (learning_mask),
KEY alert_mask (alert_mask),
KEY blocking_mask (blocking_mask),
KEY flg_has_request (flg_has_request),
KEY flg_has_response (flg_has_response)
);В таблице могут находится миллионы записей. Для примера, скажем, до 3 миллионов максимум. Для статистику скажу, что при 2 миллионах записей со стандартными, не огромными реквестам+респонсами, файлы весят: PROXY_LOG.MYD около 2ГБ, а индексный файл PROXY_LOG.MYI около 250МБ.
Пользователю нужно предоставить интерфейс для поиска по этой таблице по куче различных фильтров и показать ему результаты поиска c возможностью сортировки результатов по различным полям (log_time, uri, ip, response_code). По умолчании сортировка идет по ORDER BY log_time DESC - то есть времени.
Также нужен, естесственно, пэйджинг. То есть придется использовать оффсеты: LIMIT <offset>, <perpage>
Основной критерий поиска - это account_id. Пользователя обычно интересует траффик для определенного account_id (или нескольких определенных account_id, так называемая группа аккаунтов). То есть, всегда в селекте будет присутствовать WHERE account_id = <account_id> или WHERE account_id IN (<account_id_list>).
Дополнительные критерии поиска:
- Violation - поле status, держит в себе статус реквеста в виде bitwised кода. То есть, что найти определенный статус в этом поле нужно сделать WHERE status&<какой_то_код> = <какой_то_код>. Надеюсь понятно объясняю.
- IP - поле src_ip. Хранится как ip2long
- Time Period - поле log_time. UNIX_TIMESTAMP
- Request Type - поле status. Варианты: Legal Request и Illegal Request. Если status=0, то это Legal Request, иначе Illegal.
- Response Code - поле response_code. Просто поиск по респонс коду WHERE response_code=<response_code>
- Blocked Requests - поле flg_req_blocked. Вариант только один: WHERE flg_req_blocked=1
- Truncated Requests - поле flg_req_truncated. Вариант только один: WHERE flg_req_truncated=1
- Support ID - поле support_id. Просто поиск по полю WHERE support_id=<support_id>
- Containing String - поле uri. Поиск стринга содержащегося в поле uri, WHERE uri LIKE '%<что-то>%'.
При данной структуре и при 2 лямах записях, простые запросы:
mysql> SELECT id FROM PROXY_LOG WHERE account_id='1' AND status!=0 LIMIT 100000,10;
+--------+
| id |
+--------+
| 930511 |
| 930514 |
| 930529 |
| 930533 |
| 930534 |
| 930537 |
| 930544 |
| 930551 |
| 930566 |
| 930572 |
+--------+
10 rows in set (11.13 sec)
mysql> SELECT id FROM PROXY_LOG WHERE account_id='1' LIMIT 100000,10;
+--------+
| id |
+--------+
| 328696 |
| 328698 |
| 328700 |
| 328701 |
| 328703 |
| 328704 |
| 328708 |
| 328709 |
| 328711 |
| 328712 |
+--------+
10 rows in set (2.41 sec)
mysql> SELECT id FROM PROXY_LOG WHERE account_id='1' AND 8589934592&status=8589934592 LIMIT 0,10;
+---------+
| id |
+---------+
| 2073158 |
| 2073159 |
| 2073160 |
| 2073161 |
| 2073162 |
| 2073163 |
| 2073164 |
| 2073165 |
| 2073166 |
| 2073167 |
+---------+
10 rows in set (32.58 sec)
mysql> SELECT id FROM PROXY_LOG WHERE account_id='1' AND log_time>'1144548120' LIMIT 100000,10;
+---------+
| id |
+---------+
| 1299558 |
| 1299560 |
| 1299561 |
| 1299562 |
| 1299564 |
| 1299565 |
| 1299566 |
| 1299567 |
| 1299568 |
| 1299569 |
+---------+
10 rows in set (15.87 sec)
mysql> SELECT id FROM PROXY_LOG WHERE account_id='1' AND flg_req_blocked='1' AND flg_req_truncated='1' AND src_ip='-1062703303' AND response_code=200 AND uri LIKE '%php%' AND log_time>'1144288920' AND log_time<'1144720980' AND 8589934592&status=8589934592 LIMIT 0,10;
Empty set (0.04 sec)
Задача: Этот самолет должен быстро летать. До 5 секунд считается быстро
У меня есть 2 решения с ограничением по кол-ву записей, в которых нужно искать. Например, я могу дать пользователю опцию искать в последних 1000, 5000, 10000 или 50000. Больше начинает тормозить.
Есть мнения?
