Оптимизация на mysql сървър за Debian/Ubuntu OS при cloud и наети сървъри

Помощен център

Оптимизация на mysql сървър за Debian/Ubuntu OS при cloud и наети сървъри

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

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

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

Разбира се има основни параметри и опции, които зададени като минимални стойности е по-добре отколкото да са изключени. За съжаление при началната инсталация на mysql сървър на вашата виртуална машина, при някой дистрибуции на Linux, mysql сървърът стартира с минимални параметри и дори изключени такива, което от своя страна води до по-ниска производителност на вашето динамично приложение.

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

Да започнем и със самата оптимизация! FriendlyGeeks екипът на ICN.Bg ви препоръчва един страхотен perl скрипт, който ще провери  статуса на вашия mysql сървър, извършвайки необходимите проверки вместо вас. Като резултат от проверката, скриптът ще ви покаже кои са параметрите, които е необходимо да бъдат коригирани.

Информация за perl скрипта може да намерите тук:

<https://github.com/major/MySQLTuner-perl>

Необходимо е да имате няколко допълнителни софтуерни програми, с които да извършите изтеглянето на zip архива от github, след което да го разархивирате.

При Debian базирани системи:

su --

Логвате се в системата като root:

apt-get update
yes | apt-get install wget

След като изпълните горепосочените команди, ако имате инсталиран wget, ще се изпише, че той вече е инсталиран -- wget is already the newest version. В противен случай ще бъде инсталиран.  Необходимо е да инсталирате и unzip, тъй като ще ви е необходим за разархивирането на master.zip файла, който ще изтеглите.

yes | apt-get install unzip

Изтегляте perl скрипта от github, със следната команда, но преди това е добре да отидете в директория /opt като изпълните следната команда:

cd /opt
wget https://github.com/major/MySQLTuner-perl/archive/master.zip

Изходът трябва да е подобен на долуописания:

2014-07-04 13:02:34 (198 KB/s) - "master.zip" saved [26513]

След което разархивирате master.zip файла със следната команда:

unzip master.zip

Влизате в папката, където се намира скрипта:

cd /MySQLTuner-perl-master

Листвате съдържанието на папката:

ls

В нея трябва да откриете следните три файла:

LICENSE  mysqltuner.pl  README.md

Можете да разгледате внимателно README.md файла.  На нас ни е необходим файла mysqltuner.pl. Преди да продължим нататък, за да можете всеки път да изпълнявате скрипта без да е необходимо въвеждане на root паролата за mysql сървъра, ще направим малък трик.

Копираме mysqltuner.pl в /root със следната команда:

cp mysqltuner.pl /root

Влизате в /root директорията.

cd /root

Създавате файл .my.cnf:

nano .my.cnf

и в него въвеждате следното съдържание:

[client] user=root password=(веднага след знака"=", без интервал и без скобите въведете вашата парола за mysql)

Запаметявате с [CTRL]+[X] -> yes

Променяте правата на файла, така че той да e видим само за root потребителя:

chmod 600 .my.cnf

Въпреки, че файлът би трябвало да е executable, за всеки случай може да повторите:

chmod +x  mysqltuner.pl

Вече сте готови да изпълните скрипта с командата:  

./mysqltuner.pl

Резултатът трябва да изглежда по този начин:

# ./mysqltuner.pl

 >>  MySQLTuner 0.9.8 - Major Hayden major@mhtx.net

 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/

 >>  Run with '--help' for additional options and output filtering

Please enter your MySQL administrative login:**<-- root

Please enter your MySQL administrative password:**<-- yourrootsqlpassword

-------- General Statistics --------------------------------------------------

[--] Skipped version check for MySQLTuner script

[!!] Your MySQL version 4.1.11-Debian_etch1-log is EOL software!  Upgrade soon!

[OK] Operating on 32-bit architecture with less than 2GB RAM

-------- Storage Engine Statistics -------------------------------------------

[--] Status: +Archive -BDB -Federated +InnoDB +ISAM -NDBCluster

[--] Data in MyISAM tables: 301M (Tables: 2074)

[--] Data in HEAP tables: 379K (Tables: 9)

[!!] InnoDB is enabled but isn't being used

[!!] ISAM is enabled but isn't being used

[!!] Total fragmented tables: 215

-------- Performance Metrics -------------------------------------------------

[--] Up for: 12d 18h 33m 30s (1B q [1K qps], 185K conn, TX: 3B, RX: 377M)

