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

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

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

Получить информацию о текущем состоянии переменных, отображающих показатели производительности, можно с помощью команды «SHOW STATUS». Её полный синтаксис выглядит следующим образом: SHOW [GLOBAL | SESSION] STATUS [LIKE ‘pattern’ | WHERE expr] 1.

Перейдем к подробным описаниям показателей производительности.

MyISAM 2

Анализ показателей, которые будут рассмотрены ниже, актуален лишь в том случае, если вы используете MyISAM как низкоуровневую подсистему хранения данных. До версии MySQL 5.5 MyISAM использовалась по умолчанию.

Key_reads — количество физических операций чтения блоков ключей с диска;

Key_read_requests — операции чтения блоков ключей из кэша в оперативной памяти.
На большинстве ресурсов по оптимизации производительности MySQL рекомендуют обязательно отслеживать эти параметры вместе. Важно отношение Key_reads к Key_read_requests; если оно больше 99%, то все в порядке, если меньше 95%, то стоит проанализировать проблемы в производительности, возможно провести апгрейд оборудования или увеличить размер кэша.

Формула выглядит следующим образом:
Ratio(%)=(1-Key_reads/Key_read_requests)*100

В идеале объема выделенной для кэша памяти должно быть достаточно, чтобы разместить в ней все индексы 3.

При анализе отношения этих параметров необходимо иметь в виду также время сбора статистики, ведь если вы отслеживаете количество чтений записей с диска при только что перезагруженном сервере, то вы получите результат, который будет значительно ниже оптимальных значений. Некоторые авторы вообще утверждают что эти параметры отслеживать смысла большого нет 4.
Лично мое мнение — большое внимание придавать какому-либо одному параметру производительности не нужно, но принимать его в рассчет при общем анализе как минимум не будет лишним.

Key_writes — количество физических записей данных на диск;

Key_write_requests — количество обращений на запись данных в кэш.

Анализировать последние два параметра рекомендуется также вместе как отношение одного к другому. В идеале значение должно быть меньше 90%.

Ratio(%)=Key_writes/Key_write_requests*100

key_buffer_size — переменная, управляющая текущим размером кэша. Понять какой объем необходимо выставлять этой переменной можно из суммарного объема всех файлов с расширением .MYI, которые находятся в директории баз данных. Как это сделать применительно к определенным операционным системам в этой статье я рассматривать не планирую (при всем обилии информации в сети 5) Также я не буду затрагивать вопрос манипулирования размером кэша с помощью теплых и горячих очередей, что особо актуально для систем с 32-х разрядной архитектурой, поскольку использование в рабочей среде 32-х разрядных систем уже само по себе является проблемой, а для 64-х разрядных систем такой проблемы пока быть не должно.

InnoDB 6

Начиная с версии MySQL 5.6 движком по умолчанию стал InnoDB, параметры мониторинга которого будут рассмотрены ниже.

Innodb_buffer_pool_reads — количество операций чтения с диска, но применительно к другому движку СУБД — InnoDB;

Innodb_buffer_pool_read_requests — количество обращений к кэшу, также в контексте InnoDB.

В комплексе параметры имеют те же ограничения и формулу вычисления отношения (узнать сводную информацию можно также командой «SHOW ENGINE INNODB STATUS output») 7: выше 99% — норма, ниже 95% — есть проблемы в производительности.

Ratio(%)=(1-Innodb_buffer_pool_reads/Innodb_buffer_pool_read_requests)*100

Для анализа использования кэша также не лишним будет обратиться к следующим параметрам:

Innodb_buffer_pool_pages_dirty — отображает количество «грязных» страниц — то есть которые были изменены, находясь в кэше, но эти изменения ещё не зафиксированы на диске;

Innodb_buffer_pool_pages_data — отображает сумму чистых и грязных страниц в кэше, а также индексных страниц;

Innodb_buffer_pool_pages_flushed — количество запросов на на сброс страниц из кэша на жесткий диск;

Innodb_buffer_pool_pages_free — количество свободных страниц;

Innodb_buffer_pool_pages_misc — страницы, используемые самой СУБД для различных нужд;

Innodb_buffer_pool_pages_total — общее количество страниц в памяти, образуется суммированием параметров pages_data, pages_free, pages_misc 8.

