настроить производительность сервера базы данных MariaDB

Thank you for reading this post, don't forget to subscribe!

Сервер базы дан­ных явля­ет­ся кри­ти­че­ски важ­ным ком­по­нен­том сете­вой инфра­струк­ту­ры, необ­хо­ди­мой для совре­мен­ных при­ло­же­ний. Не имея воз­мож­но­сти хра­не­ния, извле­че­ния, обнов­ле­ния и уда­ле­ния дан­ных (при необ­хо­ди­мо­сти), полез­ность и объ­ем веб-и настоль­ных при­ло­же­ний ста­но­вит­ся очень ограниченным.

Кро­ме того, зная, как уста­нав­ли­вать, управ­лять и настра­и­вать сер­вер базы дан­ных (так что­бы он рабо­тал, как ожи­да­лось) явля­ет­ся одним из важ­ней­ших навы­ков, и каж­дый систем­ный адми­ни­стра­тор дол­жен это знать.

В этой ста­тье мы крат­ко рас­смот­рим, как уста­но­вить сер­вер базы дан­ных MariaDB, и затем мы рас­ска­жем, как настро­ить его.

Установка и обеспечение безопасности сервера MariaDB

В CentOS 7.x , MariaDB заме­нен на MySQL, кото­рый до сих пор мож­но най­ти в Ubuntu (наря­ду с MariaDB). То же самое вер­но и для OpenSUSE .

Для крат­ко­сти мы будем исполь­зо­вать толь­ко MariaDB в этом учеб­ни­ке, но обра­ти­те вни­ма­ние, что поми­мо того, что раз­лич­ные име­на и фило­со­фии раз­ви­тия, как реля­ци­он­ная база дан­ных систе­мы управ­ле­ния ( РСУБД для крат­ко­сти) прак­ти­че­ски идентичны.

Это озна­ча­ет , что на сто­роне кли­ен­та коман­ды оди­на­ко­вы на обо­их MySQL и MariaDB и кон­фи­гу­ра­ци­он­ные фай­лы назва­ны и рас­по­ло­же­ны в одних и тех же местах.

Что­бы уста­но­вить MariaDB, сде­лай­те следующее:

 

Обра­ти­те вни­ма­ние, что в Ubuntu, вам будет пред­ло­же­но вве­сти пароль для РСУБД поль­зо­ва­те­ля root.

После того, как были уста­нов­ле­ны выше­ука­зан­ные паке­ты, убе­ди­тесь, что служ­ба базы дан­ных запу­ще­на и акти­ви­ро­ва­на для запус­ка при загруз­ке (в CentOS и OpenSUSE вам нуж­но будет выпол­нить эту опе­ра­цию вруч­ную, в то вре­мя как в Ubuntu про­цесс уста­нов­ки уже поза­бо­ти­лись об этом для вас):

 

Затем запу­сти­те скрипт mysql_secure_installation. Этот про­цесс поз­во­лит вам:

  1. Уста­но­вить / сбро­сить пароль для РСУБД поль­зо­ва­те­ля root
  2. Уда­лить ано­ним­ные логи­ны (что поз­во­ля­ет толь­ко поль­зо­ва­те­лям с допу­сти­мой учет­ной запи­сью, что­бы вой­ти в РСУБД)
  3. Отклю­чить доступ root для целей, отлич­ных от машин локаль­но­го хоста
  4. Уда­лить тесто­вую базу дан­ных (к кото­рый любой жела­ю­щий может полу­чить доступ)
  5. Акти­ви­ро­вать изме­не­ния, свя­зан­ные с 1 по 4.

Для более подроб­но­го опи­са­ния это­го про­цес­са, вы може­те обра­тить­ся к раз­де­лу уста­нов­ки в посте Уста­нов­ка MariaDB базы дан­ных в RHEL / CentOS / Fedora и Debian / Ubuntu.

Настройка сервера MariaDB

Пара­мет­ры кон­фи­гу­ра­ции по умол­ча­нию счи­ты­ва­ют­ся из сле­ду­ю­щих фай­лов в задан­ном поряд­ке: /etc/mysql/my.cnf/etc/my.cnf, и ~/.my.cnf.

Чаще все­го суще­ству­ет толь­ко /etc/my.cnf. Имен­но в этом фай­ле, в кото­рый мы уста­но­вим обще­сер­вер­ных настрой­ки (кото­рые могут быть пере­опре­де­ле­ны с теми же настрой­ка­ми ~/.my.cnf для каж­до­го пользователя).

