255 Star 675 Fork 495

GVPopenGauss / openGauss-server

 / 详情

【业务范畴:开源】【测试类型:统计信息函数】【测试活动:社区】【测试版本:2.1.0】【特性名称:统计信息函数,统计结果有误】【环境:裸机】统计信息函数2.1.0版本,统计结果有误

Accepted
Bug
Opened this issue  
2021-09-29 16:21

【测试类型:统计函数】【测试版本:2.0.0】

【操作系统和硬件信息】(查询命令: cat /etc/system-release, uname -a):
openGauss:x86+centos
【测试环境】(单机/1主x备x级联备): 1主1备
【被测功能】: 统计信息函数,统计执行结果
【测试类型】: 功能测试
【数据库版本】(查询命令: gaussdb –V):
gaussdb (openGauss 2.1.0 build da72ef3c) compiled at 2021-09-27 20:24:34 commit 0 last mr
【预置条件】:数据正常

【操作步骤】(请填写详细的操作步骤):
2.1.0版本以下统计函数与2.0.0版本统计结果均不一致:
pg_stat_get_db_blocks_hit
pg_stat_get_dead_tuples
pg_stat_get_function_calls
pg_stat_get_function_total_time
pg_stat_get_tuples_changed
pg_stat_get_tuples_inserted
pg_stat_get_tuples_updated
pg_stat_get_db_xact_commit
pg_indexes_size
pg_stat_get_dead_tuples 为例
1.测试用例
--记录的是更新和删除的行数
alter system set autovacuum to off;
SELECT pg_sleep(10);
drop table if exists sales;
select pg_stat_reset();
SELECT pg_sleep(10);
--创建表sales
CREATE TABLE sales
(prod_id NUMBER(6),
cust_id NUMBER,
time_id DATE,
channel_id CHAR(1),
promo_id NUMBER(6),
quantity_sold NUMBER(3),
amount_sold NUMBER(10,2)
);
-- 无数据查询
select pg_stat_get_dead_tuples(a.oid) from PG_CLASS a where a.relname = 'sales';
-- 数据插入sales
INSERT INTO sales VALUES(1, 12, '2019-01-10 00:00:00', 'a', 1, 1, 1);
INSERT INTO sales VALUES(1, 12, '2019-02-01 00:00:00', 'b', 1, 1, 1);
select pg_stat_get_dead_tuples(a.oid) from PG_CLASS a where a.relname = 'sales';
-- 更新一行数据,旧的其实还在,只是指针指向了新数据,因此dead记录1
update sales set time_id = '2019-06-02 10:00:00' where channel_id = 'b';
SELECT pg_sleep(10);
select pg_stat_get_dead_tuples(a.oid) from PG_CLASS a where a.relname = 'sales';
-- 再添加数据 1
INSERT INTO sales VALUES(1, 12, '2019-02-05 00:00:00', 'c', 1, 1, 1);
INSERT INTO sales VALUES(1, 12, '2019-02-03 00:00:00', 'd', 1, 1, 1);
INSERT INTO sales VALUES(1, 12, '2019-02-05 00:00:00', 'e', 1, 1, 1);
SELECT pg_sleep(10);
select pg_stat_get_dead_tuples(a.oid) from PG_CLASS a where a.relname = 'sales';
-- 更新多行 4
update sales set time_id = '2015-06-02 10:00:00' where channel_id = 'c';
update sales set time_id = '2013-06-02 10:00:00' where channel_id = 'd';
update sales set time_id = '2012-06-02 10:00:00' where channel_id = 'e';
SELECT pg_sleep(10);
select pg_stat_get_dead_tuples(a.oid) from PG_CLASS a where a.relname = 'sales';
-- 删除一行 5
delete from sales where channel_id = 'b';
SELECT pg_sleep(10);
select pg_stat_get_dead_tuples(a.oid) from PG_CLASS a where a.relname = 'sales';
-- 删除多行 8
delete from sales where channel_id = 'c';
delete from sales where channel_id = 'd';
delete from sales where channel_id = 'e';
SELECT pg_sleep(10);
select pg_stat_get_dead_tuples(a.oid) from PG_CLASS a where a.relname = 'sales';
drop table sales cascade;
alter system set autovacuum to on;
【实际输出】
--记录的是更新和删除的行数
alter system set autovacuum to off;
SQL SUCCESS
SELECT pg_sleep(10);
+----------+
| pg_sleep |
+----------+
| |
+----------+
drop table if exists sales;
SQL SUCCESS
select pg_stat_reset();
+---------------+
| pg_stat_reset |
+---------------+
| |
+---------------+
SELECT pg_sleep(10);
+----------+
| pg_sleep |
+----------+
| |
+----------+
--创建表sales
CREATE TABLE sales
(prod_id NUMBER(6),
cust_id NUMBER,
time_id DATE,
channel_id CHAR(1),
promo_id NUMBER(6),
quantity_sold NUMBER(3),
amount_sold NUMBER(10,2)
);
SQL SUCCESS
-- 无数据查询
select pg_stat_get_dead_tuples(a.oid) from PG_CLASS a where a.relname = 'sales';
+-------------------------+
| pg_stat_get_dead_tuples |
+-------------------------+
| 0 |
+-------------------------+
-- 数据插入sales
INSERT INTO sales VALUES(1, 12, '2019-01-10 00:00:00', 'a', 1, 1, 1);
SQL SUCCESS
INSERT INTO sales VALUES(1, 12, '2019-02-01 00:00:00', 'b', 1, 1, 1);
SQL SUCCESS
select pg_stat_get_dead_tuples(a.oid) from PG_CLASS a where a.relname = 'sales';
+-------------------------+
| pg_stat_get_dead_tuples |
+-------------------------+
| 0 |
+-------------------------+
-- 更新一行数据,旧的其实还在,只是指针指向了新数据,因此dead记录1
update sales set time_id = '2019-06-02 10:00:00' where channel_id = 'b';
SQL SUCCESS
SELECT pg_sleep(10);
+----------+
| pg_sleep |
+----------+
| |
+----------+
select pg_stat_get_dead_tuples(a.oid) from PG_CLASS a where a.relname = 'sales';
+-------------------------+
| pg_stat_get_dead_tuples |
+-------------------------+
| 1 |
+-------------------------+
-- 再添加数据 1
INSERT INTO sales VALUES(1, 12, '2019-02-05 00:00:00', 'c', 1, 1, 1);
SQL SUCCESS
INSERT INTO sales VALUES(1, 12, '2019-02-03 00:00:00', 'd', 1, 1, 1);
SQL SUCCESS
INSERT INTO sales VALUES(1, 12, '2019-02-05 00:00:00', 'e', 1, 1, 1);
SQL SUCCESS
SELECT pg_sleep(10);
+----------+
| pg_sleep |
+----------+
| |
+----------+
select pg_stat_get_dead_tuples(a.oid) from PG_CLASS a where a.relname = 'sales';
+-------------------------+
| pg_stat_get_dead_tuples |
+-------------------------+
| 1 |
+-------------------------+
-- 更新多行 4
update sales set time_id = '2015-06-02 10:00:00' where channel_id = 'c';
SQL SUCCESS
update sales set time_id = '2013-06-02 10:00:00' where channel_id = 'd';
SQL SUCCESS
update sales set time_id = '2012-06-02 10:00:00' where channel_id = 'e';
SQL SUCCESS
SELECT pg_sleep(10);
+----------+
| pg_sleep |
+----------+
| |
+----------+
select pg_stat_get_dead_tuples(a.oid) from PG_CLASS a where a.relname = 'sales';
+-------------------------+
| pg_stat_get_dead_tuples |
+-------------------------+
| 4 |
+-------------------------+
-- 删除一行 5
delete from sales where channel_id = 'b';
SQL SUCCESS
SELECT pg_sleep(10);
+----------+
| pg_sleep |
+----------+
| |
+----------+
select pg_stat_get_dead_tuples(a.oid) from PG_CLASS a where a.relname = 'sales';
+-------------------------+
| pg_stat_get_dead_tuples |
+-------------------------+
| 4 | -------此处统计结果有误
+-------------------------+
-- 删除多行 8
delete from sales where channel_id = 'c';
SQL SUCCESS
delete from sales where channel_id = 'd';
SQL SUCCESS
delete from sales where channel_id = 'e';
SQL SUCCESS
SELECT pg_sleep(10);
+----------+
| pg_sleep |
+----------+
| |
+----------+
select pg_stat_get_dead_tuples(a.oid) from PG_CLASS a where a.relname = 'sales';
+-------------------------+
| pg_stat_get_dead_tuples |
+-------------------------+
| 8 |
+-------------------------+
drop table sales cascade;
SQL SUCCESS
alter system set autovacuum to on;
SQL SUCCESS
【原因分析】: 暂无
【日志信息】(请附上日志文件、截图、coredump信息):

