Биллинговая система Nodeny

Главная категория => Курилка => Тема начата: goletsa от 14 Октября 2009, 13:58:03


Название: Долго выполняются SQL запросы.
Отправлено: goletsa от 14 Октября 2009, 13:58:03
После обновления на новую версию столкнулся с ОЧЕНЬ медленым выполнением запросов к базе.

Запрос типа 
Код:
SELECT * FROM dopdata WHERE revision IN (SELECT rev FROM rev_users WHERE id=615)
выполняется 21 (!!!) секунду.

При этом если выполнять по отдельности то < 1c
Код:
Отображает строки 0 - 1 (2 всего, запрос занял 0.2474 сек.)
SELECT rev
FROM rev_users
WHERE id =615
LIMIT 0 , 30

Код:
Отображает строки 0 - 7 (8 всего, запрос занял 0.0001 сек.)
SELECT * 
FROM dopdata
WHERE revision
IN ( 1309, 15452 )

Код:
Отображает строки 0 - 7 (8 всего, запрос занял 21.9042 сек.)
SELECT * 
FROM dopdata
WHERE revision
IN (

SELECT rev
FROM rev_users
WHERE id =615
)


Название: Re: Долго выполняются SQL запросы.
Отправлено: goletsa от 14 Октября 2009, 14:52:14
Дали  мну тут как решение измененный SQL запрос без вложенных SELECT'ов.
Код:
select * from dopdata as d join rev_users as r on r.id=615 and d.revision=r.rev;
Работает пошустрее.

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

Код:
Отображает строки 0 - 7 (8 всего, запрос занял 0.2491 сек.)
SELECT * 
FROM dopdata AS d
JOIN rev_users AS r ON r.id =615
AND d.revision = r.rev

Название: Re: Долго выполняются SQL запросы.
Отправлено: goletsa от 14 Октября 2009, 16:43:24
Немного пропатчил код функций на предмет измения запросов.
Так хоть база не ложится.

listuser.pl.diff
Код:
d1102 1
a1102 3
#            sql                => "SELECT * FROM dopdata WHERE revision IN (SELECT rev FROM rev_users WHERE id=$id) AND $and ORDER BY template_num,field_name",
            sql         => "SELECT * FROM dopdata as d JOIN rev_users as r on r.id=$id AND $and ORDER BY template_num,field_name",
# select * from dopdata as d join rev_users as r on r.id=615 and d.revision=r.rev and template_num=2

SMain.pl.diff
Код:
d162 1
a162 2
#  $sth=&sql($dbh,"SELECT * FROM dopdata WHERE revision IN (SELECT rev FROM rev_users WHERE id=$_[0])");
   $sth=&sql($dbh,"SELECT * FROM dopdata as d join rev_users as r on r.id=$_[0] and d.revision=r.rev");

Efendy, выскажи свое слово?

Название: Re: Долго выполняются SQL запросы.
Отправлено: Efendy от 14 Октября 2009, 20:50:47
Цитата: goletsa от 14 Октября 2009, 14:52:14
Дали  мну тут как решение измененный SQL запрос без вложенных SELECT'ов.
Код:
select * from dopdata as d join rev_users as r on r.id=615 and d.revision=r.rev;
Работает пошустрее.
Да, мускул странно обрабатывает запрос. На самом деле там для него ничего не должно быть сложного. Есть проиндексированное поле revision, нужно найти уникальные значения этого поля, а потом сгруппировать по parent_id и получить максимальные значение revision (это вьюха rev_users). Почему когда используешь ее в качестве подзапроса, то мускул начинает сходить с ума и делает нереально избыточные операции - я так и не до конца понял. Насчет join у меня тоже была мысль. Надо будет потестировать.

Походу, мне versus присылал top твоей системы. Мускул откушивает около 0,5 Гб - уж очень похоже на ограничение на размер процесса в 512Мб.

