Обсуждение: database slowdown
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?
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 somethingto 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 startusing 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
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. 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@squeakycode.net> 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 >
In response to Mag Gam <magawake@gmail.com>: > 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@squeakycode.net> 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@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general -- Bill Moran http://www.potentialtech.com http://people.collaborativefusion.com/~wmoran/
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@potentialtech.com> wrote: > In response to Mag Gam <magawake@gmail.com>: > >> 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@squeakycode.net> 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@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-general > > > -- > Bill Moran > http://www.potentialtech.com > http://people.collaborativefusion.com/~wmoran/ >
On 18/01/2011 13:43, Mag Gam wrote: > Purge meaning, stop postgresql, rm -rf $PGDATA, recreate the > environment, and start up postgresql again. Goodness, that's fairly dramatic. What's wrong with DROP DATABASE? :-) Ray. -- Raymond O'Donnell :: Galway :: Ireland rod@iol.ie
> On Sun, Jan 16, 2011 at 12:28 PM, Andy Colson<andy@squeakycode.net> 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 >> > On 1/18/2011 7:11 AM, Mag Gam wrote: > 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. > > 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 ? > > > When you say "Database performance is bad"... are you doing mostly read or mostly write operations? Do you vacuum? Idle in transaction is probably bad. It means a process has started a transaction and not commit. For any rows that transaction touched PG must keep its row versions alive, plus any new versions of the same row. It can lead to excess memory usage, slow table reads (because not only does PG have to scan the rows in a table, it has to scan the versions of each row). It'll also block vacuum's, so you wont be reclaiming disk space and your database will just grow and grow. I realize I never touched on your actual question about file systems. I dont think with a two disk mirror you are going to stress your FS too much. I have seen benchmarks give the edge to XFS. I dont think it'll even be in the 10's of percentages better. I use XFS myself, so dont really have any experience with ext3. Its also important to have the memory usage setup well so PG can cache as much data as possible. (shared_buffers and effective_cache_size are probably most important) select * from pg_locks; it'll tell you if one process (that maybe has not commit?!) is holding a lock that other processes are waiting for. PG works best when you run small transactions, commit, and let autovacuum do its magic. -Andy