Problemen met een hoog CPU-gebruik in Azure Database for MySQL - flexibele server oplossen

  • Artikel

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 ?

Azure Database for MySQL Flexibele server biedt een reeks metrische gegevens die u kunt gebruiken om knelpunten en prestatieproblemen op de server te identificeren. Als u wilt bepalen of uw server een hoog CPU-gebruik ondervindt, controleert u metrische gegevens zoals 'CPU-percentage host', 'Totale Verbinding maken ions', 'Hostgeheugenpercentage' en 'IO-percentage'. Soms biedt het weergeven van een combinatie van deze metrische gegevens inzicht in wat het verhoogde CPU-gebruik op uw flexibele Azure Database for MySQL-serverexemplaren kan veroorzaken.

Denk bijvoorbeeld aan een plotselinge piek in verbindingen die pieken in databasequery's initiëren die ervoor zorgen dat het CPU-gebruik omhoog schiet.

Naast het vastleggen van metrische gegevens, is het belangrijk om ook de workload te traceren om te begrijpen of een of meer query's de piek in het CPU-gebruik veroorzaken.

Hoge CPU-oorzaken

CPU-pieken kunnen om verschillende redenen optreden, voornamelijk vanwege pieken in verbindingen en slecht geschreven SQL-query's, of een combinatie van beide:

Piek in verbindingen

Een toename van verbindingen kan leiden tot een toename van threads, wat op zijn beurt een toename van het CPU-gebruik kan veroorzaken, omdat deze verbindingen samen met hun query's en resources moeten worden beheerd. Als u problemen met een piek in verbindingen wilt oplossen, controleert u het metrische gegeven totale Verbinding maken ies en raadpleegt u de volgende sectie voor meer informatie over deze verbindingen. U kunt het performance_schema gebruiken om de hosts en gebruikers te identificeren die momenteel zijn verbonden met de server met de volgende opdrachten:

Huidige verbonden hosts

   select HOST,CURRENT_CONNECTIONS From performance_schema.hosts
   where CURRENT_CONNECTIONS > 0
   and host not in ('NULL','localhost');

Huidige verbonden gebruikers

   select USER,CURRENT_CONNECTIONS from performance_schema.users
   where CURRENT_CONNECTIONS >0
   and USER not in ('NULL','azure_superuser');

Slecht geschreven SQL-query's

Query's die duur zijn om een groot aantal rijen zonder index uit te voeren en te scannen, of query's die tijdelijke sorteringen uitvoeren samen met andere inefficiënte plannen, kunnen leiden tot CPU-pieken. Hoewel sommige query's in één sessie snel kunnen worden uitgevoerd, kunnen ze CPU-pieken veroorzaken wanneer ze in meerdere sessies worden uitgevoerd. Daarom is het van cruciaal belang om altijd uw query's uit te leggen die u vanuit de lijst met showprocessen vastlegt en ervoor te zorgen dat hun uitvoeringsplannen efficiënt zijn. Dit kan worden bereikt door ervoor te zorgen dat ze een minimaal aantal rijen scannen met behulp van filters/where-component, indexen gebruiken en voorkomen dat grote tijdelijke sortering samen met andere slechte uitvoeringsplannen wordt gebruikt. Zie DE uitvoerindeling EXPLAIN voor meer informatie over uitvoeringsplannen.

Details van de huidige workload vastleggen

Met de opdracht SHOW (FULL) PROCESSLIST wordt een lijst weergegeven met alle gebruikerssessies die momenteel zijn verbonden met het flexibele serverexemplaren van Azure Database for MySQL. Het bevat ook details over de huidige status en activiteit van elke sessie.

Deze opdracht produceert alleen een momentopname van de huidige sessiestatus en biedt geen informatie over historische sessieactiviteit.

Laten we eens kijken naar voorbeelduitvoer van het uitvoeren van deze opdracht.

mysql> SHOW FULL PROCESSLIST;
+-------+------------------+--------------------+---------------+-------------+--------+-----------------------------+------------------------------------------+
| Id    | User             | Host               | db            | Command     | Time   | State                       | Info                                     |
+-------+------------------+--------------------+---------------+-------------+--------+-----------------------------+------------------------------------------+
|     1 | event_scheduler  | localhost          | NULL          | Daemon      |     13 | Waiting for next activation | NULL                                     |
|     6 | azure_superuser  | 127.0.0.1:33571    | NULL          | Sleep       |    115 |                             | NULL                                     
|
| 24835 | adminuser        | 10.1.1.4:39296     | classicmodels | Query       |      7 | Sending data                | select * from classicmodels.orderdetails;|
| 24837 | adminuser        | 10.1.1.4:38208     | NULL          | Query       |      0 | starting                    | SHOW FULL PROCESSLIST                    |
+-------+------------------+--------------------+---------------+-------------+--------+-----------------------------+------------------------------------------+
5 rows in set (0.00 sec)

U ziet dat er twee sessies zijn die eigendom zijn van de gebruiker die eigendom is van de gebruiker 'adminuser', beide van hetzelfde IP-adres:

  • Sessie 24835 heeft de afgelopen zeven seconden een SELECT-instructie uitgevoerd.
  • Sessie 24837 voert de instructie 'volledige proceslijst weergeven' uit.

