Пер­во­на­чаль­ная настрой­ка mysql-server

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

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

key_buffer_size - очень важ­ный пара­метр, если вы исполь­зу­е­те MyISAM-таб­ли­цы. Уста­но­ви­те его рав­ным 30-40% от име­ю­щей­ся опе­ра­тив­ной памя­ти, если вы исполь­зу­е­те толь­ко MyISAM. Акту­аль­ное для вашей систе­мы зна­че­ние зави­сит от индек­сов, раз­ме­ра дан­ных и рабо­че­го про­цес­са. Помни­те, MyISAM исполь­зу­ет кэш опе­ра­ци­он­ной систе­мы для хра­не­ния дан­ных и вам необ­хо­ди­мо поза­бо­тить­ся о доста­точ­ных раз­ме­рах выде­ля­е­мой памя­ти. Во мно­гих слу­ча­ях, объ­ем дан­ных может быть зна­чи­тель­но боль­ше. Про­верь­те, не исполь­зу­ет­ся ли завы­шен­ное зна­че­ние key_buffer. Неред­ко пара­метр уста­нов­лен в зна­че­ние 4GB при сум­мар­ном объ­е­ме .MYI-фай­лов в один гига­байт. Это напрас­ная тра­та ресур­сов. Если вы исполь­зу­е­те несколь­ко таб­лиц MyISAM - понизь­те зна­че­ние это­го пара­мет­ра. Но не опус­кай­те его ниже 16-23 MB - это­го будет доста­точ­но для раз­ме­ще­ния индек­сов вре­мен­ных таб­лиц, кото­рые созда­ют­ся на диске.

innodb_buffer_pool_size - это очень важ­ный пара­метр для настрой­ки InnoDB. Таб­ли­цы это­го типа гораз­до более чув­стви­тель­ны к раз­ме­ру буфе­ра, неже­ли MyISAM. MyISAM может нор­маль­но рабо­тать даже при дефолт­ном зна­че­нии buffer_size, в отли­чии от InnoDB, про­из­во­ди­тель­ность кото­рых будет замет­но ниже при зна­че­нии innodb_buffer_pool_size по умол­ча­нию и боль­ших объ­е­мах дан­ных. Так­же пул буфе­ров InnoDB само­сто­я­тель­но кэши­ру­ет индек­сы и дан­ные, так что не нуж­но остав­лять место для кэша ОС. Обыч­но пред­по­ла­га­ет­ся выде­ле­ние 70 - 80% памя­ти для сер­ве­ров, на кото­рых ниче­го не запу­ще­но, кро­ме InnoDB. Неко­то­рые пра­ви­ла key_buffer при­ме­ни­мы и в этом пара­мет­ре: если у вас неболь­шие объ­е­мы дан­ных и они не соби­ра­ют­ся стре­ми­тель­но уве­ли­чи­вать­ся, не завы­шай­те зна­че­ние innodb_buffer_pool_size, вы смо­же­те най­ти сво­бод­ной опе­ра­тив­ной памя­ти луч­шее при­ме­не­ние.innodb_additional_mem_pool_size - этот пара­метр не име­ет силь­но­го вли­я­ния на про­из­во­ди­тель­ность. По край­ней мере в опе­ра­ци­он­ных систе­мах с гра­мот­ным рас­пре­де­ле­ни­ем памя­ти. Но вы може­те уста­но­вить зна­че­ние это­го пара­мет­ра раным 20MB (ино­гда боль­ше) и вы може­те видетm сколь­ко памя­ти выде­ля­ет InnoDB для раз­лич­ных нужд.

innodb_log_file_size - очень важ­ный пара­метр для систем с интен­сив­ной запи­сью, осо­бен­но боль­ших объ­е­мов дан­ных. Уве­ле­че­ние зна­че­ния это­го пара­мет­ра обыч­но дает при­рост про­из­во­ди­тель­но­сти, но будь­те осто­рож­ны. Обыч­но я исполь­зую зна­че­ния 64M - 512 MB в зави­си­мо­сти от сервера.

innodb_log_buffer_size - зна­че­ние по умол­ча­нию вполне подой­дет для боль­шин­ства про­ек­тов со сред­ней интен­сив­но­стью запи­си и корот­ки­ми тран­зак­ци­я­ми. Одна­ко если у вас быва­ют пики актив­но­сти или рабо­та с боль­шим объ­е­мом дан­ных, вы, веро­ят­но, захо­ти­те уве­ли­чить зна­че­ние это­го пара­мет­ра. Не делай­те его слиш­ком боль­шим, это повле­чет лиш­ний рас­ход памя­ти. Буфер сбра­сы­ва­ет­ся каж­дую секун­ду и вам не нужен бОль­ший объ­ем памя­ти. Обыч­но вполне хва­та­ет 8 - 16МB. Чем мень­ше систе­ма - тем мень­ше долж­но быть значение.

