forum.lissyara.su

Часто, когда ты думаешь, что выбираешь между "сделать хорошо" и "сделать плохо", ты лишь влияешь на момент, когда это будет "сделано через жопу".
https://forum.lissyara.su/

MySQL: скорость выполнения запросов

https://forum.lissyara.su/viewtopic.php?f=52&t=9367

Страница 1 из 2

MySQL: скорость выполнения запросов

Добавлено: 2008-06-24 11:35:56
lodErunnEr
Считаю трафик. Для этого есть база (пока примерно 50 метров.. в дальнейшем будет сильно расти) 2е таблицы (в одной 2а столбца, в другой 10). На данный момент около 300 тысяч записей.
Дык вот... вот такой вот запрос:

Код: Выделить всё

SELECT accesslog.clientadr, ip.descript, sum(accesslog.bytes) AS traf 
FROM accesslog LEFT JOIN ip ON accesslog.clientadr = ip.adr 
GROUP BY accesslog.clientadr 
ORDER BY traf desc;
выполняется где-то 5 секунд.
При этом загрузка процессора не возрастает больше 20%.

Беспокоит довольно большое время выполнения запроса.
Нормально ли это?

Re: MySQL: скорость выполнения запросов

Добавлено: 2008-06-24 11:37:36
LMik
Индексы в таблице имеются? Правильно ли расставлены?

Re: MySQL: скорость выполнения запросов

Добавлено: 2008-06-24 11:46:18
lodErunnEr
угу. насчет правильно расставлены не знаю =)
делал так:

Код: Выделить всё

create table accesslog(
id int(4) not null auto_increment,
....
....
primary key(id)
);

Re: MySQL: скорость выполнения запросов

Добавлено: 2008-06-24 12:06:54
zg
EXPLAIN в студию

Re: MySQL: скорость выполнения запросов

Добавлено: 2008-06-24 12:40:47
lodErunnEr
zg писал(а):EXPLAIN в студию

Код: Выделить всё

mysql> explain select accesslog.clientadr, ip.descript, sum(accesslog.bytes) as traf from accesslog left join ip on accesslog.clientadr = ip.adr group by accesslog.clientadr order by traf desc;
+----+-------------+-----------+------+---------------+------+---------+------+--------+---------------------------------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows   | Extra                           |
+----+-------------+-----------+------+---------------+------+---------+------+--------+---------------------------------+
|  1 | SIMPLE      | accesslog | ALL  | NULL          | NULL | NULL    | NULL | 322432 | Using temporary; Using filesort |
|  1 | SIMPLE      | ip        | ALL  | NULL          | NULL | NULL    | NULL |      1 |                                 |
+----+-------------+-----------+------+---------------+------+---------+------+--------+---------------------------------+
2 rows in set (0.00 sec)

Re: MySQL: скорость выполнения запросов

Добавлено: 2008-06-24 12:47:08
zg
тебе надо добавить индекс на accesslog.clientadr и первичный ключ на ip.adr . У тебя щас индексов вообще нет, а связь один ко многим.

Re: MySQL: скорость выполнения запросов

Добавлено: 2008-06-24 12:54:18
lodErunnEr
zg писал(а):тебе надо добавить индекс на accesslog.clientadr и первичный ключ на ip.adr . У тебя щас индексов вообще нет, а связь один ко многим.
Спасибо огромное.. только я нислова не понял =) Хотя бы ткни что читать по теме..
Что значит "индекс на accesslog.clientadr и первичный ключ на ip.adr"?
Разве primary key(id) не достаточно?

Re: MySQL: скорость выполнения запросов

Добавлено: 2008-06-24 13:00:13
zg
lodErunnEr писал(а):Спасибо огромное.. только я нислова не понял =) Хотя бы ткни что читать по теме..
http://downloads.mysql.com/docs/refman-4.0-ru.html.zip начинаешь от тега <html> и заканчиваешь </html>
lodErunnEr писал(а):Что значит "индекс на accesslog.clientadr и первичный ключ на ip.adr"?
Разве primary key(id) не достаточно?
достаточно, осталось добавить индекс на accesslog.clientadr

Код: Выделить всё

