MySQL Невольооо! (само за програмисти)

May 31, 2009

Много исках да направя в TBL опция да се добавят блогове, които хем да се агрегират, хем да не спамят. 2 различни типа са:
– Блогове за поезия (идея, да излизат в класацията, но да не излизат в статии, освен ако са ви в “любими”)
– Блогове на чужди езици (да ви излизат само, ако са ви “любими”)

Написах допълнението в заявката (1 LEFT JOIN и 1 OR), пуснах го и работеше (0.00 във всички тестови случаи). Пуснах го в TBL. Но се оказа, че нещо не е наред – в непроверени от мен други случаи, същата заявка беше бавна. Бавеше се, в зависимост от махането и поставянето на 2 несвързани с промяната join-a на маловажни таблици. Премахнах тези таблици… и сървъра умря.

Оптимизирах, мазах, слагах индекси, махах индекси и така от 00:00 до 04:00, после от 11:00 до около 16:00. Обяснявах на кю-то на приятели програмисти фрагменти от проблема.

Най-накрая, докато си говорих с Дейв, стигнах до следната последователност от действия. Добавям индекс, половината заявки се чупят. Дропя го (или добавям join с някоя от онези глупави таблици) – заявките тръгват.

mysql> alter table topblogposts add index ix_blog_id (blog_id);
Query OK, 134352 rows affected (24.84 sec)
Records: 134352 Duplicates: 0 Warnings: 0

mysql> explain
SELECT SQL_NO_CACHE p.url
FROM topblogposts p
JOIN topblogs t on p.blog_id=t.blog_id AND t.approved=1
ORDER BY topdate desc limit 0,20;

+----+-------------+-------+-------+------------------------+------------+---------+-----------------+------+-----------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+------------------------+------------+---------+-----------------+------+-----------------------------------------------------------+
| 1 | SIMPLE | t | index | PRIMARY,ix_r_pk,ix_a | ix_r_pk | 19 | NULL | 1222 | Using where; Using index; Using temporary; Using filesort |
| 1 | SIMPLE | p | ref | blog_id,kys,ix_blog_id | ix_blog_id | 4 | dzver.t.blog_id | 87 | |
+----+-------------+-------+-------+------------------------+------------+---------+-----------------+------+-----------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> alter table topblogposts drop index ix_blog_id (blog_id);
Query OK, 134352 rows affected

mysql> explain
SELECT SQL_NO_CACHE p.url
FROM topblogposts p
JOIN topblogs t on p.blog_id=t.blog_id
AND t.approved=1
ORDER BY topdate desc limit 0,20;

+----+-------------+-------+-------+----------------------+---------+---------+-----------------------+--------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+----------------------+---------+---------+-----------------------+--------+--------------------------+
| 1 | SIMPLE | p | index | blog_id,kys | ixd | 9 | NULL | 134353 | |
| 1 | SIMPLE | t | ref | PRIMARY,ix_r_pk,ix_a | ix_r_pk | 9 | dzver.p.blog_id,const | 1 | Using where; Using index |
+----+-------------+-------+-------+----------------------+---------+---------+-----------------------+--------+--------------------------+
2 rows in set (0.00 sec)

Решението ми беше налично още вчера – SELECT FROM topblogposts USE INDEX (ixd) (ixd съм кръстил индекса, който трябва да се ползва в случая). Лошото вчера беше, че има случаи, в които трябва да се ползва друг индекс и аз трябва с ифове да го играя MySQL оптимайзър. Та, принудих се днес, да направя още 2 денормализации, за да проработи пущината.

И проработи.

Дейв ми обясни някои работи, които наскоро обяснявах за Нюза – за дъмпването на още една база и за експерименти с нея.

Обичам да програмирам и да правя нови неща, но адски мразя, когато цял уикенд отиде в блъскане на главата в стената. Не мога да дам отговор на въпроса, защо MySQL избира неподходящите индекси. Четох форуми, рових в bugs.mysql.org. Ако имате идея – пишете.

Публикувано в: TBL 17 коментара RSS 2.0

Ако постът ви харесва, цъкнете на сърцето:

Коментари

