Есть мысли как бы это всё ускорить?

dusty.storm

Добрый день, хочу спросить вашего совета на предмет оптимизации, сам понимаю что чтото тут неправельно но мысли как сделать лучше пока нет.Eсть таблицы которые создаются каждый день вида users_hits_(день)_(месяц)_(год)они из себя представляют следущее:
+----------------------+--------------------------------------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------------+--------------------------------------------------------+------+-----+---------+----------------+
| id | mediumint(12) unsigned | NO | PRI | NULL | auto_increment |
| squid_timestamp | int(10) unsigned | NO | MUL | NULL | |
| squid_client_ip | varchar(20) | NO | MUL | NULL | |
| squid_answer_type | varchar(40) | NO | | NULL | |
| squid_answer_size | int(127) | NO | | NULL | |
| squid_request_method | enum('','CONNECT','GET','HEAD','OPTIONS','POST','PUT') | NO | | NULL | |
| squid_request_url | varchar(255) | NO | | NULL | |
| squid_user_id | varchar(255) | NO | MUL | NULL | |
| squid_host | varchar(255) | NO | MUL | NULL | |
+----------------------+--------------------------------------------------------+------+-----+---------+----------------+
Соотвественно записи в них приблизительно вот такие:
+----+-----------------+-----------------+-------------------+-------------------+----------------------+-------------------------------------------------+-------------------+-------------+
| id | squid_timestamp | squid_client_ip | squid_answer_type | squid_answer_size | squid_request_method | squid_request_url | squid_user_id | squid_host |
+----+-----------------+-----------------+-------------------+-------------------+----------------------+-------------------------------------------------+-------------------+-------------+
| 1 | 1336420800 | 127.1.11.12 | TCP_MISS/200 | 141531 | GET | http://www.rbc.ru/?/ | DOMAIN\im.user_one| www.rbc.ru |
| 2 | 1336420804 | 127.1.11.12 | TCP_IMS_HIT/304 | 534 | GET | http://pics.rbc.ru/img/fp_v4/skin/reset.css | DOMAIN\im.user_one| pics.rbc.ru |
| 3 | 1336420804 | 127.1.11.12 | TCP_IMS_HIT/304 | 534 | GET | http://pics.rbc.ru/img/fp_v4/skin/footer.css | DOMAIN\im.user_one| pics.rbc.ru |
| 4 | 1336420804 | 127.1.11.12 | TCP_IMS_HIT/304 | 534 | GET | http://pics.rbc.ru/img/fp_v4/skin/skin_34.css?2 | DOMAIN\im.user_one| pics.rbc.ru |
| 5 | 1336420804 | 127.1.11.12 | TCP_IMS_HIT/304 | 534 | GET | http://pics.rbc.ru/img/fp_v4/skin/header.css?5 | DOMAIN\im.user_one| pics.rbc.ru |
+----+-----------------+-----------------+-------------------+-------------------+----------------------+-------------------------------------------------+-------------------+-------------+
Ко всему этому безобразию прикручена морда на php которая формирует отчёты\статистику, следующими запросами:
(
 SELECT `squid_user_id`, SUM( `squid_answer_size` )
 FROM `users_hits_01_04_2012`
 WHERE `squid_timestamp` >= '1333276860' AND `squid_timestamp` <= '1336736760'
 GROUP BY `squid_user_id`
) 
UNION ALL 
(
 SELECT `squid_user_id`, SUM( `squid_answer_size` )
 FROM `users_hits_01_05_2012`
 WHERE `squid_timestamp` >= '1333276860' AND `squid_timestamp` <= '1336736760'
 GROUP BY `squid_user_id`
) 
UNION ALL
(
 SELECT `squid_user_id`, SUM( `squid_answer_size` )
 FROM `users_hits_02_04_2012`
 WHERE `squid_timestamp` >= '1333276860' AND `squid_timestamp` <= '1336736760'
 GROUP BY `squid_user_id`
);
запрос динамический в зависимости от необходимого кол-ва дней добавляются/убираются блоки вида:
UNION ALL
(
 SELECT `squid_user_id`, SUM( `squid_answer_size` )
 FROM `таблица`
 WHERE `squid_timestamp` >= 'левая граница диапазона' AND `squid_timestamp` <= 'правая граница диапазона'
 GROUP BY `squid_user_id`
);
Explain к этому запросу:
+----+--------------+-----------------------+------+-------------------------------------------+------+---------+------+---------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------+-----------------------+------+-------------------------------------------+------+---------+------+---------+----------------------------------------------+
| 1 | PRIMARY | users_hits_01_04_2012 | ALL | users_hits_01_04_2012_squid_timestamp_idx | NULL | NULL | NULL | 256206 | Using where; Using temporary; Using filesort |
| 2 | UNION | users_hits_01_05_2012 | ALL | users_hits_01_05_2012_squid_timestamp_idx | NULL | NULL | NULL | 81496 | Using where; Using temporary; Using filesort |
| 3 | UNION | users_hits_02_04_2012 | ALL | users_hits_02_04_2012_squid_timestamp_idx | NULL | NULL | NULL | 1879994 | Using where; Using temporary; Using filesort |
| NULL | UNION RESULT | <union1,2,3> | ALL | NULL | NULL | NULL | NULL | NULL | |
+----+--------------+-----------------------+------+-------------------------------------------+------+---------+------+---------+----------------------------------------------+
на выходе имеем нечто вроде:
+----------------+----------------------------+
| squid_user_id | SUM( `squid_answer_size` ) |
+----------------+----------------------------+
|DOMAIN\im.user_one| 75076 |
|DOMAIN\im.user_two| 633 |
|DOMAIN\im.user_one| 2136686 |
-- пропущено n строк
+----------------+----------------------------+
в последствии строки на выходе групируются уже в php по squid_user_id и считается сумма из SUM( `squid_answer_size` ).Т.е. нечто вроде:
| DOMAIN\im.user_one| 2211762 |
| DOMAIN\im.user_two| 633 |
(соотносясь с таблицей выше)за 3-5 дней статистика снимается приемлимо (т.е. время отработки запроса приблизительно 0.68 сек).Но за пару месяцев (~60 таблиц) время сбора инфы доходит до 4ех минут.
6 ответов