innodb_flush_logs_at_trx_commit - Вам кажет­ся, что InnoDB в сто раз мед­лен­нее MyISAM? Веро­ят­но, вы забы­ли изме­нить зна­че­ние это­го пара­мет­ра. Зна­че­ние по умол­ча­нию 1 озна­ча­ет, что после каж­дой завер­шен­ной тран­зак­ции (или после изме­не­ния состо­я­ния тран­зак­ции) лог дол­жен быть сбро­шен на диск. Это доста­точ­но доро­гая опе­ра­ция, осо­бен­но если у вас нет Battery backed up cache. Мно­гие при­ло­же­ния, осо­бен­но те, в кото­рых рань­ше исполь­зо­вал­ся MyISAM будут хоро­шо рабо­тать при зна­че­нии 2, кото­рый озна­ча­ет, что не надо сбра­сы­вать буфер на диск, а сле­ду­ет отпра­вить его в кэш опе­ра­ци­он­ной систе­мы. Лог по-преж­не­му будет сбра­сы­вать­ся на диск каж­дую секун­ду и мак­си­мум, что вы може­те поте­рять - это 1-2 секун­ды запи­сей. Зна­че­ние 0 обес­пе­чи­ва­ет более высо­кую ско­рость, но и более низ­кую надеж­ность. Есть веро­ят­ность поте­рять тран­зак­ции даже при паде­нии mysql-сер­ве­ра. При зна­че­нии рав­ном 2 един­ствен­ная воз­мож­ность поте­рять дан­ные - это фаталь­ный сбой опе­ра­ци­он­ной системы.

table_cache - откры­тые таб­ли­цы могут раз­рас­тать­ся. Напри­мер, таб­ли­цы MyISAM поме­ча­ют MYI-заго­ло­вок, как исполь­зу­е­мый. Вы, конеч­но же, не хоти­те, что­бы это про­ис­хо­ди­ло слиш­ком часто и это, как пра­ви­ло, хоро­шее реше­ние. Луч­ше уве­ли­чить раз­мер кэша, что­бы он мог вме­стить боль­шин­ство откры­тых таб­лиц. Кэш исполь­зу­ет неко­то­рое коли­че­ство памя­ти и ресур­сов ОС, но для совре­мен­ной тех­ни­ки это, как пра­ви­ло, не про­бле­ма. Зна­че­ние 1024 будет опти­маль­ным реше­ни­ем для несколь­ких сотен откры­тых таб­лиц (помни­те, каж­дое соеди­не­ние нуж­да­ет­ся в соб­ствен­ной копии). Если у вас мно­го соеди­не­ний или боль­шое коли­че­ство откры­тых таб­лиц - уве­личь­те это зна­че­ние. Я видел систе­мы со зна­че­ни­ем это­го пара­мет­ра > 100.000

thread_cache - Создание/уничтожение нитей (threads) может ухуд­шать про­из­во­ди­тель­ность, осо­бен­но если они создаются/уничтожаются при каж­дом соединении/разъединении. Обыч­но я уста­на­ви­л­ваю зна­че­ние это­го пара­мет­ра рав­ным 16. Если при­ло­же­ние дела­ет боль­шие прыж­ки в парал­лель­ных соеди­не­ни­ях, то мож­но уви­деть, как быст­ро рас­тет пере­мен­ная Threads_Created. Пара­метр пред­на­зна­чен для того, что­бы не созда­вать новых нитей в нор­маль­ных операциях.

query_cache_size - если ваше при­ло­же­ние чита­ет мно­го дан­ных и у вас нет кэша на уровне при­ло­же­ния, этот пара­метр может непло­хо помочь. Но не уста­нав­ли­вай­те слиш­ком боль­шо­го зна­че­ния - это может замед­лить рабо­ту и содер­жа­ние тако­го кэша может обой­тись доволь­но доро­го. Опти­маль­ные зна­че­ния - от 32MB до 512MB. Тем не менее, про­верь­те эффек­тив­ность рабо­ты кэша через неко­то­рое вре­мя. Вполне воз­мож­но, что теку­щее зна­че­ние слиш­ком велико.

При­ме­ча­ние: как вы заме­ти­ли, все эти пере­мен­ные явля­ют­ся гло­баль­ны­ми и зави­сят от аппа­рат­но­го обес­пе­че­ния и устройств хра­не­ния дан­ных. Сес­си­он­ные пере­мен­ные зави­сят от спе­ци­фи­ки кон­крет­но­го про­ек­та. Если у вас про­стые запро­сы, вам совер­шен­но не нуж­но уве­ли­чи­вать пара­метр sort_buffer_size, даже если в вашем рас­по­ря­же­нии 64GB опе­ра­тив­ной памя­ти. Кро­ме того, это может сни­зить про­из­во­ди­тель­ность. Обыч­но я остав­ляю настрой­ку сес­си­он­ных пере­мен­ных на вто­рой шаг, уже после оцен­ки фрон­та работ.

