Problemen met weinig geheugen in Azure Database for MySQL - Flexibele server oplossen

VAN TOEPASSING OP: Azure Database for MySQL - Enkele server Azure Database for MySQL - Flexibele server

Belangrijk

Azure Database for MySQL enkele server bevindt zich op het buitengebruikstellingspad. We raden u ten zeerste aan een upgrade uit te voeren naar een flexibele Azure Database for MySQL-server. Zie Wat gebeurt er met Azure Database for MySQL Enkele server voor meer informatie over migreren naar Azure Database for MySQL Flexibele server ?

Om ervoor te zorgen dat een exemplaar van een flexibele Azure Database for MySQL-server optimaal presteert, is het belangrijk dat u over de juiste geheugentoewijzing en het juiste geheugengebruik beschikt. Wanneer u een exemplaar van een flexibele Azure Database for MySQL-server maakt, is het beschikbare fysieke geheugen standaard afhankelijk van de laag en grootte die u voor uw workload selecteert. Daarnaast wordt geheugen toegewezen voor buffers en caches om databasebewerkingen te verbeteren. Zie Hoe MySQL geheugen gebruikt voor meer informatie.

Houd er rekening mee dat azure Database for MySQL flexibele server geheugen verbruikt om zoveel mogelijk cachetreffers te bereiken. Hierdoor kan het geheugengebruik vaak tussen 80 en 90% van het beschikbare fysieke geheugen van een exemplaar bewegen. Tenzij er een probleem is met de voortgang van de queryworkload, is dit geen probleem. U kunt echter te maken krijgen met geheugenproblemen om redenen zoals die u hebt:

• Er zijn te grote buffers geconfigureerd.
• Suboptimale query's die worden uitgevoerd.
• Query's die joins uitvoeren en grote gegevenssets sorteren.
• Stel de maximumverbindingen op een databaseserver te hoog in.

Het merendeel van het geheugen van een server wordt gebruikt door de globale buffers en caches van InnoDB, waaronder onderdelen zoals innodb_buffer_pool_size, innodb_log_buffer_size, key_buffer_size en query_cache_size.

De waarde van de parameter innodb_buffer_pool_size geeft het geheugengebied op waarin InnoDB de databasetabellen en indexgerelateerde gegevens in de cache opgeslagen. MySQL probeert zoveel mogelijk tabel- en indexgerelateerde gegevens in de buffergroep te verwerken. Voor een grotere buffergroep zijn minder I/O-bewerkingen vereist die naar de schijf worden omgeleid.

Geheugengebruik bewaken

Azure Database for MySQL Flexibele server biedt een reeks metrische gegevens om de prestaties van uw database-exemplaar te meten. Als u meer inzicht wilt krijgen in het geheugengebruik voor uw databaseserver, bekijkt u de metrische gegevens van het hostgeheugenpercentage of het geheugenpercentage .

Als u merkt dat het geheugengebruik plotseling is toegenomen en dat het beschikbare geheugen snel afneemt, controleert u andere metrische gegevens, zoals host-CPU-percentage, totaal aantal Verbinding maken ions en IO-percentage, om te bepalen of een plotselinge piek in de werkbelasting de oorzaak van het probleem is.

Het is belangrijk om te weten dat elke verbinding die met de databaseserver tot stand is gebracht, de toewijzing van een bepaalde hoeveelheid geheugen vereist. Als gevolg hiervan kan een piek in databaseverbindingen leiden tot geheugentekorten.

Oorzaken van hoog geheugengebruik

Laten we eens kijken naar enkele oorzaken van een hoog geheugengebruik in MySQL. Deze oorzaken zijn afhankelijk van de kenmerken van de workload.

Een toename van tijdelijke tabellen

MySQL maakt gebruik van 'tijdelijke tabellen', een speciaal type tabel dat is ontworpen voor het opslaan van een tijdelijke resultatenset. Tijdelijke tabellen kunnen meerdere keren tijdens een sessie opnieuw worden gebruikt. Aangezien tijdelijke tabellen die zijn gemaakt lokaal zijn voor een sessie, kunnen verschillende sessies verschillende tijdelijke tabellen hebben. In productiesystemen met veel sessies die compilaties van grote tijdelijke resultatensets uitvoeren, moet u regelmatig de globale statusmeteritem controleren created_tmp_tables, waarmee het aantal tijdelijke tabellen wordt bijgehouden dat tijdens piekuren wordt gemaakt. Een groot aantal tijdelijke tabellen in het geheugen kan snel leiden tot weinig beschikbaar geheugen in een exemplaar van flexibele Azure Database for MySQL-server.

Met MySQL wordt de tijdelijke tabelgrootte bepaald door de waarden van twee parameters, zoals beschreven in de volgende tabel.

