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

Ще разгледаме едни от най-важните настройки на MySQL Server. Повечето от опциите се контролират от един текстов файл. Под 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. table_cache – Отварянето на таблици е бавна операция. Когато дадена таблица се достъпва, тя се маркира като “currently in use” в своя заглавен файл (header file). Затова е хубаво да имаме достатъчно голям table_cache, за да можем да имаме колкото се може повече отворени таблици наведнъж. Ако в системата ви има не повече от 300 таблици, то стойност на table_cache около 1000 може би ще бъде достатъчна. Имайте в предвид, че всяка една връзка към базата данни отваря поне една таблица. При изключително натоварени бази данни стойността на table_cache може да се направи огромна.

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

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

6. innodb_additional_mem_pool_size – This one does not really affect performance too much, at least on OS with decent memory allocators. Still you might want to have it 20MB (sometimes larger) so you can see how much memory Innodb allocates for misc needs.

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

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

9. innodb_additional_mem_pool_size – Променлива, която няма особен ефект върху производителността. Обикновено се слага не повече от 20MB и се използва за странични операции на InnoDB.

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

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

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

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

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

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

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

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

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

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

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

21. 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
)

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

Имайте в предвид, че повечето от казаните по-горе неща важат за сървъри, които работят предимно с бази данни. Ако имате други приложения, които натоварват сървъра много, то трябва да се съобразявате с тях.
[pagebreak][/pagebreak]
Има и още някои тънкости, свързани с кеша. Например 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 (естествено не всички са използваеми и не всички свързани с производителност).

Копирането е забранено без изричното съгласие на vGuides.net