dusty.storm

олап. ну или для бедных хранить предрасчтитанные даные по периодам.


dusty.storm

олап.
Гуглил на предмет олап... Чушь какуюто выдает, есть мысль что вы имели в виду держать все в оперативке если так то укажите плз на матчасть - как это лучше сделать.
ну или для бедных хранить предрасчтитанные даные по периодам.
в моем случае не вариант, хотелось бы какнибудь поэлегантнее.Интересно мнение по поводу того правельно ли вообще так чесать таблицы или есть еще какой-либо вариант?релативные таблицы сюда возможно каким-либо боком сюда подвязать?P.s. Anyway спасибо за ответ :)


dusty.storm

ну сами то подумайте как еще можно ускорить group by ? это либо делается руками либо волшебными механизмами сервера. у mysql их нет.посмотрите на запросы SAMS. по-моему как раз ваша задач. только копирайт перемарать и можно 3 месяца выдавать за результат своей работы.


dusty.storm

ну сами то подумайте как еще можно ускорить group by ? это либо делается руками либо волшебными механизмами сервера. у mysql их нет.
Значит надо эмулировть колоночное хранилище из говна и палок подручных материалов :)Делал аналогичную задачу, но тока для логов nginx: саму по себе арифметику вы не ускорите и не распарелелите ни на одной пригодной к промышленному использованию и достуной бесплатно СУБД (по крайней мере полтора года назад было именно так)Посмотрите на систему в момент выполнения запросов - почти наверняка она упирается в i/o, ибо наверняка из большого объёма холодных (лежащих на диске) данных формируется таблица которая опять же записывается на жесткий диск.Рекомендую следующее:1) Уменьшаем все типы данных1.1) squid_client_ip - int 32bit, тип данных для этого в MySQL точно есть1.2) squid_answer_type - не понятно значение этого поля, но в любом - либо enum либо отдельный справочник, а в этой таблице - только ID1.3) squid_request_url - в отдельную таблицу, тут оставить только url id, более того рекомендую стрипать URL от параметров (всего, что идёт после ?), а еще лучше - оставить сайт, а конкретные разборки "куда именно на сайте" - уже доставть из текстового лока в случае крайней нужды1.4) squid_user_id и squid_host - в отдельные таблицы1.5) id и squid_timestamp - мне кажется, что от одного из них можно бы было избавится...1.6) Добавить поле для партиционирования и партиционировать по нему ( у меня для этого отдельное поле month в таблице) редкий случай когда партиционирование в MySQL оказывает какую-то помощьЦель всех этиъ упражнений - радикально уменьшить количество данных читамемых с жестого диска при выполнении запроса.2) На сервере монтируем tmfps приличного размер и tmpdir в конфиге направляем на этот рамдиск, это акутально даже если у вас вдруг есть батарейка в RAID-контроллере.Эти меры помогли мне перейти из состоянию "нажать и пойти курить" к состоянию "нажать и проверить почту" не выходя за пределы довольно дохлой виртуалки.Моя структура БД:
-- MySQL dump 10.13 Distrib 5.1.47, for debian-linux-gnu (x86_64)---- Host: localhost Database: log_parser2-- -------------------------------------------------------- Server version 5.1.47-1~bpo50+1/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;/*!40101 SET NAMES utf8 */;/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;/*!40103 SET TIME_ZONE='+00:00' */;/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;---- Table structure for table `domains`--DROP TABLE IF EXISTS `domains`;/*!40101 SET @saved_cs_client = @@character_set_client */;/*!40101 SET character_set_client = utf8 */;CREATE TABLE `domains` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `domain` char(255) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `domain_idx` (`domain`)) ENGINE=MyISAM AUTO_INCREMENT=15 DEFAULT CHARSET=latin1;/*!40101 SET character_set_client = @saved_cs_client */;---- Table structure for table `files`--DROP TABLE IF EXISTS `files`;/*!40101 SET @saved_cs_client = @@character_set_client */;/*!40101 SET character_set_client = utf8 */;CREATE TABLE `files` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `file` varchar(255) COLLATE utf8_bin DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `fileidx` (`file`)) ENGINE=MyISAM AUTO_INCREMENT=500061 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;/*!40101 SET character_set_client = @saved_cs_client */;---- Table structure for table `servers`--DROP TABLE IF EXISTS `servers`;/*!40101 SET @saved_cs_client = @@character_set_client */;/*!40101 SET character_set_client = utf8 */;CREATE TABLE `servers` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `server` char(32) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;/*!40101 SET character_set_client = @saved_cs_client */;---- Table structure for table `stats`--DROP TABLE IF EXISTS `stats`;/*!40101 SET @saved_cs_client = @@character_set_client */;/*!40101 SET character_set_client = utf8 */;CREATE TABLE `stats` ( `file_id` mediumint(8) unsigned NOT NULL, `server_id` *******(4) NOT NULL, `domain_id` *******(3) unsigned NOT NULL, `year` smallint(4) unsigned NOT NULL, `month` *******(1) unsigned NOT NULL, `day` *******(1) unsigned NOT NULL, `size` bigint(20) unsigned NOT NULL, `count` mediumint(32) unsigned NOT NULL DEFAULT '0', `filesize` int(32) unsigned DEFAULT NULL, PRIMARY KEY (`server_id`,`domain_id`,`file_id`,`day`,`month`,`year`), KEY `file_idx` (`file_id`), KEY `server_idx` (`server_id`), KEY `domain_idx` (`domain_id`)) ENGINE=MyISAM DEFAULT CHARSET=latin1/*!50100 PARTITION BY LIST (month)(PARTITION month1 VALUES IN (1) ENGINE = MyISAM, PARTITION month2 VALUES IN (2) ENGINE = MyISAM, PARTITION month3 VALUES IN (3) ENGINE = MyISAM, PARTITION month4 VALUES IN (4) ENGINE = MyISAM, PARTITION month5 VALUES IN (5) ENGINE = MyISAM, PARTITION month6 VALUES IN (6) ENGINE = MyISAM, PARTITION month7 VALUES IN (7) ENGINE = MyISAM, PARTITION month8 VALUES IN (8) ENGINE = MyISAM, PARTITION month9 VALUES IN (9) ENGINE = MyISAM, PARTITION month10 VALUES IN (10) ENGINE = MyISAM, PARTITION month11 VALUES IN (11) ENGINE = MyISAM, PARTITION month12 VALUES IN (12) ENGINE = MyISAM) */;/*!40101 SET character_set_client = @saved_cs_client */;/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;-- Dump completed on 2012-05-12 20:06:57
Вот типичный запрос, на разогретых данных работает 4 секунды, в таблице stats сейчас 66M записей, в files - 500k
mysql> explain partitions SELECT files.file,SUM(stats.size),SUM(stats.count),MAX(filesize) FROM files,stats,servers,domains WHERE domains.id=stats.domain_id AND servers.id=stats.server_id AND files.id=stats.file_id AND stats.month=5 AND stats.year=2012 AND stats.day=12 AND domains.domain="updates" GROUP BY file ORDER BY SUM(stats.size) DESC \G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: domains partitions: NULL type: constpossible_keys: PRIMARY,domain_idx key: domain_idx key_len: 256 ref: const rows: 1 Extra: Using temporary; Using filesort*************************** 2. row *************************** id: 1 select_type: SIMPLE table: stats partitions: month5 type: refpossible_keys: PRIMARY,file_idx,server_idx,domain_idx key: domain_idx key_len: 1 ref: const rows: 10 Extra: Using where*************************** 3. row *************************** id: 1 select_type: SIMPLE table: files partitions: NULL type: eq_refpossible_keys: PRIMARY key: PRIMARY key_len: 4 ref: log_parser2.stats.file_id rows: 1 Extra: Using where*************************** 4. row *************************** id: 1 select_type: SIMPLE table: servers partitions: NULL type: eq_refpossible_keys: PRIMARY key: PRIMARY key_len: 4 ref: log_parser2.stats.server_id rows: 1 Extra: Using where; Using index4 rows in set (0.00 sec)
Следует отметиться, что не сую все строки из лога в БД, а всё-таки агрегирую их в парсере логов, у меня задача - считать количество скачаного с нескольких веб-серверов в разрезе по доменам-файлам-времени.


dusty.storm

dusty.storm,По запросу:1. Заменить проверку диапазона дат с {<=,>=} на BETWEEN2. Поставить индекс {squid_user_id, squid_timestamp}По структуре:1. Нормализовать БД - выделить столбцы [ squid_client_ip | squid_answer_type | squid_request_url | squid_user_id | squid_host ] в отдельные таблицы.И самый главный совет (хотя будет ли он для вас приемлемым?) - Объеденить все базы вида {users_hits_(день)_(месяц)_(год)} в одну базу с секционированием


dusty.storm

Если таблицы users_hits_ xx_xx_xxxx типа MyISAM то можно создать таблицу Merged которая объединит все данные, и к ней обращаться с запросом как к одной, даже старые версии Mysql могут выгодно использовать индексы в таких объединенных таблицах