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

Дефолт­ные кон­фи­гу­ра­ци­он­ные пара­мет­ры в Mysql рас­счи­та­ны на мик­ро­ско­пи­че­ские базы дан­ных, рабо­та­ю­щие под малы­ми нагруз­ка­ми на скром­ном железе.

Про­цесс опти­маль­ной настрой­ки Mysql состо­ит из двух частей — пер­во­на­чаль­ная настрой­ка и кор­рек­ти­ров­ка пара­мет­ров во вре­мя рабо­ты. Кор­рек­ти­ров­ка пара­мет­ров в рабо­чем режи­ме во мно­гом зави­сит от спе­ци­фи­ки Вашей систе­мы и ее мони­то­рин­га. Раз­бе­рем­ся с пара­мет­ра­ми и реко­мен­да­ци­я­ми по уста­нов­ке их значений.

Настрой­ки нуж­но вно­сить в my.cnf.

innodb_buffer_pool_size

Если Вы исполь­зу­е­те толь­ко InnoDB таб­ли­цы, уста­нав­ли­вай­те это зна­че­ние мак­си­маль­но воз­мож­ным для Вашей систе­мы. Буфер InnoDB кеши­ру­ет и дан­ные и индек­сы. Поэто­му зна­че­ние это­го клю­ча сто­ит уста­нав­ли­вать в 70%...80% всей доступ­ной памяти.

# При том, что на нашем сер­ве­ре 32Гб опе­ра­тив­ной памяти

innodb_log_file_size

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

# Так два фай­ла дадут раз­мер лога в 2x512M = 1G

Сто­ит пони­мать, что уве­ли­че­ние это­го пара­мет­ра уве­ли­чит и вре­мя вос­ста­нов­ле­ния систе­мы при сбо­ях. Это про­ис­хо­дит пото­му, что при запус­ке систе­мы все дан­ные из логов будет нака­ты­вать­ся на дан­ные. Одна­ко с каж­дой новой вер­си­ей, про­из­во­ди­тель­ность это­го про­цес­са рас­тет. Поду­май­те над исполь­зо­ва­ни­ем реплик для обес­пе­че­ния доступ­но­сти, что­бы не зави­сеть от вре­ме­ни вос­ста­нов­ле­ния базы данных.

innodb_log_buffer_size

Это раз­мер буфе­ра тран­зак­ций, кото­рые не были еще зако­ми­че­ны. Зна­че­ние это­го пара­мет­ра сто­ит менять в слу­ча­ях, если вы исполь­зу­е­те боль­шие поля вро­де BLOB или TEXT.

# Зна­че­ния по умол­ча­нию в 1М долж­но быть доста­точ­но для боль­шин­ства случаев

innodb_file_per_table

Если вклю­чить эту опцию, Innodb будет сохра­нять дан­ные всех таб­лиц в отдель­ных фай­лах (вме­сто одно­го фай­ла по умол­ча­нию). При­ро­ста в про­из­во­ди­тель­но­сти не будет, одна­ко есть ряд преимуществ:

• При уда­ле­нии таб­лиц, диск будет осво­бож­дать­ся. По умол­ча­нию общий файл дан­ных может толь­ко рас­ши­рять­ся, но не уменьшаться.
• Исполь­зо­ва­ние ком­прес­си­он­но­го фор­ма­та таб­лиц потре­бу­ет вклю­чить этот параметр.

# С вер­сии 5.6 этот пара­метр вклю­чен по умолчанию

innodb_flush_method

Этот пара­метр опре­де­ля­ет логи­ку сбро­са дан­ных на диск. В совре­мен­ных систе­мах при исполь­зо­ва­нии RAID и резерв­ных узов, вы буде­те выби­рать меж­ду O_DSYNC и O_DIRECT:

# Помни­те об обя­за­тель­ном исполь­зо­ва­нии резерв­ных узлов (напри­мер, реплик)

innodb_flush_log_at_trx_commit

Изме­не­ние это­го пара­мет­ра может повы­сить про­пуск­ную спо­соб­ность запи­си дан­ных в базу в сот­ни раз. Он опре­де­ля­ет, будет ли Mysql сбра­сы­вать каж­дую опе­ра­цию на диск (в файл лога).

Тут сле­ду­ет руко­вод­ство­вать­ся такой логикой:

• innodb_flush_log_at_trx_commit = 1 для слу­ча­ев, когда сохран­ность дан­ных — это при­о­ри­тет номер один.
• innodb_flush_log_at_trx_commit = 2 для слу­ча­ев, когда неболь­шая поте­ря дан­ных не кри­тич­на (напри­мер, вы исполь­зу­е­те дуб­ли­ро­ва­ние и смо­же­те вос­ста­но­вить неболь­шую поте­рю). В этом слу­чае тран­зак­ции будут сбра­сы­вать­ся в лог на диск толь­ко раз в секунду.

Уста­нав­ли­вай­те зна­че­ние на свое усмот­ре­ние, одна­ко в боль­шин­стве слу­ча­ев подой­дет вто­рой вариант:

# Зна­чи­тель­ное уско­ре­ние запи­си в базу, одна­ко это потре­бу­ет меха­низ­мов дуб­ли­ро­ва­ния данных

query_cache_size

Зна­че­ние это­го пара­мет­ра опре­де­ля­ет сколь­ко памя­ти сто­ит исполь­зо­вать под кеш запро­сов. Самый пра­виль­ный под­ход — не пола­гать­ся на этот меха­низм. На прак­ти­ке он рабо­та­ет очень неэф­фек­тив­но. Так, весь кеш запро­сов для опре­де­лен­ной таб­ли­цы сбра­сы­ва­ет­ся вся­кий раз, когда в таб­ли­цу вно­сит­ся хотя бы одно изме­не­ние. Это может при­ве­сти к тому, что вклю­че­ние кеши­ро­ва­ния даже замед­лит базу данных:

# Одна­ко убе­ди­тесь, что исполь­зу­е­те индек­сы для обес­пе­че­ния высо­кой ско­ро­сти рабо­ты запросов

max_connections

Не сле­ду­ет изме­нять зна­че­ние это­го пара­мет­ра на стар­те. Одна­ко, если вы полу­ча­е­те ошиб­ки "Too many connections", эту опцию сто­ит под­ни­мать. Она опре­де­ля­ет мак­си­маль­ное коли­че­ство одно­вре­мен­ных соеди­не­ний с базой данных:

# Под­ни­май­те зна­че­ние посте­пен­но при появ­ле­нии оши­бок соединений

Настрой­ки по умол­ча­нию ско­рее все­го не подой­дут. Поэто­му обя­за­тель­но сто­ит прой­тись по ука­зан­ным пара­мет­рам в ста­тье и подо­брать для них зна­че­ния. Если совсем лень — гене­ра­тор настро­ек Mysql.