Аптымізацыя MySQL: індэксы, павольныя запыты, канфігурацыя

1. аптымізацыя канфігурацыі
2. ручной цюнінг
3. Variable Inspector
4. MySQL Tuner
5. індэксы
6. Унікальныя / першасныя індэксы
7. рэгулярныя індэксы
8. паўнатэкставыя індэксы
9. Зваротныя індэксы (па змяншэнні)
10. Дапаможныя прылады: Explain
11. Дапаможныя прылады: Percona Toolkit для выяўлення дублюючых індэксаў
12. Дапаможныя прылады: Percona Toolkit для невыкарыстоўваемых індэксаў
13. вузкія месцы
14. заключэнне

Наша команда-партнер Artmisto

MySQL па-ранейшаму з'яўляецца самай папулярнай у свеце рэляцыйнай базай дадзеных, але ў той жа час і найбольш ня аптымізаванай. Многія людзі застаюцца з наладамі па змаўчанні, не «капаючы" глыбей. У гэтым артыкуле мы разгледзім некаторыя парады па аптымізацыі MySQL ў спалучэнні з некаторымі навінкамі, якія выйшлі адносна нядаўна.

аптымізацыя канфігурацыі

Першае, што кожны карыстальнік MySQL павінен зрабіць для павышэння прадукцыйнасці - гэта наладзіць канфігурацыю. Аднак, большасць гэты крок прапускаюць. У 5.7 (бягучая версія) налады па змаўчанні сталі нашмат лепш, чым у яе папярэднікаў, але палепшыць іх па-ранейшаму можна і нескладана.

Мы спадзяемся, што вы карыстаецеся Linux ці нешта накшталт Vagrant -box (як наш Homestead Improved ), А, адпаведна, ваш канфігурацыйны файл будзе знаходзіцца ў /etc/mysql/my.cnf. Цалкам магчыма, што ваша ўстаноўка на самай справе будзе падгружаць дадатковы файл канфігурацыі ў гэты. Так што паглядзіце, калі файл my.cnf змяшчае трохі, то паглядзіце ў /etc/mysql/mysql.conf.d/mysqld.cnf.

ручной цюнінг

Наладзіць кампутар павінны быць зробленыя "з скрынкі". згодна гэтым саветам , Неабходна дадаць да файла конфігу ў раздзел [mysqld]:

innodb_buffer_pool_size = 1G # (тут памяняйце прыкладна 50% -70% ад агульнага аб'ёму оперативы) innodb_log_file_size = 256M innodb_flush_log_at_trx_commit = 1 # можна памяняць на 2 ці 0 innodb_flush_method = O_DIRECT

• innodb_buffer_pool_size. Пул буферызацыі (buffer pool) з'яўляецца гэтакім "складам" для кэшавання дадзеных і індэксаў у памяці. Ён выкарыстоўваецца, каб захоўваць часта выкарыстоўваныя дадзеныя ў памяці. І калі вы выкарыстоўваеце вылучаны або віртуальны сервер, на якім часцяком менавіта БД з'яўляецца вузкім месцам, то ёсць сэнс аддаць ёй вялікую частку оперативы. Такім чынам, мы даем ёй 50-70% усёй RAM. У дакументацыі MySQL ёсць кіраўніцтва па наладзе гэтага пула .
• innodb_log_file_size. Налада памеру лог-файла добра апісана тут , Але ў двух словах гэта колькасць захоўваемых дадзеных у логах, перш чым яго пачысціць. Звярніце ўвагу, што лог у гэтым выпадку - гэта не запісу пра памылкі, а нейкі дэльта-злепак змяненняў, якія яшчэ не былі скінутыя на дыск у асноўныя файлы innodb. MySQL піша ў фонавым рэжыме, але гэта ўсё ж уплывае на прадукцыйнасць ў момант запісу. Вялікі лог-файл азначае больш высокую прадукцыйнасць з-за малой колькасці ствараюцца новых і невялікіх кантрольных кропак, але пры гэтым больш доўгі час аднаўлення ў выпадку краш (больш дадзеных павінна быць перапісана ў БД).
• innodb_flush_log_at_trx_commit апісаны тут і паказвае, што адбываецца з файлам логаваў. Значэнне 1 - самае бяспечнае, т. К. Лог скідаецца на дыск пасля кожнай транзакцыі. Пры значэннях 0 і 2 - менш гарантуецца ACID , Але больш за прадукцыйнасць. Розніца не з'яўляецца дастаткова вялікі, каб пераважыць перавагі стабільнасці пры 1.
• innodb_flush_method. У дадатак да ўсяго таго, што тычыцца скіду дадзеных, гэтую наладу трэба ўсталяваць у O_DIRECT - каб пазбегнуць двайны буферызацыі. Раю заўсёды гэта рабіць, пакуль сістэма ўводу-высновы застаецца вельмі павольнай. Хоць на большасці хостынгах, тыпу DigitalOcean, вы будзеце мець SSD-дыскі, таму сістэма ўводу-вываду будзе больш прадукцыйная.

