За повечето разработчици на приложения базата данни е олтар на богове демони, които най-добре се оставят неподправени. Но не трябва да е така!


При равни други равнища, нивото на комфорт, което разработчика има с базисната база данни, определя нивото им на стаж. Малка база данни и малко опит с кодиране = младши разработчик; малко база данни и добър опит с кодиране = разработчик на средно ниво; добра база данни и добро кодиране опит = старши разработчик.

Тежка реалност е, че дори разработчиците с 6-8 години под колана се борят да обяснят тънкостите на оптимизатора на заявки и предпочитат да гледат небето, когато ги питат за настройка на база данни.

Защо?

Изненадващо, причината не е мързел (въпреки че в някаква част е така).

Въпросът е, че базите данни са собствена сила, за да се справят. Дори традиционно, когато имаше само релационни видове бази данни, за да се справят, овладяването им беше чудо и кариерен път сами по себе си; в наши дни имаме толкова много видове бази данни, че е просто невъзможно да се очаква една, смъртна душа да овладее всичко.

Имайки предвид това, има голям шанс да продължите да сте доволни от релационните бази данни или да сте част от екип, който разполага с продукт, който работи в релационна база данни задоволително дълго и дълго време. И в девет от всеки десет сте на MySQL (или MariaDB). За тези случаи, гмуркането само малко по-дълбоко под капака носи огромни ползи за повишаване на производителността на приложението и всеки бит си струва да научите.

Любопитен? Нека се потопим!

Не е любопитно? Е, гмуркайте се така или иначе, защото кариерата ви зависи от това! ��

Оптимизирайте кеша на заявките на MySQL

Почти цялата оптимизация в областта на компютрите се свежда до кеширане. От една страна, процесорът поддържа няколко нива на кеш, за да ускори изчисленията си, а от друга, уеб приложенията използват агресивно кеширащи решения като Redis за сървър на предварително изчислени резултати на потребителите, вместо да удрят базата данни всеки път.

Но ей, дори лошата база данни на MySQL има свой кеш на заявки! Тоест всеки път, когато запитвате нещо и данните все още са застояли, MySQL ще обслужва тези кеширани резултати, вместо да пуска отново заявката, правейки приложението смешно по-бързо.

Можете да проверите дали в базата данни имате наличен кеш на заявки (забележете, наличен, не е активиран), като изпълните тази заявка в конзолата на базата данни:

MariaDB [(няма)]> ПОКАЗАНЕ VARIABLES LIKE ‘have_query_cache’;
+——————+——-+
| Име на променлива | Стойност |
+——————+——-+
| have_query_cache | ДА |
+——————+——-+

Така че можете да видите, че изпълнявам MariaDB и че имам на разположение кеширане на заявки, за да бъде включен. Изключително вероятно е да го изключите, ако използвате стандартна инсталация MySQL.

Сега нека да видим дали всъщност е включен кешът на заявки:

MariaDB [(няма)]> ПОКАЗАНЕ ВАРИАБЛИ, КАТО „query_cache_type“;
+——————+——-+
| Име на променлива | Стойност |
+——————+——-+
| query_cache_type | ON |
+——————+——-+

Да, да. Но в случай, че не го направите, можете да го включите, като кажете:

MariaDB [(няма)]> SET GLOBAL query_cache_type = ВКЛЮЧЕН;

Интересното е, че тази променлива приема и трета стойност, която обозначава „при поискване“, което означава, че MySQL ще кешира само онези заявки, на които му казваме, но няма да влизаме в това тук.

С това имате кеширане на заявки и сте направили първата стъпка към по-стабилна настройка на MySQL! Казвам първата стъпка, тъй като докато я включим е голямо подобрение, ние трябва да настроим кеширането на заявки, за да отговарят на нашата настройка. Така че нека се научим да правим това.

Другата интересна променлива тук е query_cache_size, чиято функция е сама по себе си обяснителна:

MariaDB [(няма)]> ПОКАЗАНЕ VARIABLES LIKE ‘query_cache_size’;
+——————+———-+
| Име на променлива | Стойност |
+——————+———-+
| query_cache_size | 16777216 |
+——————+———-+

И така, имам кеш на заявки с размер около 16 MB. Имайте предвид, че дори ако кеширането на заявки е включено, но този размер е нула, кеширането е ефективно изключено. Ето защо проверката само на една променлива не е достатъчна. Сега трябва да зададете размер на кеша на заявките, но колко трябва да бъде? Първо, моля, обърнете внимание, че самата функция за кеширане на заявки ще се нуждае от 4 KB, за да съхранява метаданните си, така че каквото изберете, трябва да бъде над това.

Да речем, че сте задали размера на кеша на заявките да бъде 500 KB:

MariaDB [(няма)]> SET GLOBAL query_cache_size = 500000;

