MySQL. Увеличаване на производителността. Част 1 — Кеширане на данни
Мониторингът на производителността на СУБД е доста обемна задача и е много трудно за начинаещ системен администратор да се ориентира сред стотици индикатори. Основната задача е да се определят праговите стойности, над които трябва да се предприемат активни действия. В тази статия ще се опитам да хвърля светлина върху въпроса за наблюдението и анализа на ефективността на кеширането на данни и индекси, както и да дам препоръки за по-нататъшни действия.
Можете да получите информация за текущото състояние на променливи, които показват индикатори за ефективност, като използвате командата SHOW STATUS. Пълният му синтаксис е: SHOW [GLOBAL SESSION] STATUS [LIKE 'pattern' WHERE expr]1.
Нека да преминем към подробни описания на показателите за ефективност.
MyISAM 2
Анализът на индикаторите, които ще бъдат обсъдени по-долу, е уместен само ако използвате MyISAM като подсистема за съхранение на данни на ниско ниво. Преди MySQL 5.5 MyISAM беше по подразбиране.
Key_reads — брой физически четения на ключови блокове от диска;
Key_read_requests — операции за четене на блокове ключове от кеша в RAM. Повечето ресурси за оптимизиране на производителността на MySQL препоръчват да наблюдавате тези параметри заедно. Връзката между Key_reads и Key_read_requests е важна; ако е повече от 99%, тогава всичко е наред, ако е по-малко от 95%, тогава си струва да анализирате проблемите с производителността, възможно е да надстроите оборудването или да увеличите размера на кеша.
Формулата изглежда така:Съотношение(%)=(1-Key_reads/Key_read_requests)*100
В идеалния случай количеството памет, разпределено за кеша, трябва да е достатъчно, за да побере всички индекси3 в него.
Key_writes — брой записвания на физически данни на диска;
Key_write_requests — броят заявки за запис на данни в кеша.
Също така се препоръчва последните два параметъра да се анализират заедно като съотношение на единия към другия. В идеалния случай стойността трябва да е по-малка от 90%.
Съотношение (%)=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.
В комплекса параметрите имат същите ограничения и формулата за изчисляване на съотношението (можете също да намерите обобщена информация с командата „ПОКАЖЕТЕ изхода на INNODB STATUS на двигателя“)7 : над 99% - нормално, под 95% - има проблеми с производителността.
Съотношение (%)=(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_misc8.
От петте параметъра, описани по-горе, трябва да се обърне специално внимание на броя безплатни страници (Innodb_buffer_pool_pages_free), т.к. може да разкаже за излишъка или липсата на размер на кеша9. Ако обаче анализираме само този параметър, тогава няма да научим нищо разбираемо, тъй като той е представен в абсолютни стойности. Има смисъл да свържете този параметър с общия брой страници в паметта и да изчислите неговия процент. Знаейки, че обемът на страниците за нуждите на СУБД (pages_misc) обикновено не надвишава 10%10, а броят на свободните страници (pages_free) трябва да бъде минимален, можете грубо да определите границата, след превишаването на която трябва да помислите за ефективността на паметта. Тази стойност ще бъде приблизително 10-15%, когато надвишите 20-25%, се препоръчва да намалите размера на кеша, тъй като броят на безплатните страници става значителен.
Процент(%)=(1-Innodb_buffer_pool_pages_data/Innodb_buffer_pool_pages_total) *100%
Трябва да помислите за увеличаване на кеша, когато стойността на безплатните страници падне под 5% (това е само мое лично мнение) или да следите абсолютната стойност и ако е 0, това показва силна липса на кеш. Формулата ще изглежда така:
Процент (%)=Innodb_buffer_pool_pages_free/Innodb_buffer_pool_pages_total*100%
innodb_buffer_pool_size - показва количеството памет, което е разпределено за данни и индекси. На теория, колкото по-голяма е тази стойност, толкова по-добре. Ако говорим изключително за сървъри за бази данни без други роли, тогава се препоръчва да го настроите на около 70-80% от общото количество RAM, но има и други мнения11. На сървъри, които комбинират роли, внимавайте да разберете точно колко RAM е необходима на други приложения. Освен това има някои ограничения за сървъри с 32-битова архитектура12. Изчисляването на размера на необходимия InnoDB кеш не е трудна задача, има много готови примери в мрежата13. Размерът на кеша се изчислява като произведение от общия брой страници и размера на една страница (Innodb_buffer_pool_pages_total * Innodb_page_size).
Останалите параметри не представляват интерес за нас (например параметърът innodb_additional_mem_pool_size обикновено е деактивиран по подразбиране14 ). Това завършва прегледа на подобренията в производителността чрез промяна на опциите за кеширане на данни.