Название: Re: Долго выполняются SQL запросы.
Отправлено: goletsa от 14 Октября 2009, 21:55:55
Я как и рекомендовалось в мануале увеличивал размер до 2G в loader.conf (Стоит всего 4гига).
Или еще гдето надо менять? В принципе можно и мускул на досуге пересобрать...

Название: Re: Долго выполняются SQL запросы.
Отправлено: Efendy от 15 Октября 2009, 07:32:54
Цитата: goletsa от 14 Октября 2009, 21:55:55
Я как и рекомендовалось в мануале увеличивал размер до 2G в loader.conf (Стоит всего 4гига).
Или еще гдето надо менять? В принципе можно и мускул на досуге пересобрать...

Мускул не надо пересобирать.

1) Приметь по top бывает ли объем процесса mysqld > 512 мб
2) в /etc/my.cnf чему равно key_buffer?
3) это не есть решение проблемы, а обход:

заменить SELECT lalala  на SELECT SQL_BUFFER_RESULT lalala для задумчивых запросов

тогда не будут лочиться таблицы

4) покажи в консоли mysql результат:
Код:
use bill;
explain SELECT * FROM dopdata WHERE revision IN (SELECT rev FROM rev_users WHERE id=615)
explain select * from dopdata as d join rev_users as r on r.id=615 and d.revision=r.rev;


Название: Re: Долго выполняются SQL запросы.
Отправлено: goletsa от 15 Октября 2009, 16:33:13
1)
Код:
last pid: 10893;  load averages:  0.08,  0.13,  0.15   up 29+10:05:58  18:29:09
86 processes:  1 running, 85 sleeping
CPU:  0.9% user,  0.0% nice,  0.4% system,  0.0% interrupt, 98.7% idle
Mem: 2612M Active, 497M Inact, 219M Wired, 121M Cache, 112M Buf, 55M Free
Swap: 4096M Total, 40K Used, 4096M Free

  PID USERNAME   THR PRI NICE   SIZE    RES STATE  C   TIME   WCPU COMMAND
58104 root         1   8  -15   340M   333M nanslp 1 117:24  4.98% perl5.8.9
57988 mysql       27  44    0  2127M  2056M ucond  1   4:52  0.59% mysqld

2)
Код:
 cat /etc/my.cnf | grep key_buffer
key_buffer = 2000M
key_buffer = 256M
key_buffer = 256M

3)
Код:
mysql> explain SELECT * FROM dopdata WHERE revision IN (SELECT rev FROM rev_users WHERE id=615)
    -> ;
+----+--------------------+------------+-----------------+---------------------+---------+---------+--------------------+-------+----------------------------------------------+
| id | select_type        | table      | type            | possible_keys       | key     | key_len | ref                | rows  | Extra                                        |
+----+--------------------+------------+-----------------+---------------------+---------+---------+--------------------+-------+----------------------------------------------+
|  1 | PRIMARY            | v          | ALL             | NULL                | NULL    | NULL    | NULL               | 32161 | Using where                                  |
|  1 | PRIMARY            | f          | eq_ref          | PRIMARY             | PRIMARY | 2       | bill.v.dopfield_id |     1 |                                              |
|  2 | DEPENDENT SUBQUERY | <derived4> | ALL             | NULL                | NULL    | NULL    | NULL               |  7842 | Using where                                  |
|  4 | DERIVED            | v          | ALL             | NULL                | NULL    | NULL    | NULL               | 32161 | Using where; Using temporary; Using filesort |
|  4 | DERIVED            | f          | eq_ref          | PRIMARY,parent_type | PRIMARY | 2       | bill.v.dopfield_id |     1 | Using where                                  |
|  5 | DEPENDENT SUBQUERY | users      | unique_subquery | PRIMARY             | PRIMARY | 4       | func               |     1 | Using index; Using where                     |
+----+--------------------+------------+-----------------+---------------------+---------+---------+--------------------+-------+----------------------------------------------+
6 rows in set (0.25 sec)

