MySQL. Увеличение производительности. Часть 2 — Кэширование запросов

http://www.mysql.com/
http://www.mysql.com/

Одним из инструментов повышения производительности MySQL, как и большинства других СУБД, является кэширование запросов. Однако преимущества использования кэша MySQL могут сойти на нет или даже может быть ухудшение производительности, если при настройке кэша не было полного представления о принципах и отличиях его работы по сравнению с другими СУБД. В этой статье я постараюсь как можно более подробно описать все преимущества, недостатки и нюансы использования кэширования sql-запросов.

Первая часть серии статей: MySQL. Увеличение производительности. Часть 1 — Кэширование данных

Принцип работы

В отличие от других СУБД, MySQL кэширует не планы запросов, а сам текст запросов и результаты их выполнения, что четко прописано в официальном руководстве:

The query cache stores the text of a SELECT statement together with the corresponding result that was sent to the client. If an identical statement is received later, the server retrieves the results from the query cache rather than parsing and executing the statement again. The query cache is shared among sessions, so a result set generated by one client can be sent in response to the same query issued by another client.

Сам запрос и результат его выполнения хранится в виде хэша и это накладывает некоторые ограничения использования, а именно: идентичные по структуре запросы должны быть одинаковыми по написанию «байт в байт», иначе это будут разные запросы (запросы «SELECT * FROM tbl_name» и «Select * from tbl_name» являются разными — пример из руководства). В кэш могут попасть только запросы, начинающиеся на «SEL». До версии MySQL 5.0 (в последующих версиях баг исправлен) запросы, начинающиеся на пробел или комментарий, в кэш попасть могут, но извлечь их не получится. Описанные выше особенности, главным образом, стоит учитывать разработчикам баз данных, иначе использование кэширования запросов уже будет неэффективным.

При включении кэша, в памяти выделяется блок данных, равный по размеру значению переменной query_cache_size 1 (нулевой размер означает, что кэширование отключено). Размер переменной рекомендуется определять экспериментальным путем, но не менее 40КВ для записи служебной информации. На практике это означает, что с увеличением размера кэша также увеличиваются накладные расходы на его обслуживание и при переходе определенного порога, преимущества кэширования могут оказаться ниже, чем появившиеся недостатки. Обычно размер кэша в несколько сотен мегабайт уже будет являться избыточным 2.

Данные в кэше хранятся блоками. При выполнении запроса, сервер последовательно выделяет блоки данных для записи результата, размер блока определяется переменной query_cache_min_res_unit 3.

Важным моментом является то, что в кэш будут попадать далеко не все запросы, даже без каких-либо ограничений со стороны типа кэша (см. ниже). Ограничения зависят от использования определенных функций, UDF, локальных переменных, выборок из системных или временных таблиц и т.п., подробнее можно прочитать в официальном руководстве 4.

Один и тот же запрос, данные из которого взяты напрямую из базы данных или выбраны из кэша, должен возвращать одинаковый результат. Поэтому если в таблице произошли любые изменения, то связанные с ней запросы должны быть непременно удалены из кэша, что и происходит при выполнении с таблицей одной или нескольких операций из: INSERT, UPDATE, DELETE, TRUNCATE TABLE, ALTER TABLE, DROP TABLE, или DROP DATABASE.

На этом о принципах функционирования все, переходим к следующей главе.

Кэшируйте правильно

Как было сказано выше, далеко не во всех ситуациях использование кэширования запросов будет давать выигрыш в производительности. Чтобы однозначно ответить на вопрос в каких случаях использовать кэш, надо понимать специфику работы именного вашего сервера баз данных. Запросы из таблиц с частым обновлением данных необходимо будет обновлять в кэше при каждом изменении хотя бы одной записи в таблице, что не имеет смысла в принципе и в конечном итоге обернется для вас снижением производительности. Однако практически в каждой базе данных, при условии что спроектирована она должным образом, найдутся таблицы, данные в которых не изменяются или изменяются достаточно редко. Таким образом, может возникнуть вполне логичная мысль кэшировать только запросы из определенных таблиц, число операций DML 5 с которыми на порядки ниже, чем DQL.

Благо в MySQL есть возможность избирательного кэширования и определяется она значением переменной query_cache_type 6, которая может принимать три значения:

