Привет
У меня такая проблемка.
Mysql через неопределёное время доходит до такого состояния:PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
13785 mysql 18 0 126m 42m 5404 S 73.5 5.6 499:47.66 mysqldCPU - 73.5%
cpu - 1039.110 MHzВсего 2-е базы, обе на 30Мб.
Как посмотреть в чем проблема?
Вот что показывает mytop
Особенно Мне странно 30.4k qps...MySQL on localhost (5.0.32-Debian_7etch4-log) up 0+10:28:52 [02:49:15]
Queries: 30.4k qps: 1 Slow: 0.0 Se/In/Up/De(%): 19/00/00/00
qps now: 1 Slow qps: 0.0 Threads: 5 ( 3/ 3) 00/00/00/00
Key Efficiency: 97.0% Bps in/out: 0.0/ 3.2 Now in/out: 7.7/ 1.3kId User Host/IP DB Time Cmd Query or State
-- ---- ------- -- ---- --- ----------
2703 root localhost film 0 Query show full processlist
2705 root localhost phpbb3 5 Sleep
7 verlihub localhost verlihub 65 Sleep
117 filmview localhost film 35533 Query SELECT `review`,`fid` FROM `film_page` WHERE `en`='yes' AND `fid` in ( SELECT `fid` FROM `film_actors` W
106 filmview localhost film 35686 Query SELECT `review`,`fid` FROM `film_page` WHERE `en`='yes' AND `fid` in ( SELECT `fid` FROM `film_actors` W
Из вывода mytop подозрение вызывают два трида которые выполняют одинаковые/сходные запросы в течении последних ~10 часов.
Для более точной диагностики было бы хорошо получить запрос полностью - сделать это можно подключившись к сервису
mysql -u root [-p если надо]
и выполнив
show full processlist;Причин для такого поведения более вероятных я вижу две. Я бы попробовал двигаться так:
1. Возможно есть поломанные таблицы. - Я бы получил бы полный запрос, определил все исползуемые таблички в запросе/вложенных запросах и проверил бы эти таблицы (иногда после некорректного завершения работы mysqld часть индексов могут оказаться поврежденными и query optimizer "сходит с ума"). Зная список таблиц делаем следующее:
- останавливаем триды которые работают сейчас (потому как пока они не завершатся залокировать таблички для проверки не получится); для этого выполняем (id тридов из предоставленного вывода):
mysql -u root [-p если надо] -e 'kill 117; kill 106;'далее проверка таблиц film_page и film_actors:
mysql -u root [-p если надо] -e 'check table film_page; check table film_actors;' filmдалее (если по выводу проверки таблички "поломаны")
mysql -u root [-p если надо] -e 'repair table film_page; repair table film_actors;' filmЯ бы после этого ещё сделал
mysql -u root [-p если надо] -e 'analyze table film_page; analyze table film_actors;' film2. Вариант два запрос построен крайне неоптимально - это может быть что угодно, от сумашедшего количества вложенных запросов, многокилобайтных запросов "автоматически сгенирированных клиентским ПО", отсутствия индексов, полнотекстного поиска, явных и неявных временных таблиц, "неправильно" построенных join-ов (не знаю как просто это назвать :) ) до...
Если "визуально" по запросу понять где эта глупость/ошибка не получается думаю проще всего будет выполнить:
mysql -u root [-p если надо] -e 'explain и здесь вписать запрос один-в-один' film
вывод команды покажет каким образом сам mysql сервер "видит" этот запрос, какие операции выполняет/будет выполнять, какие индексы есть и какие использует, etc
В этом случае необходимо упрощать/оптимизировать/разбивать на части запрос, добавлять индексы если необходимых индексов нет, возможно (маловероятно исходя из времени выполнения запроса) менять параметры самого mysql сервиса либо глобально, либо "только для определенных запросов" через set session ... перед запросом.Возможны и другие варианты но думаю начать лучше с этого.
По поводу высокого qps - он не высокий - qps = 1;
30.4k - это количество запросов которые сервис обработал со времени запуска/последнего "flush status".
# ab -n 15000 -c 50 -t 300 http://media.server:80/
This is ApacheBench, Version 2.0.40-dev <$Revision: 1.146 $> apache-2.0
Copyright 1996 Adam Twiss, Zeus Technology Ltd, http://www.zeustech.net/
Copyright 2006 The Apache Software Foundation, http://www.apache.org/Benchmarking media.skyhome (be patient)
Completed 5000 requests
Completed 10000 requests
Finished 14585 requests
Server Software: nginx/0.4.13
Server Hostname: media.server
Server Port: 80Document Path: /
Document Length: 56336 bytesConcurrency Level: 50
Time taken for tests: 300.5481 seconds
Complete requests: 14585
Failed requests: 1537
(Connect: 0, Length: 1537, Exceptions: 0)
Write errors: 0
Total transferred: 826894847 bytes
HTML transferred: 821497967 bytes
Requests per second: 48.62 [#/sec] (mean)
Time per request: 1028.473 [ms] (mean)
Time per request: 20.569 [ms] (mean, across all concurrent requests)
Transfer rate: 2691.66 [Kbytes/sec] receivedConnection Times (ms)
min mean[+/-sd] median max
Connect: 0 12 190.4 0 3012
Processing: 8 1012 2712.6 408 51443
Waiting: 4 1005 2702.9 406 51442
Total: 8 1024 2716.1 417 51443Percentage of the requests served within a certain time (ms)
50% 417
66% 546
75% 1471
80% 1937
90% 2653
95% 2826
98% 3392
99% 6822
100% 51443 (longest request)
#mytom
MySQL on localhost (5.0.32-Debian_7etch4-log) up 0+01:03:34 [05:10:57]
Queries: 326.2k qps: 88 Slow: 0.0 Se/In/Up/De(%): 30/00/00/00
qps now: 891 Slow qps: 0.0 Threads: 49 ( 2/ 0) 30/00/00/00
Key Efficiency: 91.0% Bps in/out: 0.4/ 53.7 Now in/out: 5.4/867.6Id User Host/IP DB Time Cmd Query or State
-- ---- ------- -- ---- --- ----------
33485 filmview localhost film 0 Sleep
33489 filmview localhost film 0 Init D cleaning up
33499 filmview localhost film 0 Init D cleaning up
33514 filmview localhost film 0 Sleep
33515 filmview localhost film 0 Sleep
33516 filmview localhost film 0 Sleep
33517 filmview localhost film 0 Sleep
33518 filmview localhost film 0 Sleep
33559 filmview localhost 0 Init D
33560 filmview localhost 0 Init D
33561 filmview localhost 0 Init D
33562 filmview localhost 0 Init D
33563 filmview localhost 0 Init D
33564 filmview localhost 0 Init D
33565 filmview localhost 0 Connec login
33569 filmview localhost 0 Connec login
33570 filmview localhost 0 Connec login
33571 filmview localhost 0 Connec login
33572 filmview localhost 0 Connec login
33573 filmview localhost 0 Connec login
33574 filmview localhost 0 Connec login
33575 filmview localhost 0 Connec login
33576 filmview localhost 0 Connec login
33577 filmview localhost 0 Connec login
33578 filmview localhost 0 Connec login
22015 root localhost film 1 Query show full processlist
33503 filmview localhost film 1 Sleep
33504 filmview localhost 1 Init D
33513 filmview localhost 1 Init D
33523 filmview localhost film 1 Sleep
33557 filmview localhost 1 Init D
33558 filmview localhost 1 Init D
#top
top - 05:13:04 up 12:52, 3 users, load average: 15.15, 20.60, 15.06
Tasks: 106 total, 10 running, 96 sleeping, 0 stopped, 0 zombie
Cpu(s): 35.7%us, 64.3%sy, 0.0%ni, 0.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 767052k total, 605164k used, 161888k free, 0k buffers
Swap: 0k total, 0k used, 0k free, 0k cachedPID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
15868 root 15 0 76052 56m 5844 R 3.7 7.6 13:17.34 hlds_i686
16030 mysql 19 0 189m 29m 5284 R 18.3 3.9 3:50.49 mysqldfilmview - пользователь из под которого подкючается сайт
По пункт 1 - всё ок. Я выполнил и то что "на всякий случай"
># ab -n 15000 -c 50 -t 300 http://media.server:80/
> SELECT `review`,`fid` FROM `film_page` WHERE `en`='yes' AND `fid` in ( SELECT `fid` FROM `film_actors` WСудя по той части запроса которая видна, это более напоминает "поиск на сайте" (т.е. не основная страница) и поисковый запрос это что-то вроде 'выбрать "ревью" всех фильмов на английском языке в которых играл такой-то актер/актеры...' Без полного запроса точнее сказать сложно.
>># ab -n 15000 -c 50 -t 300 http://media.server:80/
>> SELECT `review`,`fid` FROM `film_page` WHERE `en`='yes' AND `fid` in ( SELECT `fid` FROM `film_actors` W
>
>Судя по той части запроса которая видна, это более напоминает "поиск на
>сайте" (т.е. не основная страница) и поисковый запрос это что-то вроде
>'выбрать "ревью" всех фильмов на английском языке в которых играл такой-то
>актер/актеры...' Без полного запроса точнее сказать сложно.Вот что в конфиге мускула сделал - пока не падает и не перегружается
key_buffer = 48M
max_allowed_packet = 48M
thread_stack = 128K
thread_cache_size = 8
#max_connections = 100
table_cache = 96
#thread_concurrency = 10
query_cache_type = 1
query_cache_limit = 8M
query_cache_size = 32M
На полный запрос и его "explain" всё-равно было бы интересно посмотреть.
А на счёт настроек - размер key_buffer (кэш индексов) стараются выбирать таким чтоб через несколько (2-12) часов работы сервиса попадание в кэш на опрециях чтения (можно посчитать вручную по show status; можно воспользоваться mysqlreport для подсчета, это же значение показывает и mytop - поле "Key Efficiency") составляло 99-99.9 процентов.max_allowed_packet - на производительность не влияет
thread_stack - я бы не трогал это значение, пока ситуация этого не требует явно, обычно значения по умолчанию (часто это действительно 128к) достаточно для хранения временных переменных каждого трида
thread_cache_size - судя по "порядковым номерам" тридов в первом выводе mytop новые триды создаются очень редко, думаю 4-8 - отличный выбор. Основная задумка этого кэша - не тратить ресурсы машины на частое создание новых тридов, а вместо этого делать reuse закешированных.
table_cache - возможно маловато, хотя точно сказать не могу не зная количества используемых таблиц в существующих базах. Задумка этого кэша в том чтоб не тратить ресурсы машины на открытие/закрытие таблиц (файлов) - т.е. если какая-то таблица уже была открыта, то она будет находится в кэше таблиц. Количество открытых таблиц на данный момент можно посмотреть выполнив mysql -u root -e '\s' Поля Opens: и Open tables: это соответственно "операций открытия таблиц с начала работы сервиса" и "открыто таблиц на данный момент".
С другой стороны не следует сильно завышать это значение потому как ОС также может ограничивать каждый процесс на предмет количества одновременно открытых файлов. Плюс сама ОС может иметь ограничение на "суммарное количество фалов открытых всеми процессами". Пользовательское ограничение можно увидеть выполнив
su - mysql -c 'ulimit -n'
Ещё следует помнить что при использовании isam/myisam engine одна таблица в кэше это два открытых файла.query_cache_size обычно выбирается/подбирается экспериментально исходя из доступной памяти и эффективности попадания в этот кэш. Т.е. если например увеличив query cache на 20 процентов я получаю повышение попадания в кэш всего на пару процентов, то пора остановиться - лучше пусть эта память будет использована операционкой для дискового кэша.
query_cache_limit я бы уменьшил до дефолтных 1M или даже меньше - иначе получается что всего четыре "больших" запроса/ответа могут вытеснить все остальные закешированные запросы/ответы.
Чтоб не высчитывать эффективности попадания в кэши вручную я использую mysqlreport - достаточно удобный/полезный инструмент.
#mysql -u root -p -e '\s'
Enter password:
--------------
mysql Ver 14.12 Distrib 5.0.32, for pc-linux-gnu (i486) using readline 5.2
Connection id: 54637
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.0.32-Debian_7etch4-log Debian etch distribution
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: latin1
Conn. characterset: latin1
UNIX socket: /var/run/mysqld/mysqld.sock
Uptime: 2 days 22 hours 4 min 15 secThreads: 5 Questions: 586728 Slow queries: 77 Opens: 1277 Flush tables: 41 Open tables: 96 Queries per second avg: 2.326
--------------
#top
top - 02:13:13 up 3 days, 9:52, 2 users, load average: 4.93, 5.38, 5.74
Tasks: 74 total, 4 running, 70 sleeping, 0 stopped, 0 zombie
Cpu(s): 53.2%us, 46.8%sy, 0.0%ni, 0.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 767052k total, 553896k used, 213156k free, 0k buffers
Swap: 0k total, 0k used, 0k free, 0k cachedPID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
16030 mysql 15 0 189m 66m 5472 S 96.2 8.9 380:08.45 mysqld#mysqlreport
MySQL 5.0.32-Debian_7et uptime 2 22:38:47 Sun Jan 27 02:46:10 2008__ Key _________________________________________________________________
Buffer used 6.22M of 48.00M %Used: 12.97
Current 9.45M %Usage: 19.69
Write ratio 0.56
Read ratio 0.01__ Questions ___________________________________________________________
Total 587.64k 2.31/s
Slow 77 0.00/s %Total: 0.01 %DMS: 0.09
DMS 85.37k 0.34/s 14.53__ Table Locks _________________________________________________________
Waited 6 0.00/s %Total: 0.01
Immediate 113.47k 0.45/s__ Tables ______________________________________________________________
Open 96 of 96 %Cache: 100.00
Opened 1.28k 0.01/s__ Connections _________________________________________________________
Max used 90 of 100 %Max: 90.00
Total 54.72k 0.22/s__ Created Temp ________________________________________________________
Disk table 3.54k 0.01/s
Table 12.88k 0.05/s
File 5 0.00/s#su - mysql -c 'ulimit -n'
1024
#mytop
MySQL on localhost (5.0.32-Debian_7etch4-log) up 2+22:50:36 [02:57:59]
Queries: 574.2k qps: 2 Slow: 0.0 Se/In/Up/De(%): 27/00/00/00
qps now: 0 Slow qps: 0.0 Threads: 5 ( 4/ 4) 00/00/00/00
Key Efficiency: 99.0% Bps in/out: 0.0/ 0.1 Now in/out: 7.9/ 1.4kId User Host/IP DB Time Cmd Query or State
-- ---- ------- -- ---- --- ----------
54750 root localhost film 0 Query show full processlist
596 verlihub localhost verlihub 9 Sleep
54367 filmview localhost film 7183 Query SELECT `review`,`fid` FROM `film_page` WHERE `en`='yes' AND `fi
52615 filmview localhost film 26621 Query SELECT `review`,`fid` FROM `film_page` WHERE `en`='yes' AND `fi
52242 filmview localhost film 30821 Query SELECT `review`,`fid` FROM `film_page` WHERE `en`='yes' AND `fi
Данные от mytop
EXPLAIN SELECT `review`,`fid` FROM `film_page` WHERE `en`='yes' AND `fid` in ( SELECT `fid` FROM `film_actors` WHERE `pid` in (SELECT `cid` FROM `category_actors` WHERE `actors` Like '%???? ?????%' ) ):*** row 1 ***
table: film_page
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 868
Extra: Using where
*** row 2 ***
table: film_actors
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 5734
Extra: Using where
*** row 3 ***
table: category_actors
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 5794
Extra: Using where
>Данные от mytop
>EXPLAIN SELECT `review`,`fid` FROM `film_page` WHERE `en`='yes' AND `fid` in ( SELECT
>`fid` FROM `film_actors` WHERE `pid` in (SELECT `cid` FROM `category_actors` WHERE
>`actors` Like '%???? ?????%' ) ):SELECT `review`,`fid` FROM `film_page`
`review` - это описание фильма
`fid`- индентификатор фильмаSELECT `fid` FROM `film_actors` WHERE `pid` in (SELECT `cid` FROM `category_actors` WHERE `actors` Like '%???? ?????%' )
В таблице `category_actors` хранится в поле `actors`- имя актёра, а в поле `cid` - индентификатор актёра
В таблице `film_actors` хранится связь между `fid`-индентификатором фильма и `cid` - индентификатором актёра
А весь этот запрос для того что бы найти фильмы у которых такой-то актёр %)
CREATE TABLE `film_page` (
`fid` bigint(255) NOT NULL,
`review` text character set utf8 NOT NULL,
`view` longtext character set utf8 NOT NULL,
`abc` varchar(1) character set utf8 NOT NULL default 'А',
`top` bigint(255) NOT NULL,
`abc2` varchar(1) character set utf8 NOT NULL default 'A',
`en` enum('yes','no') character set utf8 NOT NULL default 'yes',
`d` timestamp NOT NULL default CURRENT_TIMESTAMP,
KEY `fid` (`fid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE `film_actors` (
`pid` bigint(255) NOT NULL,
`fid` bigint(255) NOT NULL default '0',
PRIMARY KEY (`pid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;CREATE TABLE `category_actors` (
`cid` bigint(255) NOT NULL,
`actors` varchar(255) character set utf8 NOT NULL,
KEY `actors` (`actors`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
>>Данные от mytop.......
>>`actors` Like '%???? ?????%' ) ):Это было "возможно" из-за того что в путти не отображается русский
Cначала корректировка настроек по новым данным:
Исходя из:
MySQL 5.0.32-Debian_7et **uptime 2 22:38:47** Sun Jan 27 02:46:10 2008__ Key _________________________________________________________________
Buffer used 6.22M of 48.00M %Used: 12.97
Current 9.45M %Usage: 19.69
Write ratio 0.56
Read ratio 0.01key_buffer будет разумно сниизть до 12-16Мб, потому как "остальная" его часть всё-равно не используется.
И поднять table_cache, начать можно со значения 256
Кроме того лучше сразу добавить в my.cnf строчку
open-files-limit = 4096
Эта строка приведёт к тому что при запуске сервиса (пока ещё скрипт запуска сервиса выполняется от рута) будет переопределен лимит на максимальное количество открытых файлов для порождаемого процесса. Т.е. несмотря на то что по-умолчанию лимит составляет 1024 файла (то, что мы смотрели по ulimit -n), процесс mysqld после запуска будет иметь этот лимит установленный в 4096.
Теперь по поводу запроса и его оптимизации.
В нашем запросе и его explain можно выделить следующее:
- существует полнотекстовый поиск (там где знаки %),
- судя по explain к сожалению невозможно понять какие индексы используются при обработке такого запроса (из-за "вопросиков", потому как в данном случае знак вопроса трактуется как спецсимвол). Можно попробовать выполнить:
EXPLAIN SELECT `review`,`fid` FROM `film_page` WHERE `en`='yes' AND `fid` in ( SELECT `fid` FROM `film_actors` WHERE `pid` in (SELECT `cid` FROM `category_actors` WHERE `actors` Like '%abcd qwer%' ) );
То что можно сказать "на глаз" - не все поля по которым идет выборка имеют индексы.
- два вложенных запроса приводят к созданию двух временных таблиц (тут сложно что-то изменить, можно подумать над tmp_table_size/max_heap_table_size но я не уверен что это поможет).
Начнем с самого глубокого запроса и полнотекстного поиска - я вижу два варианта "облегчения" выполнения этого запроса:
- создать полнотекстный (FULLTEXT) индекс, - такой индекс будет применим для условий like '%....%'
- я не уверен что первый "%" добавлен в запрос обдуманно (нужен ли он при поиске по имени/фамилии актера), - возможно его следует убрать, и тогда будет достаточно "обычного" индекса (primary key из определения структуры таблицы category_actors, - т.е. необходимый индекс для такого условия уже есть).Решение с полнотекстным индексом:
use film;
create fulltext index actor_ftxt_index on category_actors (actors);Второй запрос - pid уже индексирован (primary key из определения структуры таблицы film_actors), т.е. ничего не меняем.
Самый "верхний" запрос - индексируем столбец en (fid уже индексирован):
use film;
create index en_index on film_page (en);
Примерно так... дальше проверяем время выполнения запроса (с лбымтекстом вместо вопросов), и снова смотрим на "explain запрос".PS Перед тем как выполнять какие-либо манипуляции с базой не забываем бэкапиться.
PPS Если будет выбрано решение с fulltext index, то возможно key_buffer лучше пока оставить как и прежде на отметке 48M.И ещё - если запускать mysqlreport с ключем --all (либо --qcache), то будет видна статистика использования query cache.
EXPLAIN SELECT `review`,`fid` FROM `film_page` WHERE `en`='yes' AND `fid` in ( SELECT `fid` FROM `film_actors` WHERE `pid` in (SELECT `cid` FROM `category_actors` WHERE `actors` Like '%J%' ) );
+----+--------------------+-----------------+-----------------+---------------+----------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-----------------+-----------------+---------------+----------+---------+-------+------+--------------------------+
| 1 | PRIMARY | film_page | ref | en_index | en_index | 1 | const | 879 | Using where |
| 2 | DEPENDENT SUBQUERY | film_actors | ALL | NULL | NULL | NULL | NULL | 5819 | Using where |
| 3 | DEPENDENT SUBQUERY | category_actors | unique_subquery | PRIMARY | PRIMARY | 8 | func | 1 | Using index; Using where |
+----+--------------------+-----------------+-----------------+---------------+----------+---------+-------+------+--------------------------+
3 rows in set (0.07 sec)
SELECT `review`,`fid` FROM `film_page` WHERE `en`='yes' AND `fid` in ( SELECT `fid` FROM `film_actors` WHERE `pid` in (SELECT `cid` FROM `category_actors` WHERE `actors` Like '%J%' ) ) LIMIT 10;
10 rows in set (7.97 sec)mysql> SELECT `review`,`fid` FROM `film_page` WHERE `en`='yes' AND `fid` in ( SELECT `fid` FROM `film_actors` WHERE `pid` in (SELECT `cid` FROM `category_actors` WHERE `actors` Like 'Van%' ) ) LIMIT 10;
Empty set (4 min 45.56 sec)
Таблица - `category_actors` уже 6000 строк
В данном случае я пока не могу объяснить по какой причине в первом и втором запросе в possible_keys отствуют индексы fid и pid соответственно. Вобщем надо подумать.
>В данном случае я пока не могу объяснить по какой причине в
>первом и втором запросе в possible_keys отствуют индексы fid и pid
>соответственно. Вобщем надо подумать.У Меня такой вопрос
Почему запрос
SELECT `fid` FROM `film_actors` WHERE `pid` IN ( SELECT `cid` FROM `category_actors` WHERE `actors` LIKE '%an%')LIMIT 0 , 30
Показывает записи 0 - 29 (297 всего, Запрос занял 0.0001 сек)
В общем до 0.09 секунды...Но...
MySQL вернула пустой результат (т.е. ноль рядов). (Запрос занял 86.6721 сек)
SELECT `review` , `fid` FROM `film_page` WHERE `en` = 'yes'AND `fid` IN (SELECT `fid` FROM `film_actors` WHERE `pid` IN (SELECT `cid` FROM `category_actors` WHERE `actors` LIKE 'Van%'))LIMIT 10 ;И...
Показывает записи 0 - 29 (853 всего, Запрос занял 0.0072 сек)
SELECT `fid`,`review` FROM `film_page` WHERE `en` = 'yes' AND `fid` IN (SELECT `fid` FROM `film_actors` )Тоесть Я так понимаю SELECT in ( SELECT in (SELECT ))) дает сбой
И...
Показывает записи 0 - 0 (1 всего, Запрос занял 15.6731 сек)
SELECT `fid` , `review` FROM `film_page` WHERE `en` = 'yes' AND `fid` IN (SELECT `fid` FROM `film_actors` WHERE `fid` = '2')LIMIT 0 , 30
Этот селект специально для одного значения
И так сказать на закуску
Показывает записи 0 - 0 (1 всего, Запрос занял 0.0003 сек)
SQL-запрос:
SELECT `fid` , `review` FROM `film_page` WHERE `en` = 'yes' AND `fid` IN ('2')LIMIT 0 , 30
MySQL 5.0.32-Debian_7et uptime 0 13:46:21 Wed Jan 30 07:34:00 2008__ Key _________________________________________________________________
Buffer used 2.60M of 48.00M %Used: 5.43
Current 5.96M %Usage: 12.41
Write ratio 0.55
Read ratio 0.00__ Questions ___________________________________________________________
Total 40.10k 0.81/s
DMS 16.22k 0.33/s %Total: 40.44
Com_ 14.00k 0.28/s 34.91
QC Hits 6.41k 0.13/s 15.97
COM_QUIT 3.15k 0.06/s 7.86
+Unknown 327 0.01/s 0.82
Slow 8 0.00/s 0.02 %DMS: 0.05
DMS 16.22k 0.33/s 40.44
SELECT 11.65k 0.24/s 29.06 71.87
UPDATE 2.64k 0.05/s 6.58 16.28
INSERT 1.20k 0.02/s 2.99 7.40
DELETE 722 0.01/s 1.80 4.45
REPLACE 0 0.00/s 0.00 0.00
Com_ 14.00k 0.28/s 34.91
change_db 6.28k 0.13/s 15.67
set_option 5.72k 0.12/s 14.26
show_fields 448 0.01/s 1.12__ SELECT and Sort _____________________________________________________
Scan 2.23k 0.04/s %SELECT: 19.12
Range 4.10k 0.08/s 35.15
Full join 8 0.00/s 0.07
Range check 17 0.00/s 0.15
Full rng join 4 0.00/s 0.03
Sort scan 1.42k 0.03/s
Sort range 562 0.01/s
Sort mrg pass 0 0.00/s__ Query Cache _________________________________________________________
Memory usage 1.25M of 32.00M %Used: 3.90
Block Fragmnt 23.24%
Hits 6.41k 0.13/s
Inserts 8.96k 0.18/s
Prunes 1 0.00/s
Insrt:Prune 8.96k:1 0.18/s
Hit:Insert 0.72:1__ Table Locks _________________________________________________________
Waited 0 0.00/s %Total: 0.00
Immediate 21.84k 0.44/s__ Tables ______________________________________________________________
Open 54 of 512 %Cache: 10.55
Opened 648 0.01/s__ Connections _________________________________________________________
Max used 9 of 100 %Max: 9.00
Total 3.16k 0.06/s__ Created Temp ________________________________________________________
Disk table 760 0.02/s
Table 2.78k 0.06/s
File 5 0.00/s
Хм...
Возможно это был глюк mysql'я.
Я несколько раз убивал и создавал индексы, но ничего не изменилось...
В общем Я разбил на 2-а запроса и в таком виде всё летает, но что можно сделать что бы всё работало как надо?
Сразу несколько уточнений - для того чтоб не ошибиться во временах выполенения запросов на момент экспериментов лучше выключить query cache, потому как реальный "просчет" запроса может занимать минуты, повторный вызов/обработка этого же запроса (находящегося в query cache) займёт тысячные доли секунды.В случае отсутствия fulltext индекса есть существенная разница между запросами оканичивающимися на LIKE '%an%' и LIKE 'Van%' - в первом случае необходим перебор всех данных ("обычные" индексы для такого поиска не могут быть использованы). Во втором случае производится поиск с использованием индексов, что значительно быстрее.
Достаточно часто извлечение определенного набора данных "сложным, но одним" запросом будет выполняться дольше, чем извлечение того же набора данных, но реализованного через десяток простых запросов.
Я не sql программист, и пытаюсь сообразить - для запроса
SELECT `review` , `fid` FROM `film_page` WHERE `en` = 'yes'AND `fid` IN (SELECT `fid` FROM `film_actors` WHERE `pid` IN (SELECT `cid` FROM `category_actors` WHERE `actors` LIKE 'Van%');
будет ли аналогом запрос
SELECT distinct film_page.review, film_page.fid from film_page, film_actors, category_actors where film_page.en='yes' and film_page.fid=film_actors.fid and film_actors.pid=category_actors.cid and category_actors.actors LIKE 'Van%';
?
или же
select distinct film_page.review, film_page.fid from film_page
inner join film_actors on film_page.fid=film_actors.fid and film_page.en='yes'
inner join category_actors on film_actors.pid=category_actors.cid
where category_actors.actors LIKE 'Van%';?
Наверное надо чтоб на это посмотрел sql программист. Ещё можно через явные temporary tables решить, но решение будет выглядеть немного менее "читаемо".
В моём случае (я создал такие же таблицы и каждую из них заполнил случайным набором данных, примерно 17К записей в каждой таблице) и если первый запрос выполняется точно более часа (я так и не дождался результатов), то второй и третий "варианты запроса" выполняются в пределах десятой доли секунды. Если такое решение вопроса подходит, то можно считать вопрос решенным.
Если нет - то пока могу лишь сказать что "тормозом" в запросе _не_ является понотекстный поиск, здесь причина в чем-то другом. Пока предполагаю что выполнение вложенных запросов происходит не в "привычной/логичной" человеку последовательности, - т.е. первым выплняется не самый "глубокий" запрос, а происходит попытка производить выборку "одновременно" с "фильтрацией" результатов "на лету".
Я использовал
SELECT film_page.review, film_page.fid from film_page, film_actors, category_actors where film_page.en='yes' and film_page.fid=film_actors.fid and film_actors.pid=category_actors.cid and category_actors.actors LIKE '%a%';
Это оказалось быстрее чем 2-а запроса, сможет кто-то из mysql'щиков чего-то скажет?ЗЫ:
Спасибо