Това прави ли достатъчно много? Е, не, защото как двигателят на заявките всъщност ще завърши, зависи от още няколко неща:

  • Първо, променливата query_cache_size трябва да е достатъчно голяма, за да задържи резултата от вашите заявки. Ако е твърде малък, нищо няма да се кешира.
  • На второ място, ако зададете query_cache_size на прекалено голям брой, ще има два вида проблеми: 1) Двигателят ще трябва да извърши допълнителна работа за съхранение и локализиране на резултатите от заявките в тази масивна област от паметта. 2) Ако повечето заявки водят до много по-малки размери, кешът ще се раздробява и ползите от използването на кеш ще се загубят.

Как знаете, че кешът се раздробява? Проверете общия брой блокове в кеша така:

MariaDB [(няма)]> покажете състояние като „Qcache_total_blocks“;
+———————+——-+
| Име на променлива | Стойност |
+———————+——-+
| Qcache_total_blocks | 33 |
+———————+——-+

Ако броят е много голям, кешът е фрагментиран и трябва да бъде пречистен.

Така че, за да избегнете тези проблеми, уверете се, че размерът на query_cache_size е избран разумно. Ако се чувствате разочаровани, че не ви оставих конкретен номер тук, страхувам се, че така стоят нещата, след като преминете в миналото на развитие и стъпвате в инженеринга. Трябва да разгледате приложението, което стартирате, и да видите какви са размерите на заявките за важните резултати от заявките и след това да зададете този номер. И дори тогава може да се окажете грешка. ��

Резьба, пулове с нишки, изчакване и изчакване

Това е може би най-интересната част от начина, по който работи MySQL и правилното му използване означава да направите приложението си няколко пъти по-бързо!

Threading

MySQL е многопоточен сървър. Това означава, че всеки път, когато има нова връзка към MySQL сървъра, тя отваря нова нишка с данните за връзката и предава дръжка към нея на клиента (само в случай, че се чудите каква е нишката, вижте това). След това клиентът изпраща всички заявки през тази тема и получава резултати. Това ни кара да си зададем естествен въпрос: колко нишки може да се завърти MySQL? Отговорът се крие в следващия раздел.

Басейн с конци

Нито една програма в компютърна система не може да отвори толкова нишки, колкото иска. Причината е двойна: 1) Нишките струват памет (RAM) и операционната система просто няма да ви позволи да отидете на берърк и да изядете всичко. 2) Управлението, да речем, милион нишки е мащабна задача сама по себе си и ако MySQL сървърът може да създаде толкова много нишки, ще умре при опит да се справи с режийните разходи.

За да избегнете тези проблеми, MySQL се предлага с пул с нишки – фиксиран брой нишки, които са част от пул в началото. Новите заявки за свързване причиняват MySQL да вземе една от тези нишки и да върне данните за връзката и ако всички нишки се използват, новите връзки естествено се отказват. Нека да видим колко голям е резервоарът с нишки:

ariaDB [(няма)]> показват променливи като ‘thread_pool_size’;
+——————+——-+
| Име на променлива | Стойност |
+——————+——-+
| thread_pool_size | 4 |
+——————+——-+

Така че, моята машина позволява максимум четири връзки едновременно. Интересно е да се отбележи, че числото 4 идва от факта, че имам четириядрен процесор, което означава, че компютърът ми може да изпълнява само 4 паралелни задачи наведнъж (тук говоря за наистина паралелни задачи, а не за едновременни). В идеалния случай това е лимитът, който трябва да управлява стойността на thread_pool_size, но при увеличаването на машините на бефитерите той има полза до точка. Ако не искате да накарате всички нови връзки да изчакат и е добре да предприемете някакъв хит на производителността (отново, това е област, по която можете да прецените най-добре въз основа на ефективността на приложението си под натоварване), сблъскването му до 8 може да е добра идея.

Въпреки това, задаването му след 16 е ужасна идея, освен ако нямате 32-ядрена машина, тъй като производителността намалява значително. Заешката дупка на басейни с конци в MySQL отива дълбоко, но ако се интересувате, ето по-подробна дискусия.

Изчакване и изчакване

След като нишката е създадена и прикачена към клиент, би било загуба на ресурси, ако клиентът не е изпращал запитвания през следващите няколко секунди (или минути). В резултат на това MySQL прекратява връзка след период на неактивност. Това се контролира от променливата wait_timeout:

MariaDB [(няма)]> показват променливи като „изчакайте%“;
+—————+——-+
| Име на променлива | Стойност |
+—————+——-+
| wait_timeout | 28800 |
+—————+——-+

