|
Задавайте вопросы, мы ответим
Вы не зашли.
Страниц: 1 2
Здравствуйте! Помогите, пожалуйста, решить следующую проблему. Есть таблица с ~ 10 млн. записей:
CREATE TABLE `indexdata`.`word_weight` (
`DocID` mediumint(8) unsigned NOT NULL DEFAULT '0',
`WordID` mediumint(8) unsigned NOT NULL DEFAULT '0',
`Weight` float(11,7) DEFAULT NULL,
`WeigtID` int(20) unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`WeigtID`),
UNIQUE KEY `Index_2` (`WordID`,`DocID`) USING BTREE,
UNIQUE KEY `Index_3` (`DocID`,`WordID`)
) ENGINE=MyISAM AUTO_INCREMENT=8104793 DEFAULT CHARSET=utf8;
Необходимо делать из нее выборку DocID, в которых встречаются все слова с определенными WordID (их задает пользователь).
Делаю это следующим образом (запрос генерирую программно, ниже представлен вариант запроса для четырех слов):
SELECT DocID, SUM(Weight) FROM indexdata.word_weight WHERE
WordID = 69690 OR WordID = 204534 OR WordID = 22544 OR WordID = 46839 GROUP BY DocID having count(*) > 3
(SUM(Weight) - сумма весов для данных слов).
Время выполнения порядка 4-5 секунд, что не устраивает (компьютер RAM 2 Гб, CPU Intel 2 Ггц). Основное время уходит на Copy data to temporary table (tmp_table_size = 200M).
Можно ли как-нибудь изменить данный запрос или его оптимизировать, чтобы время выполнения было на уровне 1 с? Заранее благодарен за помощь. P.S. Explain данного запроса приложен ниже.
Неактивен
Индекс используется правильный. Судя по всему, основное время занимает лазание по диску за колонкой Weight.
Попробуйте дотянуть индекс до Weight: KEY(WordID, DocID, Weight).
Неактивен
Убрал Sum(Weight) вообще из запроса. Скорость немного улучшилась, но все равно на уровне 3 секунд...
Неактивен
А что показывает EXPLAIN для запроса без SUM(Weight)?
Неактивен
Тоже самое, что и с SUM(Weight)
Неактивен
Что показывает EXPLAIN для следующих двух запросов:
SELECT WordID, DocID
FROM indexdata
WHERE WordID = ... OR WordID = ...
GROUP BY WordID, DocID
SELECT WordID, DocID
FROM indexdata
WHERE WordID = ... OR WordID = ...
GROUP BY WordID, DocID
HAVING COUNT(*) > 3
?
Неактивен
Прогнал через explain. Результаты приложил. Как я понял, сейчас MySQL не использует temporary table. Протестирую по быстродействию. Результаты сообщу. Может и быстрее будет.
Неактивен
Да, теперь индекс используется. Это достигнуто искусственным включением его первой части (WordID) в GROUP BY.
Следующий шаг - включить в индекс колонку Weight (чтобы и за Weight не приходилось идти на диск) и попробовать ваш самый первый запрос.
Неактивен
LazY, протестировал такого вида запросы, но они работать не будут, т.к. идет упорядочивание по парам WordID и DocID и при любом значении having count(*) > отличным от нуля, будут возвращать пустой результат (т.к. DocID повторяется в таблице, а WordID относительно DocID уникально). Может еще какие-соображения есть?
Неактивен
А что показывает EXPLAIN SELECT WordID, DocID FROM index WHERE Wordid = ... OR ... — без GROUP BY?
И сколько такой запрос выполняется?
Неактивен
Explain запроса приложил. Выполняется ~ 3.5 c. Особо выигрыша не дает. Может условие через OR для MySQL сложно выполнить?
Неактивен
Нет, OR в данном случае — это все равно, что IN, т.е. выборка RANGE по ключу.
EXPLAIN у вас правильный и хороший: Using where, Using index. Второе означает, что MySQL вообще не лезет на диск, и 3.5 секунды занимает операция с индексом. Это необычно медленно. Скорее всего, индекс у вас слишком большой и не помещается в key_buffer.
Будьте добры SHOW TABLE STATUS LIKE 'word_weight' и SHOW VARIABLES LIKE 'key_buffer%'
Если есть возможность - выполните эти запросы в консоли mysql с \G на конце. Т.е., например,
SHOW TABLE STATUS LIKE 'word_weight'\G
Так будет удобнее читать (да и писать) и не потребуется загружать картинки.
Неактивен
Приложил интересующие Вас запросы. Сравнил параметры. Вроде значения key_buffer хватает, но все таки есть индексы и в других таблицах. Не подскажите, каким оператором загрузить весь индекс из таблицы word_weight в память?
Неактивен
Я так понимаю, что если такого вида запрос будет быстро выполняться, то мне придеться его программно обрабатывать вместо GROUP BY ... HAVING()...
Неактивен
Не подскажите, каким оператором загрузить весь индекс из таблицы word_weight в память?
Вообще это делается командой LOAD INDEX INTO CACHE:
LOAD INDEX INTO CACHE word_weight INDEX (index_2)
Если опустить часть INDEX (..), будут загружены все индексы таблицы.
Вообще говоря, есть возможность выделить конкретной таблице отдельный буфер ключей, которым будет пользоваться только эта таблица, а другие не будут её оттуда вытеснять. Однако для этого придется потрудиться.
Прежде всего, нужно будет выполнить две команды:
SET GLOBAL word_weight_keycache.key_buffer_size = 300*1024*1024;
(запись вида что-то.key_buffer_size указывает на буфер ключей с именем 'что-то', устанавливая его значение в байтах; см. http://dev.mysql.com/doc/refman/5.1/en/ … aches.html).
И вторая команда:
CACHE INDEX word_weight INDEX (index_2) IN word_weight_keycache;
С этого момента индекс index_2 таблицы word_weight будет пользоваться специальным буфером (если нужно сделать это для всех индексов таблицы — стоит опустить часть INDEX (...); подробнее см. описание команды CACHE INDEX).
Индекс будет автоматически загружен в буфер при первом обращении к нему. Чтобы не дожидаться этого, можно загрузить индекс в буфер сразу:
LOAD INDEX INTO CACHE word_weight INDEX (index_2)
Только вот есть одна проблема: при перезапуске сервера эти установки не сохраняются, и если вы собираетесь пользоваться ими все время, нужно сделать еще две вещи:
1. В секции [mysqld] конфигурационного файла MySQL нужно добавить установку вашего буфера ключей:
word_weight_keycache.key_buffer_size = 300M
2. Нужно создать sql-файл, который будет выполняться каждый раз при запуске сервера, который должен содержать вышеуказанную команду CACHE INDEX. Это делается с помощью директивы init_file в секции [mysqld]. Например,
[mysqld]
...
init_file=/var/lib/mysql/start.sql
...
В нём же, при желании, можно указать сразу и LOAD INDEX.
Я так понимаю, что если такого вида запрос будет быстро выполняться, то мне придеться его программно обрабатывать вместо GROUP BY ... HAVING()...
Не факт. До конца станет понятно на практике, когда, ускорив этот запрос, вы перейдёте к запросу с GROUP BY и HAVING(). По идее, выполняться он должен примерно с той же скоростью, как и тот, что вы сейчас тестируете, т.к. тут лимитирующая стадия — поход за данными, а данные им обоим нужны одни и те же.
Попробуйте — а там посмотрим.
Неактивен
LaZy, ну прямо чудеса. Решил пока sql файл не делать. Выполнил следующее. Закинул в память все индексы из word_weight посредством запросов
SET GLOBAL keycache1.key_buffer_size = 300*1024*1024;
CACHE INDEX indexdata.word_weight IN keycache1;
LOAD INDEX INTO CACHE indexdata.word_weight;
Выполнил запрос
SELECT word_weight.DocID
FROM indexdata.word_weight
WHERE WordID = 892 OR WordID = 23939 OR WordID = 53020 OR WordID = 86842
OR WordID = 31615 OR WordID = 22544 OR WordID = 12707
OR WordID = 68131;
Explain: Index: Index_2; Rows: 38695; Extra: Using where; Using index.
Время выполнения: 7,6 секунд !!!!!!
Уже потерял надежду на оптимизацию ;-)
Неактивен
Да, действительно очень странно.
Скажите, а как вы измеряете время выполнения запроса?
Насколько нагружена система в остальном? Не занят ли процессор?
Неактивен
LaZy, для чистоты эксперимента поступаю следующим образом:
перезагружаю компьютер, загружаю индексы в память. В окне MySQL Query Browser выполняю запрос. Время пишет после выполнения запроса там же. На компьютере в этот момент ничего не выполняется. Процессор процентов на 80 занят mysqld. Вот такие дела.
Неактивен
После этого тестирую на разные запросы с различным количеством WordID. Время выполнения от 2 до 4 секунд в зависимости от количества условий OR.
Неактивен
MySQL Query Browser может тратить много времени на построение отображения запроса.
Попробуйте запустить клиент mysql и посмотреть время там.
Если время и там медленное — давайте попробуем детально посмотреть на этапы выполнения запроса. Для этого выполните его еще раз, а потом сразу после него выполните запрос SHOW PROFILE (см. http://webew.ru/articles/2732.webew).
Неактивен
4 тысячи строк в секунду. Мне кажется, Вы упираетесь не в MySQL, а в клиентское
приложение, которое не успевает эти строки читать 
Попробуйте аналогичный SELECT COUNT(*) — запрос должен выполняться моментально.
Неактивен
Запрос
SELECT count(*)
FROM indexdata.word_weight WHERE
WordID = 26570 OR WordID = 74843 OR WordID = 16347 OR WordID = 892;
Результат 11077 строк
1 row fetched in 0,0010 s (1.1337 s);
Ощутимо, что выполнялся запрос ~ 1 s. Неясно, что за цифра 0,0010 s?
Неактивен
Кстати, посмотрел что пишет в State запроса - основное время тратится на Sending Data
Неактивен
основное время тратится на Sending Data
Вот!
Это говорит о том, что вы упираетесь в приложение, которое медленно принимает данные, а не в производительность MySQL.
Если в вашем рабочем приложении вы эти данные выводите человеку, вряд ли ему нужны все 20 000 записей, поэтому у вас наверняка будет LIMIT и клиентскому приложению станет полегче.
Неактивен
Страниц: 1 2