Пер­вое, что нам необ­хо­ди­мо отме­тить о my.cnf, что пара­мет­ры раз­би­ты на кате­го­рии (или груп­пы), где каж­дое назва­ние кате­го­рии, заклю­чен­ное в квад­рат­ные скобки.

Систем­ные кон­фи­гу­ра­ции сер­ве­ра при­ве­де­ны в раз­де­ле [mysqld], где, как пра­ви­ло, вы най­де­те толь­ко пер­вые два пара­мет­ра в при­ве­ден­ной ниже таб­ли­це. Осталь­ные дру­гие часто исполь­зу­е­мые пара­мет­ры (если не ука­за­но иное, мы изме­ним зна­че­ние по умол­ча­нию на поль­зо­ва­тель­ское зна­че­ние по наше­му выбору):

Уста­нов­ка и описание Зна­че­ние по умолчанию
datadir это ката­лог , где сохра­ня­ют­ся фай­лы данных. datadir=/var/lib/mysql
socket ука­зы­ва­ет имя и место­по­ло­же­ние фай­ла соке­та, кото­рый исполь­зу­ет­ся для локаль­ных кли­ент­ских под­клю­че­ний. Имей­те в виду, что файл соке­та явля­ет­ся ресур­сом, кото­рый исполь­зу­ет­ся для пере­да­чи инфор­ма­ции меж­ду приложениями. socket=/var/lib/mysql/mysql.sock
bind_address это адрес, по кото­ро­му сер­вер базы дан­ных будет про­слу­ши­вать соеди­не­ния по TCP / IP. Если вам нужен сер­вер для про­слу­ши­ва­ния на более чем один IP-адрес, оставь­те эту настрой­ку (0.0.0.0, кото­рый озна­ча­ет, что он будет про­слу­ши­вать все IP-адре­са, при­сво­ен­ные это­му кон­крет­но­му хозя­и­ну). Мы изме­ним эту функ­цию, что­бы пору­чить обслу­жи­ва­ние слу­шать толь­ко на его основ­ной адрес (192.168.0.13):bind_address = 192.168.0.13 bind_address=0.0.0.0
port пред­став­ля­ет собой порт, на кото­ром сер­вер базы дан­ных будет про­слу­ши­вать. Мы заме­ним зна­че­ние по умол­ча­нию (3306) с 20500 (но нам нуж­но убе­дить­ся, что ниче­го не исполь­зу­ет этот порт):port = 20500

В то вре­мя как неко­то­рые люди утвер­жда­ют, что без­опас­ность через неиз­вест­ность не явля­ет­ся хоро­шей прак­ти­кой, изме­не­ние пор­тов при­ло­же­ний по умол­ча­нию для более высо­ких явля­ет­ся руди­мен­тар­ным -Допол­ни­тель­ное эффек­тив­но – метод, что­бы пре­пят­ство­вать ска­ни­ро­ва­ния портов.

port=3306
innodb_buffer_pool_size явля­ет­ся буфер­ный пул (в бай­тах) памя­ти, кото­рая выде­ля­ет­ся для дан­ных и индек­сов, кото­рые доступ­ны часто при исполь­зо­ва­нии Innodb (кото­рый по умол­ча­нию в MariaDB) или XtraDB в каче­стве меха­низ­ма хра­не­ния. Мы заме­ним зна­че­ние по умол­ча­нию 256 МБ:innodb_buffer_pool_size = 256M innodb_buffer_pool_size = 134217728
skip_name_resolve ука­зы­ва­ет на то, будет ли раз­ре­ше­ны или нет име­на хостов для вхо­дя­щих соеди­не­ний. Если уста­нов­ле­но зна­че­ние 1, как мы будем делать в дан­ном руко­вод­стве, т.е. толь­ко IP-адре­са. Если вам не тре­бу­ет­ся имя хоста для опре­де­ле­ния раз­ре­ше­ний, реко­мен­ду­ет­ся отклю­чить эту пере­мен­ную (для того, что­бы повы­сить ско­рость соеди­не­ния и запро­сы), уста­но­вив его зна­че­ние на 1:skip_name_resolve = 1 skip_name_resolve=0
query_cache_size пред­став­ля­ет раз­мер (в бай­тах), доступ­ной для кэша запро­сов на дис­ке, где резуль­та­ты запро­сов SELECT, сохра­ня­ют­ся для даль­ней­ше­го исполь­зо­ва­ния при выпол­не­нии иден­тич­ных запро­сов (к той же базе дан­ных и исполь­зуя тот же про­то­кол и тот же набор сим­во­лов). Вы долж­ны выбрать раз­мер кэша запро­сов, кото­рый соот­вет­ству­ет вашим потреб­но­стям на осно­ве 1) чис­ло повтор­ных запро­сов, и 2) при­бли­зи­тель­ное коли­че­ство запи­сей, где повто­ря­ют­ся эти запро­сы, как ожи­да­ет­ся, что­бы вер­нуть­ся. Мы уста­но­вим это зна­че­ние до 100 МБ на дан­ный момент:query_cache_size = 100M query_cache_size=0 (which means it is disabled by default)
max_connections мак­си­маль­ное коли­че­ство одно­вре­мен­ных кли­ент­ских под­клю­че­ний к сер­ве­ру. Мы уста­но­вим это зна­че­ние до 30:max_connections = 30

