pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedindex file's growing big
8+ messages / 4 participants
[nested] [flat]
* index file's growing big
@ 2000-03-06 10:10 zoltan.sebestyen@netvisor.hu
0 siblings, 0 replies; 8+ messages in thread
From: zoltan.sebestyen@netvisor.hu @ 2000-03-06 10:10 UTC (permalink / raw)
To: pgsql-sql
Hi,
I wrote a web+commandline based package with PostgreSQL database backend.
My problem is that as time goes by the index file grows to a huge size
[80Mb]. If I dump and then reload it the database in question I'll get the
very same data with a quite small index file. So, there's no inconsistency
or corrupt database, but it's still annoying that I must handle manually.
What I think is tha the command-line app I wrote for populating the
database uses some wrong strategy, I mean there's no erroneous SQL phares,
just may be it uses wrong tactics that's why it grows the index file to a
quite big size.
Any ideas?
> Zoltan Sebestyen
> NETvisor Kft
> 1111 Muegyetem rkpt.9. Fsz.8.
> Budapest, Hungary.
> Fax: (+36-1) 463-4396
> Tel: (+36-1) 463-4392, 463-4393
>
> "Programming is an art, not a science,
> and not all programmers are Picassos."
>
>
<<Sebestyén Zoltán.vcf>>
Attachments:
Sebestyén Zoltán.vcf (application/octet-stream, 414B) download
^ permalink raw reply [nested|flat] 8+ messages in thread
* index file's growing big
@ 2000-03-06 11:23 zoltan.sebestyen@netvisor.hu
0 siblings, 1 reply; 8+ messages in thread
From: zoltan.sebestyen@netvisor.hu @ 2000-03-06 11:23 UTC (permalink / raw)
To: pgsql-sql
Hi,
I wrote a web+commandline based package with PostgreSQL database backend.
My problem is that as time goes by the index file grows to a huge size
[80Mb]. If I dump and then reload it the database in question I'll get the
very same data with a quite small index file. So, there's no inconsistency
or corrupt database, but it's still annoying that I must handle manually.
What I think is tha the command-line app I wrote for populating the
database uses some wrong strategy, I mean there's no erroneous SQL phares,
just may be it uses wrong tactics that's why it grows the index file to a
quite big size.
Any ideas?
> Zoltan Sebestyen
> NETvisor Kft
> 1111 Muegyetem rkpt.9. Fsz.8.
> Budapest, Hungary.
> Fax: (+36-1) 463-4396
> Tel: (+36-1) 463-4392, 463-4393
>
> "Programming is an art, not a science,
> and not all programmers are Picassos."
>
>
<<Sebestyén Zoltán.vcf>>
Attachments:
Sebestyén Zoltán.vcf (application/octet-stream, 414B) download
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [SQL] index file's growing big
@ 2000-03-06 16:24 Ross J. Reedstrom <reedstrm@wallace.ece.rice.edu>
parent: zoltan.sebestyen@netvisor.hu
0 siblings, 1 reply; 8+ messages in thread
From: Ross J. Reedstrom @ 2000-03-06 16:24 UTC (permalink / raw)
To: zoltan.sebestyen@netvisor.hu; +Cc: pgsql-sql
Zoltan -
It sounds like this is a very active table in you app, with lots of
inserts and deletes, right? Basically what's happening is that deletes
from the underlying table don't remove entries from the index file,
just marks them unused. And inserts add a new record at the end.
This monotonic growth of index files is a known problem. I assume you're
using v. 6.5.3 or earlier: I believe the current 7.0 beta will reuse
index file entries, rather than append them, although it still doesn't
'vacuum' them of unused entries, if I understand correctly.
The dump->reload solves the problem for you by rebuilding the index. So,
rather than dumping the whole table, just drop and rebuild the index in
question. This can still take a long time if it's a big table, but my
guess is that it'll work for you.
Ross
On Mon, Mar 06, 2000 at 12:23:29PM +0100, zoltan.sebestyen@netvisor.hu wrote:
> Hi,
>
> I wrote a web+commandline based package with PostgreSQL database backend.
> My problem is that as time goes by the index file grows to a huge size
> [80Mb]. If I dump and then reload it the database in question I'll get the
> very same data with a quite small index file. So, there's no inconsistency
> or corrupt database, but it's still annoying that I must handle manually.
> What I think is tha the command-line app I wrote for populating the
> database uses some wrong strategy, I mean there's no erroneous SQL phares,
> just may be it uses wrong tactics that's why it grows the index file to a
> quite big size.
>
> Any ideas?
>
> > Zoltan Sebestyen
> > NETvisor Kft
> > 1111 Muegyetem rkpt.9. Fsz.8.
> > Budapest, Hungary.
> > Fax: (+36-1) 463-4396
> > Tel: (+36-1) 463-4392, 463-4393
> >
> > "Programming is an art, not a science,
> > and not all programmers are Picassos."
> >
> >
> <<Sebestyén Zoltán.vcf>>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [SQL] index file's growing big
@ 2000-03-06 16:46 Karl Denninger <karl@Denninger.Net>
parent: Ross J. Reedstrom <reedstrm@wallace.ece.rice.edu>
0 siblings, 2 replies; 8+ messages in thread
From: Karl Denninger @ 2000-03-06 16:46 UTC (permalink / raw)
To: Ross J. Reedstrom <reedstrm@wallace.ece.rice.edu>; zoltan.sebestyen@netvisor.hu; +Cc: pgsql-sql
Uh, why not just "vacuum" the table?
You want to do this periodically anyway, as Postgres does not re-use TABLE
entries either, and the table file itself will grow without bound if you
don't do this once in a while.
Further, if the tables are large you want to use "vacuum analyze" to reorder
the indices in a fashion that makes queries more efficient.
--
--
Karl Denninger (karl@denninger.net) Web: http://childrens-justice.org
Isn't it time we started putting KIDS first? See the above URL for
a plan to do exactly that!
On Mon, Mar 06, 2000 at 10:24:33AM -0600, Ross J. Reedstrom wrote:
> Zoltan -
> It sounds like this is a very active table in you app, with lots of
> inserts and deletes, right? Basically what's happening is that deletes
> from the underlying table don't remove entries from the index file,
> just marks them unused. And inserts add a new record at the end.
>
> This monotonic growth of index files is a known problem. I assume you're
> using v. 6.5.3 or earlier: I believe the current 7.0 beta will reuse
> index file entries, rather than append them, although it still doesn't
> 'vacuum' them of unused entries, if I understand correctly.
>
> The dump->reload solves the problem for you by rebuilding the index. So,
> rather than dumping the whole table, just drop and rebuild the index in
> question. This can still take a long time if it's a big table, but my
> guess is that it'll work for you.
>
> Ross
>
>
> On Mon, Mar 06, 2000 at 12:23:29PM +0100, zoltan.sebestyen@netvisor.hu wrote:
> > Hi,
> >
> > I wrote a web+commandline based package with PostgreSQL database backend.
> > My problem is that as time goes by the index file grows to a huge size
> > [80Mb]. If I dump and then reload it the database in question I'll get the
> > very same data with a quite small index file. So, there's no inconsistency
> > or corrupt database, but it's still annoying that I must handle manually.
> > What I think is tha the command-line app I wrote for populating the
> > database uses some wrong strategy, I mean there's no erroneous SQL phares,
> > just may be it uses wrong tactics that's why it grows the index file to a
> > quite big size.
> >
> > Any ideas?
> >
> > > Zoltan Sebestyen
> > > NETvisor Kft
> > > 1111 Muegyetem rkpt.9. Fsz.8.
> > > Budapest, Hungary.
> > > Fax: (+36-1) 463-4396
> > > Tel: (+36-1) 463-4392, 463-4393
> > >
> > > "Programming is an art, not a science,
> > > and not all programmers are Picassos."
> > >
> > >
> > <<Sebestyén Zoltán.vcf>>
>
>
>
> ************
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [SQL] index file's growing big
@ 2000-03-06 18:02 Ross J. Reedstrom <reedstrm@wallace.ece.rice.edu>
parent: Karl Denninger <karl@Denninger.Net>
1 sibling, 0 replies; 8+ messages in thread
From: Ross J. Reedstrom @ 2000-03-06 18:02 UTC (permalink / raw)
To: Karl Denninger <karl@Denninger.Net>; +Cc: zoltan.sebestyen@netvisor.hu; pgsql-sql
On Mon, Mar 06, 2000 at 10:46:19AM -0600, Karl Denninger wrote:
> Uh, why not just "vacuum" the table?
>
> You want to do this periodically anyway, as Postgres does not re-use TABLE
> entries either, and the table file itself will grow without bound if you
> don't do this once in a while.
>
> Further, if the tables are large you want to use "vacuum analyze" to reorder
> the indices in a fashion that makes queries more efficient.
>
Well, with a recent version, that will help keep the index from growing as
fast, but it will still grow. That's the point of my garbled description.
However, if Zoltan is _not_ vacuuming regularly, that's a problem as well.
If he wasn't, though, I'd have expected him to complain about the size
of the table file, not the index file.
Ross
--
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St., Houston, TX 77005
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [SQL] index file's growing big
@ 2000-03-07 07:13 Tom Lane <tgl@sss.pgh.pa.us>
parent: Karl Denninger <karl@Denninger.Net>
1 sibling, 0 replies; 8+ messages in thread
From: Tom Lane @ 2000-03-07 07:13 UTC (permalink / raw)
To: Karl Denninger <karl@Denninger.Net>; +Cc: Ross J. Reedstrom <reedstrm@wallace.ece.rice.edu>; zoltan.sebestyen@netvisor.hu; pgsql-sql
Karl Denninger <karl@Denninger.Net> writes:
> Further, if the tables are large you want to use "vacuum analyze" to reorder
> the indices in a fashion that makes queries more efficient.
Just a side comment here: VACUUM ANALYZE has nothing whatever to do
with "reordering" indexes. What it does that a plain VACUUM does
not do is gather statistics about the table contents (minimum, maximum,
and most common value of each column, at present). These stats are then
used by the optimizer to make better planning choices. At least we'd
like to think they're better, Lord willin' an' the creek don't rise...
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
* RE: [SQL] index file's growing big
@ 2000-03-07 12:25 zoltan.sebestyen@netvisor.hu
0 siblings, 0 replies; 8+ messages in thread
From: zoltan.sebestyen@netvisor.hu @ 2000-03-07 12:25 UTC (permalink / raw)
To: pgsql-sql
Thanks to everybody for the replies. It's the index file file that has that
enormous size compared to other files => dump and reload would definitely
help. I'll wait for a stable PostgreSQL release, until then I'm going to
write nice scripts the handle the dump/reload issue.
Zoltan Sebestyen
^ permalink raw reply [nested|flat] 8+ messages in thread
* RE: [SQL] index file's growing big
@ 2000-03-07 16:13 zoltan.sebestyen@netvisor.hu
0 siblings, 0 replies; 8+ messages in thread
From: zoltan.sebestyen@netvisor.hu @ 2000-03-07 16:13 UTC (permalink / raw)
To: reedstrm@wallace.ece.rice.edu; +Cc: pgsql-sql
Well, you're absolutely right:) Thanks.
What I would like to know is what are those [table_name].D, [table_name].D2,
[table_name].T, [table_name].T2, [table_name].TT files?
They seem to be some dump files as if pg_dump would create them as a dump
copy when I run it to create a dump to somewhere else..
Zoltan Sebestyen
NETvisor Kft
1111 Muegyetem rkpt.9. Fsz.8.
Budapest, Hungary.
Fax: (+36-1) 463-4396
Tel: (+36-1) 463-4392, 463-4393
"Programming is an art, not a science,
and not all programmers are Picassos."
-----Original Message-----
From: Ross J. Reedstrom [mailto:reedstrm@wallace.ece.rice.edu]
Sent: Tuesday, March 07, 2000 4:22 PM
To: Sebestyén Zoltán
Subject: Re: [SQL] index file's growing big
...
Seriously, try:
drop index foo_idx;
create index foo_idx on foo_table (bar_field);
That should save you a _lot_ of time relative to a complete dump and
restore.
Ross
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2000-03-07 16:13 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-03-06 10:10 index file's growing big zoltan.sebestyen@netvisor.hu
2000-03-06 11:23 index file's growing big zoltan.sebestyen@netvisor.hu
2000-03-06 16:24 ` Ross J. Reedstrom <reedstrm@wallace.ece.rice.edu>
2000-03-06 16:46 ` Karl Denninger <karl@Denninger.Net>
2000-03-06 18:02 ` Ross J. Reedstrom <reedstrm@wallace.ece.rice.edu>
2000-03-07 07:13 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-03-07 12:25 RE: [SQL] index file's growing big zoltan.sebestyen@netvisor.hu
2000-03-07 16:13 RE: [SQL] index file's growing big zoltan.sebestyen@netvisor.hu
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