pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedStrange size of pg_largeobject
3+ messages / 2 participants
[nested] [flat]
* Strange size of pg_largeobject
@ 2017-07-20 15:59 Янченко Владимир <vyanchenko@naumen.ru>
0 siblings, 1 reply; 3+ messages in thread
From: Янченко Владимир @ 2017-07-20 15:59 UTC (permalink / raw)
To: pgsql-admin
Hi all,
I found that my pg_largeobject table is bigger than it should be:
*Size:*
mybase=# SELECT pg_size_pretty(pg_total_relation_size('pg_largeobject'));
18 GB
mybase=# SELECT
pg_size_pretty(pg_total_relation_size('pg_largeobject_loid_pn_index'));
162 MB
*Rows count and large objects count:*
mybase=# select count(*) from pg_largeobject
38406
mybase=# select count(*) from pg_largeobject where pageno = 0;
778
*The largest objects:*
mybase=# select loid, count(pageno) from pg_largeobject group by loid
order by count(pageno) limit 5 desc;
loid | count
-----------+-------
406485914 | 2188
167710689 | 1114
412765517 | 996
307045064 | 842
167714441 | 645
I write the largest lo to the file system and check their sizes:
select lo_export(167710689, '/tmp/1');
select lo_export(406485914, '/tmp/2');
ls -lh /tmp/
-rw-r--r-- 1 postgres postgres *4,3M* июл 20 15:22 123
-rw-r--r-- 1 postgres postgres *2,2M* июл 20 15:23 1234
therefore, maximum size of my table should be 2.2*777 + 4.3 = 1713.7 mb
(size2*[large objects count] + size1).
Before I execute queries, that i mentioned above, I run vacuum full. But
it does not effect. Now table bloating factor about 20%.
Please, help me, if you have any suggestions about my problem.
--
Best regards,
Vladimir Yanchenko
Tech support engineer
Naumen
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Strange size of pg_largeobject
@ 2017-07-20 16:50 Tom Lane <tgl@sss.pgh.pa.us>
parent: Янченко Владимир <vyanchenko@naumen.ru>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2017-07-20 16:50 UTC (permalink / raw)
To: Янченко Владимир <vyanchenko@naumen.ru>; +Cc: pgsql-admin
=?UTF-8?B?0K/QvdGH0LXQvdC60L4g0JLQu9Cw0LTQuNC80LjRgA==?= <vyanchenko@naumen.ru> writes:
> I found that my pg_largeobject table is bigger than it should be:
Sounds like a fairly routine case of table bloat.
> Before I execute queries, that i mentioned above, I run vacuum full. But
> it does not effect. Now table bloating factor about 20%.
"vacuum verbose pg_largeobject" might be informative. If, as I suspect,
it tells you there are lots of dead but unremovable rows, look around for
long-lived open transactions or prepared transactions.
regards, tom lane
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Strange size of pg_largeobject
@ 2017-07-21 08:27 Янченко Владимир <vyanchenko@naumen.ru>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 3+ messages in thread
From: Янченко Владимир @ 2017-07-21 08:27 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
Tom, thank you for your answer.
Today I run vacuum full again and my table size dramatically reduced:
postgres@srv-pgsql1:~$ vacuumdb -fvt pg_largeobject mybase
...
INFO: vacuuming "pg_catalog.pg_largeobject"
INFO: "pg_largeobject": found 0 removable, 624465 nonremovable row
versions in 2386876 pages
DETAILS: 586358 dead row versions cannot be removed yet.
mybase=# SELECT pg_size_pretty(pg_total_relation_size('pg_largeobject'));
pg_size_pretty
----------------
1568 MB
My problem is resolved.
20.07.2017 21:50, Tom Lane пишет:
> =?UTF-8?B?0K/QvdGH0LXQvdC60L4g0JLQu9Cw0LTQuNC80LjRgA==?= <vyanchenko@naumen.ru> writes:
>> I found that my pg_largeobject table is bigger than it should be:
> Sounds like a fairly routine case of table bloat.
>
>> Before I execute queries, that i mentioned above, I run vacuum full. But
>> it does not effect. Now table bloating factor about 20%.
> "vacuum verbose pg_largeobject" might be informative. If, as I suspect,
> it tells you there are lots of dead but unremovable rows, look around for
> long-lived open transactions or prepared transactions.
>
> regards, tom lane
--
Best regards,
Vladimir Yanchenko
Tech support engineer
Naumen
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2017-07-21 08:27 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-07-20 15:59 Strange size of pg_largeobject Янченко Владимир <vyanchenko@naumen.ru>
2017-07-20 16:50 ` Tom Lane <tgl@sss.pgh.pa.us>
2017-07-21 08:27 ` Янченко Владимир <vyanchenko@naumen.ru>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox