循序渐进丨内存越大，MogDB 数据库中truncate/drop越慢？

云和恩墨

于 2024-01-04 17:30:09 发布

阅读量76

点赞数

文章标签：数据库

原文链接：https://mp.weixin.qq.com/s?__biz=MjM5MzExMTU2OQ==&mid=2650633351&idx=1&sn=13782df54b00db2d64f7fee5e8ad9118&chksm=bf362966ef39f310ba9c43f9af000e6b16c5d7da56d72d1a8c25b8585ae9c402d3e556cd5565&scene=126&sessionid=0

版权

之前白鳝写了一篇文章说在 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 -p26000
Password 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 on
Timing 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 EXECUTE
Time: 1970.105 ms
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 EXECUTE
Time: 1044.290 ms
enmotech=> 
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 EXECUTE
Time: 1522.125 ms
enmotech=> 
enmotech=>

##share Buffer 4GB

[omm@mogdb1 ~]$ gsql -c "SHOW shared_buffers"
 shared_buffers 
----------------
 4GB
(1 row)


[omm@mogdb1 ~]$ gsql -d enmotech -U enmotech -p26000
Password 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 on
Timing 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 EXECUTE
Time: 1959.191 ms
enmotech=> 
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 EXECUTE
Time: 1218.434 ms
enmotech=> 
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 EXECUTE
Time: 1573.922 ms
enmotech=> 
enmotech=>

##share Buffer 8GB

[omm@mogdb1 ~]$ gsql -c "SHOW shared_buffers"
 shared_buffers 
----------------
 8GB
(1 row)


[omm@mogdb1 ~]$ gsql -d enmotech -U enmotech -p26000
Password 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 on
Timing 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 EXECUTE
Time: 2013.693 ms
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 EXECUTE
Time: 1121.294 ms
enmotech=> 
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 EXECUTE
Time: 1519.000 ms
enmotech=> 
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 -p26000
Password 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 on
Timing 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 EXECUTE
Time: 2134.423 ms
enmotech=> 
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 EXECUTE
Time: 1060.487 ms
enmotech=> 
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 EXECUTE
Time: 1412.475 ms
enmotech=>

说明：每次调整完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多个灾难恢复项目，完成众多行业头部客户的数据库升级、迁移工作。

数据驱动，成就未来，云和恩墨，不负所托！

云和恩墨创立于2011年，以“数据驱动，成就未来”为使命，是智能的数据技术提供商。我们致力于将数据技术带给每个行业、每个组织、每个人，构建数据驱动的智能未来。

云和恩墨在数据承载（分布式存储、数据持续保护）、管理（数据库基础软件、数据库云管平台、数据技术服务）、加工（应用开发质量管控、数据模型管控、数字化转型咨询）和应用（数据服务化管理平台、数据智能分析处理、隐私计算）等领域为各个组织提供可信赖的产品、服务和解决方案，围绕用户需求，持续为客户创造价值，激发数据潜能，为成就未来敏捷高效的数字世界而不懈努力。

云和恩墨

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
循序渐进丨内存越大，MogDB 数据库中truncate/drop越慢？

之前白鳝写了一篇文章说在 PostgreSQL 中，当share buffer设置过大时，反而一定程度影响了性能，比如影响了DDL。其实这一点还是很容易理解的，之前学过 Oracle 的朋友都知道检查点，truncate/drop table是需要触发检查点的，我们称为mini checkpoint。此时触发检查点，就需要进行刷脏处理，进而去扫描LRU，而LRU的长度是跟buffer cache大...
复制链接

扫一扫