Comments (4)

Hey @zhanghuan96 , Welcome to openGauss Community.
All of the projects in openGauss Community are maintained by @opengauss-bot .
That means the developers can comment below every pull request or issue to trigger Bot Commands.
Please follow instructions at https://gitee.com/opengauss/community/blob/master/contributors/command.en.md to find the details.

zhanghuan96 created缺陷
zhanghuan96 set related repository to openGauss/openGauss-server
zhanghuan96 set priority to Secondary
zhanghuan96 set assignee to gentle_hu
zhanghuan96 changed description
zhanghuan96 changed title
zhanghuan96 changed description
zhanghuan96 changed issue state from 待办的 to 已取消
zhanghuan96 changed issue state from 已取消 to 待办的
zhangxubo added
 
sig/sqlengine
label
zhanghuan96 set branch to 2.1.0
Expand operation logs

数据库版本:

openGauss=# select version();
version | (openGauss 2.1.0 build 590b0f8e) compiled at 2021-10-16 01:38:18 commit 0 last mr  debug on x86_64-unknown-linux-gnu, compiled by g++ (GCC) 7.3.0, 64-bit

集群信息:
dusx 21085 85.1 9.8 3138720 785484 pts/1 Sl 07:49 17:10 /home/dusx/openGauss-server/dest/bin/gaussdb -D /home/dusx/openGauss-server/data/master -M primary dusx 21162 4.1 8.2 2675784 662160 pts/1 Sl 07:50 0:48 /home/dusx/openGauss-server/dest/bin/gaussdb -D /home/dusx/openGauss-server/data/slave -M standby