Если соеди­не­ние будет исполь­зо­вать нить, и ,таким обра­зом, будет потреб­лять память. При­ми­те во вни­ма­ние этот факт при уста­нов­ке max_connections.

max_connections=151
thread_cache_size ука­зы­ва­ет коли­че­ство пото­ков, кото­рые сер­вер выде­ля­ет для повтор­но­го исполь­зо­ва­ния после отклю­че­ния кли­ен­та и высво­бож­да­ет нить(и) ранее исполь­зо­ван­ные. В этой ситу­а­ции, это дешев­ле (с точ­ки зре­ния про­из­во­ди­тель­но­сти), что­бы повтор­но исполь­зо­вать нить, чем добав­лять новую. Опять же, это зави­сит от коли­че­ства соеди­не­ний, кото­рое вы ожи­да­е­те. Мы можем с уве­рен­но­стью уста­но­вить это зна­че­ние на поло­ви­ну чис­ла max_connections:thread_cache_size = 15 thread_cache_size=0 (disabled by default)

В CentOS , нам нуж­но будет сооб­щить SELinux, что­бы поз­во­лить MariaDB слу­шать на нестан­дарт­ном пор­ту ( 20500 ) до пере­за­пус­ка службы:

 

Затем пере­за­пу­стить служ­бу MariaDB.

Настройка производительности MariaDB

Что­бы помочь нам в про­вер­ке и настрой­ке кон­фи­гу­ра­ции в соот­вет­ствии с наши­ми кон­крет­ны­ми потреб­но­стя­ми, мы можем уста­но­вить mysqltuner (скрипт , кото­рый будет предо­став­лять пред­ло­же­ния по улуч­ше­нию рабо­ты наше­го сер­ве­ра баз дан­ных и повы­ше­ния ее устойчивости):

 

Затем изме­ни­те ката­лог в пап­ку, извле­чен­ной из архи­ва (точ­ная вер­сия может отли­чать­ся в вашем случае):

 

и запу­сти­те его (вам будет пред­ло­же­но вве­сти дан­ные учет­ной запи­си адми­ни­стра­то­ра MariaDB)

 

Выход сце­на­рия само по себе очень инте­ре­сен, но давай­те пере­хо­дить к ниж­ней части, где пере­мен­ные для настрой­ки пере­чис­ле­ны с реко­мен­ду­е­мо­го значения:

Параметр query_cache_type ука­зы­ва­ет на то, отклю­чен ли кэш запро­сов (0) или вклю­чен (1) . В этом слу­чае mysqltuner сове­ту­ет нам, что­бы отклю­чить его.

Так поче­му же мы сове­ту­ем отклю­чить его сей­час? При­чи­на заклю­ча­ет­ся в том, что кэш запро­сов поле­зен в основ­ном в сце­на­ри­ях высо­ких чте­ния / запи­си с низ­ким уров­нем (кото­рый не наш слу­чай, так как мы толь­ко что уста­но­ви­ли сер­вер базы данных).

ВНИМАНИЕ : Перед вне­се­ни­ем изме­не­ний в кон­фи­гу­ра­цию про­из­во­ди­тель­но­сти сер­ве­ра, насто­я­тель­но реко­мен­ду­ет­ся про­кон­суль­ти­ро­вать­ся с экс­пер­том адми­ни­стра­то­ра базы дан­ных, что­бы гаран­ти­ро­вать, что реко­мен­да­ция дает­ся mysqltuner не ска­жет­ся нега­тив­но на суще­ству­ю­щей обстановке.