语言
<< 返回文章列表

内存越大，MogDB 数据库中truncate/drop越慢？

2024年1月4日
M
o
g
D
B
,
数
据
库
调
优
,
数
据
迁
移
,
数
据
库
性
能
,
数
据
库
技
术
Roger
24

之前白鳝写了一篇文章说在 PostgreSQL 中，当share buffer设置过大时，反而一定程度影响了性能，比如影响了DDL。其实这一点还是很容易理解的，之前学过 Oracle 的朋友都知道检查点，truncate/drop table是需要触发检查点的，我们称为mini checkpoint。

此时触发检查点，就需要进行刷脏处理，进而去扫描LRU，而LRU的长度是跟buffer cache大小有关的，因此在 Oracle 数据库中，如果你把 buffer cache 设置过大，那么drop/truncate是要相对慢一些的。实际上十年前我就遇到过类似问题，当时帮一个客户迁移数据，通过impdp导入后，在正式割接之间，需要将schema全部drop，然后重新导入。最后我发现在头一天下午6点跑的drop user 命令，到第二天早上还没完成，虽然说该用户有数十万个对象。最后通过将buffer cache设置为200m，重启数据库，然后继续执行drop user操作，发现5分钟即完成了。从原理上来讲很好理解这个问题。

说到这个问题，那么就有用户询问，你们的 MogDB 是否存在这个问题呢？因此这里我想通过测试环境来简单测试并验证一下这个问题。

##share Buffer 1GB

  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
[omm@mogdb1 ~]$ gsql -c "SHOW shared_buffers" shared_buffers ---------------- 1GB(1 row)
[omm@mogdb1 ~]$ gsql -d enmotech -U enmotech -p26000Password for user enmotech: gsql ((MogDB 5.0.3 build 86d963ad) compiled at 2023-10-13 09:17:48 commit 0 last mr 1804 )Non-SSL connection (SSL connection is recommended when requiring high-security)Type "help" for help.
enmotech=> \timing onTiming is on.enmotech=> enmotech=> DO $$   enmotech$> DECLARE  enmotech$>    counter INT = 1;  BEGIN     WHILE counter <= 1000 LOOP        EXECUTE 'CREATE TABLE test_table_' || counter || ' (id INT, name VARCHAR(255))';        EXECUTE 'INSERT INTO test_table_' || counter || ' VALUES (1, ''enmotech'')';        counter := counter + 1;     END LOOP;  END $$;enmotech$> enmotech$> enmotech$> enmotech$> enmotech$> enmotech$> enmotech$> ANONYMOUS BLOCK EXECUTETime: 1970.105 msenmotech=> enmotech=> DO $$  enmotech$> DECLARE     counter INT = 1;     tableName TEXT;  BEGIN     WHILE counter <= 1000 LOOP        tableName := 'test_table_' || counter;        EXECUTE 'TRUNCATE TABLE ' || tableName;  enmotech$> enmotech$> enmotech$> enmotech$> enmotech$> enmotech$> enmotech$>       counter := counter + 1;     END LOOP;  END $$;enmotech$> enmotech$> ANONYMOUS BLOCK EXECUTETime: 1044.290 msenmotech=> enmotech=> DO $$  enmotech$> DECLARE     counter INT = 1;  enmotech$> enmotech$>    tableName TEXT;  enmotech$> BEGIN  enmotech$>    WHILE counter <= 1000 LOOP  enmotech$>       tableName := 'test_table_' || counter;  enmotech$>       EXECUTE 'drop TABLE ' || tableName;  enmotech$>       counter := counter + 1;  enmotech$>    END LOOP;  enmotech$> END $$;ANONYMOUS BLOCK EXECUTETime: 1522.125 msenmotech=> enmotech=>

##share Buffer 4GB

  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