统计语句:

select pg_stat_get_dead_tuples(a.oid) from PG_CLASS a where a.relname = 'sales';

未复现此问题

zhanghuan96 changed assignee from gentle_hu to pengjiong
zhanghuan96 changed description
zhanghuan96 changed assignee from pengjiong to gentle_hu

非问题,建议修改用例中的sleep(10)为sleep(6);sleep(6);
pgstat系列的统计原理为backend线程将自身业务产生的数据发送给collect线程,collect线程异步进行整理归档,而backend线程发送时机为执行sql前,将前一条sql的数据发送,但是如果距离上一次发送时间间隔太短的话不会发送。

因此以update为例,用例中update、sleep、select、update、sleep、select的顺序。对应的数据生产和发送时机也分别是 update(data1)、sleep、(send1) select、(时间不够不发) update(data2)、(时间不够不发) sleep、(send2)select
可以看到对于data2的发送一定是在select执行前。在这之后,一边collect线程收到data2并整理归档,另一边backend线程开始执行select进行查询,两者同时进行,能不能查到就看谁比较快了。之前用例可以过是因为以前collect业务比较轻,速度明显大于select。
改成连续两个sleep,第一个sleep满足发送间隔,第二个sleep发送且正好可以等待collect线程归档完成,之后select可保证会是最新的。

pg_indexes_size是代码逻辑变动,对于unusable的索引,不再统计其大小。另外这个用例里不用sleep。

gentle_hu changed issue state from 待办的 to 已完成

修改用例,连跑无误

zhanghuan96 changed issue state from 已完成 to 已验收

Sign in to comment

Status
Assignees
Projects
Milestones
Pull Requests
Successfully merging a pull request will close this issue.
Branches
Planed to start   -   Planed to end
-
Top level
Priority
Duration (hours)
Confirm
参与者(4)
5622128 opengauss bot 1581905080
C++
1
https://git.oschina.net/opengauss/openGauss-server.git
git@git.oschina.net:opengauss/openGauss-server.git
opengauss
openGauss-server
openGauss-server

Search