lleo_kaganov (lleo_kaganov) wrote,
lleo_kaganov
lleo_kaganov

Category:

Вопрос про MySQL часть 2

это перепост заметки, оригинал находится на моем сайте: https://lleo.me/dnevnik/2020/06/07

По советам умных людей перевез базы сайта с MyISAM на InnoDB, написав себе для этого всяких новых кнопок в админке баз движка (которая уже немного по функционалу приближается к PhpMyAdmin :). И немного уже пожалел, что переехал на InnoDB.

Да, проблема инкрементального бэкапа решилась, он стал умнее: /backup 1450M /inc 3701042
Я правда всё равно не очень понимаю, зачем мне прилетели 3,7 мегабайта данных всякий раз, когда в базе не изменилось ровным счетом ничего... Но это уже лучше, чем тупо копировать полтора гига. В любом случае я случайно нашел в коде движка собственный экспорт баз в своем формате (оказывается, я это делал когда-то), чуть подправил его, и теперь думаю делать бэкап баз средствами движка, потому что это точно будет умнее и компактнее. Например, то, что у MariaDB занимает 1450M, у меня в простом формате движка заняло 500M. Но дело не в тот, это я как-нибудь сам сделаю, как будет время.

Проблема тут другая. У меня на сервере и раньше подтормаживал MySQL уже не первый год — сайт, как вы наверно не раз наблюдали, подвисает. А с переездом на InnoDB торможение стало таким сильным, что следующие полдня сайт вообще висел, загрузка CPU 400% и всё такое... Когда я поглядываю SHOW PROCESSLIST, вижу такое:


IdUserHostdbCommandTimeStateInfoProgress
18rootlocalhostdnevQuery321Copying to tmp tableSELECT c.`id`,c.`unic`,c.`group`,c.`Name`,c.`Text`,c.`Parent`,c.`Time`,c.`whois`,c.`rul`,c.`ans`,c.`0.000
27rootlocalhostdnevQuery314Copying to tmp tableSELECT c.`id`,c.`unic`,c.`group`,c.`Name`,c.`Text`,c.`Parent`,c.`Time`,c.`whois`,c.`rul`,c.`ans`,c.`0.000
46rootlocalhostdnevQuery309Copying to tmp tableSELECT c.`id`,c.`unic`,c.`group`,c.`Name`,c.`Text`,c.`Parent`,c.`Time`,c.`whois`,c.`rul`,c.`ans`,c.`0.000
76rootlocalhostdnevQuery282Copying to tmp tableSELECT c.`id`,c.`unic`,c.`group`,c.`Name`,c.`Text`,c.`Parent`,c.`Time`,c.`whois`,c.`rul`,c.`ans`,c.`0.000
80rootlocalhostNULLQuery0initSHOW PROCESSLIST0.000

Из чего становится понятно, что тормозит вот этот запрос:

=============== cut ===============
SELECT c.`id`,c.`unic`,c.`Name`,c.`Text`,c.`Parent`,c.`Time`,c.`whois`,c.`rul`,c.`ans`,c.`golos_plu`,c.`golos_min`,c.`scr`,c.`DateID`,c.`BRO`,c.`IPN`,
u.`capchakarma`,u.`mail`,u.`admin`,u.`openid`,u.`realname`,u.`login`,u.`img`,u.`time_reg`
FROM `dnevnik_comm` AS c
LEFT JOIN `db_unic` AS u
ON c.`unic`=u.`id`
WHERE c.`DateID`='$num'
ORDER BY c.`Time`
=============== /cut ===============

Это и правда самый сложный запрос в движке — формирование ленты комментариев, по крайней мере, очень частый. Он берет все комментарии из таблицы комментариев `dnevnik_comm`, относящиеся к номеру заметки $num, добавляет к ним по номеру автора его данные из таблицы посетителей `db_unic` (там этот номер называется `unic`, а тут исторически `id`), причем информации об авторе может не быть у комментариев 15-летней давности, там unic=0 Ну и сортирует по дате комментариев Time.

По индексам — у таблицы посетителей `db_unic` есть primary индекс `id`. У таблицы комментариев `dnevnik_comm` есть индексы PRIMARY `id`, `DateID` (`DateID`), `poset` (`unic`,`scr`) и `Parent` (`Parent`), который к нашей задаче сейчас не относится.

Вопрос специалистам: в этом моем запросе что-то не так? Его можно как-то оптимизировать? Или это нормально, что он выполняется долго и время от времени подвисает на длительное время?

Может, надо добавить индекс для `Time`, а иначе он ORDER BY `Time` не может толком сделать для результата (результаты-то выборки комментариев к одной заметке обычно не слишком велики)?

Я пока что отключил вывод простыни комментариев под заметками, чтобы этот запрос хотя бы выполнялся не для каждого посетителя, а только для тех, кто хочет почитать комментарии, нажав соответствующую кнопку.

UPD: Ого, обнаружил сейчас в движке еще один запрос, начинающийся с тех же букв, только еще сложнее — он работает по адресу /comm и показывает ленту новых комметариев:

=============== cut ===============
SELECT c.`id`,c.`unic`,c.`group`,c.`Name`,c.`Text`,c.`Parent`,c.`Time`,c.`whois`,c.`rul`,c.`ans`,c.`IPN`,
c.`golos_plu`,c.`golos_min`,c.`scr`,c.`DateID`,c.`BRO`,
u.`capchakarma`,u.`mail`,u.`admin`,
u.`realname`,u.`login`,u.`openid`,u.`img`,
«.($GLOBALS['admin']?»z.Access,z.num,»:'').»
z.`opt`,z.Access,z.`Date`,z.`DateDate`,z.`Header`,z.`view_counter`
FROM `dnevnik_comm` AS c
JOIN `dnevnik_zapisi` AS z
ON c.`DateID`=z.`num` " .(empty($acn)?'':" AND z.`acn`='$acn'"). "
LEFT JOIN «.$GLOBALS['db_unic'].» AS u ON c.`unic`=u.`id`
WHERE "
.($GLOBALS['admin']?»1":«z.`Access`='all' AND (c.`scr`='0' OR c.`unic`='«.$GLOBALS['unic'].»')")
.($mode=='one'?» AND c.`unic`='«.e($_GET['unic']).»'":"")
.» AND «.($ncom!='-'?»c.`Time`>'«.$lastcom.»' ORDER BY c.`Time`":«c.`Time`<'".$lastcom."' ORDER BY c.`Time` DESC")." LIMIT ".($lim+1)
=============== /cut ===============

Я не думаю, что кто-то пользуется лентой /comm Там 50 посетителей за все время было * , поэтому запрос редкий, и вряд-ли проблема в нем. Так навскидку-то он выглядит сильно ужаснее, потому что объединяет базу комментариев, посетителей и еще базу самих заметок `dnevnik_zapisi` (индексы для z.`Access`, z.`acn` и z.`num` в ней есть). Также, я гляжу, там используются из базы комментариев c.`scr` и c.`unic`, но у меня для этого там их общий индекс `poset` (`unic`,`scr`), вот только не уверен, что в запросе типа AND c.`scr`=0 OR c.`unic`=1 этот объединенный индекс чем-то поможет, наверно надо дополнительно продублировать его последнюю часть `scr`?



это перепост заметки, оригинал находится на моем сайте: https://lleo.me/dnevnik/2020/06/07
Subscribe

Recent Posts from This Journal

  • Post a new comment

    Error

    default userpic

    Your IP address will be recorded 

    When you submit the form an invisible reCAPTCHA check will be performed.
    You must follow the Privacy Policy and Google Terms of use.
  • 1 comment