Indien nodig kan het nodig zijn om een query te beëindigen, zoals een rapportage- of HTAP-query die ervoor heeft gezorgd dat het CPU-gebruik van uw productieworkload piekt. Houd echter altijd rekening met de mogelijke gevolgen van het beëindigen van een query voordat u de actie onderneemt in een poging om het CPU-gebruik te verminderen. Andere keren als er langlopende query's zijn geïdentificeerd die leiden tot CPU-pieken, kunt u deze query's zo afstemmen dat de resources optimaal worden gebruikt.

Gedetailleerde huidige workloadanalyse

U moet ten minste twee gegevensbronnen gebruiken om nauwkeurige informatie te verkrijgen over de status van een sessie, transactie en query:

  • De proceslijst van de server uit de INFORMATION_SCHEMA. PROCESSLIST-tabel, die u ook kunt openen door de opdracht SHOW [FULL] PROCESSLIST uit te voeren.
  • InnoDB's transactiemetagegevens van de INFORMATION_SCHEMA. INNODB_TRX tabel.

Met informatie uit slechts één van deze bronnen is het onmogelijk om de verbindings- en transactiestatus te beschrijven. In de proceslijst wordt bijvoorbeeld niet aangegeven of er een geopende transactie is gekoppeld aan een van de sessies. Aan de andere kant worden in de metagegevens van de transactie de sessiestatus en de tijd die in die status is besteed niet weergegeven.

De volgende voorbeeldquery waarin proceslijstgegevens worden gecombineerd met enkele belangrijke onderdelen van metagegevens van InnoDB-transacties:

mysql> select    p.id as session_id, p.user, p.host, p.db, p.command, p.time, p.state,    substring(p.info, 1, 50) as info,    t.trx_started, unix_timestamp(now()) - unix_timestamp(t.trx_started) as trx_age_seconds, t.trx_rows_modified, t.trx_isolation_level   from information_schema.processlist p    left join information_schema.innodb_trx t    on p.id = t.trx_mysql_thread_id \G

In het volgende voorbeeld ziet u de uitvoer van deze query:

*************************** 1. row *************************** 
        session_id: 11 
               user: adminuser 
               host: 172.31.19.159:53624 
                 db: NULL 
            command: Sleep 
               time: 636 
              state: cleaned up 
               info: NULL 
        trx_started: 2019-08-01 15:25:07 
    trx_age_seconds: 2908 
  trx_rows_modified: 17825792 
trx_isolation_level: REPEATABLE READ 
*************************** 2. row *************************** 
         session_id: 12 
               user: adminuser 
               host: 172.31.19.159:53622 
                 db: NULL 
            command: Query 
               time: 15 
              state: executing 
               info: select * from classicmodels.orders 
        trx_started: NULL 
    trx_age_seconds: NULL 
  trx_rows_modified: NULL 
trx_isolation_level: NULL

Een analyse van deze informatie, per sessie, wordt vermeld in de volgende tabel.

Gebied Analyse
Sessie 11 Deze sessie is momenteel niet actief (slaapstand) zonder query's uit te voeren en het is 636 seconden. Binnen de sessie is een transactie die 2908 seconden geopend is, 17.825.792 rijen gewijzigd en wordt herhaalbare LEESisolatie gebruikt.
Sessie 12 De sessie voert momenteel een SELECT-instructie uit, die gedurende 15 seconden wordt uitgevoerd. Er wordt geen query uitgevoerd binnen de sessie, zoals aangegeven door de NULL-waarden voor trx_started en trx_age_seconds. De sessie blijft de grens van de garbagecollection behouden zolang deze wordt uitgevoerd, tenzij de meer ontspannen READ COMMIT-isolatie wordt gebruikt.

Houd er rekening mee dat als een sessie wordt gerapporteerd als niet-actief, er geen instructies meer worden uitgevoerd. Op dit moment heeft de sessie eventuele eerdere werkzaamheden voltooid en wordt gewacht op nieuwe instructies van de client. Niet-actieve sessies zijn echter nog steeds verantwoordelijk voor een bepaald CPU-verbruik en geheugengebruik.

Openstaande transacties weergeven

De uitvoer van de volgende query bevat een lijst met alle transacties die momenteel worden uitgevoerd op de databaseserver in volgorde van de begintijd van de transactie, zodat u gemakkelijk kunt vaststellen of er langlopende transacties zijn en transacties die de verwachte runtime overschrijden.

SELECT trx_id, trx_mysql_thread_id, trx_state, Unix_timestamp() - ( To_seconds(trx_started) - To_seconds('1970-01-01 00:00:00') ) AS trx_age_seconds, trx_weight, trx_query, trx_tables_in_use, trx_tables_locked, trx_lock_structs, trx_rows_locked, trx_rows_modified, trx_isolation_level, trx_unique_checks, trx_is_read_only FROM information_schema.innodb_trx ORDER BY trx_started ASC;

Informatie over threadstatussen

