postgresql - PostgreSQL LOGGED 和 UNLOGGED 表性能对比与 LOCK TABLE 使用情况
问题描述
我正在尝试测量PostgreSQL(10.3) 中的表和表的UPDATE
速度。我想用来防止其他应用程序相互干扰。LOGGED
UNLOGGED
LOCK TABLE
如果UPDATE
在不使用的情况下执行LOCK TABLE
,我会得到〜1K的LOGGED
桌子,〜4K的UNLOGGED
桌子。
如果UPDATE
使用 执行LOCK TABLE
,则两种表类型的结果相同。
为什么LOCK TABLE
在两种表类型中返回相同的结果?
我的 PL/pgSQL 函数:
CREATE OR REPLACE FUNCTION public.myfunction(user_id integer, unitprice numeric(10,6),
islock boolean, useunlogged boolean) RETURNS integer AS
$BODY$
declare howMuch integer;
begin
if islock then
if useunlogged then
LOCK TABLE credittable_unlogged IN ACCESS EXCLUSIVE MODE;
else
LOCK TABLE credittable IN ACCESS EXCLUSIVE MODE;
end if;
end if;
if useunlogged then
select (credit_amount/unitprice)::integer into howMuch from credittable where userid=user_id and credit_amount>=unitprice;
if howMuch is null then
select 0 into howMuch;
else
update credittable set credit_amount=credit_amount-unitprice where userid=user_id;
end if;
else
select (credit_amount/unitprice)::integer into howMuch from credittable_unlogged where userid=user_id and credit_amount>=unitprice;
if howMuch is null then
select 0 into howMuch;
else
update credittable_unlogged set credit_amount=credit_amount-unitprice where userid=user_id;
end if;
end if;
RETURN howMuch;
end;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION public.myfunction(integer, numeric, boolean, boolean)
OWNER TO postgres;
我的Java代码:
for(int i=1;i<=4;i++){
long startTime = System.nanoTime();
int counter = 0;
while ((System.nanoTime() - startTime) < 1000000000L) {
CallableStatement callst = null;
try {
String sql = "{? = call public.myfunction(?,?,?,?) }";
callst = con.prepareCall(sql);
callst.registerOutParameter(1, Types.INTEGER);
callst.setInt(2, 123456);
callst.setBoolean(3, (i > 2));
callst.setBoolean(4, (i%2 != 0));
callst.setBigDecimal(3, BigDecimal.valueOf(0.001));
callst.execute();
int howMuch = callst.getInt(1);
counter++;
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (callst != null) {
callst.close();
}
}
}
System.out.println("Counter :"+counter);
}
解决方案
您在这里测量的很多内容是客户端-服务器延迟和 PL/pgSQL 执行。
差异是由于需要将 WAL 同步到磁盘造成的。
如果您使用未记录的表,并且不使用该LOCK
语句,则不会写入 WAL,并且无需同步任何内容COMMIT
。
显式表锁会导致写入 WAL 记录,因此COMMIT
仍然必须同步 WAL,并且您会失去未记录表的优势。
您可以使用pg_waldump
检查 WAL 文件,然后您将看到写入了哪些事务日志记录。
但我可以用我的 PostgreSQL v11 向你展示它,它是用-DWAL_DEBUG
.
这是我的测试表:
postgres=# \d t
Unlogged table "public.t"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
id | integer | | |
这里INSERT
没有LOCK TABLE
:
postgres=# SET wal_debug=on;
SET
postgres=# BEGIN;
BEGIN
postgres=# INSERT INTO t VALUES (100);
INSERT 0 1
postgres=# COMMIT;
LOG: INSERT @ 0/166BFB8: - Transaction/COMMIT: 2018-05-18 20:34:20.060635+02
STATEMENT: COMMIT;
COMMIT
有一个提交,但没有 WAL 刷新。
postgres=# BEGIN;
BEGIN
postgres=# LOCK TABLE t;
LOG: INSERT @ 0/166C038: - Standby/LOCK: xid 569 db 13344 rel 16384
STATEMENT: LOCK TABLE t;
LOCK TABLE
postgres=# INSERT INTO t VALUES (101);
INSERT 0 1
postgres=# COMMIT;
LOG: INSERT @ 0/166C138: - Transaction/COMMIT: 2018-05-18 20:36:15.419081+02
STATEMENT: COMMIT;
LOG: xlog flush request 0/166C138; write 0/166BFF0; flush 0/166BFF0
STATEMENT: COMMIT;
COMMIT
现在我们有一个 WAL 刷新,这是昂贵的部分。
您会看到Standby/LOCK
已写入记录。
解决这个问题的一种方法是减少wal_level
tominimal
和max_wal_senders
to 0
,那么这些 WAL 记录就不必写了。但是你不能有 WAL 归档和时间点恢复。
另一种解决方法是使用低于ACCESS EXCLUSIVE
. 除非您绝对必须阻止读者,否则这应该没问题。
推荐阅读
- android - 在 recyclerview 项目上单击 show recyclerview data custom in toDialog Box
- javascript - Vue js 2 将数据发布到 JSON 对象
- c++ - ARCore NDK Pose 的 Matrix4x4 格式是什么?
- swift - 如何提供模型迁移/建表的默认值?
- kubernetes - 为什么 helm 需要一个集群端组件(tiller)?
- android - 使用 kotlin 在特定时期更新 firebase 实时数据库
- ios - Flutter - 如何创建 CupertinoAlertDialog
- linux - 如何在linux中生成可变内存负载(%)?
- python - 如何为 celery AsyncTask 使用结果回调
- java - 在 Azure Function 中从 C# 执行 java.exe