Тормозит mysql, это кэш?

Question

[WebDev]

Подскажите, как решить проблему?
Оптимизировал mysql запросы, конфиги вроде в порядке. В explain все хорошо. Но при "первом" запросе он выполняется медленно.
"Первый" - это запрос, который очень давно не выполнялся, например несколько дней. При этом я копирую его из лога, повторяю с explain и он показывает, что все замечательно. Я понимаю, что запрос кэшируется, но почему он первый раз выполняется долго? Дело точно не в запросе, он элементарный, индексы проставлены.
В чем может быть дело?

Вот результат запуска mysqltuner

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +Aria +BLACKHOLE +CSV +FEDERATED +InnoDB +MRG_MYISAM

[--] Data in MyISAM tables: 2K (Tables: 1)
[--] Data in InnoDB tables: 22G (Tables: 75)
[!!] Total fragmented tables: 75

-------- Performance Metrics -------------------------------------------------
[--] Up for: 30d 21h 5m 23s (76M q [28.840 qps], 174K conn, TX: 430B, RX: 126B)
[--] Reads / Writes: 4% / 96%
[--] Binary logging is disabled
[--] Total buffers: 10.4G global + 2.8M per thread (1000 max threads)
[OK] Maximum reached memory usage: 10.5G (33.72% of installed RAM)
[OK] Maximum possible memory usage: 13.1G (42.15% of installed RAM)
[OK] Slow queries: 0% (288K/76M)
[OK] Highest usage of available connections: 3% (33/1000)
[OK] Aborted connections: 0.00%  (7/174482)
[OK] Query cache efficiency: 35.3% (1M cached / 5M selects)
[!!] Query cache prunes per day: 11392
[OK] Sorts requiring temporary tables: 0% (903 temp sorts / 328K sorts)
[OK] Temporary tables created on disk: 0% (4K on disk / 517K total)
[OK] Thread cache hit rate: 99% (33 created / 174K connections)
[OK] Table cache hit rate: 99% (309 open / 310 opened)
[OK] Open file limit used: 2% (24/1K)
[OK] Table locks acquired immediately: 99% (75M immediate / 75M locks)

-------- MyISAM Metrics -----------------------------------------------------
[!!] Key buffer used: 18.2% (24M used / 134M cache)
[OK] Key buffer size / total MyISAM indexes: 128.0M/101.0K

-------- InnoDB Metrics -----------------------------------------------------
[--] InnoDB is enabled.
[!!] InnoDB buffer pool / data size: 10.0G/22.9G
[!!] InnoDB buffer pool instances: 1
[OK] InnoDB Used buffer: 100.00% (655358 used/ 655359 total)
[OK] InnoDB Read buffer efficiency: 99.99% (329075204856 hits/ 329098929295 total)
[!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)
[OK] InnoDB log waits: 0.00% (0 waits / 64733582 writes)

-------- AriaDB Metrics -----------------------------------------------------
[--] AriaDB is disabled.

-------- Replication Metrics -------------------------------------------------
[--] No replication slave(s) for this server.
[--] This is a standalone server..

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
   
    Increasing the query_cache size over 128M may reduce performance
Variables to adjust:
    query_cache_size (> 128M) [see warning above]
    innodb_buffer_pool_size (>= 22G) if possible.
    innodb_buffer_pool_instances(=10)

Непонятно. Если выставить innodb_buffer_pool_size 22ГБ, то сервер будет всегда загружен на 70%? Это нормально? У меня сейчас 32ГБ памяти, из них постоянно занято 15.

Answer 1

[Макс]

у мускуля несколько кэшей. Кэшироваться может как план исполнения запроса, там и данные.
наиболее интересно данные - в 99% случаев наиболее значитмый эффект.

а у Вас, судя по описанию, либо запросы повторяются не слишком часто (и данные из кэше вытесняются), либо кэш небольшой (и он, опять-же, чистится)

попробуйте запустить mysqltuner и посмотрите на "Query cache prunes per day"

Comments

[WebDev]

Кэш ведь храниться в памяти? Это параметр innodb_buffer_pool_size, верно? Таблицы у меня в inno_db, этот параметр указан 10GB, а сам мускуль занимает 15ГБ из 32

[Макс]

WebDev: да, кэш в памяти. И да, это тот самый параметр.

[WebDev]

Макс: попробую запустить mysqltuner , спасибо

[WebDev]

Макс: подскажите, пожалуйста, что подправить. Результат mysqltuner дописал в вопрос

[Макс]

WebDev: увеличьте пулл. Если на сервере крутится только мускуль, "драться за память" ему нескем. Операционке достаточно пол-гига - гиг.

Answer 2

[Дмитрий Энтелис]

Моё личное imho - пока у Вас нет какой то значимой нагрузки (и тонко-настраиваемой системы профилирования) - query cache лучше отключить вообще.

Поясню зачем: включив cache Вы наблюдаете прирост производительности на nn% запросов, но вместе с тем теряете возможность оперативно обнаруживать не оптимизированные запросы.
С отключенным кешом вы СРАЗУ видите просадку производительности, сразу применяете меры (добавляете индексы, денормализацию, просто переписываете запросы etc).

Answer 3

[AlikDex]

не будет браться из кеша, т.к. запрос другой. Мускул кеширует по строке запроса, т.е. она должна быть идентичной абсолютно. Но также принимаются во внимание некоторые функции. Например запросы с функцией NOW() не кешируются вообще.

Answer 4

[Melkij]

правильно ли я понимаю, что mysql кэширует не сам запрос, а как бы схему его выполнения?

Нет, mysql как раз кеширует непосредственно результат конкретного запроса.
План запроса строится заново для каждого запроса, не найденного в query cache.
query cache может быть и выключен, тогда mysql от запроса не кеширует ни план, ни результат.

Если запрос выполняется к редкоиспользуемой части данных - значит скорей всего пришлось читать с диска. Это варьируется от медленно до смертельно медленно в зависимости от дисков. Затем данные будут жить в кеше данных mysql (см. тюнинг используемых storage engine) и в кеше операционной системы (плюс в query cache результат запроса), пока не будут оттуда вытеснены другими более нужными данными. Сами индексы тоже могут выгружаться из памяти и даже index only scan может пойти на медленный диск.
Запрос, которому пришлось лезть на диск и запрос, все данные для которого уже оказались в памяти - две огромные разницы даже для приличных SSD.