P.S.: в дис­три­бу­ти­ве MySQL есть отлич­ные при­ме­ры фай­ла my.cnf для систем раз­лич­ных раз­ме­ров. Они могут исполь­зо­вать­ся в каче­стве базы для ваших соб­ствен­ных фай­лов кон­фи­гу­ра­ции, глав­ное пра­виль­но выбрать шаблон.

Осно­вы опти­ми­за­ции про­из­во­ди­тель­но­сти InnoDB

Про­во­дя опрос сре­ди посе­ти­те­лей раз­де­ла Job Opening я зада­вал им один про­стой вопрос: если бы у вас был сер­вер с 16GB RAM, кото­рый был бы пред­на­зна­чен для MySQL-сер­ве­ра с очень боль­шим объ­е­мом innodb-таб­лиц, рабо­та­ю­щий с стан­дарт­ным веб-про­ек­том, какие бы настрой­ки вы скор­рек­ти­ро­ва­ли? И самое инте­рес­ное, что боль­шин­ство не смог­ло чет­ко отве­тить. Поэто­му и было при­ня­то реше­ние опуб­ли­ко­вать эту замет­ку, кото­рая, воз­мож­но, рас­ши­рит ваши зна­ния об опти­ми­за­ции про­грамм­ной и аппа­рат­ной частей сервера.

Я назвал эту ста­тью Осно­вы опти­ми­за­ции про­из­во­ди­тель­но­сти InnoDB, желая под­черк­нуть, что это имен­но осно­вы. Это уни­вер­саль­ные сове­ты, рабо­та­ю­щие на боль­шин­стве систем. Но более точ­ную настрой­ку необ­хо­ди­мо про­из­во­дить, исхо­дя из кон­крет­ных постав­лен­ных задач.

Hardware

Если у вас есть база дан­ных innodb боль­шо­го объ­е­ма, и это дей­стви­тель­но важ­ные дан­ные, то 16 - 32 GB опе­ра­тив­ной памя­ти будут опти­маль­ным реше­ни­ем. Про­цес­со­ры - 2*DualCore подой­дут для боль­шой нагруз­ки, а два Quad Core помо­гут решить про­бле­мы с даль­ней­шим мас­шта­би­ро­ва­ни­ем систе­мы. Хотя име­ет­ся мно­же­ство нюан­сов. Тре­тий момент - это под­си­сте­ма ввода/вывода. Напря­мую под­клю­чен­ный DataStorage с боль­шим коли­че­ством дис­ков и RAID с воз­мож­но­стью сохра­не­ния кэша будут отлич­ным выбо­ром. Обыч­но необ­хо­ди­мо 6 - 8 жест­ких дис­ков в стан­дарт­ный блок, но порой может пона­до­бить­ся и боль­ше. Так­же обра­ти­те вни­ма­ние на новые 2.5" SAS дис­ки. Они мень­ше, но часто рабо­та­ют гораз­до быст­рее, чем обыч­ные HDD. RAID10 хоро­шо под­хо­дит как для хра­не­ния, так и для чте­ния дан­ных, но в слу­чае, если вы може­те поз­во­лить неко­то­рую избы­тыч­ность. В про­тив­ном слу­чае мож­но сде­лать RAID5, но опа­сай­тесь слу­чай­ных записей.

Опе­ра­ци­он­ная система

Дя нача­ла: уста­но­ви­те 64-бит­ную опе­ра­ци­он­ную систе­му. Часто мож­но уви­деть 32-бит­ный linux, или запу­щен­ный в режи­ме сов­ме­сти­мо­сти с 64-bit. Не делай­те так. Если вы исполь­зу­е­те LVM для хра­не­ния базы дан­ных, вы смо­же­те более эффек­тив­но рабо­тать с резерв­ны­ми копи­я­ми. Фай­ло­вая систе­ма Ext3 будет опти­маль­ным выбо­ром в боль­шин­стве слу­ча­ев, но если вы запус­ка­е­те particular roadblocks, то попро­буй­те XFS. Вы може­те исполь­зо­вать опции noatime и nodiratime, если вы исполь­зу­е­те innodb_file_per_table и боль­шое коли­че­ство таб­лиц, но это, в прин­ци­пе, не столь важ­но. Так­же убе­ди­тесь, что OS резер­ви­ру­ет доста­точ­но боль­шое коли­че­ство памя­ти для MySQL.

Опции MySQL InnoDB

Важ­ней­ши­ми опци­я­ми являются:
innodb_buffer_pool_size - 70 - 80% опе­ра­тив­ной памя­ти. Я став­лю это зна­че­ние в 12G на систе­ме с 16G RAM
innodb_log_file_size - зави­сит от необ­хо­ди­мо­го вам объ­е­ма дан­ных для вос­ста­нов­ле­ния, но 256МБ будут разум­ным ком­про­мис­сом меж­ду про­из­во­ди­тель­но­стью и раме­ром лог-файла
innodb_log_buffer_size=4M - 4 мега­бай­та - нор­маль­ное зна­че­ние, если вы не исполь­зу­е­те пода­чу боль­ших бло­ков дан­ных в InnoDB через кана­лы (pipes). Если исполь­зу­е­те, это зна­че­ние луч­ше увеличить.
innodb_flush_logs_at_trx_commit=2 - если вас не осо­бо забо­тит ACID, и вы може­те себе поз­во­лить поте­рять тран­зак­ции за послед­ние секун­ду или две, в слу­чае пол­но­го кра­ха ОС, то уста­но­ви­те это зна­че­ние. Но это может повлечь печаль­ные эффек­ты при корот­ких запи­сях транзакций.
innodb_thread_concurrency=8 - даже при име­ю­щих­ся InnoDB Scalability Fixes будет совсем не лиш­ним иметь огра­ни­чен­ное коли­че­ство пото­ков. Зна­че­ние может быть боль­ше или мень­ше в зави­со­мо­сти от ваших потреб­но­стей, но 8 будет опти­маль­ным зна­че­ни­ем для начала.
innodb_flush_method=O_DIRECT - избе­гай­те двой­ной буфе­ри­за­ции и умень­ши­те актив­ность swap, в боль­шин­стве слу­ча­ев это уве­ли­чи­ва­ет про­из­во­ди­тель­ность. Но будь­те осто­рож­ны, если у вас нет RAID с воз­мож­но­стью сохра­не­ния дан­ных, опе­ра­ции вво­да-выво­да могут про­хо­дить некор­рект­но и дан­ные могут быть повреждены.
innodb_file_per_table - если у вас немно­го таб­лиц, исполь­зуй­те эту опцию и рост зани­ма­е­мо­го таб­ли­ца­ми места не будет бес­кон­троль­ным. Эта опция добав­ле­на в MySQL 4.1 и сей­час доста­точ­но ста­биль­на для использования.

Про­верь­те так­же, могут ли ваши при­ло­же­ния запус­кать­ся в режи­ме изо­ля­ции READ-COMMITED. Если это так, то уста­но­ви­те опцию transaction-isolation=READ-COMITTED. Этот вари­ант уве­ли­чит производительность.

Есть еще нема­ло опций, зна­че­ния кото­рых мож­но поме­нять для дости­же­ния луч­шей про­из­во­ди­тель­но­сти. Об этом мож­но про­чи­тать в замет­ке Настрой­ка опций mysql-server (пере­вод) или в одной из наших презентаций.

Настрой­ка при­ло­же­ний для рабо­ты с InnoDB

При пере­хо­де с типа MyISAM, вам конеч­но будет инте­рес­но, что изме­ни­лось и какие новые воз­мож­но­сти вам теперь доступ­ны. Во-пер­вых: убе­ди­тесь, что вы исполь­зу­е­те тран­зак­ции при обнов­ле­ни­ях. Это необ­хо­ди­мо для повы­ше­ния про­из­во­ди­тель­но­сти. Во-вто­рых: гото­во ли ваше при­ло­же­ние обра­ба­ты­вать про­бле­мы, кото­рые могут про­изой­ти? И в-тре­тьих: воз­мож­но вы захо­ти­те пере­смот­реть струк­ту­ру сво­их таб­лиц и посмот­реть как вы може­те исполь­зо­вать свой­ства InooDB: рас­пре­де­ле­ние по пер­вич­но­му клю­чу, исполь­зо­ва­ние пер­вич­но­го клю­ча на всех индек­сах (это поз­во­ля­ет сокра­тить пер­ви­чеый ключ), быст­рый про­смотр по пер­вич­ным клю­чам (попро­буй­те исполь­зо­вать это при запро­сах с JOIN) или боль­шие несжа­ты индек­сы (облег­ча­ют индексирование).

При помо­щи это­го крат­ко­го опи­са­ния вы смо­же­те про­ве­сти пер­вич­ную настрой­ку InnoDB, кото­рая повы­сит про­из­во­ди­тель­ность на систе­мах без battery backup, без изме­не­ния настро­ек ОС и не вно­ся изме­не­ния в настрой­ки при­ло­же­ний, до сих пор исполь­зу­ю­щих MyISAM