10 основни съвета за производителност за MySQL

Както при всички релационни бази данни, MySQL може да се окаже сложен звяр, който може да започне да спира в един момент, оставяйки вашите приложения на покой и бизнеса ви на линия.

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

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

Ето 10 съвета за постигане на отлична производителност от MySQL.

Съвет за изпълнение на MySQL № 1: Профилирайте натоварването си

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

Най-добрият начин да профилирате работното си натоварване е с инструмент като анализатора на заявки на MySQL Enterprise Monitor или pt-query-digest от Percona Toolkit. Тези инструменти улавят заявките, които сървърът изпълнява, и връщат таблица със задачи, сортирани по намаляване на реда на реакция, незабавно изкачвайки най-скъпите и отнемащи време задачи до върха, за да можете да видите къде да насочите усилията си.

Инструментите за профилиране на натоварването групират подобни заявки заедно, като ви позволяват да видите бавните заявки, както и бързите, но изпълнявани много пъти.

Съвет №2 за изпълнение на MySQL: Разберете четирите основни ресурса

За да функционира, сървърът на база данни се нуждае от четири основни ресурса: процесор, памет, диск и мрежа. Ако някой от тях е слаб, непостоянен или претоварен, тогава сървърът на базата данни е много вероятно да работи слабо.

Разбирането на основните ресурси е важно в две конкретни области: избор на хардуер и отстраняване на проблеми.

Когато избирате хардуер за MySQL, осигурете добре работещи компоненти навсякъде. Също толкова важно е да ги балансирате сравнително добре един срещу друг. Често организациите ще избират сървъри с бързи процесори и дискове, но които са гладни за памет. В някои случаи добавянето на памет е евтин начин за увеличаване на производителността с порядъци, особено при натоварвания, които са свързани с диска. Това може да изглежда неинтуитивно, но в много случаи дисковете са прекалено използвани, тъй като няма достатъчно памет, за да съхраняват работния набор от данни на сървъра.

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

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

Съвет за изпълнение на MySQL № 3: Не използвайте MySQL като опашка

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

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

Съвет за изпълнение на MySQL № 4: Първо филтрирайте резултатите по най-евтините

Чудесен начин за оптимизиране на MySQL е първо да се извърши евтина, неточна работа, а след това упоритата и прецизна работа върху по-малкия, в резултат набор от данни.

Да предположим например, че търсите нещо в даден радиус от географска точка. Първият инструмент в инструментариума на много програмисти е формулата за голям кръг (Haversine) за изчисляване на разстоянието по повърхността на сферата. Проблемът с тази техника е, че формулата изисква много тригонометрични операции, които са много интензивни за процесора. Изчисленията с големи кръгове обикновено се извършват бавно и правят използването на процесора на машината стремително.

Преди да приложите формулата на големия кръг, съкратете записите си до малко подмножество от общия брой и отрежете получения набор до точен кръг. Квадрат, който съдържа кръга (точно или неточно) е лесен начин да направите това. По този начин светът извън площада никога не бива ударен с всички онези скъпи триг функции.

Съвет за изпълнение на MySQL № 5: Познайте двата капани за смърт при мащабиране

Мащабируемостта не е толкова неясна, колкото може би вярвате. Всъщност има точни математически определения за мащабируемост, които се изразяват като уравнения. Тези уравнения подчертават защо системите не се мащабират толкова добре, колкото би трябвало.

Вземете Универсалния закон за мащабируемост, дефиниция, която е удобна за изразяване и количествено определяне на характеристиките на мащабируемостта на системата. Той обяснява проблемите с мащабирането по отношение на две основни разходи: сериализация и кръстосани изказвания.

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

Избягвайте сериализацията и кръстосаните препратки и вашето приложение ще се мащабира много по-добре. Какво означава това в MySQL? Тя варира, но някои примери биха били избягването на изключителни заключвания на редове. Опашките, точка № 3 по-горе, обикновено се мащабират слабо поради тази причина.

Съвет за изпълнение на MySQL № 6: Не се фокусирайте твърде много върху конфигурацията

DBA са склонни да отделят огромно количество време за променяне на конфигурации. Резултатът обикновено не е голямо подобрение и понякога може дори да навреди много. Виждал съм много „оптимизирани“ сървъри, които непрекъснато се сриват, изчерпват паметта и се представят лошо, когато натоварването стане малко по-интензивно.

По подразбиране, които се доставят с MySQL, са еднозначни и лошо остарели, но не е необходимо да конфигурирате всичко. По-добре е да направите правилните основи и да промените други настройки само ако е необходимо. В повечето случаи можете да получите 95 процента от пиковата производителност на сървъра, като зададете около 10 опции правилно. Няколко ситуации, при които това не се отнася, ще бъдат уникални случаи, специфични за вашите обстоятелства.

В повечето случаи не се препоръчват инструменти за настройка на сървъра, тъй като те обикновено дават насоки, които нямат смисъл за конкретни случаи. Някои дори имат закодирани опасни, неточни съвети - като коефициенти на попадение в кеша и формули за консумация на памет. Те никога не са били прави и с времето са станали дори по-малко правилни.