Из описанных выше пяти параметров особое внимание стоит уделить количеству свободных страниц (Innodb_buffer_pool_pages_free), т.к. оно может сказать об избытке или недостатке размера кэша 9. Однако если анализировать этот параметр в одиночку, то ничего внятного мы не узнаем, поскольку он представляется в абсолютном значении. Есть смысл связать этот параметр с общим количеством страниц в памяти и вычислить его процент. Зная, что объем страниц под нужды СУБД (pages_misc) обычно не превышает 10% 10, а количество свободных страниц (pages_free) должно быть минимальным, можно примерно определить границу, по превышении которой стоит задуматься об эффективности использования памяти. Это значение будет примерно в 10-15%,  при превышении 20-25% рекомендуется снижать объем кэша, поскольку количество свободных страниц становится значительным.

Rate(%)=(1-Innodb_buffer_pool_pages_data/Innodb_buffer_pool_pages_total) *100%

Задуматься о увеличении кэша нужно при падении значения свободных страниц ниже 5% (это исключительно мое личное мнение), либо отслеживать абсолютное значение и если оно равно 0, это говорит о сильной нехватке кэша. Формула будет выглядеть примерно так:

Rate(%)=Innodb_buffer_pool_pages_free/Innodb_buffer_pool_pages_total*100%

innodb_buffer_pool_size — отображает размер памяти, которая выделена для размещения данных и индексов. По идее чем это значение больше, тем лучше. Если речь идет исключительно о серверах баз данных без каких-либо других ролей, то рекомендуют выставлять его примерно в 70-80% от общего объёма оперативной памяти, но существуют и другие мнения 11. На совмещающих роли серверах будьте внимательны, надо точно понимать сколько оперативной памяти необходимо другим приложениям. К тому же на серверах с 32-х битной архитектурой есть некоторые ограничения 12. Рассчитать необходимый вам размер кэша InnoDB не представляется сложной задачей, в сети есть масса готовых примеров 13. Размер кэша вычисляется как произведение общего количества страниц на размер одной страницы (Innodb_buffer_pool_pages_total * Innodb_page_size).

Остальные параметры нас не интересуют (например, параметр innodb_additional_mem_pool_size вообще по умолчанию отключен 14). На этом обзор увеличения производительности за счет изменения параметров кэширования данных завершен.