mysql> explain select * from dopdata as d join rev_users as r on r.id=615 and d.revision=r.rev;
+----+--------------------+------------+-----------------+-----------------------+----------+---------+--------------------+-------+----------------------------------------------+
| id | select_type        | table      | type            | possible_keys         | key      | key_len | ref                | rows  | Extra                                        |
+----+--------------------+------------+-----------------+-----------------------+----------+---------+--------------------+-------+----------------------------------------------+
|  1 | PRIMARY            | <derived3> | ALL             | NULL                  | NULL     | NULL    | NULL               |  7842 | Using where                                  |
|  1 | PRIMARY            | v          | ref             | revision,idx_revision | revision | 4       | r.rev              |     4 | Using where                                  |
|  1 | PRIMARY            | f          | eq_ref          | PRIMARY               | PRIMARY  | 2       | bill.v.dopfield_id |     1 |                                              |
|  3 | DERIVED            | v          | ALL             | NULL                  | NULL     | NULL    | NULL               | 32161 | Using where; Using temporary; Using filesort |
|  3 | DERIVED            | f          | eq_ref          | PRIMARY,parent_type   | PRIMARY  | 2       | bill.v.dopfield_id |     1 | Using where                                  |
|  4 | DEPENDENT SUBQUERY | users      | unique_subquery | PRIMARY               | PRIMARY  | 4       | func               |     1 | Using index; Using where                     |
+----+--------------------+------------+-----------------+-----------------------+----------+---------+--------------------+-------+----------------------------------------------+
6 rows in set (0.25 sec)

Название: Re: Долго выполняются SQL запросы.
Отправлено: Efendy от 15 Октября 2009, 18:48:45
с join-ами гороаздо эффективней план получается. Специфика мускула. Тогда поменяю в биллинге на join

Название: Re: Долго выполняются SQL запросы.
Отправлено: Efendy от 15 Октября 2009, 22:20:58
В общем твой запрос неправильный, но зато ты натолкнул на мысль:

Код:
"SELECT d.* FROM dopdata AS d INNER JOIN rev_users AS r ON r.rev=d.revision WHERE r.id=$id AND $and ORDER BY d.template_num,d.field_name"

и перед этим

Код:
$and=$f{q}? "template_num=$Ftmpl" : "field_flags LIKE '%q%'";

заменить на

Код:
$and=$f{q}? "d.template_num=$Ftmpl" : "d.field_flags LIKE '%q%'";

Название: Re: Долго выполняются SQL запросы.
Отправлено: Efendy от 16 Октября 2009, 07:56:22
кстати, попробуй еще изменить вьюху rev_users

Код:
DROP VIEW IF EXISTS rev_users;

CREATE ALGORITHM=TEMPTABLE VIEW rev_users AS
  SELECT parent_id AS id, template_num, MAX(revision) AS rev FROM dopdata d
  INNER JOIN users u on d.parent_id=u.id
  WHERE parent_type=0 GROUP BY parent_id,template_num;

это вообще должно сказаться на глобальном повышении производительности. Выяснил почему по-иному записанные запросы выполняются долго - мускул крайне неоптимально выполняет  IN (SELECT ...)

Название: Re: Долго выполняются SQL запросы.
Отправлено: goletsa от 16 Октября 2009, 08:01:56
Цитата: Efendy от 15 Октября 2009, 22:20:58
В общем твой запрос неправильный, но зато ты натолкнул на мысль:

Код:
"SELECT d.* FROM dopdata AS d INNER JOIN rev_users AS r ON r.rev=d.revision WHERE r.id=$id AND $and ORDER BY d.template_num,d.field_name"

и перед этим

Код:
$and=$f{q}? "template_num=$Ftmpl" : "field_flags LIKE '%q%'";

заменить на

