pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedmanaging database with thousands of tables
4+ messages / 3 participants
[nested] [flat]
* managing database with thousands of tables
@ 2006-07-05 13:07 Eugeny N Dzhurinsky <bofh@redwerk.com>
2006-07-05 13:39 ` Re: managing database with thousands of tables Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Eugeny N Dzhurinsky @ 2006-07-05 13:07 UTC (permalink / raw)
To: pgsql-performance
Hello!
I facing some strange problems with PostgreSQL 8.0 performance.
I have application which handles a lot of tasks, each task is keps in separate
table. Those tables are dropped and created again periodically (precisely -
when new task results came back from remote server). Also each table can have
hundreds of thousands records inside (but mostly they do have just few
thousands).
Sometimes I facing performance loss when working with database, and aafter I
performed vacuuming on entire database, i saw some tables and indexes in pg_*
schemas were optimized and hundreds of thousands records were deleted. Could
that be the reason of performance loss, and if so - how can I fix that?
I have pg_autovacuum up and running all the time
pg_autovacuum -d 3 -D -L /dev/null
but it seems pg_autovacuum does not do vacuuming on system tables.
--
Eugene Dzhurinsky
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: managing database with thousands of tables
2006-07-05 13:07 managing database with thousands of tables Eugeny N Dzhurinsky <bofh@redwerk.com>
@ 2006-07-05 13:39 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-07-05 15:57 ` Re: managing database with thousands of tables Eugeny N Dzhurinsky <bofh@redwerk.com>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2006-07-05 13:39 UTC (permalink / raw)
To: Eugeny N Dzhurinsky <bofh@redwerk.com>; +Cc: pgsql-performance
Eugeny N Dzhurinsky <bofh@redwerk.com> writes:
> but it seems pg_autovacuum does not do vacuuming on system tables.
There was a bug awhile back whereby autovac failed to notice temp table
cleanup at connection end --- maybe you need to update?
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: managing database with thousands of tables
2006-07-05 13:07 managing database with thousands of tables Eugeny N Dzhurinsky <bofh@redwerk.com>
2006-07-05 13:39 ` Re: managing database with thousands of tables Tom Lane <tgl@sss.pgh.pa.us>
@ 2006-07-05 15:57 ` Eugeny N Dzhurinsky <bofh@redwerk.com>
2006-07-06 00:33 ` Re: managing database with thousands of tables Chris <dmagick@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Eugeny N Dzhurinsky @ 2006-07-05 15:57 UTC (permalink / raw)
To: pgsql-performance
On Wed, Jul 05, 2006 at 09:39:31AM -0400, Tom Lane wrote:
> Eugeny N Dzhurinsky <bofh@redwerk.com> writes:
> > but it seems pg_autovacuum does not do vacuuming on system tables.
>
> There was a bug awhile back whereby autovac failed to notice temp table
> cleanup at connection end --- maybe you need to update?
May be. So should I update to newer postgres 8.1 or just upgrade
pg_autovacuum somehow (I don't know how btw ;) )?
--
Eugene N Dzhurinsky
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: managing database with thousands of tables
2006-07-05 13:07 managing database with thousands of tables Eugeny N Dzhurinsky <bofh@redwerk.com>
2006-07-05 13:39 ` Re: managing database with thousands of tables Tom Lane <tgl@sss.pgh.pa.us>
2006-07-05 15:57 ` Re: managing database with thousands of tables Eugeny N Dzhurinsky <bofh@redwerk.com>
@ 2006-07-06 00:33 ` Chris <dmagick@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Chris @ 2006-07-06 00:33 UTC (permalink / raw)
To: Eugeny N Dzhurinsky <bofh@redwerk.com>; +Cc: pgsql-performance
Eugeny N Dzhurinsky wrote:
> On Wed, Jul 05, 2006 at 09:39:31AM -0400, Tom Lane wrote:
>> Eugeny N Dzhurinsky <bofh@redwerk.com> writes:
>>> but it seems pg_autovacuum does not do vacuuming on system tables.
>> There was a bug awhile back whereby autovac failed to notice temp table
>> cleanup at connection end --- maybe you need to update?
>
> May be. So should I update to newer postgres 8.1 or just upgrade
> pg_autovacuum somehow (I don't know how btw ;) )?
Update the whole lot. You should be able to do the upgrade "in place"
but take a backup "just in case".
http://www.postgresql.org/docs/8.1/interactive/release.html
will list all changes between versions.
--
Postgresql & php tutorials
http://www.designmagick.com/
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2006-07-06 00:33 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-07-05 13:07 managing database with thousands of tables Eugeny N Dzhurinsky <bofh@redwerk.com>
2006-07-05 13:39 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-07-05 15:57 ` Eugeny N Dzhurinsky <bofh@redwerk.com>
2006-07-06 00:33 ` Chris <dmagick@gmail.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