Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / MySQL Новый топик    Ответить
 Оптимизация FullText Search запроса  [new]
Alex Vader
Member

Откуда: Самара
Сообщений: 40
Привет всем,

как можно минимизировать скорость выполнения вот такого запроса ?:
SELECT COUNT(*) AS iCount FROM biz.ct_business INNER JOIN biz.dc_city ON biz.dc_city.CityID=biz.ct_business.CityID   AND dc_city.vcValue="Columbus"  AND dc_city.StateID="ga"  WHERE MATCH (vcName) AGAINST ("John Buyers");
+--------+
| iCount |
+--------+
| 65 |
+--------+
1 row in set (36.82 sec)

Иногда время выполнения подобного запроса превышает 100 секунд :(

В biz.ct_business примерно 16 миллионов записей. Контроль над сервером полный. Версия 14.7 Distrib 4.1.12
Памяти 4 гига, процессор вроде как Intel 3гГц (не помню точно)

План выполнения запроса:
EXPLAIN SELECT COUNT(*) AS iCount FROM biz.ct_business INNER JOIN biz.dc_city ON biz.dc_city.CityID=biz.ct_business.CityID   AND dc_city.vcValue="Columbus"  AND dc_city.StateID="ga"  WHERE MATCH (vcName) AGAINST ("John Buyers");
+----+-------------+-------------+----------+---------------------------------------------------+---------+---------+------------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+----------+---------------------------------------------------+---------+---------+------------------------+------+-------------+
| 1 | SIMPLE | ct_business | fulltext | idxCityID,idxCityIDDecs,vcName | vcName | 0 | | 1 | Using where |
| 1 | SIMPLE | dc_city | eq_ref | PRIMARY,uniqStateIDvcLabel,idxStateID,idxCityName | PRIMARY | 4 | biz.ct_business.CityID | 1 | Using where |
+----+-------------+-------------+----------+---------------------------------------------------+---------+---------+------------------------+------+-------------+
2 rows in set (0.00 sec)
9 мар 07, 16:55    [3881737]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация FullText Search запроса  [new]
Nick Anikin
Member

Откуда: Москва
Сообщений: 2174
хорошо бы еще структуру таблиц увидеть
но есть подозрение, что намного быстрее сделать не получится, full-text поиск просто так долго отрабатывает... можно поиграться конечно со всякими настройками для full-text поиска, но вряд ли сильно поможет
а как долго выполняется следующий запрос?
SELECT COUNT(*) FROM ct_business WHERE MATCH (vcName) AGAINST ("John Buyers");
9 мар 07, 22:38    [3882664]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация FullText Search запроса  [new]
Alex Vader
Member

Откуда: Самара
Сообщений: 40
Путем многочисленных экпериментов и изменений параметров кешей, буферов и прочего пришел к интересному результату:

Если запускаем запрос с использованием FullText Search первый раз за 40 минут (примерное время) или сразу после перезапуска
сервера - время выполнения огромно. НО, любой следующий запрос (в том числе и на поиск других значений)
выполняется весьма быстро (секунды).
Стоит подождать около 4 минут после выполенения последнего запроса с использованием FullText Search (обычные запросы на
это не влияют), как первый же запрос опять выполняется минимум 100 секунд или дольше.


mysql> SELECT COUNT(*) FROM ct_business WHERE MATCH (vcName) AGAINST ("John Buyers");
+----------+
| COUNT(*) |
+----------+
| 133548 |
+----------+
1 row in set (4 min 26.87 sec)

mysql> sELECT COUNT(*) FROM ct_business WHERE MATCH (vcName) AGAINST ("John Buyers");
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 38
Current database: biz

+----------+
| COUNT(*) |
+----------+
| 133548 |
+----------+
1 row in set (1.23 sec)

mysql> SELECT COUNT(*) AS iCount FROM biz.ct_business INNER JOIN biz.dc_city ON biz.dc_city.CityID=biz.ct_business.CityID AND dc_city.vcValue="Columbus" AND dc_city.StateID="ga" WHERE MATCH (vcName) AGAINST ("John Buyers");
+--------+
| iCount |
+--------+
| 65 |
+--------+
1 row in set (2.13 sec)

mysql> SELECT COUNT(*) AS iCount FROM biz.ct_business INNER JOIN biz.dc_city ON biz.dc_city.CityID=biz.ct_business.CityID AND dc_city.vcValue="Columbus" AND dc_city.StateID="ga" WHERE MATCH (vcName) AGAINST ("John Doe");
+--------+
| iCount |
+--------+
| 65 |
+--------+
1 row in set (2.03 sec)

Мысль есть только такая - по первому за период запросу в памть всасывается FullText Index индекс, именно на это тратятся
эти 100 секунд и более, все остальные запросы влегкую его используют....
Затянуть обычные индексы командой
CACHE INDEX biz.ct_business IN hot_cache;
промблем нет, но как затягивать в память/кэш полнотекстовые индексы? и как их там удержать?

Ну а структура таблиц в принципе простая:

CREATE TABLE `ct_business` (
`BusinessID` bigint(10) unsigned zerofill NOT NULL default '0000000000',
`vcName` varchar(64) NOT NULL default '',
`cAlfa3` char(3) NOT NULL default '',
`vcFullAddress` varchar(44) NOT NULL default '',
`vcStreetNumber` bigint(10) default NULL,
`StreetDirectionalID` char(2) default NULL,
`vcStreetName` varchar(20) default NULL,
`vcUnitDesignator` varchar(4) default NULL,
`vcUnitNumber` varchar(8) default NULL,
`CityID` int(4) NOT NULL default '0',
`vcPhone` varchar(10) NOT NULL default '',
`PhoneCodeID` char(1) default NULL,
`dtPubDate` date NOT NULL default '0000-00-00',
`vcLatitude` varchar(10) default NULL,
`vcLongitude` varchar(11) default NULL,
`PrecisionCodeID` tinyint(1) default NULL,
`vcVanityCityName` varchar(20) default NULL,
`vcWebAddress` varchar(100) default NULL,
`PrimaryBdcFlagID` char(1) default NULL,
`AdTypeID` tinyint(1) default NULL,
`AdSizeID` char(1) default NULL,
`vcFax` varchar(10) default NULL,
`HoursOperationID` char(1) default NULL,
`dtYearStarted` date default NULL,
`iYearsInBusiness` smallint(2) default NULL,
`ZipID` varchar(9) NOT NULL default '',
`Zip5ID` varchar(5) NOT NULL default '',
`Zip4ID` varchar(4) NOT NULL default '',
`dtUpdated` datetime NOT NULL default '2006-10-10 00:00:01',
`vcLogo` varchar(200) NOT NULL default '',
PRIMARY KEY (`BusinessID`),
KEY `idxCityID` (`CityID`),
KEY `idxZipID` (`ZipID`),
KEY `idxAlfa3` (`cAlfa3`),
KEY `idxCityIDDecs` (`CityID`),
FULLTEXT KEY `vcName` (`vcName`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


CREATE TABLE `dc_city` (
`CityID` int(4) NOT NULL auto_increment,
`StateID` char(2) NOT NULL default '',
`vcValue` varchar(60) NOT NULL default '',
`vcLabel` varchar(60) NOT NULL default '',
PRIMARY KEY (`CityID`),
UNIQUE KEY `uniqStateIDvcLabel` (`StateID`,`vcLabel`),
KEY `idxStateID` (`StateID`,`vcLabel`),
KEY `idxCityName` (`vcValue`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
9 мар 07, 23:34    [3882752]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация FullText Search запроса  [new]
Nick Anikin
Member

Откуда: Москва
Сообщений: 2174
Alex Vader
Мысль есть только такая - по первому за период запросу в памть всасывается FullText Index индекс, именно на это тратятся
эти 100 секунд и более, все остальные запросы влегкую его используют....
очень может быть :-)
Alex Vader

Затянуть обычные индексы командой
CACHE INDEX biz.ct_business IN hot_cache;
промблем нет, но как затягивать в память/кэш полнотекстовые индексы? и как их там удержать?
CACHE INDEX не подгружает индексы, эта команда лишь указывает, в какой именно кеш следует подгружать индексы для указанной таблицы (если ее не использовать, все индексы mysql будет подгружать в один общий кеш, размер которого задается параметром key_buffer_size)
чтобы подгрузить индекс, можно использовать команду PRELOAD INDEX
замечу, что, как я понял из описаний, обе эти команды работают со всеми индексами, указанными в команде таблиц, отдельно какой-то индекс из таблицы подгрузить нельзя
попробуйте сделать так: выделить для таблицы ct_business отдельный кеш (чтобы при работе с другими таблицами mysql не выгружал ваш full-text индекс, загружая другие индексы в один кеш), желательно такого размера, чтобы влезли все индексы для этой таблицы, и подгрузить индексы туда:
SET GLOBAL keycache1.key_buffer_size=512*1024*1024;
CACHE INDEX ct_business IN keycache1;
LOAD INDEX INTO CACHE ct_business;
10 мар 07, 09:58    [3883045]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация FullText Search запроса  [new]
Alex Vader
Member

Откуда: Самара
Сообщений: 40
Попробовал реализовать и получил интересные результаты:


размер индексов (я так понимаю всех, включая и FullText) около 1.2 Gb:
show table status like 'ct_business' \G
*************************** 1. row ***************************
Name: ct_business
Engine: MyISAM
Version: 9
Row_format: Dynamic
Rows: 17783165
Avg_row_length: 159
Data_length: 2839772196
Max_data_length: 4294967295
Index_length: 1183727616
Data_free: 0
Auto_increment: NULL
Create_time: 2007-02-05 06:45:48
Update_time: 2007-02-05 06:55:25
Check_time: 2007-02-05 07:19:33
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:


Далее в my.cnf отдаем поболее 3Gb на кэш идексов ("key buffer"):
key_buffer_size = 3200M
hot_cache.key_buffer_size = 3000M
init_file=/home/XXX/mysql/mysqld_init.sql
-----------
mysqld_init.sql:
CACHE INDEX biz.ct_business IN hot_cache;
LOAD INDEX INTO CACHE biz.ct_business;
---------------

В результате:
автор
select count(0) AS `COUNT(*)` from biz.ct_business where (match biz.ct_business.vcName against (_latin1'pizza'));
+----------+
| COUNT(*) |
+----------+
| 65724 |
+----------+
1 row in set (57.94 sec)

mysql> select count(0) AS `COUNT(*)` from biz.ct_business where (match biz.ct_business.vcName against (_latin1'steel'));
+----------+
| COUNT(*) |
+----------+
| 12977 |
+----------+
1 row in set (38.35 sec)

mysql> select count(0) AS `COUNT(*)` from biz.ct_business where (match biz.ct_business.vcName against (_latin1'wood'));
+----------+
| COUNT(*) |
+----------+
| 18763 |
+----------+
1 row in set (1 min 5.49 sec)

mysql> select count(0) AS `COUNT(*)` from biz.ct_business where (match biz.ct_business.vcName against (_latin1'star'));
+----------+
| COUNT(*) |
+----------+
| 35101 |
+----------+
1 row in set (2 min 18.17 sec)

mysql> SELECT COUNT(*) AS iCount FROM biz.ct_business INNER JOIN biz.dc_city ON biz.dc_city.CityID=biz.ct_business.CityID AND dc_city.vcValue="Columbus" AND dc_city.StateID="ga" WHERE MATCH (vcName) AGAINST ("John Doe");
+--------+
| iCount |
+--------+
| 65 |
+--------+
1 row in set (1 min 36.79 sec)


Иными словами - все плохо.



Тогда просто отдаю 3 гига на "key buffer" и не создаю "hot_cache":

автор
select count(0) AS `COUNT(*)` from biz.ct_business where (match biz.ct_business.vcName against (_latin1'pizza'));
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 4
Current database: biz

+----------+
| COUNT(*) |
+----------+
| 65724 |
+----------+
1 row in set (1 min 8.21 sec)

mysql> select count(0) AS `COUNT(*)` from biz.ct_business where (match biz.ct_business.vcName against (_latin1'steel'));
+----------+
| COUNT(*) |
+----------+
| 12977 |
+----------+
1 row in set (6.98 sec)

mysql> select count(0) AS `COUNT(*)` from biz.ct_business where (match biz.ct_business.vcName against (_latin1'wood'));
+----------+
| COUNT(*) |
+----------+
| 18763 |
+----------+
1 row in set (3.50 sec)

mysql> select count(0) AS `COUNT(*)` from biz.ct_business where (match biz.ct_business.vcName against (_latin1'star'));
+----------+
| COUNT(*) |
+----------+
| 35101 |
+----------+
1 row in set (5.13 sec)

mysql> SELECT COUNT(*) AS iCount FROM biz.ct_business INNER JOIN biz.dc_city ON biz.dc_city.CityID=biz.ct_business.CityID AND dc_city.vcValue="Columbus" AND dc_city.StateID="ga" WHERE MATCH (vcName) AGAINST ("John Doe");
+--------+
| iCount |
+--------+
| 65 |
+--------+
1 row in set (9.64 sec)


Вроде как не так уж и плохо. Только первый запрос тратит время, далее все очень быстро.
Получается что первым запросом в "key buffer" (или еще куда либо) затягивается FullText индекс, после чего последующие запросы используют его.

Однако есть тут проблема.... по истечении примерно 40 минут, новый полнотектсовый запрос опять выполняется долго. Видимо сервер удаляет из "key buffer" FullText индекс.


В связи с этим вопросы:
1) Как поместить FullText индекс в "key buffer" насовсем?
или
2) Как предотвратить удаление FullText индекса из "key buffer" ?


Alex
11 мар 07, 21:17    [3885146]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация FullText Search запроса  [new]
Nick Anikin
Member

Откуда: Москва
Сообщений: 2174
Alex Vader
1) Как поместить FullText индекс в "key buffer" насовсем?
2) Как предотвратить удаление FullText индекса из "key buffer" ?

