pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedAbout the relation between fragmentation of file and VACUUM
5+ messages / 5 participants
[nested] [flat]
* About the relation between fragmentation of file and VACUUM
@ 2005-12-01 05:50 Tatsumi Abe <sc-abe@milan.nri.co.jp>
0 siblings, 3 replies; 5+ messages in thread
From: Tatsumi Abe @ 2005-12-01 05:50 UTC (permalink / raw)
To: pgsql-performance
Question is about the relation between fragmentation of file and VACUUM
performance.
<Environment>
OS$B!'(BRedHat Enterprise Linux AS Release 3(Taroon Update 6)
Kernel 2.4.21-37.ELsmp on an i686
Filesystem Type ext3
Filesystem features: has_journal filetype needs_recovery sparse_super large_file
CPU$B!'(BIntel(R) Xeon(TM) CPU 2.80GHz stepping 01
Memory$B!'(B2.0GB
HDD$B!'(B80GB$B!J(BS-ATA$B!K(B
SATA max UDMA/133
PostgreSQL$B!'(B7.3.8
<DB Environment>
1. Approx. there are 3500 tables in the DB
2. Index is attached to table.
3. Every two minutes interval approx. 10,000 records are inserted into 1000tables.
So total 7,200,000 records are inserted in 1000 tables per days
4. Tables data are saved for 7 days.Older than 7 days data are deleted.
So maximum total records 50,400,000 can be exist in DB.
5. VACCUME is executed against DB as below
Six times a day i.e. every 4 hours the VACCUME ANALYZE is started once.
And out of the six times once VACCUME FULL ANALYZE is processed.
At the beginning, volume of data increases linearly because
the records are added for seven days. After seven days older than
seven days data are deleted. So volume of data will not increase
after seventh days.
When the performance of inserting data was measured in the above-
mentioned environment, it takes six minutes to write 10000 lines
after 4/5 days the measurement had begun. While searching the reason
of bottleneck by executing iostat command it is understood that DISK I/O
was problem for the neck as %iowait was almost 100% at that time.
On the very first day processing time of VACUUM is not a problem but
when the day progress its process time is increasing.Then I examined the
fragmentation of database area(pgsql/data/base) by using the following tools.
Disk Allocation Viewer
http://sourceforge.net/projects/davtools/
Fragmentation rate is 28% before defrag.
The processing time of VACUUM became 20 minutes, and also inserting data
took short time, when data base area (pgsql/data/base) was copied, deleted,
copied again, and the fragmentation was canceled (defrag).
Moreover, After the fragmentation cancelled the processing time for VACCUM
was 20 minutes, but after 7 days it took 40 minutes for processing.When again
checked the fragmentation rate with the tool it was 11%.Therefore, it is
understood that the fragmentation progresses again.
However, In my current environment I can't stop PostgreSQL and cancel
fragmentation.
Could anyone advise some solutions for this fragmentation problem
without stopping PostgreSQL ? For example, using the followings or anything
else..
-Tuning of postgresql.conf
-PostgreSQL(8.1.0) of latest version and VACUUM
Thanks in advance.
Abe
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: About the relation between fragmentation of file and
@ 2005-12-01 09:59 Richard Huxton <dev@archonet.com>
parent: Tatsumi Abe <sc-abe@milan.nri.co.jp>
2 siblings, 1 reply; 5+ messages in thread
From: Richard Huxton @ 2005-12-01 09:59 UTC (permalink / raw)
To: Tatsumi Abe <sc-abe@milan.nri.co.jp>; +Cc: pgsql-performance
Tatsumi Abe wrote:
> Question is about the relation between fragmentation of file and VACUUM
> performance.
>
> <Environment>
> OS$B!'(BRedHat Enterprise Linux AS Release 3(Taroon Update 6)
> Kernel 2.4.21-37.ELsmp on an i686
> Filesystem Type ext3
> Filesystem features: has_journal filetype needs_recovery sparse_super large_file
> CPU$B!'(BIntel(R) Xeon(TM) CPU 2.80GHz stepping 01
> Memory$B!'(B2.0GB
> HDD$B!'(B80GB$B!J(BS-ATA$B!K(B
> SATA max UDMA/133
> PostgreSQL$B!'(B7.3.8
>
> <DB Environment>
> 1. Approx. there are 3500 tables in the DB
> When the performance of inserting data was measured in the above-
> mentioned environment, it takes six minutes to write 10000 lines
> after 4/5 days the measurement had begun. While searching the reason
> of bottleneck by executing iostat command it is understood that DISK I/O
> was problem for the neck as %iowait was almost 100% at that time.
>
> On the very first day processing time of VACUUM is not a problem but
> when the day progress its process time is increasing.Then I examined the
> fragmentation of database area(pgsql/data/base) by using the following tools.
>
> Disk Allocation Viewer
> http://sourceforge.net/projects/davtools/
>
> Fragmentation rate is 28% before defrag.
I'd guess the root of your problem is the number of tables (3500), which
if each has one index represents at least 7000 files. That means a lot
of your I/O time will probably be spent moving the disk heads between
the different files.
You say you can't stop the server, so there's no point in thinking about
a quick hardware upgrade to help you. Also a version-upgrade is not
do-able for you.
I can only think of two other options:
1. Change the database schema to reduce the number of tables involved.
I'm assuming that of the 3500 tables most hold the same data but for
different clients (or something similar). This might not be practical
either.
2. Re-order how you access the database. ANALYSE the updated tables
regularly, but only VACUUM them after deletions. Group your inserts so
that all the inserts for table1 go together, then all the inserts for
table2 go together and so on. This should help with the fragmentation by
making sure the files get extended in larger chunks.
Are you sure it's not possible to spend 15 mins offline to solve this?
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: About the relation between fragmentation of file and
@ 2005-12-01 12:00 Michael Stone <mstone+postgres@mathom.us>
parent: Tatsumi Abe <sc-abe@milan.nri.co.jp>
2 siblings, 0 replies; 5+ messages in thread
From: Michael Stone @ 2005-12-01 12:00 UTC (permalink / raw)
To: pgsql-performance
On Thu, Dec 01, 2005 at 02:50:56PM +0900, Tatsumi Abe wrote:
>Could anyone advise some solutions for this fragmentation problem
>without stopping PostgreSQL ?
Stop doing VACUUM FULL so often. If your table size is constant anyway
you're just wasting time by compacting the table and shrinking it, and
encouraging fragmentation as each table file grows then shrinks a little
bit each day.
Mike Stone
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: About the relation between fragmentation of file and
@ 2005-12-01 12:11 David Lang <dlang@invendra.net>
parent: Richard Huxton <dev@archonet.com>
0 siblings, 0 replies; 5+ messages in thread
From: David Lang @ 2005-12-01 12:11 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: Tatsumi Abe <sc-abe@milan.nri.co.jp>; pgsql-performance
On Thu, 1 Dec 2005, Richard Huxton wrote:
> Tatsumi Abe wrote:
>> Question is about the relation between fragmentation of file and VACUUM
>> performance.
>>
>> <Environment>
>> OS¡§RedHat Enterprise Linux AS Release 3(Taroon Update 6)
>> Kernel 2.4.21-37.ELsmp on an i686
>> Filesystem Type ext3
>> Filesystem features: has_journal filetype needs_recovery sparse_super large_file
try different filesystems, ext2/3 do a very poor job when you have lots of
files in a directory (and 7000+ files is a lot). you can also try mounting
the filesystem with noatime, nodiratime to reduce the seeks when reading,
and try mounting it with oldalloc (which changes how the files are
arranged on disk when writing and extending them), I've seen drastic
speed differences between ext2 and ext3 based on this option (ext2
defaults to oldalloc, ext3 defaults to orlov, which is faster in many
cases)
>> CPU¡§Intel(R) Xeon(TM) CPU 2.80GHz stepping 01
>> Memory¡§2.0GB
>> HDD¡§80GB¡ÊS-ATA¡Ë
>> SATA max UDMA/133
>> PostgreSQL¡§7.3.8
>>
>> <DB Environment>
>> 1. Approx. there are 3500 tables in the DB
>
>> When the performance of inserting data was measured in the above-
>> mentioned environment, it takes six minutes to write 10000 lines
>> after 4/5 days the measurement had begun. While searching the reason
>> of bottleneck by executing iostat command it is understood that DISK I/O
>> was problem for the neck as %iowait was almost 100% at that time.
>>
>> On the very first day processing time of VACUUM is not a problem but
>> when the day progress its process time is increasing.Then I examined the
>> fragmentation of database area(pgsql/data/base) by using the following tools.
>>
>> Disk Allocation Viewer
>> http://sourceforge.net/projects/davtools/
>>
>> Fragmentation rate is 28% before defrag.
>
> I'd guess the root of your problem is the number of tables (3500), which
> if each has one index represents at least 7000 files. That means a lot
> of your I/O time will probably be spent moving the disk heads between
> the different files.
depending on the size of the tables it can actually be a lot worse then
this (remember Postgres splits the tables into fixed size chunks)
when postgres adds data it will eventually spill over into additional
files, when you do a vaccum does it re-write the tables into a smaller
number of files or just rewrite the individual files (makeing each of them
smaller, but keeping the same number of files)
speaking of this, the selection of the size of these chunks is a
comprimize between the time needed to seek in an individual file and the
number of files that are created, is there an easy way to tinker with this
(I am sure the default is not correct for all filesystems, the filesystem
handling of large and/or many files differ drasticly)
> You say you can't stop the server, so there's no point in thinking about
> a quick hardware upgrade to help you. Also a version-upgrade is not
> do-able for you.
there's a difference between stopping the server once for an upgrade
(hardware or software) and having to stop it every few days to defrag
things forever after.
David Lang
> I can only think of two other options:
> 1. Change the database schema to reduce the number of tables involved.
> I'm assuming that of the 3500 tables most hold the same data but for
> different clients (or something similar). This might not be practical
> either.
>
> 2. Re-order how you access the database. ANALYSE the updated tables
> regularly, but only VACUUM them after deletions. Group your inserts so
> that all the inserts for table1 go together, then all the inserts for
> table2 go together and so on. This should help with the fragmentation by
> making sure the files get extended in larger chunks.
>
> Are you sure it's not possible to spend 15 mins offline to solve this?
> --
> Richard Huxton
> Archonet Ltd
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>
From pgsql-performance-owner@postgresql.org Thu Dec 1 11:42:40 2005
X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
by postgresql.org (Postfix) with ESMTP id 8D5629DD693
for <pgsql-performance-postgresql.org@localhost.postgresql.org>; Thu, 1 Dec 2005 11:42:39 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
with ESMTP id 47445-02
for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
Thu, 1 Dec 2005 11:42:33 -0400 (AST)
X-Greylist: domain auto-whitelisted by SQLgrey-
Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.196])
by postgresql.org (Postfix) with ESMTP id 33AAC9DD684
for <pgsql-performance@postgresql.org>; Thu, 1 Dec 2005 11:42:35 -0400 (AST)
Received: by wproxy.gmail.com with SMTP id i23so251782wra
for <pgsql-performance@postgresql.org>; Thu, 01 Dec 2005 07:42:41 -0800 (PST)
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws;
s=beta; d=gmail.com;
h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
b=E4tCkbGCIfoLMxAWvaMP+9C0TYVeKZ+/7VvYQHsgMWOPJ2NOGn0g076GCQQc5Ze4OD+x1V+8JkkLLB+NcPcD+da+77XwoGaSPgZfo5V0rnsu0nL+5PbEpl628bG8Vvhu8jGGoc7qLq4+4NAdxnZ1kd6+9Jm+BVGX5iUBIVZsFCs=
Received: by 10.65.38.14 with SMTP id q14mr871367qbj;
Thu, 01 Dec 2005 07:42:39 -0800 (PST)
Received: by 10.65.180.14 with HTTP; Thu, 1 Dec 2005 07:42:39 -0800 (PST)
Message-ID: <c2d9e70e0512010742p5e5fb61dx84dfb8e332c05352@mail.gmail.com>
Date: Thu, 1 Dec 2005 10:42:39 -0500
From: Jaime Casanova <systemguards@gmail.com>
To: Michael Riess <mlriess@gmx.de>
Subject: Re: 15,000 tables
Cc: pgsql-performance@postgresql.org
In-Reply-To: <dmmujf$2rgd$1@news.hub.org>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
References: <dmmujf$2rgd$1@news.hub.org>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0 required=5 tests=[none]
X-Spam-Score: 0
X-Spam-Level:
X-Archive-Number: 200512/15
X-Sequence-Number: 15836
On 12/1/05, Michael Riess <mlriess@gmx.de> wrote:
> Hi,
>
> we are currently running a postgres server (upgraded to 8.1) which has
> one large database with approx. 15,000 tables. Unfortunately performance
> suffers from that, because the internal tables (especially that which
> holds the attribute info) get too large.
>
> (We NEED that many tables, please don't recommend to reduce them)
>
Have you ANALYZEd your database? VACUUMing?
BTW, are you using some kind of weird ERP? I have one that treat
informix as a fool and don't let me get all of informix potential...
maybe the same is in your case...
--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: About the relation between fragmentation of file and VACUUM
@ 2005-12-01 12:44 Bill McGonigle <bill@bfccomputing.com>
parent: Tatsumi Abe <sc-abe@milan.nri.co.jp>
2 siblings, 0 replies; 5+ messages in thread
From: Bill McGonigle @ 2005-12-01 12:44 UTC (permalink / raw)
To: Tatsumi Abe <sc-abe@milan.nri.co.jp>; +Cc: pgsql-performance
On Dec 1, 2005, at 00:50, Tatsumi Abe wrote:
> However, In my current environment I can't stop PostgreSQL and cancel
> fragmentation.
>
> Could anyone advise some solutions for this fragmentation problem
> without stopping PostgreSQL ?
This is somewhat of an aside and intended just as a helpful suggestion
since I've been in this spot before: if you have this kind of uptime
requirement the first project to work on is getting the environment to
the point where you can take out at least one database server at a time
for maintenance. You're going to be forced to do this sooner or later
- whether by disk failure, software error (Pg or OS), user error
(restore from backup) or security issues (must patch fixes).
So disk fragmentation is a great thing to worry about at some point,
but IMHO you've got your neck under the guillotine and worrying about
your cuticles.
I've heard the arguments before, usually around budget, and if the
company can't spend any money but needs blood-from-stone performance
tweaks, somebody isn't doing the math right (I'm assuming this isn't
running on a satellite). Plus, your blood pressure will go down when
things are more resilient. I've tried the superhero thing before and
it's just not worth it.
-Bill
-----
Bill McGonigle, Owner Work: 603.448.4440
BFC Computing, LLC Home: 603.448.1668
bill@bfccomputing.com Mobile: 603.252.2606
http://www.bfccomputing.com/ Pager: 603.442.1833
Jabber: flowerpt@gmail.com Text: bill+text@bfccomputing.com
Blog: http://blog.bfccomputing.com/
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2005-12-01 12:44 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-12-01 05:50 About the relation between fragmentation of file and VACUUM Tatsumi Abe <sc-abe@milan.nri.co.jp>
2005-12-01 09:59 ` Re: About the relation between fragmentation of file and Richard Huxton <dev@archonet.com>
2005-12-01 12:11 ` Re: About the relation between fragmentation of file and David Lang <dlang@invendra.net>
2005-12-01 12:00 ` Re: About the relation between fragmentation of file and Michael Stone <mstone+postgres@mathom.us>
2005-12-01 12:44 ` Bill McGonigle <bill@bfccomputing.com>
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