Purge meaning, stop postgresql, rm -rf $PGDATA, recreate the environment, and start up postgresql again. On Tue, Jan 18, 2011 at 8:32 AM, Bill Moran <wmoran@xxxxxxxxxxxxxxxxx> wrote: > In response to Mag Gam <magawake@xxxxxxxxx>: > >> Hi Andy, >> >> No, I don't shrink the database. I simply purge the whole thing and >> then let it populate again. The data isn't too critical. > > What does "purge" mean? ÂAre you doing an SQL DELETE, or a TRUNCATE, > or dropping the DB and recreating? > > Each of these functions differently behind the scenes, in particular > DELETE will not actually free up the space used by the records you > deleted right away, and depending on many other factors, may be the > cause of your problems. > >> >> The disks I have are internal SAS disks. I get around 150MB/sec write >> and 250MB/sec read. Its a RAID1 . >> >> ps does show idle in transactions. I've never checked pg_locks. How >> would I do that ? >> >> >> >> On Sun, Jan 16, 2011 at 12:28 PM, Andy Colson <andy@xxxxxxxxxxxxxxx> wrote: >> > On 01/16/2011 10:44 AM, Mag Gam wrote: >> >> >> >> I am running Redhat 5.2 Linux with Postgresql 8.4.4; >> >> >> >> When my disk space is 90% free the database performance is very good. >> >> However, when it reaches close to 20% free the database performance is >> >> bad. I know its bad because I see a lot of 'D' next to 'postgresql' >> >> process when running top and I see a lot 'WAITING' in the 'ps' tree. I >> >> was wondering if there are any filesystem tweaks I can do to make >> >> postgresql run smoother. I am currently using ext3. Should I consider >> >> using a different file system for my database? >> >> >> > >> > I doubt its a disk space problem. ÂSounds like a cache/disk io problem. >> > ÂWhen you are using 80% space, do you do something to get it back to 90% >> > free (ie shrink the db)? >> > >> > When the db is small, it fits in ram, and read's will be very quick. ÂOnce >> > your db gets bigger than cache it'll have to start using a lot more disk io. >> > >> > What kind of disk IO throughput do yo have? ÂWhat kind of disk system is >> > this? (raid, scsi, etc) >> > Does ps ever show 'idle in transaction'? >> > Have you checked pg_locks to see what you are waiting on? >> > Have you watched vmstat while its at 80% full vs when its at 80% free? >> > Â(does the iowait go up?) >> > >> > >> > -Andy >> > >> >> -- >> Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-general > > > -- > Bill Moran > http://www.potentialtech.com > http://people.collaborativefusion.com/~wmoran/ > -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