Ёсць інструмент ад Percona, які дапаможа нам знайсці тыя, што засталіся праблемы аўтаматычна. Звярніце ўвагу, што калі мы б запусцілі яго без гэтай ручной налады, то толькі 1 з 4 налад была б вызначаная, т. К. Іншыя 3 залежаць ад пераваг карыстальніка і навакольнага асяроддзя прыкладання.

Variable Inspector

Ўстаноўка variable inspector на Ubuntu:

wget https://repo.percona.com/apt/percona-release_0.1-4.$(lsb_release -sc) _all.deb sudo dpkg -i percona-release_0.1-4. $ (lsb_release -sc) _all. deb sudo apt-get update sudo apt-get install percona-toolkit

Для іншых сістэм, прытрымлівайцеся гэтым інструкцыям .

Затым запусціце toolkit:

pt-variable-advisor h = localhost, u = homestead, p = secret

Вы ўбачыце такі вынік:

# WARN delay_key_write: MyISAM index blocks are never flushed until necessary. # NOTE max_binlog_size: The max_binlog_size is smaller than the default of 1GB. # NOTE sort_buffer_size-1: The sort_buffer_size variable should generally be left at its default unless an expert determines it is necessary to change it. # NOTE innodb_data_file_path: Auto-extending InnoDB files can consume a lot of disk space that is very difficult to reclaim later. # WARN log_bin: Binary logging is disabled, so point-in-time recovery and replication are not possible.

Заўв. перакладчыка:
На маёй лакальнай машыне, акрамя гэтага, выдаў яшчэ вось такі ворнинг:

set innodb_flush_method to O_DIRECT to avoid double -buffering, unless the I / O system is very low performance.

Пра тое, што параметр innodb_flush_method трэба ўсталяваць у O_DIRECT і чаму гаварылася вышэй. І калі вы прытрымліваліся паслядоўнасці цюнінгу як у артыкуле, то вы не ўбачыце гэта папярэджанне.

Ні адно з гэтых (прим.пер .: названых аўтарам) папярэджанняў не крытычна, іх неабавязкова выпраўляць. Адзінае, што можна паправіць - гэта налада бінарнага лога для рэплікацыі і снапшотов.

Заўвага: у новых версіях памер binlog-а па змаўчанні 1G і гэтага ворнинга не будзе.

max_binlog_size = 1G log_bin = /var/log/mysql/mysql-bin.log server-id = master-01 binlog-format = 'ROW'

• max_binlog_size. Вызначае наколькі вялікімі будуць бінарныя логі. У іх запісваюцца вашы транзакцыі і запыты і робяцца кантрольныя кропкі. Калі транзакцыя перавышае максімум, то лог можа перавышаць свой памер пры захаванні на дыск; у адваротным выпадку MySQL будзе падтрымліваць яго ў рамках гэтага ліміту.
• log_bin. Гэтая опцыя ўключае запіс бінарных логаў у цэлым. Без яе немагчымыя снапшоты або рэплікацыі. Звярніце ўвагу, што гэта можа вельмі адбіцца на дыскавай прасторы. server-id - гэта неабходная опцыя пры ўключэнні бінарнага лога, таму логі "ведаюць" з якога сервера яны прыйшлі (для рэплікацыі), а binlog-format - гэта проста спосаб, якім яны запісваюцца.

