Re: [BUGS] db growing out of proportion

2003-06-12 Thread Tomas Szepe
> [EMAIL PROTECTED] > > > Peter Childs <[EMAIL PROTECTED]> writes: > > > On Fri, 30 May 2003, Tomas Szepe wrote: > > >> Trouble is, as the rows in the tables get deleted/inserted/updated > > >> (the frequency being a couple thousand rows per minute), the database > > >> is growing out of proportio

Re: [BUGS] db growing out of proportion

2003-06-01 Thread Tom Lane
Robert Creager <[EMAIL PROTECTED]> writes: >> I'm interested to narrow down exactly what was the issue here. > shared_buffers was 1024, now 8192 > max_fsm_relations was 1000, now 1 > max_fsm_pages was 2, now 10 > wal_buffers was 8, now 16 > sort_mem was 1024, now 64000 > vacuum_mem was

Re: [BUGS] db growing out of proportion

2003-05-31 Thread Robert Creager
On Sat, 31 May 2003 00:11:26 -0400 Tom Lane <[EMAIL PROTECTED]> said something like: > > Cool ... but it's not immediately obvious which of these changes did the > trick for you. What settings were you at before? And what's the > details of the problem query? > > The first three settings you me

Re: [BUGS] db growing out of proportion

2003-05-31 Thread Tom Lane
Robert Creager <[EMAIL PROTECTED]> writes: > Thanks for the hint. I just upped my shared_buffers to 8192, fsm_relations to > 1, fsm_pages to 10, sort_mem to 64000, and an UPDATE which was taking over 2 > hours dropped down to 1 to 2 minutes! Cool ... but it's not immediately obvious wh

Re: [BUGS] db growing out of proportion

2003-05-31 Thread Robert Creager
On Fri, 30 May 2003 09:11:39 -0400 Tom Lane <[EMAIL PROTECTED]> said something like: > > Also note that no amount of vacuuming will save you if the FSM is not > large enough to keep track of all the free space. The default FSM > settings, like all the other default settings in Postgres, are set

Re: [BUGS] db growing out of proportion

2003-05-31 Thread Tomas Szepe
> [EMAIL PROTECTED] > > Additionally, you may want to take a look at your query performance. Are > most of your queries doing sequential scans? In my system, the crucial > columns of the primary tables are int8 and float8 fields. I have those > indexed, and I get a serious performance boost by

Re: [BUGS] db growing out of proportion

2003-05-31 Thread Tomas Szepe
> As Tom said, you probably need higher FSM settings, but also, do you have > any long lived transactions (say from some kind of persistent connection > system) that might be preventing vacuum from removing rows? No, not at all. -- Tomas Szepe <[EMAIL PROTECTED]> ---(end

Re: [BUGS] db growing out of proportion

2003-05-31 Thread Tomas Szepe
> [EMAIL PROTECTED] > > Peter Childs <[EMAIL PROTECTED]> writes: > > On Fri, 30 May 2003, Tomas Szepe wrote: > >> Trouble is, as the rows in the tables get deleted/inserted/updated > >> (the frequency being a couple thousand rows per minute), the database > >> is growing out of proportion in size.

Re: [BUGS] db growing out of proportion

2003-05-31 Thread Stephan Szabo
On Fri, 30 May 2003, Tomas Szepe wrote: > > [EMAIL PROTECTED] > > > > > Trouble is, as the rows in the tables get deleted/inserted/updated > > > (the frequency being a couple thousand rows per minute), the database > > > is growing out of proportion in size. After about a week, I have > > > to r

Re: [BUGS] db growing out of proportion

2003-05-31 Thread Todd Nemanich
I have a database with similar performance constraints. Our best estimates put the turnover on our most active table at 350k tuples/day. The hardware is a 4x1.4GHz Xeon w/ a RAID 1 disk setup, and the DB floats around 500MB of disk space taken. Here is what we do to maintain operations: 1) Cro

Re: [BUGS] db growing out of proportion

2003-05-30 Thread Tom Lane
Peter Childs <[EMAIL PROTECTED]> writes: > On Fri, 30 May 2003, Tomas Szepe wrote: >> Trouble is, as the rows in the tables get deleted/inserted/updated >> (the frequency being a couple thousand rows per minute), the database >> is growing out of proportion in size. > Would more regular vacu

Re: [BUGS] db growing out of proportion

2003-05-30 Thread Peter Childs
On Fri, 30 May 2003, Tomas Szepe wrote: > > [EMAIL PROTECTED] > > > > > Trouble is, as the rows in the tables get deleted/inserted/updated > > > (the frequency being a couple thousand rows per minute), the database > > > is growing out of proportion in size. After about a week, I have > > > to r

Re: [BUGS] db growing out of proportion

2003-05-30 Thread Tomas Szepe
> [EMAIL PROTECTED] > > > Trouble is, as the rows in the tables get deleted/inserted/updated > > (the frequency being a couple thousand rows per minute), the database > > is growing out of proportion in size. After about a week, I have > > to redump the db by hand so as to get query times back to

Re: [BUGS] db growing out of proportion

2003-05-30 Thread Stephan Szabo
On Thu, 29 May 2003, Tomas Szepe wrote: > Hello everybody, > > I'm facing a simple yet gravely problem with postgresql 7.3.2 on x86 Linux. > My db is used to store IP accounting statistics for about 30 C's. There are > a couple truly trivial tables such as the one below: > > CREATE TABLE stats_mi

[BUGS] db growing out of proportion

2003-05-30 Thread Tomas Szepe
Hello everybody, I'm facing a simple yet gravely problem with postgresql 7.3.2 on x86 Linux. My db is used to store IP accounting statistics for about 30 C's. There are a couple truly trivial tables such as the one below: CREATE TABLE stats_min ( ip inetNOT NULL,