MySQL innodb_buffer_pool_size: kā izvēlēties izmēru, mainīt to un saprast, ka tas ir par mazu

innodb_buffer_pool_size nosaka, cik daudz jūsu datu MySQL var turēt atmiņā, un vairumā serveru tas ir lasīšanas veiktspējai svarīgākais iestatījums. Noklusējums ir 134217728 baiti (128MB) – pietiekami klēpjdatoram un krietni par maz produkcijas datubāzei. Šis ceļvedis aplūko, kā izvēlēties izmēru, kā to mainīt strādājošā serverī un kuri skaitītāji parāda, vai pūls patiešām ir pietiekami liels. Viss attiecas uz MySQL 8.4, ja nav norādīts citādi.

Ko glabā bufera pūls

Bufera pūls ir atmiņas apgabals, kurā InnoDB kešo tabulu un indeksu datus. Lasījumi, kas atrod vajadzīgo lapu tur, disku neaiztiek; pārējiem lapa vispirms jāielādē. Arī ieraksti nonāk bufera pūlā kā "netīras" (dirty) lapas un fonā tiek izrakstīti uz diska.

InnoDB pārvalda pūlu kā sarakstu ar viduspunktu. Pēc noklusējuma 3/8 pūla ir "vecais" apakšsaraksts, jauni nolasīta lapa tiek ievietota viduspunktā, un, kad vajadzīga vieta, tiek izmesta vismazāk nesen lietotā lapa. Algoritms bieži lietotās lapas patur "jaunajā" apakšsarakstā. Tomēr pret lielām skenēšanām tas nav automātiski imūns: dokumentācijā norādīts, ka pēc noklusējuma tabulas skenēšana, piemēram, no mysqldump vai SELECT bez WHERE, var ienest daudz datu un izspiest tikpat daudz vecāku datu, pat ja jaunās lapas vairs nekad netiks izmantotas. Rokasgrāmatā ir atsevišķa sadaļa par to, kā padarīt bufera pūlu izturīgu pret skenēšanu, ja rezerves kopijas vai atskaites regulāri "atdzesē" jūsu kešu.

Cik lielam tam jābūt?

MySQL dokumentācijā ir divi nedaudz atšķirīgi skaitļi, un abus vērts zināt:

• Bufera pūla nodaļā teikts, ka veltītos serveros bufera pūlam bieži tiek piešķirti līdz 80% fiziskās atmiņas.
• Atmiņas lietojuma nodaļā teikts, ka tipiska ieteicamā vērtība ir 50 līdz 75 procenti sistēmas atmiņas.

Starpība starp tiem ir viss pārējais, kas vajadzīgs MySQL un operētājsistēmai. InnoDB visu bufera pūlu piešķir startējot, bet katrs savienojums papildus tam piešķir savus buferus: pavediena steku, savienojuma un rezultātu buferus, lasīšanas un kārtošanas buferus, kad vaicājumiem tie vajadzīgi. Serverim ar dažiem tūkstošiem savienojumu vajag daudz lielāku rezervi nekā serverim ar piecdesmit. Tajā pašā nodaļā brīdināts par abiem virzieniem: pārāk mazs pūls izraisa lapu "griešanos" – lapas tiek izmestas un drīz atkal vajadzīgas, bet pārāk liels "var izraisīt swap lietošanu konkurences dēļ par atmiņu". Datubāzei swap parasti ir sliktāks par mazāku kešu.

Saprātīgs veids, kā izlemt:

1. Sāciet no servera fiziskās atmiņas vai konteinera atmiņas limita, nevis no resursdatora atmiņas.
2. Atņemiet to, kas vajadzīgs citiem procesiem mašīnā: monitoringa aģentiem, rezerves kopiju rīkiem, visam pārējam, kas tur darbojas.
3. Atstājiet vietu savienojumiem. Ja max_connections ir liels, vispirms izlasiet mūsu rakstu par MySQL "Too many connections"; pareizais risinājums tur reti ir "vairāk savienojumu".
4. Lielāko daļu atlikušā atdodiet bufera pūlam un tad sekojiet tālāk aprakstītajiem skaitītājiem, nevis paļaujieties uz formulu.