Transacties die bijdragen aan een hoger CPU-gebruik tijdens de uitvoering, kunnen threads in verschillende statussen bevatten, zoals beschreven in de volgende secties. Gebruik deze informatie om meer inzicht te krijgen in de levenscyclus van query's en verschillende threadstatussen.

Machtigingen controleren/Tabellen openen

Deze status betekent meestal dat de bewerking van de geopende tabel lang duurt. Meestal kunt u de grootte van de tabelcache vergroten om het probleem te verbeteren. Het langzaam openen van tabellen kan echter ook duiden op andere problemen, zoals het hebben van te veel tabellen in dezelfde database.

Gegevens verzenden

Hoewel deze status kan betekenen dat de thread gegevens verzendt via het netwerk, kan het ook aangeven dat de query gegevens van de schijf of het geheugen leest. Deze status kan worden veroorzaakt door een sequentiële tabelscan. Controleer de waarden van de innodb_buffer_pool_reads en innodb_buffer_pool_read_requests om te bepalen of een groot aantal pagina's vanaf de schijf in het geheugen wordt geleverd. Zie Problemen met weinig geheugen in Azure Database for MySQL flexibele server oplossen voor meer informatie.

Bijwerken

Deze status betekent meestal dat de thread een schrijfbewerking uitvoert. Controleer de metrische IO-gegevens in de prestatiemeter om een beter inzicht te krijgen in wat de huidige sessies doen.

Wachten op <lock_type> vergrendeling

Deze status geeft aan dat de thread wacht op een tweede vergrendeling. In de meeste gevallen kan het een metagegevensvergrendeling zijn. Controleer alle andere threads en kijk wie de vergrendeling neemt.

Wachtende gebeurtenissen begrijpen en analyseren

Het is belangrijk om inzicht te hebben in de onderliggende wachtgebeurtenissen in de MySQL-engine, omdat lange wachttijden of een groot aantal wachttijden in een database kunnen leiden tot een verhoogd CPU-gebruik. In het volgende voorbeeld ziet u de juiste opdracht en voorbeelduitvoer.

SELECT event_name AS wait_event,
count_star AS all_occurrences,
Concat(Round(sum_timer_wait / 1000000000000, 2), ' s') AS total_wait_time,
 Concat(Round(avg_timer_wait / 1000000000, 2), ' ms') AS
avg_wait_time
FROM performance_schema.events_waits_summary_global_by_event_name
WHERE count_star > 0 AND event_name <> 'idle'
ORDER BY sum_timer_wait DESC LIMIT 10;
+--------------------------------------+-----------------+-----------------+---------------+
| wait_event                           | all_occurrences | total_wait_time | avg_wait_time |
+--------------------------------------+-----------------+-----------------+---------------+
| wait/io/file/sql/binlog              |            7090 | 255.54 s        | 36.04 ms      |
| wait/io/file/innodb/innodb_log_file  |           17798 | 55.43 s         | 3.11 ms       |
| wait/io/file/innodb/innodb_data_file |          260227 | 39.67 s         | 0.15 ms       |
| wait/io/table/sql/handler            |         5548985 | 11.73 s         | 0.00 ms       |
| wait/io/file/sql/FRM                 |            1237 | 7.61 s          | 6.15 ms       |
| wait/io/file/sql/dbopt               |              28 | 1.89 s          | 67.38 ms      |
| wait/io/file/myisam/kfile            |              92 | 0.76 s          | 8.30 ms       |
| wait/io/file/myisam/dfile            |             271 | 0.53 s          | 1.95 ms       |
| wait/io/file/sql/file_parser         |              18 | 0.32 s          | 17.75 ms      |
| wait/io/file/sql/slow_log            |               2 | 0.05 s          | 25.79 ms      |
+--------------------------------------+-----------------+-----------------+---------------+
10 rows in set (0.00 sec)

Uitvoeringstijd van SELECT-instructies beperken

Als u niet weet wat de uitvoeringskosten en uitvoeringstijd zijn voor databasebewerkingen met SELECT-query's, kunnen langlopende SELECT's leiden tot onvoorspelbaarheid of volatiliteit in de databaseserver. De grootte van instructies en transacties, evenals het bijbehorende resourcegebruik, blijft toenemen, afhankelijk van de onderliggende groei van de gegevensset. Vanwege deze niet-gebonden groei duren instructies en transacties van eindgebruikers langer en langer, en verbruiken steeds meer resources totdat ze de databaseserver overbelasten. Wanneer u niet-afhankelijke SELECT-query's gebruikt, is het raadzaam om de parameter max_execution_time te configureren, zodat query's die deze duur overschrijden, worden afgebroken.

Aanbevelingen

  • Zorg ervoor dat uw database voldoende resources heeft toegewezen om uw query's uit te voeren. Soms moet u mogelijk de grootte van het exemplaar omhoog schalen om meer CPU-kernen te krijgen om tegemoet te komen aan uw workload.
  • Vermijd grote of langlopende transacties door ze op te delen in kleinere transacties.
  • Voer indien mogelijk SELECT-instructies uit op leesreplicaservers.
  • Gebruik waarschuwingen voor 'CPU-percentage hosten' 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.