Postgresql 生产问题一例, 为什么1000行数据这么慢要27秒

最近开发部门的测试提出一个问题,在我们某一个项目的postgresql V12的服务器上某个表在查询的时候1000行数据竟然跑出了 27秒的"好成绩".  我大PG 的性能这么差,这不能呀.   好请跟着我们来,走近科学, 剥丝抽茧 1000行数据,select * 竟然要27秒 ?

首先我们验证了一下,的确在PG_ADMIN查询平台上,查询很慢这张表的确要20多秒才能将1000行数据展出,但问题是他真的只有1000行.  那么我们先看看到底是为什么.

从上图看的确是如此,并且pg_admin还因为查询时过载,重新启动了服务

既然这个事情是既定的事实,那么我们先来看看这个表的表结构是什么.

这个表中包含了两个JSON 格式的数据, 问题就从这里开始,

我们通过命令来查询这张表到底有多大,  214MB , 几百行的数据已经到了214MB, 并且还是不包含索引的情况下.那么此时心里已经有底了,  request 和 result  字段必然是这个问题的关键.

我们对result 的字段进行可读的设置,证明一个字段大小在不到400KB.

根据toast中的四种的模式的介绍 plan , extended , external, main 4种模式,

Plan  的方式是阻止toast存储,并且也禁止压缩数据,也就是当你的一个column的数据大于页面的大小,将被禁止存储.

Extended 允许压缩和跨行存储,这个是每个列最常见的存储的模式,首先要压缩然后在toast存储

EXTERNAL 这个方式和上的方式的区别就是压缩,这样的存储是不会对数据进行压缩处理的,直接而这样的方式对于text和bytea存储是可以相对于上的存储方式要快速的.

Main 方式允许压缩存储但不允许使用TOAST的方式进行数据的存储,如果你的一行恰恰在压缩后可以放到一个页面中,那这样的方式是比较适合的.

create table l_request_log_p (

id varchar(50),

trackingid varchar(20),

applicationid varchar(20),

request json,

createdate timestamp,

result json);

create table l_request_log_ed (

id varchar(50),

trackingid varchar(20),

applicationid varchar(20),

request json,

createdate timestamp,

result json);

create table l_request_log_el (

id varchar(50),

trackingid varchar(20),

applicationid varchar(20),

request json,

createdate timestamp,

result json);

create table l_request_log_m (

id varchar(50),

trackingid varchar(20),

applicationid varchar(20),

request json,

createdate timestamp,

result json);

alter table l_request_log_p alter column request set storage plain;

alter table l_request_log_p alter column result set storage plain;

alter table l_request_log_ed alter column request set storage extended;

alter table l_request_log_ed alter column result set storage extended;

alter table l_request_log_el alter column request set storage EXTERNAL;

alter table l_request_log_el alter column result set storage EXTERNAL;

alter table l_request_log_m alter column request set storage main;

alter table l_request_log_m alter column result set storage main;

我们开始对每个表导入原表的数据看错误日志中是否有什么显示

第一个往plain 中插入数据的的情况就失败了, 这也就证明上面的对于plain的解释.

第二个和第四个插入的时间基本类似

第三个数据插入的时间是最短的 2秒

第四个main的方式数据插入的时间明显要高于其他插入的时间普通的插入时间是  2秒左右 1000行, 而使用main的方式插入数据需要14秒 这也就证明了四种模式中虽然main没有使用TOAST的方式,但已经极尽全力的去压缩数据.

而使用extended的方式虽然也是可以使用TOAST的方式但他也是要先去通过main的方式来压缩数据.

我们对相关的数据表进行查询, 三个数据库表同样的数据量,但是不同的数据存储方式,提取数据的时间是相同的27秒.

我们来查看某个表的toast表的信息存储

select relname,relfilenode,reltoastrelid from pg_class where relname='l_request_log_el';

toast表中的信息主要包含 chunk_id 对应的主表的OID,同时还是 chunk_seq对应这段数据与原表存储的位置,后面chunk_data 就是数据了.

这个就是我们toast表中存储的数据

通过上面的分析,在实际生产中我们再次确认TOAST 功能的强大, 在实际应用中可以存储巨量的数据,但付出的代价是提取速度的问题,但如果27秒能提取 215MB 的数据量,这样的速度也不算慢了.

所以我们已经准备修订POSTGRESQL 数据库的规范,对于某些未标明的字段我们需要将字段的STORAGE 类型修改成PLAIN,方式滥用TOAST的功能导致数据库性能降低还不自知.

https://github.com/credativ/toastinfo  另如果想详细了解TOAST 存储的可以使用上面的插件来操作,具体的操作的以及TOAST 详细的说明在

https://cloud.tencent.com/developer/article/1760474