C, PHP, VB, .NET

Дневникът на Филип Петров


* Настройки на MySQL server

Публикувано на 08 април 2009 в раздел Бази от Данни.

MySQL сървърите са изключително лесни за настройка – повечето от опциите се контролират от един текстов файл. Под Linux/BSD той е /etc/my.cnf, а под Windows файлът се казва my.ini и се намира в инсталационната директория на MySQL.

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

В MySQL има изключително много променливи за настройка. Някои от тях обаче са изключително важни за общото бързодействие на системата. За да видите списък на променливите и техните стойности използвайте следната команда:

show variables;

За да видите работните процеси:

show status;

Сега ще разгледаме някои от най-важните променливи:

1. key_buffer_size – Изключително важна настройка за MyISAM таблици. Обикновено при първоначална инсталация стойността на тази променлива се слага грубо на около 30% от големината на RAM паметта на системата при положение, че ще се използва предимно MyISAM. По-фините настройки зависят от големината на индексите и информацията. В по-новите системи се използва предимно InnoDB, но въпреки това все пак трябва да се заделят като минимум 16MB key_buffer_size, защото той ще се използва за създаване на временни таблици. Ако тази променлива е с недостатъчна големина, то ще се използват дискови операции, които забавят системата значително;

2. innodb_buffer_pool_size – Когато става дума да InnoDB таблици, то стойността на този параметър е изключително важен. В RAM паметта се кешират и данните и индексите. Поради тази причина ако имате само бази данни от тип InnoDB, то не е неоправдано да заделите изключително голямо количество за innodb_buffer_pool_size – над 30% от паметта на системата.

3. innodb_additional_mem_pool_size – Тази настройка няма реален ефект върху производителността. Използва се за специфични нужди на InnoDB. Една стойност от около 20MB е добра за старт на натоварен сървър.

4. table_open_cache – Отварянето на таблици е бавна операция. Когато дадена таблица се достъпва, тя се маркира като „currently in use“ в своя заглавен файл (header file). Затова е хубаво да имаме достатъчно голям table_cache, за да можем да имаме колкото се може повече отворени таблици наведнъж. Ако в системата ви има не повече от 300 таблици, то стойност на table_cache около 1000 може би ще бъде достатъчна. Имайте предвид, че всяка една връзка към базата данни отваря поне една таблица. При изключително натоварени бази данни стойността на table_cache може да се направи огромна.

5. query_cache_size – Ако приложението ви изключително много чете данни, то е добре да си направите кеш на заявките и така да облекчите тяхната „компилация“. Тук обаче в никакъв случай не трябва да се прекалява, защото практиката показва влошаване при преоразмеряване. Стойности от порядъка на 32 до 128MB са нормални. Обикновено се гледа стойността от статистиките на сървъра „cache hit ratio“ и ако той е нисък, то стойността на query_cache_size се увеличава.

6. thread_cache – Създаването и унищожаването на нишки при връзка и изход от базата данни също отнема ресурси. Създаването на кеш за нишките често може да подобри бързината на инициализация. Обикновено стойността трябва да варира между 12 и 16. Увеличава се повече само при изключително натоварени сървъри.

7. sort_buffer – Увеличава скоростта на операциите myisamchk. Може да бъде изключително полезна при таблици с често сортиране.

8. read_rnd_buffer_size – Използва се при четене на вече сортирани колони от таблици. Отново ако имате често сортиране, то увеличавайки стойността ще увеличите и бързодействието. Имайте предвид, че за разлика от key_buffer_size и table_cache, тази памет се заделя отделно за всяка една нишка. Обикновено правилото е да заделите по 1KB за всеки 1MB от рам паметта на сървъра.

9. innodb_log_file_size – Ако използвате много трансакции, то тази стойност е изключително важна. По-голяма стойност ще увеличи изключително много бързодействието, но за сметка на това увеличава времето за възстановяване след неуспешна трансакция, т.е. трябва да се планира според системата. Поради таци причина препоръчителните стойности могат да варират между 64M и 512M.

10. innodb_log_buffer_size – Обикновено тази променлива не се променя и се използва стойността ѝ по подразбиране. Единствената причина за увеличаване може да е ако имате много заявки от тип UPDATE и полета от тип TEXT/BLOB. Този буфер така или иначе се изтрива всяка секунда и затова стойности над 8MB до 12MB не са оправдани и ще дадат само разход на памет. При по-малки системи дори можете да занижите под стойността по подразбиране.

