LINUX.ORG.RU

mysql Copying to tmp table


0

1

стала жутко тормозить БД mysql. выполнил профилирование запроса и ужаснулся сам запрос:

SELECT  DISTINCT(n.nid), n.title, l.comment_count 
FROM node n 
INNER JOIN node_comment_statistics l ON n.nid = l.nid  
INNER JOIN node_access na ON na.nid = n.nid 
WHERE (na.grant_view >= 1 AND 
             (
                 (na.gid = 0 AND na.realm = 'all') OR 
                 (na.gid = 2 AND na.realm = forum_access') OR 
                 (na.gid = 6 AND na.realm = 'forum_access')
              )
            ) AND 
            ( n.type = 'forum' AND n.status = 1 ) 
ORDER BY n.nid DESC 
LIMIT 0, 11;
Профилирование запроса (остальные строчки нормальное время показывают)
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| Copying to tmp table           | 3.865768 |          -- что это??
my.cnf менял вдоль и поперек. непосредственно этот результат был получен с таким:
cat /etc/mysql/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket						= /var/run/mysqld/mysqld.sock

character-set-server            = utf8
default-character-set           = utf8
user                                            = mysql
port                                            = 3306
socket                                          = /var/run/mysqld/mysqld.sock
pid-file                                        = /var/run/mysqld/mysqld.pid
log-error                                       = /var/log/mysql/mysqld.err
basedir                                         = /usr
datadir                                         = /var/lib/mysql
#skip-locking
#skip-innodb
language                                        = /usr/share/mysql/english
bind-address                            = 0.0.0.0


connect_timeout=30
interactive_timeout=3600
innodb_buffer_pool_size=1G
join_buffer_size=16M
key_buffer_size=92M
max_connections=300
max_heap_table_size=512M
myisam_sort_buffer_size=32M
old_passwords=1
query_cache_size=392M
query_cache_limit=4M
skip-bdb
skip-innodb
thread_cache_size=4
tmp_table_size=292M
table_cache=500
wait_timeout=7200

# logs #
log-error 					= /var/log/mysql/mysqld.err
set-variable=long_query_time=4
log-slow-queries=/var/log/mysql/slow.log
#log=/tmp/MySQL.log     
#log-queries-not-using-indexes


[mysql.server]
user=mysql
basedir=/var/lib

[mysqld_safe]
pid-file					= /var/run/mysqld/mysqld.pid
err-log						= /var/log/mysql/mysql.err

на машинке 8 Гб памяти кроме mysql ничего ресурсоемкого нет

чего поменять/посмотреть можно?

Не хватает индексов и/или слишком маленький join_buffer_size... Хотя, 16Мб - вроде, нормально.

А что говорит https://launchpad.net/mysql-tuning-primer или «wget mysqltuner.pl»?

KRoN73 ★★★★★ ()

Можно сразу заставить мускул использовать временную таблицу, хотя вряд ли спасет:

SELECT SQL_BIG_RESULT ...

А можно посмотреть на EXPLAIN запроса?

sjinks ★★★ ()

хех, мужик, в первую очередь реквестирую индексы (структуру таблицы в смысле, но такую чтоб индексы было видно), а во вторую explain

AndreyKl ★★★★★ ()
Ответ на: комментарий от AndreyKl
>describe node;
+-----------+------------------+------+-----+---------+----------------+
| Field     | Type                    | Null | Key   | Default | Extra          |
+-----------+------------------  +------+-----+  ---------+----------------+
| nid       | int(10) unsigned | NO   | PRI     | NULL    | auto_increment | 
| vid       | int(10) unsigned | NO   | UNI     | 0       |                | 
| type    | varchar(32)         | NO   | MUL    |         |                | 
| title     | varchar(255)       | NO   | MUL    |         |                | 
| uid       | int(10)                | NO   | MUL    | 0       |                | 
| status    | int(4)                | NO   | MUL    | 1       |                | 
| created   | int(11)             | NO   | MUL    | 0       |                | 
| changed   | int(11)            | NO   | MUL    | 0       |                | 
| comment   | int(2)             | NO   |           | 0       |                | 
| promote   | int(2)              | NO   | MUL   | 0       |                | 
| moderate  | int(2)             | NO   | MUL   | 0       |                | 
| sticky    | int(2)                 | NO   |           | 0       |                | 
| language  | varchar(12)   | NO   |        |         |                | 
| tnid      | int(10) unsigned| NO   | MUL   | 0       |                | 
| translate | int(11)            | NO   | MUL      | 0       |                | 
+-----------+------------------+------+-----+---------+----------------+
> describe node_comment_statistics;
+---------------------------------+-----------------------+------+-----+---------+----------------+
| Field                                   | Type                    | Null | Key | Default | Extra          |
+---------------------------------+----------------------+------+-----+---------+----------------+
| nid                                     | int(10) unsigned | NO   | PRI  | NULL  | auto_increment | 
| last_comment_timestamp | int(11)                 | NO   | MUL | 0       |                | 
| last_comment_name         | varchar(60)         | YES  |        | NULL  |                | 
| last_comment_uid             | int(10)                 | NO   |        | 0        |                | 
| comment_count                 | int(10) unsigned | NO   |        | 0        |                | 
+---------------------------------+------------------+------+-----+---------+----------------+

> describe node_access;            
| Field                | Type                        | Null | Key | Default | Extra |
+------------------+-------------------------+------+-----+-------+-------+
| nid                  | int(10) unsigned     | NO   | PRI | 0       |       | 
| gid                  | int(10) unsigned     | NO   | PRI | 0       |       | 
| realm              | varchar(255)           | NO   | PRI |          |       | 
| grant_view     | tinyint(1) unsigned | NO   |        | 0       |       | 
| grant_update | tinyint(1) unsigned | NO   |        | 0       |       | 
| grant_delete  | tinyint(1) unsigned | NO   |        | 0       |       | 
+--------------+---------------------+------+-----+---------+-------+

с этими же таблицами работало с месяц назад нормально, ничего не изменял, но. похоже, запускали myisamchk

mysqltuner говорит

 >>  MySQLTuner 1.0.1 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password: 

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.90-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated -InnoDB -ISAM -NDBCluster 
[--] Data in MyISAM tables: 1G (Tables: 184)
[!!] Total fragmented tables: 10

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1h 0m 49s (46K q [12.691 qps], 243 conn, TX: 68M, RX: 7M)
[--] Reads / Writes: 80% / 20%
[--] Total buffers: 1.8G global + 18.6M per thread (300 max threads)
[!!] Maximum possible memory usage: 7.2G (92% of installed RAM)
[OK] Slow queries: 0% (344/46K)
[OK] Highest usage of available connections: 43% (130/300)
[OK] Key buffer size / total MyISAM indexes: 92.0M/431.6M
[OK] Key buffer hit rate: 100.0% (88M cached / 6K reads)
[OK] Query cache efficiency: 85.7% (37K cached / 43K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (10 temp sorts / 1K sorts)
[!!] Joins performed without indexes: 351
[!!] Temporary tables created on disk: 33% (646 on disk / 1K total)
[!!] Thread cache hit rate: 46% (130 created / 243 connections)
[OK] Table cache hit rate: 79% (500 open / 626 opened)
[OK] Open file limit used: 45% (683/1K)
[OK] Table locks acquired immediately: 98% (12K immediate / 13K locks)

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Reduce your overall MySQL memory footprint for system stability
    Adjust your join queries to always utilize indexes
    Temporary table size is already large - reduce result set size
    Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    join_buffer_size (> 16.0M, or always use indexes with joins)
    thread_cache_size (> 4)

перенес базу на другую машинку, с параметрами mysql поумолчанию на вид - проблемы те же. в processlist часть процессов Copying to tmp table а часть lock

chuchandr ()
Ответ на: комментарий от chuchandr

угу, но эксплэйн таки тоже дай, а то без него как то... неопределённо..

explain SELECT....

AndreyKl ★★★★★ ()
Ответ на: комментарий от chuchandr

то что месяц назад работало, а теперь перестало, скорее всего говорит о том, что просто поднакопилось данных...

AndreyKl ★★★★★ ()

этот же запрос только что запускал с explain'ом

id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	SIMPLE 	na 	ALL 	PRIMARY 	NULL 	NULL 	NULL 	137640 	Using where; Using temporary; Using filesort
1 	SIMPLE 	l 	eq_ref 	PRIMARY 	PRIMARY 	4 	content7.na.nid 	1 	 
1 	SIMPLE 	n 	eq_ref 	PRIMARY,node_type,node_status_type,nid 	PRIMARY 	4 	content7.na.nid 	1 	Using where

http://ib1.keep4u.ru/b/2010/07/30/a1/a18a9cf82cbe087c32a71dec6e9615ae.png

там не только этот запрос «тормозит» там помимо него ещё и другие в которых правда частенько фигурируют таблицы node и node_access. есть и другие запросы.

вот пример slow-query

# Time: 100730 20:36:55
# User@Host: duser[duser] @  [10.0.1.2]
# Thread_id: 595  Schema: content7
# Query_time: 460.906117  Lock_time: 0.037000  Rows_sent: 25  Rows_examined: 210192  Rows_affected: 0  Rows_read: 25
SELECT  DISTINCT(n.nid), n.created FROM node n  INNER JOIN node_access na ON na.nid = n.nid WHERE (na.grant_view >= 1 AND ((na.gid = 0 AND na.realm = 'all') OR (na.gid = 1 AND na.realm = 'forum_access'))) AND ( n.promote = 1 AND n.status = 1 ) ORDER BY n.created DESC LIMIT 0, 25;
# Time: 100730 20:37:06
# User@Host: duser[duser] @  [10.0.1.2]
# Thread_id: 131  Schema: content7
# Query_time: 952.534357  Lock_time: 0.001391  Rows_sent: 11  Rows_examined: 285566  Rows_affected: 0  Rows_read: 11
SELECT  DISTINCT(n.nid), n.title, l.comment_count FROM node n INNER JOIN node_comment_statistics l ON n.nid = l.nid  INNER JOIN node_access na ON na.nid = n.nid WHERE (na.grant_view >= 1 AND ((na.gid = 0 AND na.realm = 'all') OR (na.gid = 2 AND na.realm = 'forum_access') OR (na.gid = 6 AND na.realm = 'forum_access'))) AND ( n.type = 'forum' AND n.status = 1 ) ORDER BY n.nid DESC LIMIT 0, 11;
# Time: 100730 20:37:21
# User@Host: duser[duser] @  [10.0.1.2]
# Thread_id: 147  Schema: content7
# Query_time: 954.781033  Lock_time: 0.001006  Rows_sent: 11  Rows_examined: 285566  Rows_affected: 0  Rows_read: 11
SELECT  DISTINCT(n.nid), n.title, l.comment_count FROM node n INNER JOIN node_comment_statistics l ON n.nid = l.nid  INNER JOIN node_access na ON na.nid = n.nid WHERE (na.grant_view >= 1 AND ((na.gid = 0 AND na.realm = 'all') OR (na.gid = 2 AND na.realm = 'forum_access') OR (na.gid = 6 AND na.realm = 'forum_access'))) AND ( n.type = 'forum' AND n.status = 1 ) ORDER BY n.nid DESC LIMIT 0, 11;

chuchandr ()
Ответ на: комментарий от chuchandr

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

chuchandr ()
Ответ на: комментарий от chuchandr

слушай, ну вроде ключи на месте.. почему-то mysql отказался испльзовать индрекс type... поопытней может ребята подтянуться сейчас.. а пока поколдуем:

попробуй написать

... INNER JOIN node_access na ON na.nid = n.nid USE INDEX (n.type) WHERE ...

и запости эксплейн этого

а так же выложи скриншоты структуры в phpmyadmin так чтобы было видно описание индексов (там где написано PRIMARY - уникальных элементов 1220 например), то есть чтобы было видно сколько элементов в том или ином индексе.

AndreyKl ★★★★★ ()
Ответ на: комментарий от AndreyKl

не, это я гоню, во первых в синтаксисе ошибка, во вторых работать не будет....

AndreyKl ★★★★★ ()
Ответ на: комментарий от chuchandr

в общем я не допонимаю почему он не использует индекс, нужно попробовать так

SELECT DISTINCT(n.nid), n.title, l.comment_count
FROM node n
INNER JOIN node_comment_statistics l ON n.nid = l.nid
INNER JOIN node_access na USE INDEX(primary) ON na.nid = n.nid
WHERE (na.grant_view >= 1 AND
(
(na.gid = 0 AND na.realm = 'all') OR
(na.gid = 2 AND na.realm = forum_access') OR
(na.gid = 6 AND na.realm = 'forum_access')
)
) AND
( n.type = 'forum' AND n.status = 1 )
ORDER BY n.nid DESC
LIMIT 0, 11;

если не работает, то добавить ключ, который будет содержать в себе gid и realm и тогда попробовать убрать use index (или заменить на use index(новый ключ)) и поглядеть что получиться.

AndreyKl ★★★★★ ()
Ответ на: комментарий от AndreyKl

Не сработает.

Во-первых, оптимизатор читает первой node_access, так как не может найти подходящий индекс. Как следствие, USE INDEX(primary) не имеет смысла, так как node_access читается целиком.

Во-вторых, ключ по gid и realm не спасет, так как используется OR, а там с оптимизацией все хреново.

sjinks ★★★ ()
Ответ на: комментарий от sjinks
Ответ на: комментарий от sjinks

тут кажется дело в чем то другом, сегодня обнаружил что запрос

SELECT nc.nid, nc.comment_count FROM node_comment_statistics nc WHERE nc.comment_count > 0 ORDER BY nc.last_comment_timestamp DESC LIMIT 0, 14;

Отображает строки 0 - 11 (12 всего, запрос занял 7.3665 сек.)

7 сек!!

id 	select_type 	table 	type 	possible_keys 	   key 	key_len 	ref 	rows 	Extra
1 	SIMPLE 	       nc 	index 	NULL 	node_comment_timestamp 	4 	NULL 	82740 	Using where

chuchandr ()
Ответ на: комментарий от chuchandr

Да. Так как Вы зачем-то отвели 392 метра под кэш запросов.

sjinks ★★★ ()
Ответ на: комментарий от chuchandr

А после

ANALYZE TABLE node_comment_statistics;

ситуация не меняется?

sjinks ★★★ ()
Ответ на: комментарий от sjinks

2sjinks, скажи пжл, а то не пойму никак, а как получается что он не использует примари-индекс, ведь индекс идеально подходит (три поля во вхере (одно неявно там где ON na.nid=n.nid, и ещё два прямо явно указаны (na.gid=0 AND na.ralm = 'all)) разве нет? Или я неверно толкую ситуацию?

может как-то поиграться чтобы построитель запросов явно увидел индекс? что-то вроде

...

WHERE (na.grant_view >= 1 AND ( (na.gid = 0 AND na.realm = 'all' AND na.nic=n.nid) OR (na.gid = 2 AND na.realm = forum_access' AND na.nic=n.nid) OR (na.gid = 6 AND na.realm = 'forum_access' AND na.nic=n.nid) ) ) AND ( n.type = 'forum' AND n.status = 1 )

...

ТС, можешь попробовать это условние с explain вместо твоего?

AndreyKl ★★★★★ ()
Ответ на: комментарий от chuchandr

так у тебя comment_count не индексированный совсем, чего ж ты хочешь?

AndreyKl ★★★★★ ()
Ответ на: комментарий от AndreyKl

> может как-то поиграться чтобы построитель запросов явно увидел индекс?

Можно сделать EXPLAIN EXTENDED SELECT..., после него — SHOW WARNINGS, тогда будет видно, как оптимизатор воспринимает запрос.

С PRIMARY KEY очень просто — одна из таблиц будет прочитана целиком (либо с использованием условия в WHERE), после чего будет join'иться на остальные таблицы (как LEFT JOIN с IS NOT NULL). Для первой таблицы использование PRIMARY KEY не нужно, так как она читается первой (как следствие, ID для левой части JOIN у нас есть и его искать не нужно).

sjinks ★★★ ()
Ответ на: комментарий от chuchandr

> http://imglink.ru/pictures/31-07-10/27cc92489b89f95e3b76170a2e52a083.png

Уже лучше — range вместо ALL и сканируется практически в два раза меньше строк.

Попробуйте для таблицы na сделать ключ (nid, grant_view). Старый ключ по grant_view можно убрать. Ну и EXPLAIN. Сейчас надежда на то, что оптимизатор выберет другую таблицу для сканирования.

sjinks ★★★ ()
Ответ на: комментарий от AndreyKl

[code] na.grant_view >= 1 AND ( na.gid = 0 AND na.realm = 'all' OR na.gid = 2 AND na.realm = 'forum_access' OR na.gid = 6 AND na.realm = 'forum_access' ) [/code]

Если na читается первой, то nid из индекса не будет использоваться. А так как nid является префиксом этого индекса, то индекс не будет использован. Соответственно, остаются grant_view, gid и realm. Максимум, на что можно рассчитывать — это на range-оптимизацию.

По идее, тут может помочь индекс по всем этим полям. А может и не помочь. Нужно смотреть.

@ТС: попробуйте индекс по полям (grant_view, gid, realm) и EXPLAIN.

sjinks ★★★ ()
Ответ на: комментарий от chuchandr

спасибо вам люди добрые, подобным макаром переделалещё пару табличек/запросов. сайт стал пошустрее.
непойму только что делать с такими вот
http://imglink.ru/pictures/01-08-10/4a06c5befc9cc53d2e7ba3c409047232.png

где можно почитать про работу оптимизатора mysql?

chuchandr ()
Ответ на: комментарий от chuchandr

> PRIMARY (cid,nid)

INDEX (cid)

Если есть PRIMARY KEY по cid, отдельный индекс по cid скорее всего не нужен, так как MySQL умеет использовать префикс индекса.

sjinks ★★★ ()
Ответ на: комментарий от chuchandr

> где можно почитать про работу оптимизатора mysql?

http://dev.mysql.com/doc/refman/5.0/en/statement-optimization.html, http://dev.mysql.com/doc/refman/5.0/en/optimization-indexes.html, http://dev.mysql.com/doc/refman/5.0/en/execution-plan-information.html

непойму только что делать с такими вот

Там трудно что-нибудь сделать.

Во-первых, избавляемся от DISTINCT.

Во-вторых, в чем смысл делать группировку по sticky, title, created? Ведь nid записи уникален.

Во-третьих, нужно попробовать создать индекс по (nid, cid) и сравнить с тем, что есть сейчас. Возможно, оптимизатор будет рассматривать таблицы в другом порядке (может понадобиться индекс по n.status).

Можно разбить его на два запроса и посмотреть, не будет ли быстрее:

SELECT DISTINCT nid FROM category_node WHERE cid IN (...); /* Скорее всего, здесь будет лучше индекс по (cid, nid) */

SELECT nid, sticky, title, created FROM node WHERE status = 1 AND nid IN (результаты прошлого запроса) ORDER BY sticky DESC, created DESC

Так как nid - PRIMARY KEY, он будет уникальным

sjinks ★★★ ()
Ответ на: комментарий от sjinks

вопервых, избавился от distinct. кстати от group by я тоже избавился.
индекс по (nid, cid) делал, все также было. на два запроса решил не разбивать, но сделал вот что.

С учетом того что таблица node и category_node обновляются не так часто, 1 раз в несколько минут, а то и реже, то я
1) добавил в таблицу node индексное поле cid
2) повесил на category_node триггер на insert/update/delete который записывает соответствующие изменения в node.cid
3) переписал исходный запрос безо всяких join

и ещё. приведу скрины
исходный
http://imglink.ru/pictures/02-08-10/30067e062966e206c35f1b82f8fe23e5.png

здесь я только добавил в where условие n.nid > 0
http://imglink.ru/pictures/02-08-10/d827c28bc1bf866f97390cdc4ad55602.png

второй на вид пошустрее, хоть и появилось дополнительно Using temporary

так вот, выигрыш количества строк здесь значительнее или же лучше без временных таблиц?

chuchandr ()
Вы не можете добавлять комментарии в эту тему. Тема перемещена в архив.