Колко RAM да заделя за MySQL

[cyberpower]

Здравейте,

Тези дни един от сървърите взе често да забива, имах съмнения че проблема се дължи на MySQL и май от това се оказа, но още не се знае промените които направих са от тази вечер. Ще видим през деня като скочи натоварването какъв ефект ще имат.

Инсталирах си един скрипт mysqltuner и се оказа че Max Memory Limit за MySQL е два пъти повече от реалната памет на сървъра. Като се поразрових се оказа че нищо не е както трябва в конфигурацията и направих промени почти по всички настройки.

В момента my.cnf настройките изглеждат по следния начин:
table_cache=2200
table_definition_cache=2200
query_cache_size=80M
query_cache_limit=8M
sort_buffer_size=8M
read_rnd_buffer_size=8M
open_files_limit=4800
max_heap_table_size=60M
tmp_table_size=60M
read_buffer_size=2M
innodb_buffer_pool_size=250M
thread_cache_size=25
join_buffer_size=15M
key_buffer=100M
max_connections=22
max_connect_errors=10
max_user_connections=22
wait_timeout=6000
interactive_timeout=6000
max_allowed_packet=4M
myisam_sort_buffer_size=16M
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow-query.log
long_query_time = 2
thread_concurrency=4

Apacehe-то е worker fastcgi с включени следните модули:
LoadModule auth_basic_module modules/mod_auth_basic.so
LoadModule authn_file_module modules/mod_authn_file.so
LoadModule authz_host_module modules/mod_authz_host.so
LoadModule authz_user_module modules/mod_authz_user.so
LoadModule include_module modules/mod_include.so
LoadModule log_config_module modules/mod_log_config.so
LoadModule env_module modules/mod_env.so
LoadModule expires_module modules/mod_expires.so
LoadModule deflate_module modules/mod_deflate.so
LoadModule headers_module modules/mod_headers.so
LoadModule setenvif_module modules/mod_setenvif.so
LoadModule mime_module modules/mod_mime.so
LoadModule autoindex_module modules/mod_autoindex.so
LoadModule dir_module modules/mod_dir.so
LoadModule actions_module modules/mod_actions.so
LoadModule alias_module modules/mod_alias.so
LoadModule substitute_module modules/mod_substitute.so
LoadModule rewrite_module modules/mod_rewrite.so
LoadModule cgi_module modules/mod_cgi.so
LoadModule version_module modules/mod_version.so
LoadModule fcgid_module /usr/lib/httpd/modules/mod_fcgid.so

Сървъра е със следните параметри:
Intel(R) Xeon(R) CPU E5-2630 0 @ 2.30GHz, 4 cores
Real memory:1.63GB(Реално са 1.70GB но явно част отиват за системни ресурси и толкова ми засича total)
Virtual memory:1GB


В момента mysqltuner ми показва че Max Memory Limit за MySQL е 1.13GB. Това добре ли е или трябва да го намаля или увелича. Коментари за самите настройки също са добре дошли, че сървърите не са ми силна страна. Притеснява ме най вече max_connections дали не е малко че преди беше 50, а mysqltuner ми показваше че max е достигало до 45 и ме съветваше да го увелича, но като го увелича скача и Max Memory Limit за MySQL


Ето и някои допълнителни данни:

>>top
6527 apache 20 0 62244 26m 6932 S 18.9 1.6 0:03.12 php-cgi
6315 apache 20 0 74908 39m 7396 S 17.3 2.3 0:29.32 php-cgi
6427 apache 20 0 73104 37m 7520 S 7.0 2.2 0:25.79 php-cgi
1116 mysql 20 0 603m 421m 5024 S 3.0 25.2 3:21.91 mysqld

mysqltuner
Total buffers: 492.0M global + 33.2M per thread (22 max threads)

Като бази данни имам:
Една с големина 1.2GB на InnoDB към която са по-голяма част от заявките над 50%
Една с големина около 400MB MyISAM
И други 10-тина около 100MB пак на MyISAM

 

[eclipze]

За: Колко RAM да заделя за MySQL

За: Колко RAM да заделя за MySQL

При тези таблици увеличи реалната памет поне на 4G за да работиш с тях, query_cache_size=80M и innodb_buffer_pool_size=250M са ти с доста малки стойности на фона на дадените размери на таблици ако имаш над 15% селекти спрямо всички заявки към тях. Виртуалната памет я разкарай, направи я не повече от 100 МБ, колкото да не си килваш всеки път латентните процеси като ти изяде mysql-a паметта.

Аз лично не бих сложил база данни на машина с под 16G памет, а реално нямам такава с под 48G, така че да си вземеш 2G отгоре е едно добро начало, което няма да те спасява дълго

Помисли и за междинно кеширане като например memcached, защото за query cache за mysql разумната граница е половин-1G поради чисто архитектурни проблеми. Реално погледнато забиваща база данни при уеб система се превежда като "повече селекти отколкото може да понесе сървъра", а кеширането ще ти удължи времето до по-сериозен ъпгрейт.

 

[s1yf0x]