11. innodb_flush_log_at_trx_commit – Ако използвате InnoDB, то тази променлива може да окаже изключителна разлика в бързодействието, но за сметка на това е опасна за цялостта на данните. По подразбиране то е 1, т.е. за всеки COMMIT на заявка или всяка заявка извън трансакция (т.е. не участваща в такава) ще трябва да се изчиства log файла на диска. Най-често проблем се вижда при преминаване на едно приложение от MyISAM към InnoDB, защото MyISAM не използва трансакции. Ако сложите стойност 2, то ще се изчиства само кеша на операционната система. Стойност 0 няма да изчиства кеша и бързодействието се повишава значително, но риска от загуба на информация при непредвидено спиране на системата е огромен. При стойност 2 може да се изгуби информация само при блокиране на операционната система.

12. tmp_table_size – Ако често използвате временни таблици е добре да предвидите повече рам памет, за да не се записват на хард диска.

13. myisam_sort_buffer_size – Използва се при REPAIR TABLE, CREATE TABLE и CREATE INDEX. Тези операции са редки и често можете да намалите тази стойност до минимум.

14. max_tmp_tables – Колко временни таблици могат да бъдат използвани едновременно? Отговорът е, че зависи от приложенията.

15. max_tmp_table_size – Колко рам памет да бъде заделена за временните ви таблици? Ако работите с BLOB/TEXT, то може би доста. Ако паметта е недостатъчна е ясно, че ще се прибегне до дискови операции.

16. max_connections – Това е най-опасната опция. За всяка една връзка към базата данни се заделя голямо количество памет (всички буфери). Ако преоразмерите тази стойност, то обикновено няма да има проблем. При пикови моменти обаче може да се надхвърли физическата оперативна памет и това да доведе до изключително слаба производителност за всички нишки. Ако пък стойността се направи малка, то може лесно да се достигне лимита и много клиентски приложения да бъдат блокирани. Затова се казва, че max_connections е аларма за upgrade на системата. Обикновено тази стойност трябва да се държи на 200% от средната стойност на едновременни връзки към базата данни от статистиката. Ако статистическите данни покажат увеличаване на едновременните връзки към базата данни, а физическото количество памет ще бъде прехвърлено при увеличаване на max_connections, то трябва да сложите още рам на сървъра.

17. read_buffer_size – Ако са ви нужни бързи „full table scan“, т.е. заявки без използване на index, то стойността на read_buffer_size трябва да е голяма. На теория е така, но на практика се оказва, че поради външни фактори (най-често операционна система) производителността не се увеличава. Статистиката сочи, че дори при доста голяма таблица от около 5 000 000 записа, стойности над 128KB на read_buffer_size ще намалят производителността, вместо да я подобрят!

18. join_buffer_size – Буфера за правене на JOIN между таблици е стандартно доста малък, като се има предвид, че тъй или иначе не е добра идея да се правят големи JOIN връзки между таблици. Обикновено администраторите предпочитат да го увеличат до около 1MB.

19. thread_stack – Обикновено не е полезно да пипате тази променлива (в никакъв случай не я намалявайте). При Linux системи увеличаването ѝ няма да подобри почти нищо, а само ще изразходва повече рам. При FreeBSD може да се забележат минимални подобрения. Това ще бъде една от последните опции, които би трябвало да гледате за оптимизация.

20. max_packet_size – Стойност, която трябва да се променя спрямо базите данни в системата. Увеличаването ѝ ще подобри скоростта, като намали броят на пакетите, но за сметка на това увеличава значително изискването за повече рам памет. Ако не се качват големи файлове в базите данни в системата, то дръжте тази стойност ниска.

Всичко казано дотук е добро, но все пак трябва да се съобразим с размера на рам паметта на сървъра. Общата формула(*) е:

Рам паметта на сървъра >=
рам паметта използвана от операционната система +
рам паметта използвана от другите програми (Apache, mail server, и т.н.)
рам паметта използвана от програмите на MySQL (средно 32MB) +
key_buffer_size +
innodb_buffer_pool_size +
innnodb_additional_memory_pool_size +
innodb_log_buffer_size +
max_tmp_tables * tmp_table_size +
query_cache_size +
3 * myisam_sort_buffer_size +
max_connections * (
	read_buffer_size +
	join_buffer_size +
	read_rnd_buffer_size +
	thread_stack +
	2*max_packet_size
)

Следната заявка описва паметта, която заема като потенциален максимум само MySQL:

SELECT ( @@key_buffer_size + @@query_cache_size + 
         @@tmp_table_size + @@innodb_buffer_pool_size + 
         @@innodb_additional_mem_pool_size + 
         @@innodb_log_buffer_size + 
         @@max_connections * ( 
             @@read_buffer_size + @@read_rnd_buffer_size + 
             @@sort_buffer_size + @@join_buffer_size + 
             @@binlog_cache_size + @@thread_stack 
         ) 
) / 1073741824 AS MAX_MEMORY_GB

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