0 или OFF Не кэшировать или не извлекать результаты из кэша запросов. Стоит отметить, что эта опция не отключает буфер кэша в оперативной памяти. Чтобы его отключить, вы должны установить переменную размера кэша в значение 0.
1 или ON Кэшировать все результаты запросов, которые являются кэшируемыми, кроме запросов, начинающихся на SELECT SQL_NO_CACHE.
2 или DEMAND Кэшировать только результаты запросов, которые, во-первых, поддаются кэшированию, а во-вторых, начинаются на SELECT SQL_CACHE.

Если есть необходимость кэшировать только определенные запросы, рекомендуется в них напрямую указать необходимость кэширования с помощью инструкции SELECT SQL_CACHE, а также выставить переменную типа кэша в значение 2 или DEMAND.

Важным моментом в использовании кэша является максимальное ограничение размера результата запроса в 1МВ (значение по умолчанию). Запросы, результат которых больше этого значения, не попадут в кэш. Поэтому если у вас такие запросы могут быть, рекомендуется увеличить значение переменной query_cache_limit 7 до необходимого.

Основные показатели производительности кэша

Помочь определить, когда увеличение размера кэша будет полезным, может анализ данных индикатора Qcache_lowmem_prunes 8, который отображает количество удаленных из кэша запросов по причине нехватки памяти. Анализировать этот индикатор нужно совместно с Qcache_free_memory 9. Если происходит удаление запросов из памяти, в то время когда свободного места предостаточно, это может говорить о сильной фрагментированности памяти — невозможно записать запрос и результат его выполнения целиком, поскольку нет непрерывного свободного участка необходимой длины, а лишь мелкие свободные фрагменты. Это может наблюдаться когда есть большое количество запросов с небольшим по объему результатом. В этом случае наименее используемые кэшированные запросы постепенно вытесняются из кэша 10.

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

Процент свободной памяти:

Free Cache Memory (%)=Qcache_free_memory/query_cache_size*100

Процент свободных блоков:

Free Cache Blocks (%)=Qcache_free_blocks 11/Qcache_total_blocks 12*100

Процент вытесненных из кэша запросов по отношению к общему количеству запросов к кэшу:

Prunes and Total Queries Ratio (%) 13=Qcache_lowmem_prunes/Queries 14*100

Анализировать данные необходимо следующим образом: оптимальный размер свободной памяти, исключительно на мой взгляд, должен быть не менее 30% от общего объема. При этом процент свободных блоков минимально может опускаться к 50% от общего количество блоков кэша 15. Минимальное абсолютное значение свободных блоков равно 1 (сразу после выполнения дефрагментации или очистки кэша — FLUSH QUERY CACHE 16 и RESET QUERY CACHE соответственно).

Также для оценки эффективности кэширования на некоторых ресурсах рекомендуют использовать формулу:

Select and Qcache Ratio (%) 17=Qcache_hits 18/(Qcache_hits+Com_select)*100

Однако на мой взгляд эта характеристика является немного абстрактной и применимой далеко не во всех ситуациях (почему, постараюсь ответить ниже). Так, хоть и формула отображает процентный вес обращений к кэшированным запросам (Qcache_hits) к обращениям к диску (Com_select), но ответить на вопрос «что нужно сделать для оптимизации» она не может.

Окончательную точку в размышлениях «все же сколько запросов из общего числа потенциально могут попасть в кэш?» может поставить анализ отношения операций INSERT/UPDATE/DELETE/REPLACE к общему количеству запросов в кэше:

Cache Queries and Modification Statements(%) 19=(Com_insert+Com_delete+Com_update+Com_replace) 20/Qcache_hits

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

Каких-то других важных индикаторов производительности и их производных я больше не вижу. Вы можете сколько угодно экспериментировать над комбинациями различных индикаторов, пробовать менять значения переменных и тому подобное, но основная задача отслеживания показателей производительности кэша — ответ на вопросы: эффективно ли кэширование в данный момент и в данном окружении; если нет, то можно ли его сделать эффективным; если да, то можно ли его ещё улучшить? В некоторых ситуациях использование кэша запросов, наряду с грамотно определенными параметрами, может дать серьезный прирост производительности — кто его знает, может быть у вас есть огромная база данных только для чтения, большинство необходимых запросов из которой (и их результатов) можно вполне поместить в оперативную память? Однако в большинстве случаев прирост производительности может оказаться минимальным, поэтому не стоит сильно заострять внимание только на кэшировании выборок, есть также масса других способов повышения производительности, о которых я расскажу в других статьях.