За: Колко RAM да заделя за MySQL

За: Колко RAM да заделя за MySQL

Сигурен ли си, че в момента на забиването точно недостиг на RAM-а е проблема? Машината прави ли много SWAP ?

 

[cyberpower]

Re: Колко RAM да заделя за MySQL

eclipze
Знам че query_cache_size=80M и innodb_buffer_pool_size=250M са малко. Всъщност скрипта за който споменах по горе mysqltuner ми показва че query_cache_size е в норма, но за innodb_buffer_pool_size ми препоръчва да го вдигна до размера на базата данни която е на innodb тоест на 1.2GB.

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


s1yf0x
Да и Да. То реално не забива де почва да работи много много бавно и накрая просто трябва да рестартираш. Поне съм на cloud hosting и като се случи мога да вдигна временно ресурсите. През времето когато няма проблем не прави много swap.

 

[s1yf0x]

За: Колко RAM да заделя за MySQL

За: Колко RAM да заделя за MySQL

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

Важно е да разбереш кое кара mysql да работи бавно - дали е бавна заявка или голям брой заявки. Това което описваш е почти сигурно, че е бавна заявка. Гледал ли си в /var/log/mysql/slow-query.log ?

 

[cyberpower]

Re: Колко RAM да заделя за MySQL

Вчера занулирах файла точно да видя ще излезе нещо от там но за сега няма при long_query_time = 2

Трябва да му задам да следи и за joins без indexes че вчера ги оправих част от заявките но останаха още.

Мисля че проблема по скоро е че connections към базата данни не се затварят правилно и навреме. Няма чак такава конкуренция че да изразходва всичките connections.

Ще пробвам довечера да сменя apache-то на prefork с mod_php да видим дали ще има резултат.

 

[s1yf0x]

За: Колко RAM да заделя за MySQL

За: Колко RAM да заделя за MySQL

Ако беше от броя връзки, mysql щеше да ти връща грешка и в съобщението за грешка да пише, че max_connections или max_user_connections не са достатъчно. Освен това, ако не са активини, т.е. не изпълняват заявка те няма да ти лапат от RAM-а.

Apache като worker + fcgid си е добър избор, защо се връщаш към prefork + mod_php ?

 

[eclipze]

За: Колко RAM да заделя за MySQL

За: Колко RAM да заделя за MySQL

Със сигурност не ти е от конекциите, самата конекция хаби пренебрежимо малко ресурси, създаването на нова нишка от друга страна да, но thread_cache_size=25 си е повече от ок, за слабо натоварване. Но ако на същата машина имаш и друго освен mysql, отиваш все повече в проблем с паметта. А join-овете правят врмеенни таблици те със сигурност го задълбочават.

Погледни го от тази гледна точка, имаш софтуер който трябва да отвори 1.2GB, по няколко пъти в секунда, дори и да го прави само на малки сегменти, пак 2 GB изглеждат неразумно малко. От друга страна за да ти работи нормално софтуер интензивно обработващ файлове разумното е да обработва кеширани версии на файловете и на файловата система да и остава само да ги синква с дисковете, а не да чете всеки файл по отделно всеки път. От там нататък тръгваш с кеширането на mysql, а след това следва изчакването на заявките при голяма конкурентност. Всичко това е RAM.

Ако увеличението на рама е трудна опция, поне си разбий таблиците на по-малки. Трудно ми е да повярвам, че добре проектирана DB има една-две огромни таблици и няколко малки.

 

[cyberpower]

Re: Колко RAM да заделя за MySQL

s1yf0x
При max_connections=22 директно контент мениджмънт системата от време на време ми връща че няма връзка към базата данни, даже при 30, 40 го има проблема. Не знаех че неактивните не хабят памет мислех че като има отворена то за нея се заделя памет независимо дали е активна.

За prefork + mod_php мислех просто да тествам, а и при този вариант четох някъде че обработва по различен начин връзките към базата данни. Знам ли може да се пооправи или да се влоши, но сайта определено се зарежда по бързо на prefork + mod_php, преди доста време беше на този вариант, но после минахме на worker + fcgid по препоръки от форумите.

eclipze
Не си разбрал правилно май не таблицата е голяма 1.2GB, а имам една база данни която е голяма 1.2GB вътре в нея има около 120 таблици. Имам и големи таблици вътре разбира се едната е голяма около 400MB, но резултата от повечето заявките към нея се пазят във временни кеш таблици и до като няма промяна в таблицата се връща кеширания резултат.

 

[Dimo]

За: Re: Колко RAM да заделя за MySQL

За: Re: Колко RAM да заделя за MySQL

Тунинга на sql бази данни си е тънка работа. Прекарвал съм по цели дни в тестове на различни настройки към определена база.
Но като цяло най-много тормозят join-овете. Даже за голяма база данни най-добре да се избегнат.
Също така опростяването на select-овете. Пример:
SELECT * FROM ... в повечето случаи едва ли ви трябват всички полета и можете да го преобразувате в
SELECT col1,col2 ... като това ще ви спести доста натоварване и т.н