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

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%.

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

Хостинг HostFood.ru · **Хостинг HostFood.ru**

Тарифы на хостинг в России, от 12 рублей: https://www.host-food.ru/tariffs/hosting/
Тарифы на виртуальные сервера (VPS/VDS/KVM) в РФ, от 189 руб.: https://www.host-food.ru/tariffs/virtualny-server-vps/
Выделенные сервера, Россия, Москва, от 2000 рублей (HP Proliant G5, Intel Xeon E5430 (2.66GHz, Quad-Core, 12Mb), 8Gb RAM, 2x300Gb SAS HDD, P400i, 512Mb, BBU):
https://www.host-food.ru/tariffs/vydelennyi-server-ds/
Недорогие домены в популярных зонах: https://www.host-food.ru/domains/

LMik · Непрочитанное сообщение **LMik** » 2008-06-24 11:37:36

Индексы в таблице имеются? Правильно ли расставлены?

lodErunnEr

угу. насчет правильно расставлены не знаю

делал так:

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

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

zg · Непрочитанное сообщение zg » 2008-06-24 12:06:54

EXPLAIN в студию

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)

zg · Непрочитанное сообщение zg » 2008-06-24 12:47:08

тебе надо добавить индекс на accesslog.clientadr и первичный ключ на ip.adr . У тебя щас индексов вообще нет, а связь один ко многим.

lodErunnEr

zg писал(а):тебе надо добавить индекс на accesslog.clientadr и первичный ключ на ip.adr . У тебя щас индексов вообще нет, а связь один ко многим.

Спасибо огромное.. только я нислова не понял

Хотя бы ткни что читать по теме..
Что значит "индекс на accesslog.clientadr и первичный ключ на ip.adr"?
Разве primary key(id) не достаточно?

zg · Непрочитанное сообщение zg » 2008-06-24 13:00:13

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` )

zg · Непрочитанное сообщение zg » 2008-06-24 13:06:13

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';

вот и все дела ))

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 |                                 |
+----+-------------+-----------+------+---------------+------+---------+------+--------+---------------------------------+

zg · Непрочитанное сообщение zg » 2008-06-24 14:45:26

lodErunnEr писал(а):Сделал. Кажись добавил

не, надо так

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

show create table `accesslog`

lodErunnEr писал(а):скорость не изменилась =(

вот когда possible_keys и key будут заполнены, тогда изменится, кстати приведи пару строчек из таблицы accesslog

dikens3 · Непрочитанное сообщение **dikens3** » 2008-06-24 15:00:53

http://phpclub.ru/detail/article/mysql_optimize

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)

zg · Непрочитанное сообщение zg » 2008-06-24 15:43:34

в своей практике столкнулся с тем, что пихать логи в базу нет никакого смысла, обрабатывать их проще на С и awk, при этом скорость в разы больше, а в базу пихать только отчётную статистику, которая занимает копейки

lodErunnEr

zg писал(а):в своей практике столкнулся с тем, что пихать логи в базу нет никакого смысла, обрабатывать их проще на С и awk, при этом скорость в разы больше, а в базу пихать только отчётную статистику, которая занимает копейки

эхх... т.е. ничем мне не помочь ?

zg · Непрочитанное сообщение zg » 2008-06-24 16:00:19

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

я тебе для чего ссылку кидал?

zg писал(а):http://downloads.mysql.com/docs/refman-4.0-ru.html.zip начинаешь от тега <html> и заканчиваешь </html>

и выложи show create table

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

zg · Непрочитанное сообщение zg » 2008-06-24 17:42:25

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

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 ) )

это позволит сократить количество записей в индексе, после этого выложи предыдущий запрос

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)

=(
эх.. пошел я читать документацию ..

dikens3 · Непрочитанное сообщение **dikens3** » 2008-06-24 19:56:22

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

order by traf

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

zg · Непрочитанное сообщение zg » 2008-06-24 21:11:57

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

это поле sum(), на него индекс нельзя добавить, только если сделать временную таблицу

dikens3 · Непрочитанное сообщение **dikens3** » 2008-06-24 22:01:13

Млять, я парю. Невнимательность.

zg · Непрочитанное сообщение zg » 2008-06-25 6:01:34

lodErunnEr писал(а):=(

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

lodErunnEr

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

отправил ЛС.

zg · Непрочитанное сообщение zg » 2008-06-25 9:35:16

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

Всю остальную информацию нужно брать из логов, а в базе хранить только отчётную информацию.

У меня в статистике по логам бегает греп или авк, отчёты берутся из базы, в итоге скорость достпуа к любой информации составляет доли секунды.

forum.lissyara.su

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

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

Услуги хостинговой компании Host-Food.ru

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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