MariaDB verrückt bei 500 % CPU-Zeit

Question 1

Eine hohe CPU-Auslastung bedeutet normalerweise viele Tabellenscans im Arbeitsspeicher.

Sehen wir uns eine Ihrer langsamen Abfragen an:

Time=5.64s (5s)  Rows_sent=0.0 (0)  Rows_examined=4088258.0 (4088258)
SELECT  udownload_id AS retval 
FROM hb_udownloads 
WHERE  user_id = 415064  
AND file_id = 78499  
LIMIT 1

Die Abfrage liest die gesamte 4M-Zeilen-Tabelle, gibt aber höchstens zurückeinsvon ihnen (und in diesem Fall überhaupt keine).
Sie brauchenIndizesin dieser Tabelle, um diese Abfrage zu unterstützen. Eine zusammengesetzte Tabelle in user_idund file_idwäre ein guter Anfang.

Noch ein Performancekiller hier:

Time=20.58s (41s)  Rows_sent=4089261.5 (8178523)  Rows_examined=4089261.5 (8178523)
SELECT /*!40001 SQL_NO_CACHE */ * 
FROM `hb_udownloads`

Benutze niemals "wählen *" im Produktionscode.
StetsGeben Sie die gewünschten Spalten explizit an. Ich würde vermuten, dass diese Tabelle in letzter Zeit „gewachsen“ ist (Spalten hinzugewonnen hat).
OK, ohne eine Where-Klausel wird dies sowieso einen Tabellenscan durchführen, aber das wirft die Frage auf: Was wird der arme Client [die Anwendung] tun?Tunmit den 4 Millionen Zeilen, die diese Abfrage ihm „zuwirft“?

Answer

Eine hohe CPU-Auslastung bedeutet normalerweise viele Tabellenscans im Arbeitsspeicher.

Sehen wir uns eine Ihrer langsamen Abfragen an:

Time=5.64s (5s)  Rows_sent=0.0 (0)  Rows_examined=4088258.0 (4088258)
SELECT  udownload_id AS retval 
FROM hb_udownloads 
WHERE  user_id = 415064  
AND file_id = 78499  
LIMIT 1

Die Abfrage liest die gesamte 4M-Zeilen-Tabelle, gibt aber höchstens zurückeinsvon ihnen (und in diesem Fall überhaupt keine).
Sie brauchenIndizesin dieser Tabelle, um diese Abfrage zu unterstützen. Eine zusammengesetzte Tabelle in user_idund file_idwäre ein guter Anfang.

Noch ein Performancekiller hier:

Time=20.58s (41s)  Rows_sent=4089261.5 (8178523)  Rows_examined=4089261.5 (8178523)
SELECT /*!40001 SQL_NO_CACHE */ * 
FROM `hb_udownloads`

Benutze niemals "wählen *" im Produktionscode.
StetsGeben Sie die gewünschten Spalten explizit an. Ich würde vermuten, dass diese Tabelle in letzter Zeit „gewachsen“ ist (Spalten hinzugewonnen hat).
OK, ohne eine Where-Klausel wird dies sowieso einen Tabellenscan durchführen, aber das wirft die Frage auf: Was wird der arme Client [die Anwendung] tun?Tunmit den 4 Millionen Zeilen, die diese Abfrage ihm „zuwirft“?

Question 2

Bei einigen dieser Abfragen scheint eine Optimierung erforderlich zu sein.

Zum Beispiel:

Rows_examined=10026558.0 (30079674), Rows_affected=0.0 (0), server***_fusion[server***_fusion]@localhost
  SELECT *,(select count(id) from tbl_swkey
  where group_id=tbl_keygroup.id and used=0) as selled,(select count(id) from tbl_swkey
  where group_id=tbl_keygroup.id and used=1) as used FROM tbl_keygroup
  order by `displayorder` asc

Diese Abfrage durchsucht 3.000.000 Zeilen.

Möglicherweise können Sie diese ändern, beispielsweise in zwei Abfragen:

SELECT * FROM tbl_keygroup ORDER by `displayorder` asc;
SELECT count(id) FROM tbl_swkey WHERE used IN (0,1) GROUP BY used;

Da die Abfrage selbst einen nutzlosen Join und zwei vollständige Tabellenscans beinhaltet, könnte diese halbiert werden.

Vieles von dem, was Sie gezeigt haben, weist auf Probleme bei der Abfrageoptimierung hin, und ich würde mir das Protokoll dieser langsamen Abfragen ansehen, um zu ermitteln, wie sich diese Daten am besten und mit möglichst wenigen Tabellenscans abrufen lassen.

Aus demselben Grund möchten Sie wahrscheinlich auch viele dieser Daten indizieren.

Answer