Има и още някои тънкости, свързани с кеша. Например query_cache прави разлика между малки и големи букви. Например заявките „SELECT * FROM banks“ и „select * from banks“ за query_cache ще са две различни заявки. Това е и една от причините да спазваме някакви конвенции при програмирането на бази данни.

При силно използване на MyISAM е добре да гледате т.нар. „key_buffer_size hit ratio“. То се изчислява по следната формула:

Key_reads/Key_read_requests

Ако стойността е над 0,01, то е добре да помислите за увеличаване на key_buffer_size.

Накрая е важно да споменем и променливата за „прекъсване“ (timeout). Wait_timeout е променлива, която контролира „спящите“ връзки. Често програмистите на приложения забравят да затворят връзките си към базата данни. Това естествено рефлектира с изразходване на памет, което никак не е добре. Намаляване на wait_timeout ще затваря спящите връзки по-бързо. Обикновено предпочитанията на администраторите са в интервала 10 до 15 секунди. Имайте предвид обаче, че намаляването на wait_timeout може да рефлектира в нужда от увеличаване на max_connections. Тук в голяма помощ идва thread_cache променливата, за която писахме по-горе.

Също така опитайте оптимизация по следната формула:

table_cache = opened table / max_used_connection

Трябва да знаете, че няма стриктна формула, по която нашия MySQL сървър да работи най-добре. Обикновено настройките са един постоянен и продължителен процес. Добрите администратори непрекъснато следят системата си и я „тунинговат“ според натоварването.

В тази статия сме дали само най-важните променливи, свързани с производителност на системата. Имайте предвид, че общо за MySQL 5.1 общо променливите са над 260 (естествено не всички са използваеми и не всички свързани с производителност)…

 



8 коментара


  1. Благодаря за добрите думи :)

  2. Пламен Лаков каза:

    Браво мой човек!
    От много време исках да прочета нещо такова, за да ми се изяснят някой неща. 6-ца! :)

  3. Е. Н. каза:

    Искам да кажа, че Филип е единственият преподавател, който наистина ме научи на нещо свързано със софтуер (като изключа Личка Гърневска, която ми помогна много с математиката). Той ми преподава по предмета Бази Данни. Не желая да се подмазвам или каквото и да било от този род, но това е самата истина като погледна изминалите 3 семесътъра и преподавателите по лекции и упражнения от тях.
    До сега, често пъти съм иронизирал преподаватели пред самите тях в часовете им, заради това, че не знаят как да преподадат така, че на студента да му стане ясно, да го приложи и съответно да го запомни.

    Свалям шапка на Филип Петров за това, че е приел преподавателската професия като нещо, което му идва от сърцето, защоти си личи, когато някой наистина иска да ти предаде знанията си без да се стиска за тях и без да му е безразлично какво ще ти остане в главата. И за разлика от някои други…не се надува. Браво за желанието и браво за метода на обучение! Браво и на хората, които са те научили!
    Дано в по-горните курсове да срещнем още някой като теб колкото и ти за нас да си изключение…
    Успех!!!

    Мислех да се пусна анонимен, от съображения за сигурност…ама айде надали четат много колегите преподаватели, а и в крайна сметка казвам истината

  4. Е. Н. – Е, благодаря за коментара. Изглежда все едно аз сам съм си го писал хахаха :)

  5. E. Н. каза:

    Това си е истината. Еми и така да изглежда-това не е толкова важно,който не вярва ще му докажа. Важното е да си оценен и ти от хората за това, което вършиш. Дано ти е за стимул. Само не бива да се главозамайваш, че се сещам за един такъв „закръглен“ пример във ФКСУ, за който и ти се сещаш..:) хахах
    айде,че най-накрая седнах да си видя курсовата задача и май не е толкова лесно, колкото си мислех… :P

  6. E. Н. каза:

    Aaa moment :D V tozi red na misli – izteglih si db forge studio,no ne znam kak da go izpolzvam. I da te pitam,ako vreme:
    Kursovata moje li da e samo script na mysql i shema? :S
    Концептуален модел на БД – shte re4e shema,kato tazi ot upr li?,
    Схема на БД – според избрана от студента БД. Примерни БД: MySQL -?
    Нормализация на модела – поне 3НФ – tova mai ne sme go vzimali i shte vidq v slaidovete ot lekcii

  7. seo каза:

    Искам да предложа и една статия на близка тематика Оптимизиране на mySQL заявки – http://ganbox.com/blog/оптимизиране-на-sql-заявки/

  8. Предложената статията е много добра

Добави коментар

Адресът на електронната поща няма да се публикува


*