Parameter	Beschrijving
tmp_table_size	Hiermee geeft u de maximale grootte van interne tijdelijke tabellen in het geheugen.
max_heap_table_size	Hiermee geeft u de maximale grootte op waarop door de gebruiker gemaakte GEHEUGEN-tabellen kunnen groeien.

Notitie

Bij het bepalen van de maximale grootte van een interne, tijdelijke tabel in het geheugen, houdt MySQL rekening met de lagere waarden die zijn ingesteld voor de parameters tmp_table_size en max_heap_table_size.

Aanbevelingen

Als u problemen met weinig geheugen met betrekking tot tijdelijke tabellen wilt oplossen, kunt u de volgende aanbevelingen overwegen.

• Controleer voordat u de tmp_table_size waarde verhoogt of uw database correct is geïndexeerd, met name voor kolommen die betrokken zijn bij joins en gegroepeerd op bewerkingen. Als u de juiste indexen voor onderliggende tabellen gebruikt, beperkt u het aantal tijdelijke tabellen dat wordt gemaakt. Door de waarde van deze parameter en de parameter max_heap_table_size te verhogen zonder te controleren of uw indexen inefficiënte query's kunnen worden uitgevoerd zonder indexen en meer tijdelijke tabellen kunnen worden gemaakt dan nodig is.
• Stem de waarden van de parameters max_heap_table_size en tmp_table_size af om te voldoen aan de behoeften van uw workload.
• Als de waarden die u hebt ingesteld voor de parameters max_heap_table_size en tmp_table_size te laag zijn, kunnen tijdelijke tabellen regelmatig overlopen naar de opslag, waarbij latentie wordt toegevoegd aan uw query's. U kunt tijdelijke tabellen bijhouden die naar schijf overlopen met behulp van de globale statusmeteritem created_tmp_disk_tables. Door de waarden van de variabelen created_tmp_disk_tables en created_tmp_tables te vergelijken, bekijkt u het aantal interne tijdelijke tabellen op schijf dat is gemaakt met het totale aantal interne tijdelijke tabellen dat is gemaakt.

Tabelcache

Als systeem met meerdere threads onderhoudt MySQL een cache met tabelbestandsdescriptors, zodat de tabellen afzonderlijk door meerdere sessies kunnen worden geopend. MySQL gebruikt enige hoeveelheid geheugen en besturingssysteembestandsdescriptors om deze tabelcache te onderhouden. De variabele table_open_cache definieert de grootte van de tabelcache.

Aanbevelingen

Als u problemen met weinig geheugen met betrekking tot de tabelcache wilt oplossen, kunt u de volgende aanbevelingen overwegen.

• De parameter table_open_cache geeft het aantal geopende tabellen voor alle threads op. Als u deze waarde verhoogt, wordt het aantal bestandsdescriptors dat mysqld vereist, verhoogd. U kunt controleren of u de tabelcache wilt vergroten door de opened_tables statusvariabele in de globale statusmeteritem weergeven te controleren. Verhoog de waarde van deze parameter in stappen om tegemoet te komen aan uw workload.
• Als u table_open_cache te laag instelt, kan dit ertoe leiden dat de flexibele Server van Azure Database for MySQL meer tijd besteedt aan het openen en sluiten van tabellen die nodig zijn voor het verwerken van query's.
• Als u deze waarde te hoog instelt, kan het gebruik van meer geheugen en het besturingssysteem met bestandsdescriptors leiden tot geweigerde verbindingen of het niet verwerken van query's.

Andere buffers en de querycache

Bij het oplossen van problemen met betrekking tot weinig geheugen kunt u met een paar buffers en een cache werken om u te helpen bij de oplossing.

Netbuffer (net_buffer_length)

De nettobuffer is de grootte voor verbindings- en threadbuffers voor elke clientthread en kan groeien tot de waarde die is opgegeven voor max_allowed_packet. Als een query-instructie groot is, bijvoorbeeld alle invoegingen/updates een zeer grote waarde hebben, helpt het verhogen van de waarde van de parameter net_buffer_length om de prestaties te verbeteren.

Joinbuffer (join_buffer_size)

De joinbuffer wordt toegewezen aan tabelrijen in de cache wanneer een join geen index kan gebruiken. Als uw database veel joins zonder indexen heeft uitgevoerd, kunt u indexen toevoegen voor snellere joins. Als u geen indexen kunt toevoegen, kunt u overwegen om de waarde van de parameter join_buffer_size te verhogen, waarmee de hoeveelheid geheugen wordt opgegeven die per verbinding is toegewezen.

Sorteerbuffer (sort_buffer_size)

De sorteerbuffer wordt gebruikt voor het uitvoeren van sorteringen voor sommige QUERY's ORDER BY en GROUP BY. Als u veel Sort_merge_passes per seconde in de uitvoer SHOW GLOBAL STATUS ziet, kunt u overwegen de sort_buffer_size waarde te verhogen om ORDER BY- of GROUP BY-bewerkingen te versnellen die niet kunnen worden verbeterd met behulp van queryoptimalisatie of betere indexering.