Ja viss darba datu apjoms jau ērti ietilpst, lielāks pūls neko nepaātrinās. 20GB datubāzei mašīnā ar 128GB atmiņas nav vajadzīgs 100GB pūls.

innodb_dedicated_server: lai MySQL izlemj pats

Ja MySQL ir vienīgais, kas darbojas serverī vai virtuālajā mašīnā, to var startēt ar innodb_dedicated_server=ON. Tad InnoDB aprēķina bufera pūla izmēru no konstatētās atmiņas: 128MB, ja atmiņas ir mazāk par 1GB, puse atmiņas no 1GB līdz 4GB un 75% virs 4GB. Tas iestata arī innodb_redo_log_capacity. Vērtības tiek aprēķinātas tikai startējot. Ja innodb_buffer_pool_size iestatīts tieši, uzvar tiešā vērtība, un MySQL startējot izdrukā brīdinājumu, ka šim mainīgajam opcija ignorēta. MySQL 8.0 šī opcija iestatīja arī innodb_flush_method; 8.4 tā to vairs nedara. Versijā 8.4 mainījās arī vairāki citi InnoDB noklusējumi, tostarp pats innodb_flush_method; tie uzskaitīti mūsu MySQL 8.0 uz 8.4 atjaunināšanas ceļvedī.

Dokumentācija iesaka šo opciju tikai tad, ja MySQL var izmantot visus mašīnas resursus. Serverī, ko tas dala ar lietotni, iestatiet izmēru manuāli.

Gabali un instances

Divi saistīti iestatījumi izskaidro, kāpēc iestatītā vērtība ne vienmēr ir tā, ko saņemat:

• innodb_buffer_pool_chunk_size (noklusējums 128MB) ir vienība, kurā pūls aug un sarūk. To var mainīt tikai startējot.
• innodb_buffer_pool_instances sadala pūlu vairākās neatkarīgās daļās. 8.4 noklusējums ir 1 pūlam līdz 1GB; virs tā – mazākais no pusei gabalu skaita un ceturtdaļai loģisko CPU, robežās no 1 līdz 64.

Izmēra maiņas dokumentācijā noteikts likums: bufera pūla izmērs vienmēr ir chunk_size * instances daudzkārtnis, un citas vērtības tiek pielāgotas automātiski. Tās piemērs: ar 16 instancēm un noklusējuma gabala izmēru pieprasītie 9G kļūst par 10G. Tajā pašā lapā ieteikts gabalu skaitu (pūla izmērs dalīts ar gabala izmēru) turēt ne vairāk kā 1000 un brīdināts, ka gabala izmēra maiņa var negaidīti palielināt pūla izmēru, tāpēc vispirms aprēķiniet sekas.

Pašreizējā stāvokļa pārbaude

Šie vaicājumi parāda konfigurāciju un noderīgākos statusa skaitītājus:

SELECT @@innodb_buffer_pool_size / 1024 / 1024 / 1024 AS pool_gb,
       @@innodb_buffer_pool_instances                AS instances,
       @@innodb_buffer_pool_chunk_size / 1024 / 1024 AS chunk_mb;

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_free';
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_wait_free';