[--] Reads / Writes: 78% / 22%

[--] Total buffers: 2.6M per thread and 58.0M global

[OK] Maximum possible memory usage: 320.5M (20% of installed RAM)

[OK] Slow queries: 0% (17/1B)

[OK] Highest usage of available connections: 32% (32/100)

[OK] Key buffer size / total MyISAM indexes: 16.0M/72.3M

[OK] Key buffer hit rate: 99.9%

[OK] Query cache efficiency: 99.9%

[!!] Query cache prunes per day: 47549

[OK] Sorts requiring temporary tables: 0%

[!!] Temporary tables created on disk: 28%

[OK] Thread cache hit rate: 99%

[!!] Table cache hit rate: 0%

[OK] Open file limit used: 12%

[OK] Table locks acquired immediately: 99%

[!!] Connections aborted: 20%

-------- Recommendations -------------------------------------------------

General recommendations:

    Add skip-innodb to MySQL configuration to disable InnoDB

    Add skip-isam to MySQL configuration to disable ISAM

    Run OPTIMIZE TABLE to defragment tables for better performance

    Enable the slow query log to troubleshoot bad queries

*    When making adjustments, make tmptablesize/maxheaptable_size equal*

    Reduce your SELECT DISTINCT queries without LIMIT clauses

    Increase table_cache gradually to avoid file descriptor limits

    Your applications are not closing MySQL connections properly

Variables to adjust:

*    querycachesize (> 16M)*

*    tmptablesize (> 32M)*

*    maxheaptable_size (> 16M)*

*    table_cache (> 64)**

Важните неща, които трябва да прочетете са oт Recommendations надолу като настройките, които трябва да промените можете да видите надолу от Variables to adjust:. Настройките се задават в конфигурационния файл на myslq сървъра, който в debian базираните системи се намира в директория:

/etc/mysql/my.cnf

Важно: Преди да направите каквито и да е промени в този файл, е добре да създадете резервно копие (backup). За да направим backup, отиваме в директорията # cd /etc/mysql/ и правим копие изпълнявайки командата:

# cp my.cnf my.cnf.backup

Отваряме файла с текстов редактор с командата  # nano my.cnf.

След реда [mysqld], трябва да откриете следните стойностите:

    query_cache_size (> 16M)
    tmp_table_size (> 32M)
    max_heap_table_size (> 16M)
    table_cache (> 64)

тези стойности трябва да са зададени със знак =. В този файл не би трябвало да има стойности зададени съз знак < или >!  След като ги откриете, задавате стойностите, които ви дава скрипта, а именно следните:

Variables to adjust:

*    querycachesize (> 16M)*

*    tmptablesize (> 32M)*

*    maxheaptable_size (> 16M)*

    table_cache (> 64)

Имайте предвид, че ако базата данни не е добре направена, колкото и да увеличавате стойността на *joinbuffersize*, винаги ще иска повече и повече, но проблемът: [!!] Joins performed without indexes: 16751, винаги ще е наличен. Така че не прекалявайте с увеличаването на тази стойност.

Стойностите се задават по този начин:

query_cache_size = 64М
tmp_table_size   = 32М

След като коригирате параметрите записвате файла със същото имe (при nano ctrl+x, след това кликате yes) и задължително тряба да рестартирате mysql, като е добра практика преди това да се спре web сървъра. Това можете да направите по следния начин:

/etc/init.d/apache2 stop

изчаквате да спре и изпълнявате:

/etc/init.d/mysql    restart

изчаквате да видите следното:

Starting MySQL database server: mysqld.
Checking for corrupt, not cleanly closed and upgrade needing tables..

Ако mysql-сървърът върне отговор [failed], не се безпокойте, защото лесно и бързо можете да върнете файла, който сте архивирали по-рано по следния начин:

cp my.cnf.backup my.cnf

и отново стартирате mysql:

/etc/init.d/mysql start

Стартирате отново web сървъра:

/etc/init.d/apache2 start

Проверявате дали приложението работи!

Можете да стартирате # ./mysqltuner.pl колкото пъти желаете докато не достигнете желаните от вас резултати.

ВАЖЕН СЪВЕТ! Наблюдавайте този параметър:

[OK] Maximum possible memory usage: 320.5M (20% of installed RAM) 

Процентният индикатор не трябва да надвишава 70%. Все пак вашият сървър има нужда от RAM памет и за другите работещи процеси.