comments powered by HyperComments
powerpoint for creative writing
2022-07-05 16:13:38
<strong>sample dissertation https://professionaldissertationwriting.org/</strong>
doctoral dissertation help qualitative
2022-07-05 17:10:39
<strong>write a dissertation abstract https://professionaldissertationwriting.com/</strong>
help dissertation thesis advice
2022-07-05 21:13:22
<strong>dissertation literature review help https://helpwithdissertationwritinglondon.com/</strong>
how long is a dissertation
2022-07-06 00:57:57
<strong>writing a masters dissertation https://dissertationhelpexpert.com/</strong>
help dissertation thesis advice
2022-07-06 04:00:42
<strong>phd dissertation writing help https://accountingdissertationhelp.com/</strong>
dissertation writing tips
2022-07-06 06:06:45
<strong>english dissertation help https://examplesofdissertation.com/</strong>
dissertation cover page
2022-07-06 09:19:45
<strong>dissertation writing plan https://writing-a-dissertation.net/</strong>
tips for writing a dissertation
2022-07-06 13:31:51
<strong>mathematics dissertation help https://bestdissertationwritingservice.net/</strong>
writing your dissertation proposal
2022-07-06 16:06:55
<strong>acknowledgements dissertation https://businessdissertationhelp.com/</strong>
electronic engineering dissertation ideas
2022-07-06 17:24:30
<strong>help with writing dissertation proposal https://customdissertationwritinghelp.com/</strong>
custom dissertation writing help
2022-07-06 22:38:40
<strong>doctoral dissertation writing assistance https://writingadissertationproposal.com/</strong>
doctoral dissertation writing assistance
2022-07-07 00:44:03
<strong>online dissertation help https://dissertationhelpspecialist.com/</strong>
masters dissertation
2022-07-07 03:16:40
<strong>dissertation database https://dissertationhelperhub.com/</strong>
how to write a dissertation proposal
2022-07-07 06:16:42
<strong>get help with dissertation https://customthesiswritingservices.com/</strong>
harrahs online casino pa
2022-07-25 20:19:41
<strong>online casino no deposit bonus code https://download-casino-slots.com/</strong>
live casino online
2022-07-25 22:15:40
<strong>monarch online casino https://firstonlinecasino.org/</strong>
instant withdrawal online casino usa 2021
2022-07-25 23:31:13
<strong>online casino deposit bonus https://onlinecasinofortunes.com/</strong>
resorts online casino
2022-07-26 02:16:23
<strong>inferno online casino https://newlasvegascasinos.com/</strong>
best payout online casino
2022-07-26 07:58:09
<strong>winstar online social casino https://onlinecasinosdirectory.org/</strong>
beat online casino
2022-07-26 12:18:04
<strong>online casino arizona https://free-online-casinos.net/</strong>
free online casino no deposit
2022-07-26 14:29:01
<strong>online casino with free signup bonus real money usa no deposit https://internet-casinos-online.net/</strong>
jumba bet online casino
2022-07-26 19:13:02
<strong>online casino usa real money xb777 https://1freeslotscasino.com/</strong>
fast payout online casino
2022-07-27 01:35:43
<strong>hollywood online casino pa https://casino-online-roulette.com/</strong>
harrah's online casino
2022-07-27 04:48:44
<strong>online casino https://casino-online-jackpot.com/</strong>
best online casino to win money
2022-07-27 07:09:43
<strong>jacks casino online https://onlineplayerscasino.com/</strong>
goldennugget online casino
2022-07-27 10:25:59
<strong>golden nugget casino online https://ownonlinecasino.com/</strong>
online casino paypal
2022-07-27 12:47:38
<strong>online casino gaming https://all-online-casino-games.com/</strong>
online casino stock
2022-07-27 15:27:56
<strong>michigan online casino https://casino8online.com/</strong>
surfshark vpn
2022-08-07 15:50:47
<strong>avira phantom vpn https://freevpnconnection.com/</strong>
tor free vpn
2022-08-07 16:59:30
<strong>free vpn canada https://shiva-vpn.com/</strong>
best free vpn for roobet
2022-08-07 19:08:19
<strong>vpn free ip address https://freehostingvpn.com/</strong>
tor free vpn
2022-08-07 22:51:45
<strong>online vpn https://ippowervpn.net/</strong>
vpn for netflix free
2022-08-07 23:52:31
<strong>browsec vpn https://imfreevpn.net/</strong>
ivacy vpn
2022-08-08 03:51:14
<strong>best antivirus with vpn https://superfreevpn.net/</strong>
nord vpn buy
2022-08-08 07:07:56
<strong>best vpn service providers https://rsvpnorthvalley.com/</strong>
gay true dating site
2022-08-23 14:51:41
<strong>gay stoner dating https://gay-singles-dating.com/</strong>
gay nmale dating sites in southern california
2022-08-23 16:44:03
<strong>young gay dating porn video https://gayedating.com/</strong>
best gay dating app
2022-08-23 19:07:32
<strong>gay foot fetish dating https://datinggayservices.com/</strong>
cdff dating site login
2022-08-24 12:20:48
<strong>dating website free https://freephotodating.com/</strong>
totally free dating sites local
2022-08-24 16:05:43
<strong>datingnow life https://onlinedatingbabes.com/</strong>
datesite
2022-08-24 16:54:24
<strong>popular dating sites https://adult-singles-online-dating.com/</strong>
freeadultdating
2022-08-24 20:22:39
<strong>dating web site https://adult-classifieds-online-dating.com/</strong>
freetalk45
2022-08-24 21:41:21
<strong>personals online https://online-internet-dating.net/</strong>
share dating
2022-08-25 00:24:44
<strong>lets-casual-dating https://speedatingwebsites.com/</strong>
free online dating service
2022-08-25 01:59:36
<strong>date personal https://datingpersonalsonline.com/</strong>
n dating site
2022-08-25 04:10:33
<strong>free dating date https://wowdatingsites.com/</strong>
dating sites for free
2022-08-25 08:19:58
<strong>adult date site https://lavaonlinedating.com/</strong>
dating free online
2022-08-25 09:02:44
<strong>free local dating https://freeadultdatingpasses.com/</strong>
meet online
2022-08-25 12:07:58
<strong>online websites https://virtual-online-dating-service.com/</strong>
dating women online
2022-08-25 15:15:31
<strong>free site dating https://zonlinedating.com/</strong>
chinese dating
2022-08-25 17:26:05
<strong>senior bi log in https://onlinedatingservicesecrets.com/</strong>
2022-08-30 17:18:26
<strong>online casino no deposit free spin https://casinosonlinex.com/</strong>
gay nude video chat
2022-09-03 00:53:45
<strong>gay sex chat rooms https://newgaychat.com/</strong>
mature gay chat app
2022-09-03 08:10:33
<strong>gay video chat https://gaychatcams.net/</strong>
gay phone sex chat
2022-09-03 11:40:59
<strong>gay sex chat room https://gaychatspots.com/</strong>
gay chat rouetee
2022-09-03 13:49:37
<strong>free gay chat rooms in new york state https://gay-live-chat.net/</strong>
gay universe man chat
2022-09-03 21:38:00
<strong>gay chat room in nj https://chatcongays.com/</strong>
gay furry chat one on one
2022-09-03 23:51:00
<strong>free 60 minute trial phone chat gay https://gayphillychat.com/</strong>
gay teen chat rooms
2022-09-04 05:05:48
<strong>free gay adult chat https://gaychatnorules.com/</strong>
gay chat city
2022-09-04 10:03:56
<strong>gay page chat roulette https://gaymusclechatrooms.com/</strong>
free asian chat lines gay
2022-09-04 16:33:14
<strong>gay chat ru=oulette https://free-gay-sex-chat.com/</strong>
free asain gay chat lines
2022-09-05 00:09:55
<strong>free adult gay chat https://gayinteracialchat.com/</strong>
local gay chat
2022-10-20 17:15:42
<strong>chat avenue gay room https://gaymanchatrooms.com/</strong>
paper writing service cheap
2022-10-20 22:56:02
<strong>write my paper please https://paperwritinghq.com/</strong>
write my paper in apa format
2022-10-20 23:22:05
<strong>where can i find someone to write my college paper https://writepapersformoney.com/</strong>
writing services for college papers
2022-10-21 02:41:33
<strong>order paper online https://doyourpapersonline.com/</strong>
paper writer
2022-10-21 05:12:23
<strong>help writing a college paper https://researchpaperswriting.org/</strong>
do my papers
2022-10-21 06:37:16
<strong>help with your paper https://cheapcustompaper.org/</strong>
papers help
2022-10-21 09:01:36
<strong>write my english paper for me https://buyessaypaperz.com/</strong>
someone to write my paper
2022-10-21 10:27:06
<strong>paper writing services https://mypaperwritinghelp.com/</strong>
paper writing services for college students
2022-10-21 13:14:30
<strong>papers writing service https://essaybuypaper.com/</strong>
ghost writer for college papers
2022-10-21 15:59:54
<strong>paper writing website https://premiumpapershelp.com/</strong>
write my papers
2022-10-21 17:23:43
<strong>do my paper for money https://ypaywallpapers.com/</strong>
write my paper canada
2022-10-21 18:47:54
<strong>write my thesis paper https://studentpaperhelp.com/</strong>
2accumulates
2023-01-26 18:42:03
<strong>2lutheran</strong>
coursework writers
2023-02-05 14:48:34
<strong>creative writing english coursework https://brainycoursework.com/</strong>
coursework writer
2023-02-05 16:40:35
<strong>coursework resources https://courseworkninja.com/</strong>
do my coursework
2023-02-05 17:52:29
<strong>custom coursework https://writingacoursework.com/</strong>
coursework history
2023-02-05 18:58:02
<strong>coursework science https://mycourseworkhelp.net/</strong>
coursework writers
2023-02-05 20:05:32
<strong>coursework in english https://courseworkdownloads.com/</strong>
custom coursework writing
2023-02-05 21:45:13
<strong>coursework service https://courseworkinfotest.com/</strong>
coursework writing
2023-02-05 23:22:45
<strong>coursework science https://coursework-expert.com/</strong>
coursework help
2023-02-05 23:49:20
<strong>coursework plagiarism https://teachingcoursework.com/</strong>
coursework history
2023-02-06 00:58:26
<strong>coursework papers https://buycoursework.org/</strong>
youwu666
2023-02-08 12:29:52
<strong>dating service site https://freewebdating.net/</strong>
dating sites without registering
2023-02-08 14:45:24
<strong>singles singles https://jewish-dating-online.net/</strong>
single senior dating sites
2023-02-08 16:19:50
<strong>meet me now dating site https://free-dating-sites-free-personals.com/</strong>
dating simulator
2023-02-08 20:04:35
<strong>no credit card dating sites https://onlinedatingsuccessguide.com/</strong>
juicydatessites
2023-02-08 21:43:16
<strong>dating sims https://onlinedatinghunks.com/</strong>
dating online chat and meet
2023-02-08 23:06:21
<strong>dating personal https://datingwebsiteshopper.com/</strong>
best site for online dating
2023-02-08 23:57:48
<strong>zoosk dating login https://allaboutdatingsites.com/</strong>
best dating websites
2023-02-09 01:09:03
<strong>absolutely free local dating site https://freedatinglive.com/</strong>
Яндекс.Метрика