Vermijd willekeurig het verhogen van de sort_buffer_size waarde, tenzij u gerelateerde informatie hebt die anders aangeeft. Geheugen voor deze buffer wordt per verbinding toegewezen. In de MySQL-documentatie wordt in het artikel Serversysteemvariabelen aangegeven dat er in Linux twee drempelwaarden zijn, 256 kB en 2 MB, en dat het gebruik van grotere waarden de geheugentoewijzing aanzienlijk kan vertragen. Als gevolg hiervan vermijdt u het verhogen van de sort_buffer_size waarde boven 2M, omdat de prestatiestraf opweegt tegen eventuele voordelen.

Querycache (query_cache_size)

De querycache is een geheugengebied dat wordt gebruikt voor het opslaan van queryresultatensets in de cache. De parameter query_cache_size bepaalt de hoeveelheid geheugen die is toegewezen voor queryresultaten in de cache. De querycache is standaard uitgeschakeld. Bovendien is de querycache afgeschaft in MySQL versie 5.7.20 en verwijderd in MySQL versie 8.0. Als de querycache momenteel is ingeschakeld in uw oplossing voordat u deze uitschakelt, controleert u of er geen query's van afhankelijk zijn.

Buffercachetrefferverhouding berekenen

Buffercachetrefferverhouding is belangrijk in de flexibele serveromgeving van Azure Database for MySQL om te begrijpen of de buffergroep al dan niet geschikt is voor de workloadaanvragen. Als algemene vuistregel is het een goede gewoonte om altijd een bufferpoolcachetrefferverhouding van meer dan 99% te hebben.

Als u de innoDB-buffergroeptrefferverhouding voor leesaanvragen wilt berekenen, kunt u de ALGEMENE STATUS WEERGEVEN uitvoeren om tellers 'Innodb_buffer_pool_read_requests' en 'Innodb_buffer_pool_reads' op te halen en vervolgens de waarde te berekenen met behulp van de onderstaande formule.

InnoDB Buffer pool hit ratio = Innodb_buffer_pool_read_requests / (Innodb_buffer_pool_read_requests + Innodb_buffer_pool_reads) * 100

Bekijk het volgende voorbeeld.

mysql> show global status like "innodb_buffer_pool_reads";
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| Innodb_buffer_pool_reads | 197   |
+--------------------------+-------+
1 row in set (0.00 sec)

mysql> show global status like "innodb_buffer_pool_read_requests";
+----------------------------------+----------+
| Variable_name                    | Value    |
+----------------------------------+----------+
| Innodb_buffer_pool_read_requests | 22479167 |
+----------------------------------+----------+
1 row in set (0.00 sec)

Als u de bovenstaande waarden gebruikt, levert het berekenen van de trefferverhouding van de InnoDB-buffergroep voor leesaanvragen het volgende resultaat op:

InnoDB Buffer pool hit ratio = 22479167/(22479167+197) * 100 

Buffer hit ratio = 99.99%

Naast selecte instructies buffer cache hit ratio, voor eventuele DML-instructies, schrijft naar de InnoDB-buffergroep op de achtergrond. Als het echter nodig is om een pagina te lezen of te maken en er geen schone pagina's beschikbaar zijn, moet u ook wachten totdat de pagina's eerst worden leeggemaakt.

De Innodb_buffer_pool_wait_free teller telt hoe vaak dit is gebeurd. Innodb_buffer_pool_wait_free groter dan 0 is een sterke indicator dat de InnoDB-buffergroep te klein is en de grootte van de buffergroep of de instantiegrootte toeneemt om de schrijfbewerkingen die in de database binnenkomen, te kunnen opvangen.

Aanbevelingen

• Zorg ervoor dat uw database voldoende resources heeft toegewezen om uw query's uit te voeren. Soms moet u de instantiegrootte omhoog schalen om meer fysiek geheugen te krijgen, zodat de buffers en caches geschikt zijn voor uw workload.
• Vermijd grote of langlopende transacties door ze op te delen in kleinere transacties.
• Gebruik waarschuwingen 'Percentage hostgeheugen' zodat u meldingen ontvangt als het systeem een van de opgegeven drempelwaarden overschrijdt.
• Gebruik Query Performance Insights of Azure Workbooks om problematische of langzaam uitgevoerde query's te identificeren en deze vervolgens te optimaliseren.
• Verzamel voor productiedatabaseservers regelmatig diagnostische gegevens om ervoor te zorgen dat alles soepel verloopt. Als dat niet het geval is, kunt u eventuele problemen oplossen die u identificeert.

Volgende stappen

Als u peerantwoorden wilt vinden op uw belangrijkste vragen of als u een vraag wilt posten of beantwoorden, gaat u naar Stack Overflow.