Bei einigen dieser Abfragen scheint eine Optimierung erforderlich zu sein.

Zum Beispiel:

Rows_examined=10026558.0 (30079674), Rows_affected=0.0 (0), server***_fusion[server***_fusion]@localhost
  SELECT *,(select count(id) from tbl_swkey
  where group_id=tbl_keygroup.id and used=0) as selled,(select count(id) from tbl_swkey
  where group_id=tbl_keygroup.id and used=1) as used FROM tbl_keygroup
  order by `displayorder` asc

Diese Abfrage durchsucht 3.000.000 Zeilen.

Möglicherweise können Sie diese ändern, beispielsweise in zwei Abfragen:

SELECT * FROM tbl_keygroup ORDER by `displayorder` asc;
SELECT count(id) FROM tbl_swkey WHERE used IN (0,1) GROUP BY used;

Da die Abfrage selbst einen nutzlosen Join und zwei vollständige Tabellenscans beinhaltet, könnte diese halbiert werden.

Vieles von dem, was Sie gezeigt haben, weist auf Probleme bei der Abfrageoptimierung hin, und ich würde mir das Protokoll dieser langsamen Abfragen ansehen, um zu ermitteln, wie sich diese Daten am besten und mit möglichst wenigen Tabellenscans abrufen lassen.

Aus demselben Grund möchten Sie wahrscheinlich auch viele dieser Daten indizieren.

Question 3

** Dadurch sollte der erste Schritt wesentlich schneller erfolgen:

SELECT  k.*, s.sold, s.used
    FROM ( SELECT group_id,
                  SUM(used = 0) AS sold,
                  SUM(used = 1) AS used
               FROM tbl_swkey
         ) AS s
    JOIN tbl_keygroup AS k  ON s.group_id = k.id
    ORDER BY displayorder

Und haben

tbl_swkey:    INDEX(group_id, used)
tbl_keygroup: INDEX(displayorder)

Wenn Sie diese Abfrage weiter erläutern möchten, geben Sie bitte SHOW CREATE TABLEbeide Tabellen, die Tabellengrößen usw. an EXPLAIN SELECT ....

** Das

SELECT  udownload_id AS retval
    FROM  hb_udownloads
    WHERE  user_id = 415064
      AND  file_id = 78499
    LIMIT  1

benötigt einen 3-spaltigen zusammengesetzten Index, um viel schneller zu sein:

INDEX(user_id, file_id, udownload_id)

Es ist Ihnen egal, welche übereinstimmende Zeile Sie erhalten? Oder sollten Sie ein hinzufügen ORDER BY? Wenn Sie ein hinzufügen ORDER BY, muss mein Index-Rat möglicherweise geändert werden.

**Kommt das woher mysqldump?

SELECT /*!40001 SQL_NO_CACHE */ * FROM `hb_udownloads`

Wenn Sie mit der Invasivität von Dumps nicht zufrieden sind, gibt es auf dba.stackexchange.com mehrere Diskussionen zu diesem Thema.

Answer

** Dadurch sollte der erste Schritt wesentlich schneller erfolgen:

SELECT  k.*, s.sold, s.used
    FROM ( SELECT group_id,
                  SUM(used = 0) AS sold,
                  SUM(used = 1) AS used
               FROM tbl_swkey
         ) AS s
    JOIN tbl_keygroup AS k  ON s.group_id = k.id
    ORDER BY displayorder

Und haben

tbl_swkey:    INDEX(group_id, used)
tbl_keygroup: INDEX(displayorder)

Wenn Sie diese Abfrage weiter erläutern möchten, geben Sie bitte SHOW CREATE TABLEbeide Tabellen, die Tabellengrößen usw. an EXPLAIN SELECT ....

** Das

SELECT  udownload_id AS retval
    FROM  hb_udownloads
    WHERE  user_id = 415064
      AND  file_id = 78499
    LIMIT  1

benötigt einen 3-spaltigen zusammengesetzten Index, um viel schneller zu sein:

INDEX(user_id, file_id, udownload_id)

Es ist Ihnen egal, welche übereinstimmende Zeile Sie erhalten? Oder sollten Sie ein hinzufügen ORDER BY? Wenn Sie ein hinzufügen ORDER BY, muss mein Index-Rat möglicherweise geändert werden.

**Kommt das woher mysqldump?

SELECT /*!40001 SQL_NO_CACHE */ * FROM `hb_udownloads`

Wenn Sie mit der Invasivität von Dumps nicht zufrieden sind, gibt es auf dba.stackexchange.com mehrere Diskussionen zu diesem Thema.

MariaDB verrückt bei 500 % CPU-Zeit

Antwort1

Antwort2

Antwort3

verwandte Informationen