наверное, все же так, как я написал

мне кажется, у вас могут быть следующие проблемы:
1) вы написали, что на сервере 4 гб памяти, а вы создали кеш на 3.2 гб + 3 гб, итого 6.2 гб + другие процессы на сервере память жрут, в результате, скорее всего, получается, что весь ваш hot_cache создается не в оперативной памяти, а в файле подкачки на жестком диске, и вы туда подгружаете full-text индекс
т.е. ситуация становится только хуже, чем если бы вы использовали общий key cache
2) а точно исполняются команды из mysqld_init.sql?

уменьшите key cache до 1.2 гб, а hot_cache до 2 гб, перезапустите mysql, сделайте все команды на подгрузку индексов вручную, проверьте, используется ли файл подкачки на сервере (если да - еще урежте key cache для проверки, так, чтобы hot_cache помещался в память)
11 мар 07, 21:42    [3885200]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация FullText Search запроса  [new]
Alex Vader
Member

Откуда: Самара
Сообщений: 40
нет, все верно. Кеш в обоих случаях по 3 гига. Это две приведенные РАЗНЫЕ конфигурации, запускаемые НЕ ЕДИНВРЕМЕНО. Видимо я просто неточно выразился.


В первом случае я использую
key_buffer_size = 3200M
hot_cache.key_buffer_size = 3000M
init_file=/home/XXX/mysql/mysqld_init.sql

