Thread: server space increasing very fast but transaction are very low
Hi all My database server db01 is on linux environment and size of base folder increasing very fast unexpectedly(creating renamed files of 1 GB in base folder like 1667234568.10) details as below path of the table space/base file /opt/appl/pgsql82/data/base/453447624/ [postgres@wcatlsatdb01 453447624]$ ls -ltrh 1662209326* -rw------- 1 postgres postgres 1.0G Aug 27 06:32 1662209326 -rw------- 1 postgres postgres 1.0G Aug 30 02:16 1662209326.1 -rw------- 1 postgres postgres 1.0G Aug 30 17:39 1662209326.2 -rw------- 1 postgres postgres 1.0G Sep 1 05:42 1662209326.3 -rw------- 1 postgres postgres 1.0G Sep 2 09:28 1662209326.4 -rw------- 1 postgres postgres 1.0G Sep 4 10:08 1662209326.5 -rw------- 1 postgres postgres 1.0G Sep 6 02:17 1662209326.6 -rw------- 1 postgres postgres 1.0G Sep 8 03:43 1662209326.7 -rw------- 1 postgres postgres 1.0G Sep 8 08:16 1662209326.8 -rw------- 1 postgres postgres 1.0G Sep 10 10:53 1662209326.9 -rw------- 1 postgres postgres 1.0G Sep 11 04:59 1662209326.10 -rw------- 1 postgres postgres 1.0G Sep 13 05:22 1662209326.11 -rw------- 1 postgres postgres 1.0G Sep 15 01:43 1662209326.12 -rw------- 1 postgres postgres 1.0G Sep 16 08:32 1662209326.13 -rw------- 1 postgres postgres 1.0G Sep 17 14:30 1662209326.14 -rw------- 1 postgres postgres 1.0G Sep 19 07:24 1662209326.15 -rw------- 1 postgres postgres 1.0G Sep 21 02:55 1662209326.16 -rw------- 1 postgres postgres 1.0G Sep 22 09:59 1662209326.17 -rw------- 1 postgres postgres 1.0G Sep 23 06:22 1662209326.18 -rw------- 1 postgres postgres 1.0G Sep 25 08:42 1662209326.19 -rw------- 1 postgres postgres 1.0G Sep 26 04:13 1662209326.20 -rw------- 1 postgres postgres 1.0G Sep 29 03:58 1662209326.21 -rw------- 1 postgres postgres 1.0G Sep 29 08:06 1662209326.22 -rw------- 1 postgres postgres 1.0G Oct 2 02:23 1662209326.23 -rw------- 1 postgres postgres 1.0G Oct 2 06:45 1662209326.24 -rw------- 1 postgres postgres 1.0G Oct 5 03:17 1662209326.25 -rw------- 1 postgres postgres 1.0G Oct 5 14:39 1662209326.26 -rw------- 1 postgres postgres 1.0G Oct 7 08:46 1662209326.27 -rw------- 1 postgres postgres 1.0G Oct 8 18:24 1662209326.28 -rw------- 1 postgres postgres 1.0G Oct 10 08:24 1662209326.29 -rw------- 1 postgres postgres 1.0G Oct 12 03:24 1662209326.30 -rw------- 1 postgres postgres 1.0G Oct 14 02:31 1662209326.31 -rw------- 1 postgres postgres 1.0G Oct 16 02:36 1662209326.32 -rw------- 1 postgres postgres 1.0G Oct 17 02:37 1662209326.33 -rw------- 1 postgres postgres 1.0G Oct 19 02:37 1662209326.34 -rw------- 1 postgres postgres 1.0G Oct 20 04:45 1662209326.35 -rw------- 1 postgres postgres 1.0G Oct 22 02:37 1662209326.36 -rw------- 1 postgres postgres 1.0G Oct 23 02:37 1662209326.37 -rw------- 1 postgres postgres 1.0G Oct 25 02:38 1662209326.38 -rw------- 1 postgres postgres 1.0G Oct 26 02:40 1662209326.39 -rw------- 1 postgres postgres 1.0G Oct 28 02:40 1662209326.40 -rw------- 1 postgres postgres 1.0G Oct 29 02:39 1662209326.41 -rw------- 1 postgres postgres 1.0G Oct 31 05:11 1662209326.42 -rw------- 1 postgres postgres 1.0G Nov 1 02:43 1662209326.43 -rw------- 1 postgres postgres 1.0G Nov 3 03:31 1662209326.44 -rw------- 1 postgres postgres 1.0G Nov 4 03:18 1662209326.45 -rw------- 1 postgres postgres 1.0G Nov 6 02:37 1662209326.46 -rw------- 1 postgres postgres 1.0G Nov 6 02:38 1662209326.47 -rw------- 1 postgres postgres 249M Nov 6 02:39 1662209326.48 [postgres@wcatlsatdb01 453447624]$ what is significance of these files and how can i avoid it.can i delete these renamed files from base folder or any thing else. Please help with regards! Brahma Prakash Tiwari DBA iBoss Tech Solutions Sec-63 Noida ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ The contents of this email, including the attachments, are PRIVILEGED AND CONFIDENTIAL to the intended recipient at the email address to which it has been addressed. |
Get rid of Add-Ons in your email ID. Get yourname@rocketmail.com. Sign up now!
1. Don't email people directly to start a new thread (unless you have a
support contract with them of course).
2. Not much point in sending to two mailing lists simultaneously. You'll
just split your responses.
brahma tiwari wrote:
> Hi all
>
> My database server db01 is on linux environment and size of base
> folder increasing very fast unexpectedly(creating renamed files of 1
> GB in base folder like 1667234568.10) details as below
These are the files containing your tables / indexes.
When a file gets larger than 1GB the file gets split and you get .1, .2 etc
on the end)
> what is significance of these files and how can i avoid it.can i
> delete these renamed files from base folder or any thing else. Please
> help
NEVER delete any files in .../data/base.
Since these files all seem to have the same number they are all the same
object (table or index). You can see which by looking in pg_class.
You'll want to use the number 1662209326 of course.
=> SELECT relname,relpages,reltuples,relfilenode FROM pg_class WHERE
relfilenode=2336591;
relname | relpages | reltuples | relfilenode
---------+----------+-----------+-------------
outputs | 3 | 220 | 2336591
(1 row)
This is the table outputs on mine which occupies 3 pages on disk and has
about 220 rows. You can find out the reverse (size of any table by name)
with some useful functions:
select pg_size_pretty(pg_total_relation_size('my_table_name'));
I'm guessing what you've got is a table that's not being vacuumed
because you've had a transaction that's been open for weeks.
--
Richard Huxton
Archonet Ltd
Richard Huxton <dev 'at' archonet.com> writes: > I'm guessing what you've got is a table that's not being vacuumed > because you've had a transaction that's been open for weeks. Or because no vacuuming at all is performed on this table (no autovacuum and no explicit VACUUM on database or table). -- Guillaume Cottenceau