17 коментара на “MySQL Невольооо! (само за програмисти)”

  1. ju on May 31st, 2009 19:29

    Костов? 😉

  2. Свилен on May 31st, 2009 19:36

    Ако махнеш ORDER BY как стоят нещата?
    InnoDB ли ползваш за storage engine?

  3. Марио Пешев on May 31st, 2009 20:09

    И аз понякога получавам WARNING-и, че ползвам неправилните индекси. Според мен е проблем с базата – с Oracle не съм имал подобен проблем.

    Колкото до проверките на ниво база, пробвал ли си да си оптимизираш някои функции със stored procedures? Вече ги има и в MySQL от версия 5 насам.

  4. Васил Колев on May 31st, 2009 20:14

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

  5. Калоян К. Цветков on May 31st, 2009 20:27

    Едно от местата, които аз чета в такива ситуации е http://www.mysqlperformanceblog.com/ и от скоро, “High Performance MySQL” 😉

  6. Lyubomir Petrov on May 31st, 2009 20:29

    InnoDB или MyISAM ползваш за тея таблици с които имаш проблеми ?

  7. dzver on May 31st, 2009 21:22

    5.0.51a-24
    Engine е MyISAM

    @Свилен, махането на order by оправя нещата.

  8. Свилен on May 31st, 2009 21:48

    Пробвай да сложиш index на полето topdate, понеже от поста горе, не разбирам дали има.

  9. Свилен on May 31st, 2009 21:57

    Този пост може да не го публикуваш.

    По долу идеята ми беше да събереш сортирани по date определен брой редове, които биха свършили работа при листинг и биха задоволили външния SELECT.

    Виж аз каква хитрина използвах за да смъкна от 8сек на 0.12сек

    CREATE TABLE `stats` (
    `id` int(11) NOT NULL auto_increment,
    `search_string` varchar(255) collate utf8_unicode_ci default NULL,
    `filetype` enum(”,’test’,’alabala’,’other’) collate utf8_unicode_ci NOT NULL,
    `date` timestamp NOT NULL default CURRENT_TIMESTAMP
    PRIMARY KEY (`id`),
    KEY `search_string` (`search_string`),
    KEY `filetype` (`filetype`),
    KEY `date` (`date`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

    SELECT DISTINCT(search_string) FROM stats
    WHERE search_string NOT REGEXP (‘(keyword1|keyword2)’) AND filetype = ‘test’
    ORDER BY date DESC LIMIT 10

    execution time: 8 сек

    SELECT DISTINCT(s1.search_string) FROM
    (
    SELECT s.search_string, s.filetype
    FROM stats s
    WHERE s.search_string NOT REGEXP (‘(keyword1|keyword2)’)
    ORDER BY s.date DESC LIMIT 100
    ) s1
    WHERE s1.filetype = ‘test’
    LIMIT 10

    execution time: 0.12 сек

  10. dzver on May 31st, 2009 22:02

    текущата каца с индекси е такава:

    topblogposts

    PK post_id
    ix_blog_id (blog_id, url)
    ixd (topdate)
    kys (blog_id, url, topdate)
    ixc (comments)
    ixv (votes)
    ix_btn (url)
    ixd2 (topdate, blog_id)

    имаше и самостоятелен индекс по blog_id, който махнах с оглед описания по-горе проблем.

  11. dzver on May 31st, 2009 22:20

    sorry, като съм публикувал предния ми коментар вече е имало отговор, просто не съм го видял, чета… ps. ако искаш ще го изтрия.

    идеята на заявката в TBL е, в зависимост от разни условия се добавят или премахват JOIN-и. Заявката е 1 и има разнообразни варианти. Не мога да преценя, дали текущото състояние е по-добро или по-лошо от обикновено, защото сървърът се понатовари от друг сайт, а от месеци не ми се е налагало да оптимизирам. mysql_slow е изключен и няма да бъде включен поне до утре.

  12. Марто on June 1st, 2009 03:48

    Ха сега дойде на моя територия:)

    Като цяло грешката ти идва от употребата на join!

    Join-а е полезна функция, която не може да си позволяваме да използвам на големи таблици – ефекта сам го виждаш:)

    Пиши ми на Q-то и ще оправим нещата.

  13. Ivan on June 1st, 2009 07:39

    И аз си мисля че проблема е в JOIN. без да съм чел много подробно за какво става въпрос (7 и половина сутринта е и не съм пил кафе) си мисля че един UNION би свършил по-добра работа в случая

  14. dzver on June 1st, 2009 09:42

    Наистина съм озадачен какво предложение ще даде Марто 🙂

  15. Гонзо on June 1st, 2009 09:49

    Проблема на е в JOIN. Проблемът е, че върху голяма таблица трябва да се извърши сортиране и оптимизатора не успява да определи правилно индекса, по който да сортира. И се стига до filesort, което е много бавна операция. Повече тук:
    http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html
    Най-простото решение е да включиш в WHERE клаузите условие по полето, по което сортираш, но не винаги помага.
    Понякога вместо да указвам кой индекс да ползва, се оказва удачно да кажа на MySQL кой индекс ДА НЕ ползва чрез IGNORE INDEX (някой си).

  16. kjnhij on June 1st, 2009 11:25

    join било функция 🙂
    по принцип тези проблеми се решават много лесно, като смениш mysql с postgresql.

  17. dzver on June 1st, 2009 13:08

    Ха! Гонзо, това е супер идея 🙂 IGNORE INDEX би било много по-ефективно от USE INDEX. Не знаех изобщо за такава опция. Благодаря.

    Благодаря и на Марто, който ми обърна внимание, че ми е много малък key_buffer_size.

Оставете отговор