pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedserver space increasing very fast but transaction are very low
4+ messages / 4 participants
[nested] [flat]
* server space increasing very fast but transaction are very low
@ 2008-11-06 10:45 brahma tiwari <prakashr2n5@yahoo.com>
2008-11-06 10:57 ` Re: server space increasing very fast but transaction are very low Tino Schwarze <postgresql@tisc.de>
2008-11-06 11:04 ` Re: server space increasing very fast but transaction are very low Richard Huxton <dev@archonet.com>
0 siblings, 2 replies; 4+ messages in thread
From: brahma tiwari @ 2008-11-06 10:45 UTC (permalink / raw)
To: tgl@sss.pgh.pa.us; Kevin.Grittner@wicourts.gov; amitjain.bit@gmail.com; +Cc: pgsql-performance; heikki@enterprisedb.com; pgsql-admin@postgresql.org
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! http://in.promos.yahoo.com/address
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: server space increasing very fast but transaction are very low
2008-11-06 10:45 server space increasing very fast but transaction are very low brahma tiwari <prakashr2n5@yahoo.com>
@ 2008-11-06 10:57 ` Tino Schwarze <postgresql@tisc.de>
1 sibling, 0 replies; 4+ messages in thread
From: Tino Schwarze @ 2008-11-06 10:57 UTC (permalink / raw)
To: pgsql-admin@postgresql.org
Hi,
On Thu, Nov 06, 2008 at 04:15:02PM +0530, brahma tiwari wrote:
> 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)
This sounds like your max_fsm_pages setting is too low, so the server
cannot track free pages any more
Bump it up (there used to be a rule of thumb, IIRC it was 65536/1 GB of
data/disk space - please correct me, if I'm wrong).
IIRC, you need to restart PostgresSQL after altering that setting. A
VACUUM is prbably a good thing as well, maybe even VACUUM FULL. Do not
delete anything!
HTH,
Tino.
--
"What we nourish flourishes." - "Was wir nähren erblüht."
www.lichtkreis-chemnitz.de
www.craniosacralzentrum.de
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: server space increasing very fast but transaction are very low
2008-11-06 10:45 server space increasing very fast but transaction are very low brahma tiwari <prakashr2n5@yahoo.com>
@ 2008-11-06 11:04 ` Richard Huxton <dev@archonet.com>
2008-11-06 11:57 ` Re: server space increasing very fast but transaction are very low Guillaume Cottenceau <gc@mnc.ch>
1 sibling, 1 reply; 4+ messages in thread
From: Richard Huxton @ 2008-11-06 11:04 UTC (permalink / raw)
To: prakashr2n5@yahoo.com; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: server space increasing very fast but transaction are very low
2008-11-06 10:45 server space increasing very fast but transaction are very low brahma tiwari <prakashr2n5@yahoo.com>
2008-11-06 11:04 ` Re: server space increasing very fast but transaction are very low Richard Huxton <dev@archonet.com>
@ 2008-11-06 11:57 ` Guillaume Cottenceau <gc@mnc.ch>
0 siblings, 0 replies; 4+ messages in thread
From: Guillaume Cottenceau @ 2008-11-06 11:57 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2008-11-06 11:57 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-11-06 10:45 server space increasing very fast but transaction are very low brahma tiwari <prakashr2n5@yahoo.com>
2008-11-06 10:57 ` Tino Schwarze <postgresql@tisc.de>
2008-11-06 11:04 ` Richard Huxton <dev@archonet.com>
2008-11-06 11:57 ` Guillaume Cottenceau <gc@mnc.ch>
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