Съвет за изпълнение на MySQL № 7: Внимавайте за заявки за разбиване на страници

Приложенията, които са странични, са склонни да поставят сървъра на колене. Показвайки ви страница с резултати, с връзка за преминаване към следващата страница, тези приложения обикновено се групират и сортират по начини, които не могат да използват индекси, и те използват a LIMITи offsetкоито карат сървъра да свърши много работа, генерирайки, след това изхвърляне на редове.

Оптимизациите често могат да бъдат намерени в самия потребителски интерфейс. Вместо да показвате точния брой страници в резултатите и връзки към всяка страница поотделно, можете просто да покажете връзка към следващата страница. Можете също така да попречите на хората да отиват на страници твърде далеч от първата страница.

От страна на заявката, вместо да използвате LIMITс offset, можете да изберете още един ред, отколкото ви е необходим, и когато потребителят щракне върху връзката „следваща страница“, можете да посочите този последен ред като начална точка за следващия набор от резултати. Например, ако потребителят разглежда страница с редове от 101 до 120, бихте избрали и ред 121; за да изобразите следващата страница, бихте поискали сървъра за редове, по-големи или равни на 121, ограничение 21.

Съвет за изпълнение на MySQL № 8: Запазвайте статистиката с нетърпение, предупреждавайте неохотно

Мониторингът и предупреждението са от съществено значение, но какво се случва с типичната система за мониторинг? Започва да изпраща фалшиви положителни резултати и системните администратори задават правила за филтриране на имейли, за да спрат шума. Скоро вашата система за наблюдение е напълно безполезна.

Обичам да мисля за мониторинг по два начина: улавяне на показатели и предупреждение. Много е важно да уловите и запазите всички показатели, които евентуално можете, защото ще се радвате да ги имате, когато се опитвате да разберете какво се е променило в системата. Някой ден ще се появи странен проблем и ще ви хареса възможността да сочите към графика и да показвате промяна в натоварването на сървъра.

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

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

Съвет за изпълнение на MySQL № 9: Научете трите правила за индексиране

Индексирането е може би най-неразбраната тема в базите данни, защото има толкова много начини да се объркате как работят индексите и как сървърът ги използва. Необходими са много усилия, за да се разбере наистина какво се случва.

Индексите, когато са правилно проектирани, служат на три важни цели в сървъра на база данни:

  1. Индексите позволяват на сървъра да намира групи от съседни редове вместо единични редове. Много хора смятат, че целта на индекса е да намира отделни редове, но намирането на единични редове води до произволни операции на диска, което е бавно. Много по-добре е да намерите групи редове, всички или повечето от които са интересни, отколкото да намерите редове един по един.
  2. Индексите позволяват на сървъра да избягва сортирането, като чете редовете в желания ред. Сортирането е скъпо. Четенето на редове в желания ред е много по-бързо.
  3. Индексите позволяват на сървъра да задоволява цели заявки само от индекса, избягвайки изобщо необходимостта от достъп до таблицата. Това е известно по различен начин като обхващащ индекс или заявка само за индекс.

Ако можете да проектирате своите индекси и заявки, за да използвате тези три възможности, можете да направите заявките си с няколко порядъка по-бързи.

Съвет за изпълнение на MySQL № 10: Използвайте опита на вашите връстници

Не се опитвайте да го направите сами. Ако се озадачавате за даден проблем и правите това, което ви се струва логично и разумно, това е страхотно. Това ще работи около 19 пъти от 20. Другия път ще слезете в заешка дупка, което ще бъде много скъпо и отнема много време, именно защото решението, което се опитвате, изглежда има много смисъл.

Изградете мрежа от свързани с MySQL ресурси - и това надхвърля наборите от инструменти и ръководствата за отстраняване на неизправности. Има някои изключително знаещи хора, които дебнат в пощенски списъци, форуми, уебсайтове с въпроси и отговори и т.н. Конференции, търговски изложения и събития от местни потребителски групи предоставят ценни възможности за получаване на прозрения и изграждане на взаимоотношения с връстници, които могат да ви помогнат в краен случай.

За тези, които търсят инструменти за допълване на тези съвети, можете да разгледате Percona Configuration Wizard за MySQL, Percona Query Advisor за MySQL и Percona Monitoring Plugins. (Забележка: Ще трябва да създадете акаунт в Percona, за да получите достъп до тези две връзки. Безплатно е.) Съветникът за конфигуриране може да ви помогне да генерирате изходен файл my.cnf за нов сървър, който е по-добър от примерните файлове, които се доставят с сървър. Съветникът за заявки ще анализира вашия SQL, за да ви помогне да откриете потенциално лоши модели, като заявки за разбиване на страници (№ 7). Приставките за наблюдение на Percona са набор от приставки за наблюдение и графики, които ви помагат да запазвате статистически данни с нетърпение и да предупреждавате неохотно (№ 8). Всички тези инструменти са свободно достъпни.