MariaDB が CPU 時間 500% で異常終了

Question 1

CPU 使用率が高いということは、通常、メモリ内のテーブルスキャンが大量に行われていることを意味します。

遅いクエリの 1 つを見てみましょう。

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

クエリは400万行のテーブル全体を読み取りますが、最大で1つ
それらのうちの1つ（そして、この場合は、まったくありません）。インデックスuser_idこのクエリをサポートするには、このテーブルを使用します。との複合クエリからfile_id始めるのが良いでしょう。

ここでもパフォーマンスを低下させる要因が 1 つあります。

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

「選択する *「製品コード内」
いつも必要な列を明示的に指定します。このテーブルは最近「成長」している（列が増えている）と思います。WHERE
句がなくても、これはとにかくテーブルスキャンになりますが、それでは疑問が残ります。かわいそうなクライアント（アプリケーション）はどうなるのでしょうか？するこのクエリが「投げる」 400 万行ではどうでしょうか?

Answer

CPU 使用率が高いということは、通常、メモリ内のテーブルスキャンが大量に行われていることを意味します。

遅いクエリの 1 つを見てみましょう。

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

クエリは400万行のテーブル全体を読み取りますが、最大で1つ
それらのうちの1つ（そして、この場合は、まったくありません）。インデックスuser_idこのクエリをサポートするには、このテーブルを使用します。との複合クエリからfile_id始めるのが良いでしょう。

ここでもパフォーマンスを低下させる要因が 1 つあります。

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

「選択する *「製品コード内」
いつも必要な列を明示的に指定します。このテーブルは最近「成長」している（列が増えている）と思います。WHERE
句がなくても、これはとにかくテーブルスキャンになりますが、それでは疑問が残ります。かわいそうなクライアント（アプリケーション）はどうなるのでしょうか？するこのクエリが「投げる」 400 万行ではどうでしょうか?

Question 2

これらのクエリの一部は最適化が必要であると思われます。

例えば：

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

このクエリは 30000000 行をスキャンしています。

これを、たとえば 2 つのクエリに変更することも可能です。

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

クエリ自体に無駄な結合と 2 つの完全なテーブルスキャンが含まれるため、半分に減らすことができます。

あなたが示した内容の多くはクエリの最適化に関する問題を指摘しており、その遅いクエリログを調べて、最小限のテーブルスキャンでそのデータを取得する最適な方法を決定します。

おそらく、同じ目的で、そのデータの多くもインデックス化する必要があるでしょう。

Answer

これらのクエリの一部は最適化が必要であると思われます。

例えば：

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

このクエリは 30000000 行をスキャンしています。

これを、たとえば 2 つのクエリに変更することも可能です。

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

クエリ自体に無駄な結合と 2 つの完全なテーブルスキャンが含まれるため、半分に減らすことができます。

あなたが示した内容の多くはクエリの最適化に関する問題を指摘しており、その遅いクエリログを調べて、最小限のテーブルスキャンでそのデータを取得する最適な方法を決定します。

おそらく、同じ目的で、そのデータの多くもインデックス化する必要があるでしょう。

Question 3

** これにより、最初のものが大幅に高速化されるはずです。

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

そして

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

このクエリについてさらに議論する必要がある場合は、SHOW CREATE TABLE両方のテーブル、テーブルのサイズ、およびを提供してくださいEXPLAIN SELECT ...。

** これ

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

大幅に高速化するには、3 列の複合インデックスが必要です。

INDEX(user_id, file_id, udownload_id)

どの行が一致するかは気にしませんか? または、を追加する必要がありますかORDER BY? を追加するとORDER BY、インデックスに関するアドバイスを変更する必要がある場合があります。

** これはから来たのですかmysqldump?

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

ダンプの侵入性に不満がある場合は、そのトピックについて dba.stackexchange.com でいくつかのディスカッションがあります。

Answer