内存越大，openGauss/MogDB中truncate/drop越慢？

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

     此时触发检查点，就需要进行刷脏处理，进而去扫描LRU，而LRU的长度是跟Buffer cache大小有关的，因此在Oracle数据库中，如果你buffer cache设置过大，那么drop/truncate是要相对慢一些的。实际上10年前我就遇到过类似问题，当时帮一个客户迁移数据，通过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 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 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 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 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 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 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 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 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 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 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 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 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内存的增加，实际上我这里无论是truncate还是drop table操作，单从执行时间上来讲，几乎没有任何变化，也就不存在PostgreSQL中类似的问题。 我想这或许是openGauss/MogDB中有增量检查点的缘故吧。