Код:
$and=$f{q}? "d.template_num=$Ftmpl" : "d.field_flags LIKE '%q%'";
Это в каком месте?
В listuser.pl ?
Кстати а как изменить тот запрос чтобы кнопки с домами появлялись? Там тоже вроде есть IN (SELECT...

Цитировать
кстати, попробуй еще изменить вьюху rev_users
ок, попробую сегодня

Название: Re: Долго выполняются SQL запросы.
Отправлено: goletsa от 16 Октября 2009, 08:06:17
И еще вопрос.
Мне тут еще советовали сделать индекс для поля revision в исходной таблице (dopfileds чтоли...).
Это повышает производительность или смысла нету?

Название: Re: Долго выполняются SQL запросы.
Отправлено: Efendy от 16 Октября 2009, 08:10:58
Цитата: goletsa от 16 Октября 2009, 08:06:17
И еще вопрос.
Мне тут еще советовали сделать индекс для поля revision в исходной таблице (dopfileds чтоли...).
Это повышает производительность или смысла нету?

там итак стоит индекс.

в выборке уникальных домов на улице там так просто не получится, но я уже придумал и оттестировал одну вещь. суть ее вот в чем:

Код:
CREATE TABLE `test` (
`id` INT NOT NULL ,
`name` TEXT NOT NULL ,
`value` TEXT NOT NULL
) ENGINE = MYISAM ;

INSERT INTO test (`id`,`name`,`value`) VALUES ('1','улица','10'), ('1','дом','11');
INSERT INTO test (`id`,`name`,`value`) VALUES ('2','улица','20'), ('2','дом','21');
SELECT t1.value AS street,t2.value AS house FROM test t1 INNER JOIN test t2 ON t1.id=t2.id WHERE t1.name='улица' AND t2.name='дом';

street     house
10     11
20     21

Название: Re: Долго выполняются SQL запросы.
Отправлено: dad Min от 23 Октября 2009, 17:39:39
при открытии "бланка настроек" и "клиентской статистике" запрос отрабатывался очень долго
пришлось сменить вложеные селекты
выходит что мускул очень сильно затыкается из-за того что обрабатывается на каждую строку внешней выборки достаточно большое количество переборов строк внутренней.
в "клиентской статистике"
 
Код:
$sth=&sql($dbh,"SELECT * FROM dopdata WHERE revision IN (SELECT rev FROM rev_users WHERE id=$_[0])");
замена
 
Код:
$sth=&sql($dbh,"SELECT * FROM dopdata LEFT JOIN rev_users ON revision=rev WHERE rev_users.id=$_[0]");

В "бланке настроек"
Код:
$sth=&sql($dbh,"SELECT * FROM dopdata WHERE revision IN ".
   "(SELECT rev FROM rev_users WHERE parent_id=$Fid AND template_num=(SELECT template_num FROM dopfields WHERE parent_type=0 AND field_alias LIKE '_adr%' LIMIT 1)) ORDER BY field_name");
                                                             
замена
 
Код:
$sth=&sql($dbh,"SELECT dopdata.* FROM dopdata LEFT JOIN  rev_users ON revision=rev WHERE parent_id=$Fid AND rev_users.template_num= ".                                                                                                                   
   "(SELECT template_num FROM dopfields WHERE parent_type=0 AND field_alias LIKE '_adr%' LIMIT 1) ORDER BY field_name");     

увеличение производительности видно невооруженным глазом
в консоле тестились запросы 6сек. первый 0.19 измененный

Название: Re: Долго выполняются SQL запросы.
Отправлено: Efendy от 23 Октября 2009, 20:09:58
Какая версия биллинга?  Ибо

Код:
WHERE revision IN (SELECT rev
уже не используется. Например в Smain.pl:

Код:
SELECT * FROM dopdata WHERE revision=
      ( SELECT MAX(revision) FROM dopdata WHERE parent_id=$_[0] AND template_num=(SELECT template_num FROM dopfields WHERE field_alias LIKE '_adr_%' LIMIT 1) )


Powered by SMF 1.1.20 | SMF © 2006-2009, Simple Machines