Долго выполняются SQL запросы.

[goletsa]

После обновления на новую версию столкнулся с ОЧЕНЬ медленым выполнением запросов к базе.

Запрос типа 

Код:

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
)

[goletsa]

Дали  мну тут как решение измененный 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

[goletsa]

Немного пропатчил код функций на предмет измения запросов.
Так хоть база не ложится.

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, выскажи свое слово?

[Efendy]

Дали  мну тут как решение измененный 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Мб.

[goletsa]

Я как и рекомендовалось в мануале увеличивал размер до 2G в loader.conf (Стоит всего 4гига).
Или еще гдето надо менять? В принципе можно и мускул на досуге пересобрать...

[Efendy]

Я как и рекомендовалось в мануале увеличивал размер до 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;

[goletsa]

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)

[Efendy]

с join-ами гороаздо эффективней план получается. Специфика мускула. Тогда поменяю в биллинге на join

[Efendy]

В общем твой запрос неправильный, но зато ты натолкнул на мысль:

Код:

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

[Efendy]

кстати, попробуй еще изменить вьюху 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 ...)

[goletsa]

В общем твой запрос неправильный, но зато ты натолкнул на мысль:

Код:

"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

ок, попробую сегодня

[goletsa]

И еще вопрос.
Мне тут еще советовали сделать индекс для поля revision в исходной таблице (dopfileds чтоли...).
Это повышает производительность или смысла нету?

[Efendy]

И еще вопрос.
Мне тут еще советовали сделать индекс для поля 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

[dad Min]

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

Код:

$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 измененный

[Efendy]

Какая версия биллинга?  Ибо

Код:

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