comments powered by HyperComments
dissertation abstract
2022-07-05 16:25:28
<strong>writing dissertation and grant proposals https://professionaldissertationwriting.org/</strong>
dissertation writing skills
2022-07-05 18:05:50
<strong>dissertation introduction https://professionaldissertationwriting.com/</strong>
acknowledgements dissertation
2022-07-05 20:08:52
<strong>dissertation statistics help https://helpwithdissertationwritinglondon.com/</strong>
help writing
2022-07-05 22:48:34
<strong>how to write a dissertation proposal https://dissertationwritingcenter.com/</strong>
dissertation writers
2022-07-06 01:39:07
<strong>writing methodology for dissertation https://dissertationhelpexpert.com/</strong>
help with dissertations
2022-07-06 03:45:51
<strong>dissertation research https://accountingdissertationhelp.com/</strong>
writing
2022-07-06 07:24:26
<strong>best dissertation writing service uk https://examplesofdissertation.com/</strong>
dissertation writing tips
2022-07-06 12:39:51
<strong>dissertation thesis writing https://bestdissertationwritingservice.net/</strong>
dissertation help ireland editing
2022-07-06 16:12:33
<strong>premium dissertation writing service https://businessdissertationhelp.com/</strong>
disertation
2022-07-06 17:54:32
<strong>dissertation literature review help https://customdissertationwritinghelp.com/</strong>
research writing help
2022-07-06 22:11:04
<strong>help with dissertation proposal https://writingadissertationproposal.com/</strong>
tips on writing a dissertation
2022-07-06 23:04:10
<strong>writing dissertation methodology https://dissertationhelpspecialist.com/</strong>
custom dissertation writing service 2019
2022-07-07 03:24:45
<strong>writing dissertation proposal https://dissertationhelperhub.com/</strong>
dissertation acknowledgement sample
2022-07-07 05:41:40
<strong>dissertation help articles https://customthesiswritingservices.com/</strong>
online casino borgata
2022-07-25 18:31:26
<strong>mi online casino https://download-casino-slots.com/</strong>
sx vegas casino online
2022-07-25 21:50:42
<strong>borgata online casino https://firstonlinecasino.org/</strong>
online casino deposit bonuses
2022-07-26 00:50:04
<strong>turning stone casino online https://onlinecasinofortunes.com/</strong>
online casino for real money no deposit
2022-07-26 04:29:44
<strong>motor city online casino https://trust-online-casino.com/</strong>
diamond online casino
2022-07-26 06:51:46
<strong>watch casino online https://onlinecasinosdirectory.org/</strong>
online casino no minimum deposit
2022-07-26 10:00:48
<strong>online casino free bonus https://9lineslotscasino.com/</strong>
online casino spielen
2022-07-26 11:37:15
<strong>md live online casino https://free-online-casinos.net/</strong>
online casino roulette
2022-07-26 15:17:31
<strong>online casino welcome bonus https://internet-casinos-online.net/</strong>
online casino gambling
2022-07-26 16:45:53
<strong>mgm online casino pa https://cybertimeonlinecasino.com/</strong>
tropicana online casino promo code
2022-07-26 19:21:18
<strong>sugarhouse casino online nj https://1freeslotscasino.com/</strong>
hollywood casino online
2022-07-26 22:09:19
<strong>online casino with sign up bonus https://vrgamescasino.com/</strong>
online casino las vegas
2022-07-27 02:01:44
<strong>instant withdrawal online casino usa 2020 https://casino-online-roulette.com/</strong>
rivers online casino pa
2022-07-27 04:50:33
<strong>turning stone online casino login https://casino-online-jackpot.com/</strong>
online casino bonuses
2022-07-27 07:15:45
<strong>vegas casino online no deposit bonus 2021 https://onlineplayerscasino.com/</strong>
real online casino win real money
2022-07-27 10:40:59
<strong>raging bull casino online slots https://ownonlinecasino.com/</strong>
hollywood casino online pa
2022-07-27 15:35:02
<strong>tropicana online casino promo code https://casino8online.com/</strong>
best vpn protocol
2022-08-07 14:09:14
<strong>best vpn for china https://freevpnconnection.com/</strong>
buy vpn with credit card
2022-08-07 17:29:08
<strong>best vpn for chromebook https://shiva-vpn.com/</strong>
vpn best buy
2022-08-07 19:05:38
<strong>avast vpn buy https://freehostingvpn.com/</strong>
secure line vpn review
2022-08-07 21:24:33
<strong>vpn free online https://ippowervpn.net/</strong>
windows vpn
2022-08-08 01:26:51
<strong>buy vpn online https://imfreevpn.net/</strong>
zenmate vpn free
2022-08-08 03:25:30
<strong>best vpn provider https://superfreevpn.net/</strong>
buy vpn uk
2022-08-08 04:52:46
<strong>best vpn services https://free-vpn-proxy.com/</strong>
vpn buy online
2022-08-08 07:14:22
<strong>best vpn service https://rsvpnorthvalley.com/</strong>
craigslist gay dating site
2022-08-23 15:16:37
<strong>gay teen dating https://gay-singles-dating.com/</strong>
gay mature dating sites
2022-08-23 17:02:29
<strong>gay dating site out personal customer service https://gayedating.com/</strong>
dating transwoman gay
2022-08-23 19:51:38
<strong>gay black men dating https://datinggayservices.com/</strong>
meet men online
2022-08-24 12:17:02
<strong>internet dating https://freephotodating.com/</strong>
best online dating site
2022-08-24 15:49:11
<strong>best date online https://onlinedatingbabes.com/</strong>
100% free dating service
2022-08-24 16:44:07
<strong>dating websites for free https://adult-singles-online-dating.com/</strong>
which online dating site is best?
2022-08-24 20:02:49
<strong>100% free dating service https://adult-classifieds-online-dating.com/</strong>
dating chat site
2022-08-24 23:09:10
<strong>senior dating services https://online-internet-dating.net/</strong>
online dating app
2022-08-25 00:34:31
<strong>local single https://speedatingwebsites.com/</strong>
dating web
2022-08-25 02:21:35
<strong>plentyoffish login https://datingpersonalsonline.com/</strong>
massianic singles free dating sites
2022-08-25 04:44:32
<strong>free single dating online https://wowdatingsites.com/</strong>
free personals site
2022-08-25 06:25:53
<strong>catholic christian dating website https://lavaonlinedating.com/</strong>
dating online chat and meet
2022-08-25 09:25:59
<strong>dating online sites https://freeadultdatingpasses.com/</strong>
lets-casual-dating
2022-08-25 12:56:17
<strong>meeting with my lady https://virtual-online-dating-service.com/</strong>
best local dating sites
2022-08-25 14:52:26
<strong>free and single https://zonlinedating.com/</strong>
meet women online free
2022-08-25 17:28:34
<strong>rhondacaringmom online dating https://onlinedatingservicesecrets.com/</strong>
blackjack online casino
2022-08-30 13:40:25
<strong>party casino online https://online2casino.com/</strong>
bally online casino
2022-08-30 17:38:49
<strong>online casino sign up bonuses https://casinosonlinex.com/</strong>
black bottom gay chat
2022-09-03 00:26:25
<strong>gay chat site https://newgaychat.com/</strong>
chat for free gay webcam
2022-09-03 03:36:34
<strong>gay advise chat https://gaychatcams.net/</strong>
gay connect chat
2022-09-03 09:43:18
<strong>gay and bi male text chat https://gaychatspots.com/</strong>
free gay chat room
2022-09-03 16:21:38
<strong>chat ave gay https://gay-live-chat.net/</strong>
older gay chat
2022-09-03 22:06:20
<strong>nc gay free chat room https://chatcongays.com/</strong>
gay chicago webcam chat
2022-09-04 02:45:45
<strong>gay chat text https://gayphillychat.com/</strong>
gay video chat sites
2022-09-04 16:47:30
<strong>gay male incest chat https://free-gay-sex-chat.com/</strong>
gay chub chat
2022-09-05 00:02:02
<strong>hairy gay men chat free https://gayinteracialchat.com/</strong>
Яндекс.Метрика