Як вы бачыце, новы MySQL мае значэння па змаўчанні, якія практычна гатовыя да ўжывання ў продакшене. Вядома, кожнае прыкладанне адрозніваецца і мае дадатковыя прымяняюцца ім хітрасці і цюнінгу.

MySQL Tuner

Tuner прызначаны для маніторынгу базы ў больш працяглых інтэрвалах (запускайце яго раз у тыдзень або каля таго на жывым дадатку). Ён будзе рэкамендаваць змены, заснаваныя на тым, што ён убачыў у логах.

Ўсталяваць яго проста. Трэба проста спампаваць яго:

wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl chmod + x mysqltuner.pl

Пры запуску ./mysqltuner.pl ён спытае вас імя карыстальніка і пароль адміністратара базы дадзеных і выведзе інфармацыю хуткага сканавання. Напрыклад, вось мой раздзел InnoDB:

[-] InnoDB is enabled. [-] InnoDB Thread Concurrency: 0 [OK] InnoDB File per table is activated [OK] InnoDB buffer pool / data size: 1.0G / 11.2M [!!] Ratio InnoDB log file size / InnoDB Buffer pool size (50% ): 256.0M * 2 / 1.0G should be equal 25% [!!] InnoDB buffer pool <= 1G and Innodb_buffer_pool_instances (! = 1). [-] Number of InnoDB Buffer Pool Chunk: 8 for 8 Buffer Pool Instance (s) [OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances [OK] InnoDB Read buffer efficiency: 96.65% (19146 hits / 19809 total) [!!] InnoDB Write Log efficiency: 83.88% (640 hits / 763 total) [OK] InnoDB log waits: 0.00% (0 waits / 123 writes)

Зноў жа, важна адзначыць, што гэты інструмент павінен запускацца прыкладна раз на тыдзень, бо сэрвэр гэты час працаваў і назапашваў інфармацыю. Калі вы зразумелі значэнне ў конфігах і перазапусціць сервер, ён павінен спачатку прапрацаваць тыдзень ад гэтага часу. Добрая ідэя стварыць cron-задачу, якая будзе рабіць гэта за вас і перыядычна адпраўляць вам вынікі.

Пераканайцеся, што вы перазапусціце MySQL пасля любога змены канфігурацыі:

sudo service mysql restart

індэксы

Далей, звернем увагу на індэксы - галоўная болевая кропка многіх адмінаў БД аматараў! Асабліва тых, хто адразу стаў выкарыстоўваць ORM і ніколі не нюхаў чыстага SQL.

Заўвага: тэрміны ключы і індэксы могуць быць выкарыстаны як узаемазаменныя.

Вы можаце параўнаць індэксы MySQL з зместа ў кнізе, якое дазваляе вам лёгка знайсці патрэбную старонку, якая ўтрымлівае інфармацыю, якую вы шукаеце. Калі няма ніякіх індэксаў, вам прыйдзецца прагортваць усю кнігу ў пошуку патрэбнай старонкі.

Як вы можаце сабе ўявіць, знайсці па зместа нашмат хутчэй, чым прагортваць кожную старонку. Такім чынам, даданне індэксаў у базу ў цэлым паскарае select-запыты. Аднак, гэты індэкс павінен быць створаны і захаваны. А значыць, запыты update і insert будуць павольней і гэта зойме трохі больш месцы на дыску. У цэлым вы не заўважыце розніцы пры абнаўленнях і устаўках, калі вы праіндэксавалі вашу табліцу правільна, а таму важна дадаваць індэксы ў патрэбныя месцы.

Табліцы, якія ўтрымліваюць ўсяго некалькі радкоў, на самай справе няма сэнсу індэксаваць. Вы можаце сабе ўявіць, што прагартаць 5 старонак - гэта ненашмат павольней, чым спачатку схадзіць у змест, атрымаць нумар старонкі, а затым адкрыць гэтую старонку.

Так як жа мы даведаемся, якія індэксы трэба дадаць, і якія віды індэксаў існуюць?

Унікальныя / першасныя індэксы

Першасныя індэксы з'яўляюцца асноўнымі індэксамі, якія выкарыстоўваюцца па змаўчанні пры пошуку па дадзеных. Для ўліковага запісу карыстальніка гэта могуць быць ідэнтыфікатар карыстальніка, ці лагін, або нават асноўны email. Першасныя індэксы з'яўляюцца унікальнымі. Унікальныя індэксы - гэта індэксы, якія не могуць мець паўтораў на ўсім наборы дадзеных.

Напрыклад, калі карыстальнік абраў канкрэтны username, ніхто больш не можа выбраць яго. Даданне унікальнага індэкса на слупок username вырашае гэтую праблему. MySQL будзе "лаяцца", калі хто-то паўторна паспрабуе ўставіць радок з імем карыстальніка (username), якое ўжо існуе.

... ALTER TABLE `users` ADD UNIQUE INDEX` username` ( `username`); ...

Першасныя ключы / індэксы, як правіла, задаюцца пры стварэнні табліцы, а унікальныя індэксы пазней праз змяненне (ALTER) табліцы.

І першасныя ключы, і унікальныя ключы могуць быць створаны на адзін слупок або на некалькі слупкоў адразу. Напрыклад, калі вы хочаце пераканацца, што толькі адно імя карыстальніка выкарыстоўваецца ў рамках адной краіны, то можна стварыць унікальны індэкс на абодва гэтыя слупка, напрыклад, так:

... ALTER TABLE `users` ADD UNIQUE INDEX` usercountry` ( `username`,` country`), ...

Унікальныя індэксы ставяцца на слупкі, да якіх вы будзеце часта звяртацца. Так што калі уліковы запіс карыстальніка часта запытваецца, а ў вас шмат уліковых запісаў карыстальнікаў у базе дадзеных, гэта добрая прыкмета для выкарыстання індэкса.

рэгулярныя індэксы

Рэгулярныя індэксы дапамагае ў пошуках. Яны вельмі карысныя, калі вам неабходна хутка знайсці дадзеныя па вызначаным слупка або камбінацыі слупкоў, але гэтыя дадзеныя не абавязкова павінны быць унікальнымі.

... ALTER TABLE `users` ADD INDEX` usercountry` ( `username`,` country`), ...

Прыведзены вышэй прыклад паскарае пошук па імя карыстальніка ў краіне.

Індэксы таксама дапамагаюць павялічыць хуткасць сортировок і груповак.

паўнатэкставыя індэксы

Паўнатэкставыя індэксы (FULLTEXT) выкарыстоўваюцца для паўнатэкставага пошуку. Іх падтрымліваюць толькі InnoDB і MyISAM і толькі для слупкоў з тыпамі CHAR, VARCHAR і TEXT.

Гэтыя індэксы вельмі карысныя, калі вам патрэбен пошук па ўсім тэксце ў калонцы. Ён спецыялізуецца на пошуку слоў усярэдзіне тэксту. Выкарыстоўвайце яго на пасадах, каментарах, апісаннях, водгуках і інш., Калі ваша прыкладанне дазваляе шукаць у іх.

Зваротныя індэксы (па змяншэнні)

Пачынаючы з версіі 8+, MySQL падтрымлівае зваротныя індэксы , Што азначае, што ён можа захоўваць індэксы ў парадку змяншэння. Гэта можа спатрэбіцца, калі ў вас ёсць велізарныя табліцы, з якіх часцей за ўсё патрэбныя апошнія дададзеныя дадзеныя. Вядома, заўсёды можна адсартаваць па змяншэнні, але гэта будзе трохі павольней. А вось гэта яшчэ больш паскорыць.

CREATE TABLE t (c1 INT, c2 INT, INDEX idx1 (c1 ASC, c2 ASC), INDEX idx2 (c1 ASC, c2 DESC), INDEX idx3 (c1 DESC, c2 ASC), INDEX idx4 (c1 DESC, c2 DESC)) ;

Ужывайце зваротныя індэксы калі, напрыклад, пішаце логі ў базу або, напрыклад, для пастоў і каментароў, у якіх у першую чаргу падцягваюцца апошнія запісы з БД і т. П ..

Дапаможныя прылады: Explain

Калі вы глядзіце ў бок аптымізацыі запытаў, інструмент EXPLAIN будзе неацэнны. Запыт, наперадзе якога вы проста паставілі EXPLAIN, будзе апрацоўвацца ня як запыт, а як аналіз яго выканання. MySql адлюструе вам аналіз выкарыстоўваюцца індэксаў і пакажа вам суадносіны трапленняў і промахаў. А таксама колькі радкоў ён павінен прабегчы / параўнаць, каб атрымаць вынікі, якія вы шукаеце.

EXPLAIN SELECT City.Name FROM City JOIN Country ON (City.CountryCode = Country.Code) WHERE City.CountryCode = 'IND' AND Country.Continent = 'Asia'

Вы можаце пашырыць справаздачу з дапамогай EXTENDED:

EXPLAIN EXTENDED SELECT City.Name FROM City JOIN Country ON (City.CountryCode = Country.Code) WHERE City.CountryCode = 'IND' AND Country.Continent = 'Asia'

Больш падрабязна вы можаце даведацца ў дакументацыі .

Дапаможныя прылады: Percona Toolkit для выяўлення дублюючых індэксаў

Раней усталяваны намі Percona Toolkit таксама мае інструмент для выяўлення дублюючых індэксаў, які можа спатрэбіцца пры выкарыстанні іншых CMS або проста праверыць сябе - раптам вы выпадкова дадалі больш індэксаў, чым трэба. Напрыклад, ўстаноўка WordPress па змаўчанні мае дублюючыя індэксы ў табліцы wp_posts:

pt-duplicate-key-checker h = localhost, u = homestead, p = secret # ############################### ######################################### # homestead.wp_posts # #### ################################################## ################## # Key type_status_date ends with a prefix of the clustered index # Key definitions: # KEY `type_status_date` (` post_type`, `post_status`,` post_date` , `ID`), # PRIMARY KEY (` ID`), # Column types: # `post_type` varchar (20) collate utf8mb4_unicode_520_ci not null default 'post' #` post_status` varchar (20) collate utf8mb4_unicode_520_ci not null default 'publish '# `post_date` datetime not null default' 0000-00-00 00:00:00 '#` id` bigint (20) unsigned not null auto_increment # To shorten this duplicate clustered index, execute: ALTER TABLE `homestead`.` wp_posts` DROP INDEX `type_status_date`, ADD INDEX` type_status_date` ( `post_type`,` post_status`, `post_date`);

Як відаць з апошняга радка, гэты інструмент таксама дае вам парады аб тым, як пазбавіцца ад паўтаральных індэксаў.

Дапаможныя прылады: Percona Toolkit для невыкарыстоўваемых індэксаў

Percona Toolkit можа таксама выявіць невыкарыстоўваныя індэксы. Калі вы логируете павольныя запыты (гл. Раздзел "вузкія месцы" ніжэй), вы можаце запусціць утыліту і яна будзе правяраць, выкарыстоўваюць гэтыя запыты індэксы ў табліцах і як менавіта.

pt-index-usage /var/log/mysql/mysql-slow.log

Падрабязную інфармацыю аб выкарыстанні гэтай утыліты см. тут .

вузкія месцы

У гэтым раздзеле апісваецца, як выяўляць і адсочваць вузкія месцы ў базе дадзеных.

Для пачатку, давайце ўключым лагаванне павольных запытаў:

slow_query_log = /var/log/mysql/mysql-slow.log long_query_time = 1 log-queries-not-using-indexes = 1

Радкі вышэй павінны быць дададзены ў канфігурацыю mysql. БД будзе адсочваць запыты, якія выконваліся больш чым 1 секунду, і тыя, якія не выкарыстоўваюць індэксы.

Як толькі ў гэтым логу з'явяцца некаторыя дадзеныя, вы можаце прааналізаваць іх на прадмет выкарыстання індэксаў з дапамогай вышэйпаказанай ўтыліты pt-index-usage або з дапамогай pt-query-digest, якая выведзе прыкладна такія вынікі:

pt-query-digest /var/log/mysql/mysql-slow.log # 360ms user time, 20ms system time, 24.66M rss, 92.02M vsz # Current date: Thu Feb 13 22:39:29 2014 # Hostname: * # Files: mysql-slow.log # Overall: 8 total, 6 unique, 1.14 QPS, 0.00x concurrency ________________ # Time range: 2014/02/13 22:23:52 to 22:23:59 # Attribute total min max avg 95% stddev median # ============ ======= ======= ======= ======= ===== == ======= ======= # Exec time 3ms 267us 406us 343us 403us 39us 348us # Lock time 827us 88us 125us 103us 119us 12us 98us # Rows sent 36 1 15 4.50 14.52 4.18 3.89 # Rows examine 87 4 30 10.88 28.75 7.37 7.70 # Query size 2.15k 153 296 245.11 284.79 48.90 258.32 # ==== ================== ========== === ===== ====== ===== =============== # Profile # Rank Query ID Response time Calls R / Call V / M Item # ==== ================== ============= ===== ====== === == =============== # 1 0x728E539F7617C14D 0.0011 41.0% 3 0.0004 0.00 SELECT blog_article # 2 0x1290EEE0B201F3FF 0.0003 12.8% 1 0.0003 0.00 S ELECT portfolio_item # 3 0x31DE4535BDBFA465 0.0003 12.6% 1 0.0003 0.00 SELECT portfolio_item # 4 0xF14E15D0F47A5742 0.0003 12.1% 1 0.0003 0.00 SELECT portfolio_category # 5 0x8F848005A09C9588 0.0003 11.8% 1 0.0003 0.00 SELECT blog_category # 6 0x55F49C753CA2ED64 0.0003 9.7% 1 0.0003 0.00 SELECT blog_article # === = ================== ============= ===== ====== ===== == ============= # Query 1: 0 QPS, 0x concurrency, ID 0x728E539F7617C14D at byte 736 ______ # Scores: V / M = 0.00 # Time range: all events occurred at 2014/02/13 22:23:52 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ==== === ======= ======= ======= ======= # Count 37 3 # Exec time 40 1ms 352us 406us 375us 403us 22us 366us # Lock time 42 351us 103us 125us 117us 119us 9us 119us # Rows sent 25 9 1 4 3 3.89 1.37 3.89 # Rows examine 24 21 5 8 7 7.70 1.29 7.70 # Query size 47 1.02k 261 262 261.25 258.32 0 258.32 # String: # Hosts localhost # Users * # Query_time distribution # 1us # 10us # 10 0us ################################################# ############### # 1ms # 10ms # 100ms # 1s # 10s + # Tables # SHOW TABLE STATUS LIKE 'blog_article' \ G # SHOW CREATE TABLE `blog_article` \ G # EXPLAIN / * ! 50100 PARTITIONS * / SELECT b0_.id AS id0, b0_.slug AS slug1, b0_.title AS title2, b0_.excerpt AS excerpt3, b0_.external_link AS external_link4, b0_.description AS description5, b0_.created AS created6, b0_. updated AS updated7 FROM blog_article b0_ ORDER BY b0_.created DESC LIMIT 10

Калі вы аддаеце перавагу аналізаваць гэтыя логі ўручную, вы можаце зрабіць тое ж самае, але спачатку вам трэба экспартаваць лог ў больш аналізуемы фармат. Гэта можна зрабіць так:

mysqldumpslow /var/log/mysql/mysql-slow.log

З дадатковымі параметрамі можна адфільтраваць дадзеныя, каб экспартаваць толькі патрэбнае. Напрыклад, топ-10 запытаў у парадку па сярэднім часу выканання:

mysqldumpslow -t 10 -s at /var/log/mysql/localhost-slow.log

Астатнія параметры гл. У дакументацыі .

заключэнне

У гэтым усёабдымнай пасце па аптымізацыі MySQL мы разгледзелі розныя метады і тэхнікі, з дапамогай якіх мы можам дамагчыся, каб наш MySQL лётаў.

Мы разабраліся з аптымізацыяй канфігурацыі, мы прапампавалі па індэксах, і мы вызваліліся ад некаторых вузкіх месцаў. Усё гэта было ў асноўным тэорыяй, аднак, усё гэта дастасавальна на рэальных прыкладаннях.