Т.е. всего 3.2 гига на весь кеш, а 3 гига на hot_cache.
Скрипт mysqld_init.sql отрабатывается однозначно, мало того, я его даже отключал и руками после перезапуска сервера двала команды
CACHE INDEX biz.ct_business IN hot_cache;
LOAD INDEX INTO CACHE biz.ct_business;

Во втором случае я вообще не создаю hot_cahce , и никакой скрипт не выполняю. Только
key_buffer_size = 3200M

Своп при обоих конфигруациях практически не используется.. Т.е. виртуальная память имеет 168Кб использованной, и около 3 гигов не использованой. Так что памяти физической хватает.

А вот это я пробовал,

автор
уменьшите key cache до 1.2 гб, а hot_cache до 2 гб, перезапустите mysql, сделайте все команды на подгрузку индексов вручную, проверьте, используется ли файл подкачки на сервере (если да - еще урежте key cache для проверки, так, чтобы hot_cache помещался в память)


просто была мысль что несмотря на показываемый размер индексов в 1.2 гига (кстати файл индекса на эту таблицу весит примерно столько же) FullText поиск все равно тормозил. 3 гига я взял "для запасу", больше взять не получится, поскольку на остальные буферы и всякие там джойны тоже нужна память.


У меня складывается ощущение, что первый вариант (когда 3.2 гига на весь key_buffer, и ИЗ НИХ 3 гига на hot_cache) работает гораздо медленее чем вариант когда 3 гига на общий (т.е. неименованный буфер) потому, что FullText индекс не затягивается в hot_cache, а затягивается ТОЛЬКО в общий буфер (а в пером варианте общий буфер имеет всего 3.2Gb-3Gb=200 мегабайт).
Ну а поскольку в первом варианте ОБЩИЙ буфер намного меньше, индекс там надолго незадерживается (или даже не может вместится в него целиком, отсюда и стабильно низкая скорость).