ALTER TABLE `accesslog` ADD INDEX ( `clientadr` )

Re: MySQL: скорость выполнения запросов

Добавлено: 2008-06-24 13:06:13
zg
lodErunnEr писал(а):Считаю трафик

Код: Выделить всё

billing_traffic_ips   2,216,371   55.3 MB
и ничего не тормозит вся база в три раза больше, это самая большая таблица.

Код: Выделить всё

-- 
-- Структура таблицы `billing_traffic_ips`
-- 

CREATE TABLE billing_traffic_ips (
  udate date NOT NULL default '0000-00-00',
  ip_dest int(1) unsigned NOT NULL default '0',
  net_id tinyint(1) unsigned NOT NULL default '0',
  bytes bigint(1) unsigned NOT NULL default '0',
  KEY udate (udate)
) ENGINE=MyISAM DEFAULT CHARSET=koi8r COMMENT='Траффик по ip';
вот и все дела ))

Re: MySQL: скорость выполнения запросов

Добавлено: 2008-06-24 13:07:24
lodErunnEr
Сделал. Кажись добавил

Код: Выделить всё

 describe accesslog;
+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int(4)       | NO   | PRI | NULL    | auto_increment |
| time       | varchar(255) | NO   |     |         |                |
| duration   | varchar(255) | NO   |     |         |                |
| clientadr  | varchar(15)  | NO   | MUL |         |                |
| resultcode | varchar(255) | NO   |     |         |                |
| bytes      | int(4)       | YES  |     | NULL    |                |
| method     | varchar(255) | NO   |     |         |                |
| site       | varchar(255) | NO   |     |         |                |
| url        | text         | YES  |     | NULL    |                |
| username   | varchar(255) | YES  |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+
- скорость не изменилась =(

Код: Выделить всё

35 rows in set (4.59 sec)

Код: Выделить всё

explain select accesslog.clientadr, ip.descript, sum(accesslog.bytes) as traf from accesslog left join ip on accesslog.clientadr = ip.adr group by accesslog.clientadr order by traf desc;
+----+-------------+-----------+------+---------------+------+---------+------+--------+---------------------------------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows   | Extra                           |
+----+-------------+-----------+------+---------------+------+---------+------+--------+---------------------------------+
|  1 | SIMPLE      | accesslog | ALL  | NULL          | NULL | NULL    | NULL | 322432 | Using temporary; Using filesort |
|  1 | SIMPLE      | ip        | ALL  | NULL          | NULL | NULL    | NULL |      1 |                                 |
+----+-------------+-----------+------+---------------+------+---------+------+--------+---------------------------------+

Re: MySQL: скорость выполнения запросов

Добавлено: 2008-06-24 14:45:26
zg
lodErunnEr писал(а):Сделал. Кажись добавил
не, надо так

Код: Выделить всё

show create table `accesslog`
lodErunnEr писал(а):скорость не изменилась =(
вот когда possible_keys и key будут заполнены, тогда изменится, кстати приведи пару строчек из таблицы accesslog

Re: MySQL: скорость выполнения запросов

Добавлено: 2008-06-24 15:00:53
dikens3
http://phpclub.ru/detail/article/mysql_optimize

Re: MySQL: скорость выполнения запросов

Добавлено: 2008-06-24 15:10:51
lodErunnEr
zg писал(а):скорость не изменилась =(
вот когда possible_keys и key будут заполнены, тогда изменится, кстати приведи пару строчек из таблицы accesslog[/quote]

Код: Выделить всё

mysql> select * from accesslog limit 2;
+--------+----------------+----------+----------------+--------------+-------+--------+--------------+--------------------------------+----------+
| id     | time           | duration | clientadr      | resultcode   | bytes | method | site         | url                            | username |
+--------+----------------+----------+----------------+--------------+-------+--------+--------------+--------------------------------+----------+
| 934658 | 1213715357.902 | 817      | 192.168.242.34 | TCP_MISS/200 | 47440 | GET    | cars.avto.ru | http://cars.avto.ru/search/?   | -        |
| 934659 | 1213715358.064 | 339      | 192.168.242.34 | TCP_MISS/200 | 15222 | GET    | bs.yandex.ru | http://bs.yandex.ru/code/1157? | -        |
+--------+----------------+----------+----------------+--------------+-------+--------+--------------+--------------------------------+----------+
2 rows in set (0.02 sec)

Re: MySQL: скорость выполнения запросов

Добавлено: 2008-06-24 15:43:34
zg
в своей практике столкнулся с тем, что пихать логи в базу нет никакого смысла, обрабатывать их проще на С и awk, при этом скорость в разы больше, а в базу пихать только отчётную статистику, которая занимает копейки

Re: MySQL: скорость выполнения запросов

Добавлено: 2008-06-24 15:54:38
lodErunnEr
zg писал(а):в своей практике столкнулся с тем, что пихать логи в базу нет никакого смысла, обрабатывать их проще на С и awk, при этом скорость в разы больше, а в базу пихать только отчётную статистику, которая занимает копейки
эхх... т.е. ничем мне не помочь ?

Re: MySQL: скорость выполнения запросов

Добавлено: 2008-06-24 16:00:19
zg
lodErunnEr писал(а):
zg писал(а):в своей практике столкнулся с тем, что пихать логи в базу нет никакого смысла, обрабатывать их проще на С и awk, при этом скорость в разы больше, а в базу пихать только отчётную статистику, которая занимает копейки
эхх... т.е. ничем мне не помочь ?
я тебе для чего ссылку кидал?
zg писал(а):http://downloads.mysql.com/docs/refman-4.0-ru.html.zip начинаешь от тега <html> и заканчиваешь </html>
и выложи show create table

Re: MySQL: скорость выполнения запросов

Добавлено: 2008-06-24 16:19:51
lodErunnEr

Код: Выделить всё

 accesslog | CREATE TABLE `accesslog` (
  `id` int(4) NOT NULL auto_increment,
  `time` varchar(255) default NULL,
  `duration` varchar(255) default NULL,
  `clientadr` varchar(15) default NULL,
  `resultcode` varchar(255) default NULL,
  `bytes` int(4) default NULL,
  `method` varchar(255) default NULL,
  `site` varchar(255) default NULL,
  `url` text,
  `username` varchar(255) default NULL,
  PRIMARY KEY  (`id`),
  KEY `clientadr` (`clientadr`)
) ENGINE=MyISAM AUTO_INCREMENT=345928 DEFAULT CHARSET=latin1

Re: MySQL: скорость выполнения запросов

Добавлено: 2008-06-24 17:42:25
zg

Код: Выделить всё

explain select 
	accesslog.clientadr, 
	sum(accesslog.bytes) as traf 
from 
	accesslog 
group by 
	accesslog.clientadr 
order by 
	traf desc;
я думаю, результат будет тот же... индексы не используются, если количсетво записей в них больше определённого процента, поскольку будет быстрее прочесать всю таблицу, это уже пройдено. Тебе нужно скоратить количество записей в индексе, нужно проиндексировать только часть ip-адреса

Код: Выделить всё

ALTER TABLE `accesslog` DROP INDEX `clientadr` ,
ADD INDEX `clientadr` ( `clientadr` ( 4 ) )
это позволит сократить количество записей в индексе, после этого выложи предыдущий запрос

Re: MySQL: скорость выполнения запросов

Добавлено: 2008-06-24 19:32:36
lodErunnEr

Код: Выделить всё

mysql> explain select accesslog.clientadr, ip.descript, sum(accesslog.bytes) as traf from accesslog left join ip on accesslog.clientadr = ip.adr group by accesslog.clientadr order by traf desc;

+----+-------------+-----------+------+---------------+------+---------+------+--------+---------------------------------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows   | Extra                           |
+----+-------------+-----------+------+---------------+------+---------+------+--------+---------------------------------+
|  1 | SIMPLE      | accesslog | ALL  | NULL          | NULL | NULL    | NULL | 357518 | Using temporary; Using filesort |
|  1 | SIMPLE      | ip        | ALL  | NULL          | NULL | NULL    | NULL |      1 |                                 |
+----+-------------+-----------+------+---------------+------+---------+------+--------+---------------------------------+

Код: Выделить всё

mysql> show create table accesslog;
.....
accesslog | CREATE TABLE `accesslog` (
  `id` int(4) NOT NULL auto_increment,
  `time` varchar(255) default NULL,
  `duration` varchar(255) default NULL,
  `clientadr` varchar(15) default NULL,
  `resultcode` varchar(255) default NULL,
  `bytes` int(4) default NULL,
  `method` varchar(255) default NULL,
  `site` varchar(255) default NULL,
  `url` text,
  `username` varchar(255) default NULL,
  PRIMARY KEY  (`id`),
  KEY `clientadr` (`clientadr`(4))
) ENGINE=MyISAM AUTO_INCREMENT=357519 DEFAULT CHARSET=latin1 |

Код: Выделить всё

mysql> select accesslog.clientadr, ip.descript, sum(accesslog.bytes) as traf from accesslog left join ip on accesslog.clientadr = ip.adr group by accesslog.clientadr order by traf desc;

+-----------------+----------+-----------+
| clientadr       | descript | traf      |
+-----------------+----------+-----------+
| 192.168.242.197 | NULL     | 468522648 |
| 192.168.242.13  | NULL     | 253419872 |
| 192.168.242.53  | NULL     | 220497743 |
| 192.168.242.34  | NULL     | 215650269 |
| 192.168.242.64  | NULL     | 210015783 |
| 192.168.242.73  | NULL     | 205025671 |
| 192.168.242.16  | NULL     | 188901301 |
| 192.168.242.55  | NULL     | 177384665 |
| 192.168.242.35  | NULL     | 148118629 |
| 192.168.242.71  | NULL     | 130003238 |
| 192.168.242.43  | NULL     | 122762579 |
| 192.168.242.208 | NULL     |  94173834 |
| 192.168.242.105 | NULL     |  90276998 |
| 192.168.242.82  | NULL     |  58765293 |
| 192.168.242.135 | NULL     |  57748097 |
| 192.168.242.157 | NULL     |  53682158 |
| 192.168.242.141 | user01   |  50552397 |
| 192.168.242.75  | NULL     |  47247918 |
| 192.168.242.76  | NULL     |  35265138 |
| 192.168.242.93  | NULL     |  30843712 |
| 192.168.242.21  | NULL     |  24335505 |
| 192.168.242.41  | NULL     |  23471645 |
| 192.168.242.88  | NULL     |  23201228 |
| 192.168.242.32  | NULL     |  22929654 |
| 192.168.242.65  | NULL     |  16545952 |
| 192.168.242.46  | NULL     |  14672151 |
| 192.168.242.18  | NULL     |  11877211 |
| 192.168.242.19  | NULL     |   9843094 |
| 192.168.242.211 | NULL     |   8973557 |
| 192.168.242.30  | NULL     |   4379815 |
| 192.168.242.200 | NULL     |   1952373 |
| 192.168.242.79  | NULL     |   1049814 |
| 192.168.242.58  | NULL     |    475212 |
| 192.168.242.201 | NULL     |    339680 |
| 192.168.242.66  | NULL     |     37465 |
+-----------------+----------+-----------+
35 rows in set (5.05 sec)
=(
эх.. пошел я читать документацию ..

Re: MySQL: скорость выполнения запросов

Добавлено: 2008-06-24 19:56:22
dikens3

Код: Выделить всё

order by traf
На это поле попробуй index добавить. Вообще поэкперементируй, не всегда всё работает так как хочешь.

Re: MySQL: скорость выполнения запросов

Добавлено: 2008-06-24 21:11:57
zg
dikens3 писал(а):

Код: Выделить всё

order by traf
На это поле попробуй index добавить. Вообще поэкперементируй, не всегда всё работает так как хочешь.
это поле sum(), на него индекс нельзя добавить, только если сделать временную таблицу

Re: MySQL: скорость выполнения запросов

Добавлено: 2008-06-24 22:01:13
dikens3
Млять, я парю. Невнимательность.

Re: MySQL: скорость выполнения запросов

Добавлено: 2008-06-25 6:01:34
zg
lodErunnEr писал(а):=(
как вариант можешь либо базу выложить, либо доступ открыть, либо сделать копию базы и на неё дать доступ, я гляну чё и как, потом отпишусь.

Re: MySQL: скорость выполнения запросов

Добавлено: 2008-06-25 8:34:15
lodErunnEr
zg писал(а):
lodErunnEr писал(а):=(
как вариант можешь либо базу выложить, либо доступ открыть, либо сделать копию базы и на неё дать доступ, я гляну чё и как, потом отпишусь.
отправил ЛС.

Re: MySQL: скорость выполнения запросов

Добавлено: 2008-06-25 9:35:16
zg
lodErunnEr писал(а):отправил ЛС.

Код: Выделить всё

SQL-запрос #1: 
--------------
CREATE TABLE `log2` (
`udate` DATE NOT NULL ,
`ip` INT( 1 ) UNSIGNED NOT NULL ,
`bytes` BIGINT( 1 ) UNSIGNED NOT NULL ,
PRIMARY KEY ( `udate` , `ip` )
) TYPE = MYISAM CHARACTER SET cp1251 COLLATE cp1251_general_ci COMMENT = 'Суммарный трафик пользователей';


SQL-запрос #2: 
--------------
INSERT INTO `log2`
SELECT from_unixtime( `time` , '%Y-%m-%d' ) `udate` , inet_aton( `clientadr` ) ip, sum( `bytes` ) `bytes`
FROM accesslog
GROUP BY `udate` , `ip`


Отчётный запрос по сотрудникам 
(Показывает записи 0 - 29 (146 всего, Запрос занял 0.0941 сек))
--------------
SELECT 
  lg.udate,
  INET_NTOA(lg.ip) ip,
  lg.bytes,
  `ip`.descript

FROM 
  `log2` lg 
      LEFT JOIN `ip`
        ON
      lg.`ip` = INET_ATON(`ip`.`adr`)

WHERE
  `udate` BETWEEN "2008-06-01" AND "2008-06-31"
---------------
 udate   	  ip   	  bytes   	  descript
2008-06-17 	192.168.242.34 	7794283 	NULL
2008-06-17 	192.168.242.200 	1931790 	NULL
2008-06-17 	192.168.242.211 	672064 	NULL
2008-06-18 	192.168.242.13 	64731682 	NULL
2008-06-18 	192.168.242.16 	42679375 	NULL
2008-06-18 	192.168.242.18 	2375509 	NULL
2008-06-18 	192.168.242.19 	2166456 	NULL
2008-06-18 	192.168.242.21 	14700619 	NULL
2008-06-18 	192.168.242.30 	159556 	NULL
2008-06-18 	192.168.242.34 	97672539 	NULL
2008-06-18 	192.168.242.35 	33631446 	test user 3
2008-06-18 	192.168.242.41 	22958417 	NULL
2008-06-18 	192.168.242.43 	21321293 	NULL
2008-06-18 	192.168.242.53 	26118536 	NULL
2008-06-18 	192.168.242.55 	57986412 	NULL
2008-06-18 	192.168.242.58 	471890 	NULL
2008-06-18 	192.168.242.64 	43282787 	NULL
2008-06-18 	192.168.242.65 	2967533 	NULL
2008-06-18 	192.168.242.71 	300448 	NULL
2008-06-18 	192.168.242.73 	112823136 	test user 2
.....


Итого

Код: Выделить всё

Было:
-------------
Данные 	46,936 	KB
Индекс 	7,877 	KB
Всего 	54,813 	KB

Формат  	 динамический
Сравнение 	latin1_swedish_ci
Ряды 	357,518
Длина ряда ? 	134
Размер ряда  ? 	157 Bytes

Стало:
-------------
Данные  	2,336  	Bytes
Индекс 	4,096 	Bytes
Всего 	6,432 	Bytes

Формат  	 фиксированный
Сравнение 	cp1251_general_ci
Ряды 	146
Длина ряда ? 	16
Всю остальную информацию нужно брать из логов, а в базе хранить только отчётную информацию.

У меня в статистике по логам бегает греп или авк, отчёты берутся из базы, в итоге скорость достпуа к любой информации составляет доли секунды.
Часовой пояс: UTC+03:00
Страница 1 из 2
Powered by phpBB® Forum Software © phpBB Limited
https://www.phpbb.com/