Получената стойност е в секунди. Така че да, по подразбиране MySQL е настроен да чака 8+ часа, преди да захване кабела! Това може да е добре, ако имате продължителни заявки и всъщност искате да ги изчакате (но дори и тогава, осем часа е абсурдно!), Но в повечето случаи е ужасно. Когато се изпълнява заявка, тази стойност е зададена на 0 (което означава завинаги), но като цяло тя трябва да бъде зададена на много ниска стойност (например 5 секунди или може би дори по-малко), за да се освободи връзката за други процеси.

Настройка на временни таблици

Нека започнем с това, какви са временните таблици в MySQL.

Да предположим, че имаме MySQL, който структурно изглежда по следния начин: ТАБЛИЦА А СЪЮЗ (ТАБЛИЦА B ВЪТРЕШНО ПРИЛОЖЕНИЕ C). Тоест, ние се интересуваме от присъединяването на таблици B и C и след това да извършим обединение на резултата с таблица А. Сега, MySQL първо ще пристъпи към присъединяване към таблици B и C, но преди да може да извърши обединение, той се нуждае да съхранявате тези данни някъде. Тук влизат временни таблици – MySQL ги използва за временно съхраняване на данни на междинни етапи в сложни заявки и след като заявката приключи, тази временна таблица се изхвърля.

Сега въпросът е: защо да се занимаваме с всичко това?

Просто защото временната таблица, само резултат от заявката, са данни, които се използват от MySQL при изчисляване, а скоростта на достъпа му (наред с други ограничения) ще определи колко бързо се изпълнява заявката. Например, съхраняването на временната таблица в RAM паметта ще бъде няколко пъти по-бързо от съхранението й на диска.

Има две променливи, които контролират това поведение:

MariaDB [(няма)]> показване на променливи като „MariaDB [(няма)]> показват променливи като ‘tmp_table_size’;
+—————-+———-+

| Име на променлива | Стойност |

+—————-+———-+

| tmp_table_size | 16777216 |

+—————-+———-+
“;
+———————+———-+
| Име на променлива | Стойност |
+———————+———-+
| max_heap_table_size | 16777216 |
+———————+———-+

MariaDB [(няма)]> показват променливи като ‘tmp_table_size’;
+—————-+———-+
| Име на променлива | Стойност |
+—————-+———-+
| tmp_table_size | 16777216 |
+—————-+———-+

Първият, max_heap_table_size, ни казва колко RAM може да бъде използвана от MySQL таблица (тук „тук купчина“ се отнася до структурата на данните, използвана при разпределението и управлението на RAM – прочетете повече тук), докато втората, tmp_table_size, показва какъв е максималният размер на временната таблица. В моя случай и двете са настроени на 16 MB, макар че се опитвам да направя, че увеличаването само на tmp_table_size няма да работи като цяло, MySQL ще бъде ограничен от max_table_heap_size.

Сега идва въпросът: ако създадените временни таблици са по-големи от допустимия от тези променливи, MySQL ще бъде принуден да ги запише на твърдия диск, което води до изключително слаба производителност. Нашата работа сега е проста: направете всичко възможно да гадаем най-точния размер на данните за временните таблици и да настроите тези променливи до тази граница. Въпреки това бих искал да предупредя за абсурда: задаването на този лимит на 16 GB (ако приемем, че имате толкова много RAM памет), когато повечето от вашите временни таблици са с размер по-малък от 24 MB, е глупост – просто губите RAM, която би могла да ” използван е от други заявки или части от системата (кеш, например).

заключение

Не е възможно да се покрият всички системни променливи в една статия или дори всички важни в една статия, когато самата документация на MySQL обхваща няколко хиляди думи. Докато тук покрихме някои универсални променливи, препоръчвам ви да разгледате системните променливи за двигателя, който използвате (InnoDB или MyISAM).

Най-желаният ми резултат от написването на тази статия е да отнемете три неща:

  1. MySQL е типичен софтуер, който работи в граници, определени от операционната система. Това не е някаква загадъчна програма, която знае какво знае и е невъзможно да се укроти. Освен това, за щастие, не е толкова трудно да разберем как е създаден и се контролира от неговите системни променливи.
  2.  Няма нито една настройка, която да накара инсталирането на MySQL да се увеличи. Нямате друг избор, освен да погледнете в работещите си системи (не забравяйте, че оптимизацията идва след като приложението е в производство, а не преди), правите най-добрите предположения и измервания и живейте с реалността, която никога няма да бъде перфектна.
  3. Настройката на променливите не е единственият начин за оптимизиране на MySQL – ефективни заявки за писане е друга голяма работа, но това е нещо, което ще адресирам в друга статия. Но въпросът е, че дори да сте направили богоподобен анализ и да настроите тези параметри по най-добрия начин, все още е възможно да доведете всичко до скрипт.

Коя е любимата ти системна променлива за настройка? ��

ЕТИКЕТИ:

  • База данни

Jeffrey Wilson Administrator
Sorry! The Author has not filled his profile.
follow me