Так что думаю вопрос "как затянуть FullText индекс в буфер/память" все еще актуален.
К сожалению нигде явно не написано что команда LOAD INDEX INTO CACHE затрагивает также и FullText индекс в буфер... Т.е. я предпологаю что затрагивает, но результаты приведенного в прошлом моем сообщении эксперимента почемуто говорят об обратном.
12 мар 07, 01:11    [3885437]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация FullText Search запроса  [new]
Nick Anikin
Member

Откуда: Москва
Сообщений: 2174
Alex Vader
первый вариант (когда 3.2 гига на весь key_buffer, и ИЗ НИХ 3 гига на hot_cache)
а с чего вы взяли, что ИЗ НИХ?
если в файле конфигурации у вас прописаны эти две строчки:
key_buffer_size = 3200M
hot_cache.key_buffer_size = 3000M
то они означают, что создается общий кеш объемом 3.2 гб ПЛЮС кеш с названием hot_cache объемом 3 гб
http://dev.mysql.com/doc/refman/5.1/en/structured-system-variables.html
12 мар 07, 10:51    [3886003]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация FullText Search запроса  [new]
Alex Vader
Member

Откуда: Самара
Сообщений: 40
Да, верно. Моя ошибка.

Поправил, вот только не помогло опять :(

Все так же некоторые запросы выполняются непредсказуемо долго .
12 мар 07, 18:58    [3889121]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация FullText Search запроса  [new]
Alex Vader
Member

Откуда: Самара
Сообщений: 40
За несколько дней проблему не решил. :(
Пришлось таки создавать таблицу в памяти куда запихивать поля BusinessID & vcName с обычными (не FullText) индексами, делать поиск слов по ней, а потом джойнить с оригинальной.

Криво, зато работает максимум 5 секунд.

У кого нибудь есть еще идеи как таки заставить родной FullText пахать 24/7/365 ?
16 мар 07, 21:31    [3909737]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация FullText Search запроса  [new]
upper
Member

Откуда: Msk
Сообщений: 167
Извиняюсь за ламерский вопрос, а что если простенький запрос для full-text выполнять каждые 20-25 минут(по расписнию), чтоб кэш не обнулялся, данные в этом кэше не устареют? или MySQL сама будет подтягивать новые данные по ходу?
8 дек 07, 19:33    [5022494]     Ответить | Цитировать Сообщить модератору
Все форумы / MySQL Ответить