CREATE TABLE AS SELECT 杀死 MySQL

发布于 2024-08-05 02:58:43 字数 2618 浏览 10 评论 0原文

我们在相当强大的硬件（具有 8 个 Xeon 核心、8Gb RAM 和 RAID10 的 HP DL360）上运行中等负载（200-300 QPS）的 MySQL 服务器。所有表都是 innodb，活动数据集适合分配的 innodb_buffer_pool_size。

我们的数据库已标准化，为了减少连接数量，我们使用物化视图来展平数据集。由于数据每天会分批添加几次，因此会使用 CREATE TABLE AS SELECT 重新生成 MV，而不是使用复杂的触发器动态更新。

问题是，有时当这些 CREATE 查询运行时（每个查询都需要 5 到 50 秒），其他与服务器无关的查询似乎会在 CREATE 后面排队> 查询，导致数据库无响应。

为了（重新）生成 MV:s，我们使用类似这样的东西：

BEGIN TRANSACTION;
DROP TABLE IF EXISTS TableName_TMP;
CREATE TABLE TableName_TMP ENGINE=INNODB CHARACTER SET utf8 COLLATE utf8_swedish_ci AS 
    SELECT about100columns, and10Expressions 
    FROM Table1 
    JOIN Table2 ON Table1.fk = Table2.pk 
    /* join up to 13 other tables */
    WHERE ((removed IS NULL OR removed = 0)) 
    ORDER BY created DESC, id ASC;
ALTER TABLE TableName_TMP ADD PRIMARY KEY(id), INDEX(created);
DROP TABLE IF EXISTS TableName;
ALTER TABLE TableName_TMP RENAME TO TableName;
COMMIT;

SELECT 的 EXPLAIN 产生类似的东西：

+----+-------------+------------------+-------------+---------------+------------+---------+------------------------------+-------+-----------------------------+
| id | select_type | table            | type        | possible_keys | key        | key_len | ref                          | rows  | Extra                       |
+----+-------------+------------------+-------------+---------------+------------+---------+    ------------------------------+-------+-----------------------------+
|  1 | SIMPLE      | Table1           | ref_or_null | removed       | removed    | 5       | const                        | 76093 | Using where; Using filesort | 
|  1 | SIMPLE      | Table2           | eq_ref      | PRIMARY       | PRIMARY    | 4       | Table1.fk1                   |     1 |                             | 
|  1 | SIMPLE      | Table3           | eq_ref      | PRIMARY       | PRIMARY    | 4       | Table1.fk2                   |     1 |                             | 
/* More of the same */
|  1 | SIMPLE      | TableN           | eq_ref      | PRIMARY       | PRIMARY    | 4        | TableM.fk                    |     1 | Using index                 | 
|  1 | SIMPLE      | TableX           | eq_ref      | PRIMARY       | PRIMARY    | 4       | TableY.fk                    |     1 |                             | 
/* More of the same */    
+----+-------------+------------------+-------------+---------------+------------+---------+------------------------------+-------+-----------------------------+

有什么想法为什么 CREATE TABLE AS 完全超载我们的服务器以及如何防止它？

问候，

原文

We run a MySQL server with moderate load (200-300 QPS) on quite powerful hardware (HP DL360 with 8 Xeon cores, 8Gb RAM and RAID10). All the tables are innodb and the active dataset fits within the allocated innodb_buffer_pool_size.

Our database is normalized and to reduce the number of joins we use materialized views to flatten the dataset. As the data is added in batches a few times a day the MV:s are regenerated using CREATE TABLE AS SELECT instead of dynamically updated using complex triggers.

The problem is that sometimes while these CREATE queries are run (each of which takes anything from 5 to 50 seconds) other unrelated queries to the server seems to get queued up behind the CREATE query, leading to a unresponsive database.

To (re-)generate the MV:s we use something like this:

BEGIN TRANSACTION;
DROP TABLE IF EXISTS TableName_TMP;
CREATE TABLE TableName_TMP ENGINE=INNODB CHARACTER SET utf8 COLLATE utf8_swedish_ci AS 
    SELECT about100columns, and10Expressions 
    FROM Table1 
    JOIN Table2 ON Table1.fk = Table2.pk 
    /* join up to 13 other tables */
    WHERE ((removed IS NULL OR removed = 0)) 
    ORDER BY created DESC, id ASC;
ALTER TABLE TableName_TMP ADD PRIMARY KEY(id), INDEX(created);
DROP TABLE IF EXISTS TableName;
ALTER TABLE TableName_TMP RENAME TO TableName;
COMMIT;

The EXPLAIN of the SELECT produces something like:

+----+-------------+------------------+-------------+---------------+------------+---------+------------------------------+-------+-----------------------------+
| id | select_type | table            | type        | possible_keys | key        | key_len | ref                          | rows  | Extra                       |
+----+-------------+------------------+-------------+---------------+------------+---------+    ------------------------------+-------+-----------------------------+
|  1 | SIMPLE      | Table1           | ref_or_null | removed       | removed    | 5       | const                        | 76093 | Using where; Using filesort | 
|  1 | SIMPLE      | Table2           | eq_ref      | PRIMARY       | PRIMARY    | 4       | Table1.fk1                   |     1 |                             | 
|  1 | SIMPLE      | Table3           | eq_ref      | PRIMARY       | PRIMARY    | 4       | Table1.fk2                   |     1 |                             | 
/* More of the same */
|  1 | SIMPLE      | TableN           | eq_ref      | PRIMARY       | PRIMARY    | 4        | TableM.fk                    |     1 | Using index                 | 
|  1 | SIMPLE      | TableX           | eq_ref      | PRIMARY       | PRIMARY    | 4       | TableY.fk                    |     1 |                             | 
/* More of the same */    
+----+-------------+------------------+-------------+---------------+------------+---------+------------------------------+-------+-----------------------------+

Any ideas why the CREATE TABLE AS completly overload our server and how I can prevent it?

Regards,

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

末骤雨初歇 2024-08-12 02:58:43

我们通过切换到 SELECT INTO 和 LOAD DATA INFILE 解决了这个问题，如 http://www.mysqlperformanceblog.com/2006/07/12/insert-into-select-performance-with-innodb-tables/ 。非常感谢伦道夫·波特为我们指明了正确的方向。

回复收藏 0 原文