[omm@mogdb1 ~]$ gsql -c "SHOW shared_buffers" shared_buffers ---------------- 4GB(1 row)
[omm@mogdb1 ~]$ gsql -d enmotech -U enmotech -p26000Password for user enmotech: gsql ((MogDB 5.0.3 build 86d963ad) compiled at 2023-10-13 09:17:48 commit 0 last mr 1804 )Non-SSL connection (SSL connection is recommended when requiring high-security)Type "help" for help.
enmotech=> enmotech=> \timing onTiming is on.enmotech=> DO $$   enmotech$> DECLARE     counter INT = 1;  BEGIN     WHILE counter <= 1000 LOOP        EXECUTE 'CREATE TABLE test_table_' || counter || ' (id INT, name VARCHAR(255))';        EXECUTE 'INSERT INTO test_table_' || counter || ' VALUES (1, ''enmotech'')';        counter := counter + 1;  enmotech$> enmotech$> enmotech$> enmotech$> enmotech$> enmotech$> enmotech$>    END LOOP;  END $$;enmotech$> ANONYMOUS BLOCK EXECUTETime: 1959.191 msenmotech=> enmotech=> enmotech=> DO $$  enmotech$> DECLARE     counter INT = 1;     tableName TEXT;  BEGIN     WHILE counter <= 1000 LOOP        tableName := 'test_table_' || counter;        EXECUTE 'TRUNCATE TABLE ' || tableName;  enmotech$> enmotech$> enmotech$> enmotech$> enmotech$> enmotech$> enmotech$>       counter := counter + 1;     END LOOP;  END $$;enmotech$> enmotech$> ANONYMOUS BLOCK EXECUTETime: 1218.434 msenmotech=> enmotech=> enmotech=> DO $$  enmotech$> DECLARE  enmotech$>    counter INT = 1;     tableName TEXT;  BEGIN     WHILE counter <= 1000 LOOP        tableName := 'test_table_' || counter;        EXECUTE 'drop TABLE ' || tableName;        counter := counter + 1;  enmotech$> enmotech$> enmotech$> enmotech$> enmotech$> enmotech$> enmotech$>    END LOOP;  enmotech$> END $$;ANONYMOUS BLOCK EXECUTETime: 1573.922 msenmotech=> enmotech=>

##share Buffer 8GB

  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
[omm@mogdb1 ~]$ gsql -c "SHOW shared_buffers" shared_buffers ---------------- 8GB(1 row)
[omm@mogdb1 ~]$ gsql -d enmotech -U enmotech -p26000Password for user enmotech: gsql ((MogDB 5.0.3 build 86d963ad) compiled at 2023-10-13 09:17:48 commit 0 last mr 1804 )Non-SSL connection (SSL connection is recommended when requiring high-security)Type "help" for help.
enmotech=> \timing onTiming is on.enmotech=> DO $$   enmotech$> DECLARE  enmotech$>    counter INT = 1;  enmotech$> BEGIN     WHILE counter <= 1000 LOOP        EXECUTE 'CREATE TABLE test_table_' || counter || ' (id INT, name VARCHAR(255))';  enmotech$> enmotech$> enmotech$>       EXECUTE 'INSERT INTO test_table_' || counter || ' VALUES (1, ''enmotech'')';  enmotech$>       counter := counter + 1;     END LOOP;  END $$;enmotech$> enmotech$> ANONYMOUS BLOCK EXECUTETime: 2013.693 msenmotech=> enmotech=> DO $$  enmotech$> DECLARE     counter INT = 1;     tableName TEXT;  BEGIN     WHILE counter <= 1000 LOOP        tableName := 'test_table_' || counter;        EXECUTE 'TRUNCATE TABLE ' || tableName;  enmotech$> enmotech$> enmotech$> enmotech$> enmotech$> enmotech$> enmotech$>       counter := counter + 1;     END LOOP;  END $$;enmotech$> enmotech$> ANONYMOUS BLOCK EXECUTETime: 1121.294 msenmotech=> enmotech=> DO $$  enmotech$> DECLARE     counter INT = 1;     tableName TEXT;  BEGIN     WHILE counter <= 1000 LOOP        tableName := 'test_table_' || counter;        EXECUTE 'drop TABLE ' || tableName;  enmotech$> enmotech$> enmotech$> enmotech$> enmotech$> enmotech$> enmotech$>       counter := counter + 1;     END LOOP;  END $$;enmotech$> enmotech$> ANONYMOUS BLOCK EXECUTETime: 1519.000 msenmotech=> enmotech=> \q[omm@mogdb1 ~]$

