Бавна заявка след надграждане на mysql от 5.5 на 5.6

Надстройваме mysql от 5.5 на 5.6 и някои заявки вече са убийствено бавни.

Заявките, които преди отнемаха 0,005 секунди, сега отнемат 49 секунди.

Заявките на 5.6 пропускат индексите, изглежда:

+----+-------------+-------+-------+----------------------------------------------------+---------+---------+------+--------+-------------+
| id | select_type | table | type  | possible_keys                                      | key     | key_len | ref  | rows   | Extra       |
+----+-------------+-------+-------+----------------------------------------------------+---------+---------+------+--------+-------------+
|  1 | SIMPLE      | pens  | index | index_contents_on_slug,index_contents_on_slug_hash | PRIMARY | 4       | NULL | 471440 | Using where |
+----+-------------+-------+-------+----------------------------------------------------+---------+---------+------+--------+-------------+
1 row in set (0.00 sec)

Но не се пропускат на 5.5:

+----+-------------+-------+-------------+----------------------------------------------------+----------------------------------------------------+---------+------+------+----------------------------------------------------------------------------------------------+
| id | select_type | table | type        | possible_keys                                      | key                                                | key_len | ref  | rows | Extra                                                                                        |
+----+-------------+-------+-------------+----------------------------------------------------+----------------------------------------------------+---------+------+------+----------------------------------------------------------------------------------------------+
|  1 | SIMPLE      | pens  | index_merge | index_contents_on_slug,index_contents_on_slug_hash | index_contents_on_slug_hash,index_contents_on_slug | 768,768 | NULL |    2 | Using union(index_contents_on_slug_hash,index_contents_on_slug); Using where; Using filesort |
+----+-------------+-------+-------------+----------------------------------------------------+----------------------------------------------------+---------+------+------+----------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

И двете DB са създадени от един и същ дъмп на mysql.

Тези индекси не се ли конструират, когато направя импортирането на 5.6? Как да принудя създаването на индекс?

Заявката:

SELECT  `pens`.* FROM `pens`  WHERE (slug_hash = 'style' OR slug = 'style') ORDER BY `pens`.`id` DESC LIMIT 1

Редактиране: Премахнахте схемата


person timsabat    schedule 29.07.2013    source източник
comment
Можете ли да покажете схемата на таблицата и заявката?   -  person Flo Doe    schedule 29.07.2013
comment
Можете да пренапишете заявката като UNION или да опитате IGNORE INDEX (PRIMARY). Не съм сигурен, че игнорирането ще работи в този случай, но си струва да опитате.   -  person Vatev    schedule 29.07.2013
comment
Добавена е още информация за схемата. Както можете да видите, това е единична таблица, а не обединение. Изглежда, че в заявката липсва индексът на slug и slug_hash   -  person timsabat    schedule 29.07.2013


Отговори (2)


В крайна сметка приетият отговор по-горе е правилен.

Помощта от @RandomSeed ме накара да мисля в правилната посока. По принцип плановете за оптимизация, създадени в 5.6, са значително различни от тези в 5.5, така че вероятно ще трябва да преработите вашата заявка, подобно на мен.

В крайна сметка не използвах FORCE INDEX, но вместо това премахнах части от заявката, докато установя какво причинява 5.6 да пропусне индекса. След това преработих логиката на приложението, за да се справя с това.

person timsabat    schedule 30.07.2013

Бавната заявка във v5.6 е причинена от това, че двигателят не е в състояние или е решил да не обедини двата съответни индекса (index_contents_on_slug_hash, index_contents_on_slug), за да обработи вашата заявка. Не забравяйте, че една заявка може да използва само един индекс на таблица. За да може да се възползва от няколко индекса на една и съща таблица, трябва предварително да обедини тези индекси в движение в един (в паметта). Това е значението на index_merge и Using union(...) забележки във вашия план за изпълнение. Това очевидно отнема време и памет.

Бързо решение (и вероятно предпочитано решение така или иначе): добавете индекс с две колони на slug и slug_hash.

ALTER TABLE pens ADD INDEX index_contents_on_slug_and_slug_hash (
    slug, slug_hash
);

Сега вашият нов сървър вероятно не може да обедини тези индекси, защото води до индекс, който е твърде голям, за да се побере в буфера. Вашият нов сървър вероятно има много по-малка стойност за key_buffer_size (ако таблицата е MyISAM) или за innodb_buffer_pool_size (ако InnoDB), отколкото имаше в по-старата ви инсталация.

person RandomSeed    schedule 29.07.2013
comment
Все още не ми достига. Повиших innodb_buffer_pool_size така: | innodb_buffer_pool_size | 1572864000 | и рестартирах. Добавих и индекса, който предложихте по-горе, и все още получавам 45 секунди заявка, която не използва индексите. Някакви други предложения? - person timsabat; 30.07.2013
comment
Опитайте да добавите клауза FORCE INDEX (index_contents_on_slug_hash, index_contents_on_slug) (точно преди клаузата WHERE). Също така опитайте да премахнете клаузата ORDER BY. По-различен ли е планът за изпълнение? - person RandomSeed; 30.07.2013
comment
Хм, значи FORCE INDEX помогна, но не ни доведе до приемливо място. 10s при първото запитване и 9s при всяко следващо. Има ли значение, че съм преминал от Mac OSx и 5.5, инсталиран от HomeBrew, и 5.6, инсталиран от ubuntu .deb, предоставен от mysql? Освен това инсталацията 5.6 работи на Vagrant VM. Мислех, че ще видя незначителен хит в изпълнението, но нищо подобно на порядъка на величината, както имам тук. - person timsabat; 30.07.2013
comment
Не мога да отговоря със сигурност, но не трябва. Винаги обаче бих внимавал да стартирам MySQL във виртуална машина. Но моля, нека сравним това, което е сравнимо. Как изглежда новият план(ове) за изпълнение? - person RandomSeed; 30.07.2013
comment
Като цяло, нека оставим VM извън това, особено. тъй като все още не достигаме до индекса: gist.github.com/tsabat/6108206 - person timsabat; 30.07.2013
comment
И без клаузата ORDER BY? - person RandomSeed; 30.07.2013
comment
Последно предложение: опитайте да преизчислите статистическите данни на таблицата с ANALYZE TABLE pens. Страхувам се, че идеите ми се изчерпват. - person RandomSeed; 30.07.2013
comment
нека да продължим тази дискусия в чата - person timsabat; 30.07.2013