Обсуждение: [ADMIN] Strange size of pg_largeobject
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.
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
=?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
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