• Innodb_buffer_pool_read_requests skaita loģiskos lasīšanas pieprasījumus; Innodb_buffer_pool_reads – tos, kurus nevarēja apkalpot no pūla un nācās lasīt no diska. Skatieties, kā abi mainās noteiktā laika intervālā, nevis uz to kopsummām kopš startēšanas. Ja diska lasījumi parastas slodzes laikā turpina augt, darba datu apjoms neietilpst.
• Innodb_buffer_pool_wait_free skaita gadījumus, kad InnoDB vajadzēja tīru lapu, tādas nebija un nācās gaidīt, kamēr tiek izrakstītas netīrās lapas. Dokumentācijā teikts, ka pareizi izvēlēta izmēra pūlam šai vērtībai jābūt nelielai.
• Innodb_buffer_pool_pages_free tuvu nullei noslogotā serverī ir normāli: InnoDB izmanto atmiņu, kas tam ir. Brīvās lapas vien nenozīmē, ka pūls ir par mazu.
• SHOW ENGINE INNODB STATUS sadaļā BUFFER POOL AND MEMORY izdrukā rindu Buffer pool hit rate – ātru skatu uz to pašu.

Augsts trāpījumu īpatsvars nepierāda, ka visi vaicājumi ir kārtībā. Vaicājums, kas no atmiņas skenē miljoniem rindu, joprojām ir lēns; tos atrod ar MySQL lēno vaicājumu žurnālu.

Var rasties vēlme paskatīties, kuras tabulas aizņem pūlu, izmantojot sys.innodb_buffer_stats_by_table. Dokumentācija brīdina, ka skati, kas balstīti uz INNODB_BUFFER_PAGE, var ietekmēt veiktspēju un tos nevajag vaicāt produkcijā, ja neesat izlēmuši, ka ietekme ir pieņemama. Tā vietā izpildiet tos testa instancē ar slodzes kopiju.

Izmēra maiņa bez restarta

innodb_buffer_pool_size ir dinamisks, tāpēc to var mainīt ar SET GLOBAL:

-- 24 GiB; tiek noapaļots līdz chunk_size * instances daudzkārtnim
SET GLOBAL innodb_buffer_pool_size = 25769803776;

-- sekojiet izmēra maiņai
SELECT variable_name, variable_value
FROM performance_schema.global_status
WHERE LOWER(variable_name) LIKE 'innodb_buffer_pool_resize%';

-- kad rezultāts apmierina, saglabājiet to arī pēc restarta
SET PERSIST innodb_buffer_pool_size = 25769803776;

Ko sagaidīt saskaņā ar dokumentāciju:

• Izmēra maiņa nesākas, kamēr nav pabeigtas aktīvās transakcijas.
• Tās laikā jaunām transakcijām un operācijām, kurām vajadzīgs bufera pūls, jāgaida, izņemot fāzes, kurās pūls tiek defragmentēts un, to samazinot, tiek izņemtas lapas. Šo fāžu laikā piekļuve turpinās, bet var rasties īslaicīgs brīvo lapu trūkums.
• Innodb_buffer_pool_resize_status_code parāda pašreizējo posmu (0 nozīmē, ka izmēra maiņa nenotiek, 7 – ka tā neizdevās), bet Innodb_buffer_pool_resize_status_progress – procentus katram posmam.

innodb_buffer_pool_chunk_size vai innodb_buffer_pool_instances maiņai joprojām vajadzīgs restarts.

Restarti un uzsildīšana

Pēc restarta pūls ir tukšs, un sākumā trafiks visu lasa no diska. MySQL to mazina ar bufera pūla izgāšanu un ielādi, kas abas ieslēgtas pēc noklusējuma: innodb_buffer_pool_dump_at_shutdown pieraksta, kuras lapas bija kešotas, un innodb_buffer_pool_load_at_startup startējot tās nolasa atpakaļ. Pēc noklusējuma tiek pierakstīti tikai 25% pēdējo lietoto lapu (innodb_buffer_pool_dump_pct). Progress redzams Innodb_buffer_pool_load_status, un, ja ielāde rada pārāk lielu slodzi, to var apturēt ar innodb_buffer_pool_load_abort. Ja serveris pēc katra restarta kādu laiku ir manāmi lēns, pārbaudiet, vai šie iestatījumi nav izslēgti.