##share Buffer 16GB

  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
[omm@mogdb1 ~]$ gsql -c "SHOW shared_buffers" shared_buffers ---------------- 16GB(1 row)
[omm@mogdb1 ~]$ gsql -d enmotech -U enmotech -p26000Password for user enmotech: gsql ((MogDB 5.0.3 build 86d963ad) compiled at 2023-10-13 09:17:48 commit 0 last mr 1804 )Non-SSL connection (SSL connection is recommended when requiring high-security)Type "help" for help.
enmotech=> \timing onTiming is on.enmotech=> DO $$   enmotech$> DECLARE     counter INT = 1;  BEGIN     WHILE counter <= 1000 LOOP        EXECUTE 'CREATE TABLE test_table_' || counter || ' (id INT, name VARCHAR(255))';  enmotech$> enmotech$> enmotech$> enmotech$> enmotech$>       EXECUTE 'INSERT INTO test_table_' || counter || ' VALUES (1, ''enmotech'')';  enmotech$>       counter := counter + 1;  enmotech$>    END LOOP;  END $$;enmotech$> ANONYMOUS BLOCK EXECUTETime: 2134.423 msenmotech=> enmotech=> enmotech=> DO $$  enmotech$> DECLARE     counter INT = 1;     tableName TEXT;  BEGIN     WHILE counter <= 1000 LOOP        tableName := 'test_table_' || counter;        EXECUTE 'TRUNCATE TABLE ' || tableName;  enmotech$> enmotech$> enmotech$> enmotech$> enmotech$> enmotech$> enmotech$>       counter := counter + 1;     END LOOP;  END $$;enmotech$> enmotech$> ANONYMOUS BLOCK EXECUTETime: 1060.487 msenmotech=> enmotech=> enmotech=> DO $$  enmotech$> DECLARE     counter INT = 1;     tableName TEXT;  BEGIN     WHILE counter <= 1000 LOOP  enmotech$> enmotech$> enmotech$> enmotech$> enmotech$>       tableName := 'test_table_' || counter;  enmotech$>       EXECUTE 'drop TABLE ' || tableName;        counter := counter + 1;     END LOOP;  END $$;enmotech$> enmotech$> enmotech$> ANONYMOUS BLOCK EXECUTETime: 1412.475 msenmotech=>
说明：每次调整完share buffer参数，我都是用ptk进行了数据库集群的重启。

我们来简单总结一下上述的测试，汇总一下测试结果：

shared Buffers create(时间) truncate(时间) drop table(时间)
1GB 1970.105ms 1044.290ms 1522.125ms
4GB 1959.191ms 1218.434ms 1573.922ms
8GB 2013.693ms 1121.294ms 1519.000ms
16GB 2134.423ms 1060.487ms 1412.475ms
我们可以看到，随着shared buffers内存的增加，实际上我这里无论是truncate还是drop table操作，单从执行时间上来讲，几乎没有任何变化，也就不存在 PostgreSQL 中类似的问题。

关于作者

Roger，本名：李真旭，云和恩墨西区服务部总经理，数据库灾难恢复专家，主要带领团队完成数据库升级迁移、整合、灾难恢复等专业服务交付，十年间已交付500多个灾难恢复项目，完成众多行业头部客户的数据库升级、迁移工作。