Re: [HACKERS] Reduce maximum error in tuples estimation after vacuum.
Hello, I'm very sory to take your time on this mess. ul 2013 16:06:11 +0530, Amit Kapila amit.kap...@huawei.com wrote in 014201ce7bc6$f71eb950$e55c2bf0$@kap...@huawei.com I understood your patch's algorithm, but still I have doubt in my mind that if the next analyze can correct the estimates, Why would that be not sufficient. Please refer my last mail for analysis of same http://www.postgresql.org/message-id/000601ce77ad$7d3388e0$779a9aa0$@kapila@ huawei.com Hmm. I've reconfirmed what was happened on my test set. As the result, the misestimation with dead_tup = 0 which I thought to observe has turned out to be an illusion.. Tuple number estimation is working as it is expected. I withdraw this patch. regards, -- Kyotaro Horiguchi NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Reduce maximum error in tuples estimation after vacuum.
On Wednesday, July 03, 2013 1:21 PM Kyotaro HORIGUCHI wrote: Hello, I could see the same output with your latest script, also I could reproduce the test if I run the test with individual sql statements. One of the main point for reproducing individual test was to keep autovacuum = off. I see. Autovacuum's nap time is 60 sconds for the default settings. Your operation might help it to snipe the window between the last massive delete and the next explict vacuum in store_result().. Anyway setting autovacuum to off should aid to make clean environment fot this issue. Now I can look into it further, I have still not gone through in detail about your new approach to calculate the reltuples, but I am wondering whether there can be anyway with which estimates can be improved with different calculation in vac_estimate_reltuples(). I'll explain this in other words alghough It might be repetitious. It is tough to decide how to modify there. Currently I decided to preserve vac_estimate_reltuples as possible as it is. For that objective, I picked up old_rel_tuples as intermediate variable for the aid to 'deceive' the function. This can be different form deciding to separate this estimation function from that for analyze. As I described before, vac_estimates_reltuples has a presumption that the tuple density in skipped pages is not so different from that in whole table before vacuuming. Since the density is calculated without using any hint about the skipped pages, and it cannot tell how much tuples aganst pg_class.reltuples is already dead, the value can be far different from the true one and cannot be verified. Given that we canot use pg_stat_user_tables.n_dead_tup, reading all pages can fix it but the penalty should be intolerable. Using FSM to know the used bytes in skipped pages (which is all visible by the definition) seems to give good estimations of the tuples in the skipped pages to some extent assuming the uniformity of tuple length. Of course strong deviation in length can deceive the algorithm. Does it make sense for you? I understood your patch's algorithm, but still I have doubt in my mind that if the next analyze can correct the estimates, Why would that be not sufficient. Please refer my last mail for analysis of same http://www.postgresql.org/message-id/000601ce77ad$7d3388e0$779a9aa0$@kapila@ huawei.com Performance Data -- I have checked few cases where FSM is not updated accurately, this patch seems to give much worse results than current code. Test with Patch 1. Test given by you where tuple density is non-uniform postgres=# drop table if exists t; DROP TABLE postgres=# create table t (a int, b int, c int, d int default 0, e int default 0 , f int default 0); CREATE TABLE postgres=# insert into t (select a, (random() * 10)::int from generate_serie s((select count(*) from t) + 1, 100) a); INSERT 0 100 postgres=# update t set b = b + 1 where a (select count(*) from t) * 0.7; UPDATE 69 postgres=# vacuum t; VACUUM postgres=# delete from t where a (select count(*) from t) * 0.99; DELETE 98 postgres=# vacuum t; VACUUM postgres=# select c.relpages, s.n_live_tup, c.reltuples, (select count(*) from t ) as tuples, reltuples::float / (select count(*) from t) as ratio from pg_stat_ user_tables s, pg_class c where s.relname = 't' and c.relname = 't'; relpages | n_live_tup | reltuples | tuples | ratio --++---++-- 6370 | 13596 | 13596 | 10001 | 1.35946405359464 (1 row) 2. Test where tuple density is non-uniform and FSM updates before calculation in Vacuum are not accurate. I have created index on table to simulate this test postgres=# drop table if exists t; DROP TABLE postgres=# create table t (a int, b int, c int, d int default 0, e int default 0 , f int default 0); CREATE TABLE postgres=# create index on t(a); CREATE INDEX postgres=# insert into t (select a, (random() * 10)::int from generate_serie s((select count(*) from t) + 1, 100) a); INSERT 0 100 postgres=# update t set b = b + 1 where a (select count(*) from t) * 0.7; UPDATE 69 postgres=# vacuum t; VACUUM postgres=# delete from t where a (select count(*) from t) * 0.99; DELETE 98 postgres=# vacuum t; VACUUM postgres=# select c.relpages, s.n_live_tup, c.reltuples, (select count(*) from t ) as tuples, reltuples::float / (select count(*) from t) as ratio from pg_stat_ user_tables s, pg_class c where s.relname = 't' and c.relname = 't'; relpages | n_live_tup | reltuples | tuples | ratio --++--++-- 6370 |1001327 | 1.00133e+006 | 10001 | 100.122687731227 (1 row) Now this result in tuple estimation worse than current code. I think we need to have more tests to show that new calculation is better in all cases than
Re: [HACKERS] Reduce maximum error in tuples estimation after vacuum.
On 07/03/2013 12:51 AM, Kyotaro HORIGUCHI wrote: It is tough to decide how to modify there. Currently I decided to preserve vac_estimate_reltuples as possible as it is. For that objective, I picked up old_rel_tuples as intermediate variable for the aid to 'deceive' the function. This can be different form deciding to separate this estimation function from that for analyze. Kyotaro, do you think you'll be revising this patch in the next 2 days, or should I bounce it, and you can resubmit it for the next commitfest? -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Reduce maximum error in tuples estimation after vacuum.
Hello, I could see the same output with your latest script, also I could reproduce the test if I run the test with individual sql statements. One of the main point for reproducing individual test was to keep autovacuum = off. I see. Autovacuum's nap time is 60 sconds for the default settings. Your operation might help it to snipe the window between the last massive delete and the next explict vacuum in store_result().. Anyway setting autovacuum to off should aid to make clean environment fot this issue. Now I can look into it further, I have still not gone through in detail about your new approach to calculate the reltuples, but I am wondering whether there can be anyway with which estimates can be improved with different calculation in vac_estimate_reltuples(). I'll explain this in other words alghough It might be repetitious. It is tough to decide how to modify there. Currently I decided to preserve vac_estimate_reltuples as possible as it is. For that objective, I picked up old_rel_tuples as intermediate variable for the aid to 'deceive' the function. This can be different form deciding to separate this estimation function from that for analyze. As I described before, vac_estimates_reltuples has a presumption that the tuple density in skipped pages is not so different from that in whole table before vacuuming. Since the density is calculated without using any hint about the skipped pages, and it cannot tell how much tuples aganst pg_class.reltuples is already dead, the value can be far different from the true one and cannot be verified. Given that we canot use pg_stat_user_tables.n_dead_tup, reading all pages can fix it but the penalty should be intolerable. Using FSM to know the used bytes in skipped pages (which is all visible by the definition) seems to give good estimations of the tuples in the skipped pages to some extent assuming the uniformity of tuple length. Of course strong deviation in length can deceive the algorithm. Does it make sense for you? I might could show the numerical explanation but I'm afraind I can't do it for now. I'll be able to take time sooner... (also for reviewing..) One thing I have observed that 2nd parameter is_analyze of vac_estimate_reltuples() is currently not used. Mmm, it seems to have been useless from the beginning of the function... I cannot work on it till early next week, so others are welcome to join regards, -- Kyotaro Horiguchi NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Reduce maximum error in tuples estimation after vacuum.
On Thursday, June 27, 2013 4:58 PM Amit Kapila wrote:
On Wednesday, June 26, 2013 7:40 AM Kyotaro HORIGUCHI wrote:
I've recovered from messing up.
snip
Please let me have a bit of time to diagnose this.
I was completely messed up and walking on the wrong way. I looked
into
the vacuum for UPDATEs, not DELETE's so it's quite resonable to have
such results.
The renewed test script attached shows the verbose output of vacuum
after the deletes. I had following output from it.
# I belive this runs for you..
| INFO: t: found 98 removable, 110 nonremovable row
| versions in 6308 out of 10829 pages
On such a case of partially-scanned, lazy_scan_heap() tries to
estimate
resulting num_tuples in vac_estimate_reltuples() assuming the
uniformity of tuple density, which failes for such a a strong
imbalance
made by bulk updates.
Do you find any differences between what you will have and the
following I had?
I could see the same output with your latest script, also I could
reproduce
the test if I run the test with individual sql statements.
One of the main point for reproducing individual test was to keep
autovacuum
= off.
I checked further that why I could not reproduce the issue with
autovacuum=on.
The reason is that it starts analyzer which changes the value for reltuples
in pg_class and after that the estimated and real values become same.
Kindly refer below code:
relation_needs_vacanalyze()
{
..
anltuples = tabentry-changes_since_analyze;
..
anlthresh = (float4) anl_base_thresh + anl_scale_factor * reltuples;
..
*doanalyze = (anltuples anlthresh);
}
Test Results
--
postgres=# drop table if exists t;
DROP TABLE
postgres=# create table t (a int, b int, c int, d int default 0, e int
default 0
, f int default 0);
CREATE TABLE
postgres=# insert into t (select a, (random() * 10)::int from
generate_serie
s((select count(*) from t) + 1, 100) a);
INSERT 0 100
postgres=# update t set b = b + 1 where a (select count(*) from t) * 0.7;
UPDATE 69
postgres=# vacuum t;
VACUUM
postgres=# delete from t where a (select count(*) from t) * 0.99;
DELETE 98
postgres=# vacuum t;
VACUUM
postgres=# select c.relpages, s.n_live_tup, c.reltuples, (select count(*)
from t
) as tuples, reltuples::float / (select count(*) from t) as ratio from
pg_stat_
user_tables s, pg_class c where s.relname = 't' and c.relname = 't';
relpages | n_live_tup | reltuples | tuples | ratio
--++---++--
6370 | 417600 |417600 | 10001 | 41.7558244175582
(1 row)
Here I waited for 1 minute (sufficient time so that analyzer should get
trigger if required).
Infact if you run Analyze t, that also would have served the purpose.
postgres=# select c.relpages, s.n_live_tup, c.reltuples, (select count(*)
from t
) as tuples, reltuples::float / (select count(*) from t) as ratio from
pg_stat_
user_tables s, pg_class c where s.relname = 't' and c.relname = 't';
relpages | n_live_tup | reltuples | tuples | ratio
--++---++---
6370 | 10001 | 10001 | 10001 | 1
(1 row)
Now if subsequent analyzer run corrects the estimate, don't you think that
it is sufficient for the problem reported?
With Regards,
Amit Kapila.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Reduce maximum error in tuples estimation after vacuum.
On Wednesday, June 26, 2013 7:40 AM Kyotaro HORIGUCHI wrote: I've recovered from messing up. snip Please let me have a bit of time to diagnose this. I was completely messed up and walking on the wrong way. I looked into the vacuum for UPDATEs, not DELETE's so it's quite resonable to have such results. The renewed test script attached shows the verbose output of vacuum after the deletes. I had following output from it. # I belive this runs for you.. | INFO: t: found 98 removable, 110 nonremovable row | versions in 6308 out of 10829 pages On such a case of partially-scanned, lazy_scan_heap() tries to estimate resulting num_tuples in vac_estimate_reltuples() assuming the uniformity of tuple density, which failes for such a a strong imbalance made by bulk updates. Do you find any differences between what you will have and the following I had? I could see the same output with your latest script, also I could reproduce the test if I run the test with individual sql statements. One of the main point for reproducing individual test was to keep autovacuum = off. Now I can look into it further, I have still not gone through in detail about your new approach to calculate the reltuples, but I am wondering whether there can be anyway with which estimates can be improved with different calculation in vac_estimate_reltuples(). One thing I have observed that 2nd parameter is_analyze of vac_estimate_reltuples() is currently not used. I cannot work on it till early next week, so others are welcome to join review. With Regards, Amit Kapila. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Reduce maximum error in tuples estimation after vacuum.
On Thu, Jun 27, 2013 at 7:27 AM, Amit Kapila amit.kap...@huawei.com wrote: Now I can look into it further, I have still not gone through in detail about your new approach to calculate the reltuples, but I am wondering whether there can be anyway with which estimates can be improved with different calculation in vac_estimate_reltuples(). I think this is getting at the threshold question for this patch, which is whether it's really making things better or just moving the problems around. I mean, I have no problem accepting that the new algorithm is (1) reasonably cheap and (2) better in some cases. But if it's worse in other cases, which AFAICS hasn't been discussed, then it's no good. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Reduce maximum error in tuples estimation after vacuum.
Hello,
I have tried to reproduce the problem in different m/c's, but couldn't
reproduce it.
I have ran tests with default configuration.
I think you had reproduced it.
Output on Windows:
---
postgres=# create table t (a int, b int);
(snip)
postgres=# select n_live_tup, n_dead_tup from pg_stat_user_tables where
relname=
't';
n_live_tup | n_dead_tup
+
10001 | 98
(1 row)
Yes, this is the same for me. You should've done this instead,
postgres=# select reltuples from pg_class where relname = 't';
reltuples
---
1e+06
(1 row)
This is 100 times larger than n_live_tup, and it is this value
which used for judge the necessity of autovacuum.
autovacuum.c: 2695
| reltuples = classForm-reltuples;
| vactuples = tabentry-n_dead_tuples;
| vacthresh = (float4) vac_base_thresh + vac_scale_factor * reltuples;
| anlthresh = (float4) anl_base_thresh + anl_scale_factor * reltuples;
Although..
Output on Suse
postgres=# drop table if exists t;
create table t (a int, b int);
insert into t (select a, (random() * 10)::int from
generate_series((select count(*) from t) + 1, 100) a);
update t set b = b + 1 where a (select count(*) from t) * 0.7;
vacuum t;
delete from t where a (select count(*) from t) * 0.99;
vacuum t;
select c.relpages, s.n_live_tup, c.reltuples, (select count(*) from t) as
tuples, reltuples::float / (select count(*) from t) as ratio from
pg_stat_user_tables s, pg_class c where s.relname = 't' and c.relname =
't';DROP TABLE
postgres=# CREATE TABLE
postgres=# INSERT 0 100
postgres=# UPDATE 69
postgres=# VACUUM
postgres=# DELETE 98
postgres=# VACUUM
postgres=#
relpages | n_live_tup | reltuples | tuples | ratio
--++---++---
4425 | 10001 | 10001 | 10001 | 1
(1 row)
... Mmm.. I have following figures for the same operation.
relpages | n_live_tup | reltuples | tuples | ratio
--++---++--
4425 | 417670 |417670 | 10001 | 41.7628237176282
I condisider on this for a time..
When I tried to run vactest.sh, it gives below error:
linux:~/akapila/vacuum_nlivetup ./vactest.sh
./vactest.sh: line 11: syntax error near unexpected token `'
./vactest.sh: line 11: `psql ${dbname} -c vacuum verbose t |
egrep INFO: *\t\: found | sed -e 's/^.* versions in \([0-9]*\)
.*$/\1/''
Can you help me in reproducing the problem by letting me know if I am doing
something wrong or results of test are not predictable?
Could you let me know the pg's version you're running? And it is
appreciated if you're kindly show me the vacuum logs while
testing.
# I found a silly bug in the patch, but I put it off.
regards,
--
Kyotaro Horiguchi
NTT Open Source Software Center
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Reduce maximum error in tuples estimation after vacuum.
Sorry for lots of mistakes I've made,
I noticed that I was dancing on the 9.3dev at sometime, so I
reran on 9.4devel up to date, having the same result from the
view of inaccuracy of pg_class.reltuples after vacuuming.
Although, what differs from my old testset, vacuum reported that
it sanned the whole table pages. It cannot be the case that it
gives such a reltuples value for the case since before...
Please let me have a bit of time to diagnose this.
=
When I tried to run vactest.sh, it gives below error:
linux:~/akapila/vacuum_nlivetup ./vactest.sh
./vactest.sh: line 11: syntax error near unexpected token `'
./vactest.sh: line 11: `psql ${dbname} -c vacuum verbose t |
egrep INFO: *\t\: found | sed -e 's/^.* versions in \([0-9]*\)
.*$/\1/''
'|' I carelessly used should not be understood by real
/bin/sh. Since I might use other notations out of sh syntex, it
seems to more helpful to replace the shbang with '/bin/bash'
instead of '/bin/sh'..
In addition, the test script I brought up here discards all
outputs of sql commands. The attached script shows the verbose
response from vacuum.
- replaced shbang with /bin/bash
- replaced '|' notation with '21 |'
- removed ' /dev/null' from psql commandline
Can you help me in reproducing the problem by letting me know
if I am doing something wrong or results of test are not
predictable?
Thank you for getting involved and sorry for the insufficient
preparation. But please wait for a while to go on.
I ran the attached revised script for the distcleaned current
head on the master branch (9.4devel) on CentOS 6.4 and got the
same result as previous, shown below. But I found the result
ununderstandable. I'd like to have a bit time to diagnose this.
regards,
--
Kyotaro Horiguchi
NTT Open Source Software Center
===
$ ./vactest.sh
test1 ratio = 0.4
DROP TABLE
CREATE TABLE
INSERT 0 100
UPDATE 69
psql:stdin:5: INFO: vacuuming public.t
psql:stdin:5: INFO: t: removed 69 row versions in 4459 pages
psql:stdin:5: INFO: t: found 69 removable, 100 nonremovable row
versions in 10829 out of 10829 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.13u sec elapsed 0.13 sec.
VACUUM
DELETE 39
test1 ratio = 0.99
DROP TABLE
CREATE TABLE
INSERT 0 100
UPDATE 69
psql:stdin:5: INFO: vacuuming public.t
psql:stdin:5: INFO: t: removed 69 row versions in 4459 pages
psql:stdin:5: INFO: t: found 69 removable, 100 nonremovable row
versions in 10829 out of 10829 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.15u sec elapsed 0.25 sec.
VACUUM
DELETE 98
test1 ratio = 1.00
DROP TABLE
CREATE TABLE
INSERT 0 100
UPDATE 69
psql:stdin:5: INFO: vacuuming public.t
psql:stdin:5: INFO: t: removed 69 row versions in 4459 pages
psql:stdin:5: INFO: t: found 69 removable, 100 nonremovable row
versions in 10829 out of 10829 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.14u sec elapsed 0.19 sec.
VACUUM
DELETE 99
# | del% | ## | pages | n_live_tup | tups est | tups real | est/real | bufs
---+--++---++--+---++--
1 | 0.4 | 1 | 10829 | 61 | 764808 |61 | 1.275 | 2549
1 | 0.99 | 1 | 6370 | 10001 | 417600 | 10001 | 41.756 | 6308
1 |1 | 1 | 6370 | 1 | 411673 | 1 | 411673.000 | 6371
(3 rows)
#! /bin/bash
dbname=postgres
function insert_result() {
psql ${dbname} -f - /dev/null EOF
insert into result select $1, $2, $3, c.relpages, s.n_live_tup, c.reltuples,
(select count(*) from t), reltuples::float / (select count(*) from t) from
pg_stat_user_tables s, pg_class c where s.relname = 't' and c.relname = 't';
EOF
}
function vac_with_bufs() {
psql ${dbname} -c vacuum verbose t 21 | egrep INFO: *\t\: found
| sed -e 's/^.* versions in \([0-9]*\) .*$/\1/'
}
function update_result_bufs() {
local test_no=$1
local delratio=$2
local vac_no=$3
local bufs=$4
psql ${dbname} -c update result set bufs=${bufs} where \#\=$test_no
and \del%\=$delratio and \##\=$vac_no /dev/null
}
function store_result() {
local test_no=$1
local delratio=$2
scanned_bufs=`vac_with_bufs`
insert_result $test_no $delratio 1
update_result_bufs $test_no $delratio 1 $scanned_bufs
# scanned_bufs=`vac_with_bufs`
# insert_result $test_no $delratio 2
# update_result_bufs $test_no $delratio 2 $scanned_bufs
}
function test1() {
local delratio=$1
echo test1 ratio = $delratio
psql ${dbname} -f - EOF
drop table if exists t;
create table t (a int, b int, c int, d int default 0, e int default 0, f int
default 0);
Re: [HACKERS] Reduce maximum error in tuples estimation after vacuum.
I've recovered from messing up.
snip
Please let me have a bit of time to diagnose this.
I was completely messed up and walking on the wrong way. I looked
into the vacuum for UPDATEs, not DELETE's so it's quite resonable
to have such results.
The renewed test script attached shows the verbose output of
vacuum after the deletes. I had following output from it.
# I belive this runs for you..
| INFO: t: found 98 removable, 110 nonremovable row
| versions in 6308 out of 10829 pages
On such a case of partially-scanned, lazy_scan_heap() tries to
estimate resulting num_tuples in vac_estimate_reltuples()
assuming the uniformity of tuple density, which failes for such a
a strong imbalance made by bulk updates.
Do you find any differences between what you will have and the
following I had?
| $ ./vactest.sh
| ### test1 ratio = 0.4
| INFO: vacuuming public.t
| INFO: t: removed 39 row versions in 2549 pages
| INFO: t: found 39 removable, 194 nonremovable row versions in 2549 out
of 10829 pages
| DETAIL: 0 dead row versions cannot be removed yet.
| There were 0 unused item pointers.
| 0 pages are entirely empty.
| CPU 0.00s/0.04u sec elapsed 0.04 sec.
| ### test1 ratio = 0.99
| INFO: vacuuming public.t
| INFO: t: removed 98 row versions in 6308 pages
| INFO: t: found 98 removable, 110 nonremovable row versions in 6308 out
of 10829 pages
| DETAIL: 0 dead row versions cannot be removed yet.
| There were 93 unused item pointers.
| 0 pages are entirely empty.
| CPU 0.00s/0.11u sec elapsed 0.24 sec.
| INFO: t: truncated 10829 to 6370 pages
| DETAIL: CPU 0.00s/0.00u sec elapsed 0.01 sec.
| ### test1 ratio = 1.00
| INFO: vacuuming public.t
| INFO: t: removed 99 row versions in 6371 pages
| INFO: t: found 99 removable, 1 nonremovable row versions in 6371 out
of 10829 pages
| DETAIL: 0 dead row versions cannot be removed yet.
| There were 93 unused item pointers.
| 0 pages are entirely empty.
| CPU 0.00s/0.11u sec elapsed 0.20 sec.
| INFO: t: truncated 10829 to 6370 pages
| DETAIL: CPU 0.01s/0.00u sec elapsed 0.27 sec.
| # | del% | ## | pages | n_live_tup | tups est | tups real | est/real |
bufs
|
---+--++---++--+---++--
| 1 | 0.4 | 1 | 10829 | 61 | 764808 |61 | 1.275 | 2549
| 1 | 0.99 | 1 | 6370 | 10001 | 417600 | 10001 | 41.756 | 6308
| 1 |1 | 1 | 6370 | 411673 | 411673 | 1 | 411673.000 | 6371
| (3 rows)
regards,
--
Kyotaro Horiguchi
NTT Open Source Software Center
#! /bin/bash
dbname=postgres
function insert_result() {
psql ${dbname} -f - /dev/null EOF
insert into result select $1, $2, $3, c.relpages, s.n_live_tup, c.reltuples,
(select count(*) from t), reltuples::float / (select count(*) from t) from
pg_stat_user_tables s, pg_class c where s.relname = 't' and c.relname = 't';
EOF
}
function vac_with_bufs() {
local f=`mktemp vactest_`
psql ${dbname} -c vacuum verbose t 2$f 1/dev/null
cat $f 12
cat $f | egrep INFO: *\t\: found | sed -e 's/^.* versions in
\([0-9]*\) .*$/\1/'
rm $f
}
function update_result_bufs() {
local test_no=$1
local delratio=$2
local vac_no=$3
local bufs=$4
psql ${dbname} -c update result set bufs=${bufs} where \#\=$test_no
and \del%\=$delratio and \##\=$vac_no /dev/null
}
function store_result() {
local test_no=$1
local delratio=$2
scanned_bufs=`vac_with_bufs`
insert_result $test_no $delratio 1
update_result_bufs $test_no $delratio 1 $scanned_bufs
# scanned_bufs=`vac_with_bufs`
# insert_result $test_no $delratio 2
# update_result_bufs $test_no $delratio 2 $scanned_bufs
}
function test1() {
local delratio=$1
echo ### test1 ratio = $delratio
psql ${dbname} -f - /dev/null EOF
drop table if exists t;
create table t (a int, b int, c int, d int default 0, e int default 0, f int
default 0);
insert into t (select a, (random() * 10)::int from generate_series((select
count(*) from t) + 1, $nrows) a);
update t set b = b + 1 where a (select count(*) from t) * 0.7;
vacuum t;
delete from t where a (select count(*) from t) * $delratio
EOF
store_result 1 $delratio
}
function test2() {
local delratio=$1
echo ### test2 ratio = $delratio
psql ${dbname} -f - /dev/null EOF
drop table if exists t;
create table t (a int, b text);
insert into t (select a, 'abcdefg' from generate_series((select count(*) from
t) + 1, $nrows) a);
update t set b = repeat('abcdefghij', 250) where a (select count(*) from t)
* 0.7;
vacuum t;
delete from t where a (select count(*) from t) * $delratio;
EOF
store_result 2 $delratio
}
psql ${dbname} -f - /dev/null EOF
drop table if exists result;
create table result (# int, del% float, ## int, pages int, n_live_tup
int, tups est int, tups real int, est/real numeric(10, 3),
Re: [HACKERS] Reduce maximum error in tuples estimation after vacuum.
On Tuesday, June 25, 2013 2:06 PM Kyotaro HORIGUCHI wrote:
Hello,
I have tried to reproduce the problem in different m/c's, but
couldn't
reproduce it.
I have ran tests with default configuration.
I think you had reproduced it.
Output on Windows:
---
postgres=# create table t (a int, b int);
(snip)
postgres=# select n_live_tup, n_dead_tup from pg_stat_user_tables
where
relname=
't';
n_live_tup | n_dead_tup
+
10001 | 98
(1 row)
Yes, this is the same for me. You should've done this instead,
postgres=# select reltuples from pg_class where relname = 't';
reltuples
---
1e+06
(1 row)
This is 100 times larger than n_live_tup, and it is this value
which used for judge the necessity of autovacuum.
autovacuum.c: 2695
| reltuples = classForm-reltuples;
| vactuples = tabentry-n_dead_tuples;
| vacthresh = (float4) vac_base_thresh + vac_scale_factor *
reltuples;
| anlthresh = (float4) anl_base_thresh + anl_scale_factor *
reltuples;
Although..
Output on Suse
postgres=# drop table if exists t;
create table t (a int, b int);
insert into t (select a, (random() * 10)::int from
generate_series((select count(*) from t) + 1, 100) a);
update t set b = b + 1 where a (select count(*) from t) * 0.7;
vacuum t;
delete from t where a (select count(*) from t) * 0.99;
vacuum t;
select c.relpages, s.n_live_tup, c.reltuples, (select count(*) from
t) as
tuples, reltuples::float / (select count(*) from t) as ratio from
pg_stat_user_tables s, pg_class c where s.relname = 't' and c.relname
=
't';DROP TABLE
postgres=# CREATE TABLE
postgres=# INSERT 0 100
postgres=# UPDATE 69
postgres=# VACUUM
postgres=# DELETE 98
postgres=# VACUUM
postgres=#
relpages | n_live_tup | reltuples | tuples | ratio
--++---++---
4425 | 10001 | 10001 | 10001 | 1
(1 row)
... Mmm.. I have following figures for the same operation.
relpages | n_live_tup | reltuples | tuples | ratio
--++---++--
4425 | 417670 |417670 | 10001 | 41.7628237176282
I condisider on this for a time..
When I tried to run vactest.sh, it gives below error:
linux:~/akapila/vacuum_nlivetup ./vactest.sh
./vactest.sh: line 11: syntax error near unexpected token `'
./vactest.sh: line 11: `psql ${dbname} -c vacuum verbose t
|
egrep INFO: *\t\: found | sed -e 's/^.* versions in \([0-9]*\)
.*$/\1/''
Can you help me in reproducing the problem by letting me know if I am
doing
something wrong or results of test are not predictable?
Could you let me know the pg's version you're running?
I had used 9.4 Head to run above tests.
Sorry, yesterday I was busy with some other work so could not got time to
check and work on this issue further.
I shall try to work on it today.
And it is
appreciated if you're kindly show me the vacuum logs while
testing.
# I found a silly bug in the patch, but I put it off.
regards,
--
Kyotaro Horiguchi
NTT Open Source Software Center
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Reduce maximum error in tuples estimation after vacuum.
On Friday, June 14, 2013 2:05 PM Kyotaro HORIGUCHI wrote:
Hello,
Postgresql estimates the number of live tuples after the vacuum has
left some buffers unscanned. This estimation does well for most cases,
but makes completely different result with a strong imbalance of tuple
density.
For example,
create table t (a int, b int);
insert into t (select a, (random() * 10)::int from
generate_series((select count(*) from t) + 1, 100) a); update t set
b = b + 1 where a (select count(*) from t) * 0.7; vacuum t; delete
from t where a (select count(*) from t) * 0.99;
After this, pg_stat_user_tables.n_live_tup shows 417670 which is
41 times larger than the real number of rows 11.
Number should be 10001 not 11.
And what makes it
worse, autovacuum nor autoanalyze won't run until n_dead_tup goes above
8 times larger than the real number of tuples in the table for the
default settings..
| postgres=# select n_live_tup, n_dead_tup
|from pg_stat_user_tables where relname='t'; n_live_tup |
| n_dead_tup
| +
| 417670 | 0
|
| postgres=# select reltuples from pg_class where relname='t';
| reltuples
| ---
| 417670
|
| postgres=# select count(*) from t;
| count
| ---
| 10001
I have tried to reproduce the problem in different m/c's, but couldn't
reproduce it.
I have ran tests with default configuration.
Output on Windows:
---
postgres=# create table t (a int, b int);
CREATE TABLE
postgres=# insert into t (select a, (random() * 10)::int from
generate_serie
s((select count(*) from t) + 1, 100) a);
INSERT 0 100
postgres=# update t set b = b + 1 where a (select count(*) from t) * 0.7;
UPDATE 69
postgres=# vacuum t;
VACUUM
postgres=# delete from t where a (select count(*) from t) * 0.99;
DELETE 98
postgres=#
postgres=# select n_live_tup, n_dead_tup from pg_stat_user_tables where
relname=
't';
n_live_tup | n_dead_tup
+
10001 | 98
(1 row)
Output on Suse
postgres=# drop table if exists t;
create table t (a int, b int);
insert into t (select a, (random() * 10)::int from
generate_series((select count(*) from t) + 1, 100) a);
update t set b = b + 1 where a (select count(*) from t) * 0.7;
vacuum t;
delete from t where a (select count(*) from t) * 0.99;
vacuum t;
select c.relpages, s.n_live_tup, c.reltuples, (select count(*) from t) as
tuples, reltuples::float / (select count(*) from t) as ratio from
pg_stat_user_tables s, pg_class c where s.relname = 't' and c.relname =
't';DROP TABLE
postgres=# CREATE TABLE
postgres=# INSERT 0 100
postgres=# UPDATE 69
postgres=# VACUUM
postgres=# DELETE 98
postgres=# VACUUM
postgres=#
relpages | n_live_tup | reltuples | tuples | ratio
--++---++---
4425 | 10001 | 10001 | 10001 | 1
(1 row)
When I tried to run vactest.sh, it gives below error:
linux:~/akapila/vacuum_nlivetup ./vactest.sh
./vactest.sh: line 11: syntax error near unexpected token `'
./vactest.sh: line 11: `psql ${dbname} -c vacuum verbose t |
egrep INFO: *\t\: found | sed -e 's/^.* versions in \([0-9]*\)
.*$/\1/''
Can you help me in reproducing the problem by letting me know if I am doing
something wrong or results of test are not predictable?
With Regards,
Amit Kapila.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Reduce maximum error in tuples estimation after vacuum.
Sorry, I made an mistake. Kyotaro HORIGUCHI horiguchi.kyot...@lab.ntt.co.jp: Overall algorithm could be illistrated as below, - summing up used bytes, max offnum(PageGetMaxOffsetNumber), Not max offnum, the number of linp's used after page vacuum. maximum free bytes for tuple data , and free bytes after page vacuum through all scanned pages. - summing up free bytes informed by FSM through all skipped pages. - Calculate mean tuple length from the overall used bytes and sum of max offnums, and scanned pages. Here also is the same. not sum of max offnum but total of used entrre(linp)s. - Guess tuple density in skipped pages using overall free bytes from FSM and the mean tuple length calculated above. - Finally, feed estimated number of the live tuples BEFORE vacuum into vac_estimate_reltuples. regards, -- Kyotaro Horiguchi
