pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedDatabase size Vs performance degradation
23+ messages / 12 participants
[nested] [flat]
* Database size Vs performance degradation
@ 2008-07-30 12:09 Dave North <DNorth@signiant.com>
2008-07-30 12:28 ` Re: Database size Vs performance degradation Richard Huxton <dev@archonet.com>
2008-07-30 12:36 ` Re: Database size Vs performance degradation Matthew Wakeling <matthew@flymine.org>
2008-07-30 14:57 ` Re: Database size Vs performance degradation Valentin Bogdanov <valiouk@yahoo.co.uk>
2008-07-30 16:48 ` Re: Database size Vs performance degradation Greg Smith <gsmith@gregsmith.com>
0 siblings, 4 replies; 23+ messages in thread
From: Dave North @ 2008-07-30 12:09 UTC (permalink / raw)
To: pgsql-performance
Morning folks,
Long time listener, first time poster. Having an interesting
problem related to performance which I'll try and describe below and
hopefully get some enlightenment. First the environment:
Postgres 8.1.8
shared_buffers = 2000
max_fsm_pages = 400000
Redhat Enterprise 4
Running on HP DL380 w/ 4GB RAM, dual 10K HDDs in RAID 0+1
Also running on the server is a tomcat web server and other ancillaries
Now, the problem. We have an application that continually writes a
bunch of data to a few tables which is then deleted by a batch job each
night. We're adding around 80,000 rows to one table per day and
removing around 75,000 that are deemed to be "unimportant". Now, the
problem we see is that after a period of time, the database access
becomes very 'slow' and the load avg on the machine gets up around 5.
When this happens, the application using the DB basically grinds to a
halt. Checking the stats, the DB size is around 7.5GB; no tables or
indexes look to be 'bloated' (we have been using psql since 7.3 with the
classic index bloat problem) and the auto-vac has been running solidly.
We had this problem around a month ago and again yesterday. Because the
application needs reasonably high availability, we couldn't full vacuum
so what we did was a dump and load to another system. What I found here
was that after the load, the DB size was around 2.7GB - a decrease of
5GB. Re-loading this back onto the main system, and the world is good.
One observation I've made on the DB system is the disk I/O seems
dreadfully slow...we're at around 75% I/O wait sometimes and the read
rates seem quite slow (hdparm says around 2.2MB/sec - 20MB/sec for
un-cached reads). I've also observed that the OS cache seems to be
using all of the remaining memory for it's cache (around 3GB) which
seems probably the best it can do with the available memory.
Now, clearly we need to examine the need for the application to write
and remove so much data but my main question is:
Why does the size of the database with so much "un-used" space seem to
impact performance so much? If (in this case) the extra 5GB of space is
essentially "unallocated", does it factor into any of the caching or
performance metrics that the DBMS uses? And if so, would I be better
having a higher shared_buffers rather than relying so much on OS cache?
Yes, I know we need to upgrade to 8.3 but that's going to take some time
:)
Many thanks in advance.
Dave
___
Dave North
dnorth@signiant.com
Signiant - Making Media Move
Visit Signiant at: www.signiant.com <http://www.signiant.com/;
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Database size Vs performance degradation
2008-07-30 12:09 Database size Vs performance degradation Dave North <DNorth@signiant.com>
@ 2008-07-30 12:28 ` Richard Huxton <dev@archonet.com>
2008-07-30 12:41 ` Re: Database size Vs performance degradation Dave North <DNorth@signiant.com>
3 siblings, 1 reply; 23+ messages in thread
From: Richard Huxton @ 2008-07-30 12:28 UTC (permalink / raw)
To: Dave North <DNorth@signiant.com>; +Cc: pgsql-performance
Dave North wrote:
> Morning folks,
> Long time listener, first time poster.
Hi Dave
> Postgres 8.1.8
> shared_buffers = 2000
> max_fsm_pages = 400000
> Redhat Enterprise 4
> Running on HP DL380 w/ 4GB RAM, dual 10K HDDs in RAID 0+1
> Also running on the server is a tomcat web server and other ancillaries
The value of 2000 seems a bit low for shared_buffers perhaps. Oh, and
8.1.13 seems to be the latest bugfix for 8.1 too.
> Now, the problem. We have an application that continually writes a
> bunch of data to a few tables which is then deleted by a batch job each
> night. We're adding around 80,000 rows to one table per day and
> removing around 75,000 that are deemed to be "unimportant".
[snip]
> We had this problem around a month ago and again yesterday. Because the
> application needs reasonably high availability, we couldn't full vacuum
> so what we did was a dump and load to another system. What I found here
> was that after the load, the DB size was around 2.7GB - a decrease of
> 5GB. Re-loading this back onto the main system, and the world is good.
Well, that's pretty much the definition of bloat. Are you sure you're
vacuuming enough? I don't have an 8.1 to hand at the moment, but a
"vacuum verbose" in 8.2+ gives some details at the end about how many
free-space slots need to be tracked. Presumably you're not tracking
enough of them, or your vacuuming isn't actually taking place.
Check the size of your database every night. It will rise from 2.7GB,
but it should stay roughly static (apart from whatever data you add of
course). If you can keep it so that most of the working-set of your
database fits in RAM speed will stay just fine.
> Yes, I know we need to upgrade to 8.3 but that's going to take some time
> :)
I think you'll like some of the improvements, but it's probably more
important to get 8.1.13 installed soon-ish.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Database size Vs performance degradation
2008-07-30 12:09 Database size Vs performance degradation Dave North <DNorth@signiant.com>
2008-07-30 12:28 ` Re: Database size Vs performance degradation Richard Huxton <dev@archonet.com>
@ 2008-07-30 12:41 ` Dave North <DNorth@signiant.com>
2008-07-30 15:05 ` Re: Database size Vs performance degradation Tom Lane <tgl@sss.pgh.pa.us>
2008-07-30 17:18 ` Re: Database size Vs performance degradation Richard Huxton <dev@archonet.com>
0 siblings, 2 replies; 23+ messages in thread
From: Dave North @ 2008-07-30 12:41 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: pgsql-performance
-----Original Message-----
From: Richard Huxton [mailto:dev@archonet.com]
Sent: July 30, 2008 8:28 AM
To: Dave North
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Database size Vs performance degradation
Dave North wrote:
> Morning folks,
> Long time listener, first time poster.
Hi Dave
> Postgres 8.1.8
> shared_buffers = 2000
> max_fsm_pages = 400000
> Redhat Enterprise 4
> Running on HP DL380 w/ 4GB RAM, dual 10K HDDs in RAID 0+1 Also running
> on the server is a tomcat web server and other ancillaries
The value of 2000 seems a bit low for shared_buffers perhaps. Oh, and
8.1.13 seems to be the latest bugfix for 8.1 too.
DN: Yeah, I was thinking the same. I spent several hours reading info
on this list and other places and it's highly inconclusive about having
high or low shared buffs Vs letting the OS disk cache handle it.
> Now, the problem. We have an application that continually writes a
> bunch of data to a few tables which is then deleted by a batch job
> each night. We're adding around 80,000 rows to one table per day and
> removing around 75,000 that are deemed to be "unimportant".
[snip]
> We had this problem around a month ago and again yesterday. Because
> the application needs reasonably high availability, we couldn't full
> vacuum so what we did was a dump and load to another system. What I
> found here was that after the load, the DB size was around 2.7GB - a
> decrease of 5GB. Re-loading this back onto the main system, and the
world is good.
Well, that's pretty much the definition of bloat. Are you sure you're
vacuuming enough?
DN: Well, the auto-vac is kicking off pretty darn frequently...around
once every 2 minutes. However, you just made me think of the obvious -
is it actually doing anything?! The app is pretty darn write intensive
so I wonder if it's actually able to vacuum the tables?
I don't have an 8.1 to hand at the moment, but a "vacuum verbose" in
8.2+ gives some details at the end about how many free-space slots need
to be tracked. Presumably you're not tracking enough of them, or your
vacuuming isn't actually taking place.
DN: I think you've hit it. Now the next obvious problem is how to make
the vac actually vac while maintaining a running system?
Check the size of your database every night. It will rise from 2.7GB,
but it should stay roughly static (apart from whatever data you add of
course). If you can keep it so that most of the working-set of your
database fits in RAM speed will stay just fine.
DN: Yep, I'm just implementing a size tracker now to keep a track on it.
It grew from the 2.5GB to 7GB in around a month so it's pretty easy to
see big jumps I'd say. Does the auto-vac log it's results somewhere by
any chance do you know?
Fantastic post, thanks so much.
Dave
> Yes, I know we need to upgrade to 8.3 but that's going to take some
> time
> :)
I think you'll like some of the improvements, but it's probably more
important to get 8.1.13 installed soon-ish.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Database size Vs performance degradation
2008-07-30 12:09 Database size Vs performance degradation Dave North <DNorth@signiant.com>
2008-07-30 12:28 ` Re: Database size Vs performance degradation Richard Huxton <dev@archonet.com>
2008-07-30 12:41 ` Re: Database size Vs performance degradation Dave North <DNorth@signiant.com>
@ 2008-07-30 15:05 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 23+ messages in thread
From: Tom Lane @ 2008-07-30 15:05 UTC (permalink / raw)
To: Dave North <DNorth@signiant.com>; +Cc: Richard Huxton <dev@archonet.com>; pgsql-performance
"Dave North" <DNorth@signiant.com> writes:
> From: Richard Huxton [mailto:dev@archonet.com]
>> Well, that's pretty much the definition of bloat. Are you sure you're
>> vacuuming enough?
> DN: Well, the auto-vac is kicking off pretty darn frequently...around
> once every 2 minutes. However, you just made me think of the obvious -
> is it actually doing anything?! The app is pretty darn write intensive
> so I wonder if it's actually able to vacuum the tables?
IIRC, the default autovac parameters in 8.1 were pretty darn
unaggressive. You should also check for long-running transactions
that might be preventing vacuum from removing recently-dead rows.
One of the reasons for updating off 8.1 is that finding out what autovac
is really doing is hard :-(. I think it does log, but at level DEBUG2
or so, which means that the only way to find out is to accept a huge
amount of useless chatter in the postmaster log. Newer releases have
a saner logging scheme.
regards, tom lane
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Database size Vs performance degradation
2008-07-30 12:09 Database size Vs performance degradation Dave North <DNorth@signiant.com>
2008-07-30 12:28 ` Re: Database size Vs performance degradation Richard Huxton <dev@archonet.com>
2008-07-30 12:41 ` Re: Database size Vs performance degradation Dave North <DNorth@signiant.com>
@ 2008-07-30 17:18 ` Richard Huxton <dev@archonet.com>
1 sibling, 0 replies; 23+ messages in thread
From: Richard Huxton @ 2008-07-30 17:18 UTC (permalink / raw)
To: Dave North <DNorth@signiant.com>; +Cc: pgsql-performance
Dave North wrote:
> -----Original Message-----
> From: Richard Huxton [mailto:dev@archonet.com]
>
> Well, that's pretty much the definition of bloat. Are you sure you're
> vacuuming enough?
>
> DN: Well, the auto-vac is kicking off pretty darn frequently...around
> once every 2 minutes. However, you just made me think of the obvious -
> is it actually doing anything?! The app is pretty darn write intensive
> so I wonder if it's actually able to vacuum the tables?
If you've got a big batch delete, it can't hurt to manually vacuum that
table immediately afterwards.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Database size Vs performance degradation
2008-07-30 12:09 Database size Vs performance degradation Dave North <DNorth@signiant.com>
@ 2008-07-30 12:36 ` Matthew Wakeling <matthew@flymine.org>
2008-07-30 12:43 ` Re: Database size Vs performance degradation Dave North <DNorth@signiant.com>
3 siblings, 1 reply; 23+ messages in thread
From: Matthew Wakeling @ 2008-07-30 12:36 UTC (permalink / raw)
To: pgsql-performance
On Wed, 30 Jul 2008, Dave North wrote:
> Running on HP DL380 w/ 4GB RAM, dual 10K HDDs in RAID 0+1
> Checking the stats, the DB size is around 7.5GB;
Doesn't fit in RAM.
> ...after the load, the DB size was around 2.7GB
Does fit in RAM.
> One observation I've made on the DB system is the disk I/O seems
> dreadfully slow...we're at around 75% I/O wait sometimes and the read
> rates seem quite slow (hdparm says around 2.2MB/sec - 20MB/sec for
> un-cached reads).
That's incredibly slow in this day and age, especially from 10krpm HDDs.
Definitely worth investigating.
However, I think vacuuming more agressively is going to be your best win
at the moment.
Matthew
--
Patron: "I am looking for a globe of the earth."
Librarian: "We have a table-top model over here."
Patron: "No, that's not good enough. Don't you have a life-size?"
Librarian: (pause) "Yes, but it's in use right now."
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Database size Vs performance degradation
2008-07-30 12:09 Database size Vs performance degradation Dave North <DNorth@signiant.com>
2008-07-30 12:36 ` Re: Database size Vs performance degradation Matthew Wakeling <matthew@flymine.org>
@ 2008-07-30 12:43 ` Dave North <DNorth@signiant.com>
0 siblings, 0 replies; 23+ messages in thread
From: Dave North @ 2008-07-30 12:43 UTC (permalink / raw)
To: pgsql-performance
-----Original Message-----
From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Matthew
Wakeling
Sent: July 30, 2008 8:37 AM
To: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Database size Vs performance degradation
On Wed, 30 Jul 2008, Dave North wrote:
> Running on HP DL380 w/ 4GB RAM, dual 10K HDDs in RAID 0+1
> Checking the stats, the DB size is around 7.5GB;
Doesn't fit in RAM.
> ...after the load, the DB size was around 2.7GB
Does fit in RAM.
> One observation I've made on the DB system is the disk I/O seems
> dreadfully slow...we're at around 75% I/O wait sometimes and the read
> rates seem quite slow (hdparm says around 2.2MB/sec - 20MB/sec for
> un-cached reads).
That's incredibly slow in this day and age, especially from 10krpm HDDs.
Definitely worth investigating.
DN: Yeah, I was thinking the same thing. Unlike the folks here, I'm no
performance whiz but it did seem crazy slow. Given the 10K disks, it
seems to me there is most likely something on the RAID Array itself that
is set sub-optimally. Next thing to look at.
However, I think vacuuming more agressively is going to be your best win
at the moment.
DN: As I just replied to the past (very helpful) chap, I think I need to
go see what exactly the vac is vac'ing (autovac that is) because
although it's running super frequently, the big question is "is it doing
anything" :)
Cheers
Dave
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Database size Vs performance degradation
2008-07-30 12:09 Database size Vs performance degradation Dave North <DNorth@signiant.com>
@ 2008-07-30 14:57 ` Valentin Bogdanov <valiouk@yahoo.co.uk>
2008-07-30 15:02 ` Re: Database size Vs performance degradation Dave North <DNorth@signiant.com>
2008-07-30 21:58 ` Re: Database size Vs performance degradation Miernik <public@public.miernik.name>
3 siblings, 2 replies; 23+ messages in thread
From: Valentin Bogdanov @ 2008-07-30 14:57 UTC (permalink / raw)
To: pgsql-performance@postgresql.org, Dave North <DNorth@signiant.com>
I am guessing that you are using DELETE to remove the 75,000 unimportant.
Change your batch job to CREATE a new table consisting only of the 5,000 important. You can use "CREATE TABLE table_name AS select_statement" command. Then drop the old table. After that you can use ALTER TABLE to change the name of the new table to that of the old one.
I am not an expert but if this is a viable solution for you then I think doing it this way will rid you of your bloating problem.
Regards,
Val
--- On Wed, 30/7/08, Dave North <DNorth@signiant.com> wrote:
> From: Dave North <DNorth@signiant.com>
> Subject: [PERFORM] Database size Vs performance degradation
> To: pgsql-performance@postgresql.org
> Date: Wednesday, 30 July, 2008, 1:09 PM
> Morning folks,
> Long time listener, first time poster. Having an
> interesting
> problem related to performance which I'll try and
> describe below and
> hopefully get some enlightenment. First the environment:
>
>
> Postgres 8.1.8
> shared_buffers = 2000
> max_fsm_pages = 400000
> Redhat Enterprise 4
> Running on HP DL380 w/ 4GB RAM, dual 10K HDDs in RAID 0+1
> Also running on the server is a tomcat web server and other
> ancillaries
>
> Now, the problem. We have an application that continually
> writes a
> bunch of data to a few tables which is then deleted by a
> batch job each
> night. We're adding around 80,000 rows to one table
> per day and
> removing around 75,000 that are deemed to be
> "unimportant". Now, the
> problem we see is that after a period of time, the database
> access
> becomes very 'slow' and the load avg on the machine
> gets up around 5.
> When this happens, the application using the DB basically
> grinds to a
> halt. Checking the stats, the DB size is around 7.5GB; no
> tables or
> indexes look to be 'bloated' (we have been using
> psql since 7.3 with the
> classic index bloat problem) and the auto-vac has been
> running solidly.
>
> We had this problem around a month ago and again yesterday.
> Because the
> application needs reasonably high availability, we
> couldn't full vacuum
> so what we did was a dump and load to another system. What
> I found here
> was that after the load, the DB size was around 2.7GB - a
> decrease of
> 5GB. Re-loading this back onto the main system, and the
> world is good.
>
> One observation I've made on the DB system is the disk
> I/O seems
> dreadfully slow...we're at around 75% I/O wait
> sometimes and the read
> rates seem quite slow (hdparm says around 2.2MB/sec -
> 20MB/sec for
> un-cached reads). I've also observed that the OS cache
> seems to be
> using all of the remaining memory for it's cache
> (around 3GB) which
> seems probably the best it can do with the available
> memory.
>
> Now, clearly we need to examine the need for the
> application to write
> and remove so much data but my main question is:
>
> Why does the size of the database with so much
> "un-used" space seem to
> impact performance so much? If (in this case) the extra
> 5GB of space is
> essentially "unallocated", does it factor into
> any of the caching or
> performance metrics that the DBMS uses? And if so, would I
> be better
> having a higher shared_buffers rather than relying so much
> on OS cache?
>
> Yes, I know we need to upgrade to 8.3 but that's going
> to take some time
> :)
>
> Many thanks in advance.
>
> Dave
>
> ___
> Dave North
> dnorth@signiant.com
> Signiant - Making Media Move
> Visit Signiant at: www.signiant.com
> <http://www.signiant.com/;
>
>
> --
> Sent via pgsql-performance mailing list
> (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
__________________________________________________________
Not happy with your email address?.
Get the one you really want - millions of new email addresses available now at Yahoo! http://uk.docs.yahoo.com/ymail/new.html
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Database size Vs performance degradation
2008-07-30 12:09 Database size Vs performance degradation Dave North <DNorth@signiant.com>
2008-07-30 14:57 ` Re: Database size Vs performance degradation Valentin Bogdanov <valiouk@yahoo.co.uk>
@ 2008-07-30 15:02 ` Dave North <DNorth@signiant.com>
2008-07-30 15:32 ` Re: Database size Vs performance degradation Craig James <craig_james@emolecules.com>
2008-07-30 15:46 ` Re: Database size Vs performance degradation Mark Roberts <mailing_lists@pandapocket.com>
1 sibling, 2 replies; 23+ messages in thread
From: Dave North @ 2008-07-30 15:02 UTC (permalink / raw)
To: pgsql-performance
Thank you for the suggestion..much appreciated. Alas, I don't think
this will be possible without a change to the application but it's a
good idea nonetheless.
Where I am now is looking at the autovac tuning parameters. I strongly
suspect that the 2 tables that are "frequent changers" are just not
getting enough cleaning. It's hard to tell though because the AV
messages are only at debug2 and setting debug2 on this server would be a
killer. However, just from running the math using the autovac limit and
how it's a percentage of table size, I'm pretty sure that we're not
vac'ing enough and that reducing the multiplier down from 0.4 would make
a significant difference. My main question was answered though I think
- the growth is NOT normal which was the stimulus I needed to
investigate further.
Thanks again
Dave
> -----Original Message-----
> From: Valentin Bogdanov [mailto:valiouk@yahoo.co.uk]
> Sent: July 30, 2008 10:58 AM
> To: pgsql-performance@postgresql.org; Dave North
> Subject: Re: [PERFORM] Database size Vs performance degradation
>
> I am guessing that you are using DELETE to remove the 75,000
> unimportant.
> Change your batch job to CREATE a new table consisting only
> of the 5,000 important. You can use "CREATE TABLE table_name
> AS select_statement" command. Then drop the old table. After
> that you can use ALTER TABLE to change the name of the new
> table to that of the old one.
>
> I am not an expert but if this is a viable solution for you
> then I think doing it this way will rid you of your bloating problem.
>
> Regards,
> Val
>
>
> --- On Wed, 30/7/08, Dave North <DNorth@signiant.com> wrote:
>
> > From: Dave North <DNorth@signiant.com>
> > Subject: [PERFORM] Database size Vs performance degradation
> > To: pgsql-performance@postgresql.org
> > Date: Wednesday, 30 July, 2008, 1:09 PM Morning folks,
> > Long time listener, first time poster. Having an
> interesting problem
> > related to performance which I'll try and describe below
> and hopefully
> > get some enlightenment. First the environment:
> >
> >
> > Postgres 8.1.8
> > shared_buffers = 2000
> > max_fsm_pages = 400000
> > Redhat Enterprise 4
> > Running on HP DL380 w/ 4GB RAM, dual 10K HDDs in RAID 0+1
> Also running
> > on the server is a tomcat web server and other ancillaries
> >
> > Now, the problem. We have an application that continually writes a
> > bunch of data to a few tables which is then deleted by a batch job
> > each night. We're adding around 80,000 rows to one table
> per day and
> > removing around 75,000 that are deemed to be "unimportant".
> Now, the
> > problem we see is that after a period of time, the database access
> > becomes very 'slow' and the load avg on the machine gets up
> around 5.
> > When this happens, the application using the DB basically
> grinds to a
> > halt. Checking the stats, the DB size is around 7.5GB; no
> tables or
> > indexes look to be 'bloated' (we have been using psql since
> 7.3 with
> > the classic index bloat problem) and the auto-vac has been running
> > solidly.
> >
> > We had this problem around a month ago and again yesterday.
> > Because the
> > application needs reasonably high availability, we couldn't full
> > vacuum so what we did was a dump and load to another
> system. What I
> > found here was that after the load, the DB size was around
> 2.7GB - a
> > decrease of 5GB. Re-loading this back onto the main
> system, and the
> > world is good.
> >
> > One observation I've made on the DB system is the disk I/O seems
> > dreadfully slow...we're at around 75% I/O wait sometimes
> and the read
> > rates seem quite slow (hdparm says around 2.2MB/sec - 20MB/sec for
> > un-cached reads). I've also observed that the OS cache seems to be
> > using all of the remaining memory for it's cache (around 3GB) which
> > seems probably the best it can do with the available memory.
> >
> > Now, clearly we need to examine the need for the
> application to write
> > and remove so much data but my main question is:
> >
> > Why does the size of the database with so much "un-used"
> space seem to
> > impact performance so much? If (in this case) the extra
> 5GB of space
> > is essentially "unallocated", does it factor into any of
> the caching
> > or performance metrics that the DBMS uses? And if so, would I be
> > better having a higher shared_buffers rather than relying
> so much on
> > OS cache?
> >
> > Yes, I know we need to upgrade to 8.3 but that's going to take some
> > time
> > :)
> >
> > Many thanks in advance.
> >
> > Dave
> >
> > ___
> > Dave North
> > dnorth@signiant.com
> > Signiant - Making Media Move
> > Visit Signiant at: www.signiant.com
> > <http://www.signiant.com/;
> >
> >
> > --
> > Sent via pgsql-performance mailing list
> > (pgsql-performance@postgresql.org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-performance
>
>
> __________________________________________________________
> Not happy with your email address?.
> Get the one you really want - millions of new email addresses
> available now at Yahoo! http://uk.docs.yahoo.com/ymail/new.html
>
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Database size Vs performance degradation
2008-07-30 12:09 Database size Vs performance degradation Dave North <DNorth@signiant.com>
2008-07-30 14:57 ` Re: Database size Vs performance degradation Valentin Bogdanov <valiouk@yahoo.co.uk>
2008-07-30 15:02 ` Re: Database size Vs performance degradation Dave North <DNorth@signiant.com>
@ 2008-07-30 15:32 ` Craig James <craig_james@emolecules.com>
2008-07-30 16:16 ` Re: Database size Vs performance degradation Matthew Wakeling <matthew@flymine.org>
1 sibling, 1 reply; 23+ messages in thread
From: Craig James @ 2008-07-30 15:32 UTC (permalink / raw)
To: Dave North <DNorth@signiant.com>; pgsql-performance
Dave North wrote:
> Thank you for the suggestion..much appreciated. Alas, I don't think
> this will be possible without a change to the application but it's a
> good idea nonetheless.
I assume you mean the "create table as select ..." suggestion (don't forget to include a little quoted material so we'll know what you are replying to :-)
You don't have to change the application. One of the great advantages of Postgres is that even table creation, dropping and renaming are transactional. So you can do the select / drop / rename as a transaction by an external app, and your main application will be none the wiser. In pseudo-SQL:
begin
create table new_table as (select * from old_table);
create index ... on new_table ... (as needed)
drop table old_table
alter table new_table rename to old_table
commit
You should be able to just execute this by hand on a running system, and see if some of your bloat goes away.
Craig
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Database size Vs performance degradation
2008-07-30 12:09 Database size Vs performance degradation Dave North <DNorth@signiant.com>
2008-07-30 14:57 ` Re: Database size Vs performance degradation Valentin Bogdanov <valiouk@yahoo.co.uk>
2008-07-30 15:02 ` Re: Database size Vs performance degradation Dave North <DNorth@signiant.com>
2008-07-30 15:32 ` Re: Database size Vs performance degradation Craig James <craig_james@emolecules.com>
@ 2008-07-30 16:16 ` Matthew Wakeling <matthew@flymine.org>
2008-07-30 16:49 ` Re: Database size Vs performance degradation Mark Roberts <mailing_lists@pandapocket.com>
0 siblings, 1 reply; 23+ messages in thread
From: Matthew Wakeling @ 2008-07-30 16:16 UTC (permalink / raw)
To: pgsql-performance
On Wed, 30 Jul 2008, Craig James wrote:
> You don't have to change the application. One of the great advantages of
> Postgres is that even table creation, dropping and renaming are
> transactional. So you can do the select / drop / rename as a transaction by
> an external app, and your main application will be none the wiser. In
> pseudo-SQL:
>
> begin
> create table new_table as (select * from old_table);
> create index ... on new_table ... (as needed)
> drop table old_table
> alter table new_table rename to old_table
> commit
I believe this SQL snippet could cause data loss, because there is a
period during which writes can be made to the old table that will not be
copied to the new table.
On a side note, I would be interested to know what happens with locks when
renaming tables. For example, if we were to alter the above SQL, and add a
"LOCK TABLE old_table IN ACCESS EXCLUSIVE" line, would this fix the
problem? What I mean is, if the application tries to run "INSERT INTO
old_table ...", and blocks on the lock, when the old_table is dropped,
will it resume trying to insert into the dropped table and fail, or will
it redirect its attentions to the new table that has been renamed into
place?
Also, if a lock is taken on a table, and the table is renamed, does the
lock follow the table, or does it stay attached to the table name?
Anyway, surely it's much safer to just run VACUUM manually?
Matthew
--
Change is inevitable, except from vending machines.
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Database size Vs performance degradation
2008-07-30 12:09 Database size Vs performance degradation Dave North <DNorth@signiant.com>
2008-07-30 14:57 ` Re: Database size Vs performance degradation Valentin Bogdanov <valiouk@yahoo.co.uk>
2008-07-30 15:02 ` Re: Database size Vs performance degradation Dave North <DNorth@signiant.com>
2008-07-30 15:32 ` Re: Database size Vs performance degradation Craig James <craig_james@emolecules.com>
2008-07-30 16:16 ` Re: Database size Vs performance degradation Matthew Wakeling <matthew@flymine.org>
@ 2008-07-30 16:49 ` Mark Roberts <mailing_lists@pandapocket.com>
2008-07-30 17:51 ` Re: Database size Vs performance degradation Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 23+ messages in thread
From: Mark Roberts @ 2008-07-30 16:49 UTC (permalink / raw)
To: Matthew Wakeling <matthew@flymine.org>; +Cc: pgsql-performance
On Wed, 2008-07-30 at 17:16 +0100, Matthew Wakeling wrote:
>
> I believe this SQL snippet could cause data loss, because there is a
> period during which writes can be made to the old table that will not
> be
> copied to the new table.
It could indeed cause data loss.
> On a side note, I would be interested to know what happens with locks
> when
> renaming tables. For example, if we were to alter the above SQL, and
> add a
> "LOCK TABLE old_table IN ACCESS EXCLUSIVE" line, would this fix the
> problem? What I mean is, if the application tries to run "INSERT INTO
> old_table ...", and blocks on the lock, when the old_table is
> dropped,
> will it resume trying to insert into the dropped table and fail, or
> will
> it redirect its attentions to the new table that has been renamed
> into
> place?
Yes, that would resolve the issue. It would also block the
application's writes for however long the process takes (this could be
unacceptable).
> Also, if a lock is taken on a table, and the table is renamed, does
> the
> lock follow the table, or does it stay attached to the table name?
The lock will follow the table itself (rather than the table name).
> Anyway, surely it's much safer to just run VACUUM manually?
Generally, you would think so. The problem comes from Vacuum blocking
the application process' writes.
-Mark
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Database size Vs performance degradation
2008-07-30 12:09 Database size Vs performance degradation Dave North <DNorth@signiant.com>
2008-07-30 14:57 ` Re: Database size Vs performance degradation Valentin Bogdanov <valiouk@yahoo.co.uk>
2008-07-30 15:02 ` Re: Database size Vs performance degradation Dave North <DNorth@signiant.com>
2008-07-30 15:32 ` Re: Database size Vs performance degradation Craig James <craig_james@emolecules.com>
2008-07-30 16:16 ` Re: Database size Vs performance degradation Matthew Wakeling <matthew@flymine.org>
2008-07-30 16:49 ` Re: Database size Vs performance degradation Mark Roberts <mailing_lists@pandapocket.com>
@ 2008-07-30 17:51 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-07-30 18:04 ` Re: Database size Vs performance degradation Mark Roberts <mailing_lists@pandapocket.com>
0 siblings, 1 reply; 23+ messages in thread
From: Tom Lane @ 2008-07-30 17:51 UTC (permalink / raw)
To: Mark Roberts <mailing_lists@pandapocket.com>; +Cc: Matthew Wakeling <matthew@flymine.org>; pgsql-performance
Mark Roberts <mailing_lists@pandapocket.com> writes:
> On Wed, 2008-07-30 at 17:16 +0100, Matthew Wakeling wrote:
>> Anyway, surely it's much safer to just run VACUUM manually?
> Generally, you would think so. The problem comes from Vacuum blocking
> the application process' writes.
Huh? Vacuum doesn't block writes.
regards, tom lane
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Database size Vs performance degradation
2008-07-30 12:09 Database size Vs performance degradation Dave North <DNorth@signiant.com>
2008-07-30 14:57 ` Re: Database size Vs performance degradation Valentin Bogdanov <valiouk@yahoo.co.uk>
2008-07-30 15:02 ` Re: Database size Vs performance degradation Dave North <DNorth@signiant.com>
2008-07-30 15:32 ` Re: Database size Vs performance degradation Craig James <craig_james@emolecules.com>
2008-07-30 16:16 ` Re: Database size Vs performance degradation Matthew Wakeling <matthew@flymine.org>
2008-07-30 16:49 ` Re: Database size Vs performance degradation Mark Roberts <mailing_lists@pandapocket.com>
2008-07-30 17:51 ` Re: Database size Vs performance degradation Tom Lane <tgl@sss.pgh.pa.us>
@ 2008-07-30 18:04 ` Mark Roberts <mailing_lists@pandapocket.com>
2008-07-31 17:49 ` Re: Database size Vs performance degradation Andrzej Zawadzki <zawadaa@wp.pl>
0 siblings, 1 reply; 23+ messages in thread
From: Mark Roberts @ 2008-07-30 18:04 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Matthew Wakeling <matthew@flymine.org>; pgsql-performance
On Wed, 2008-07-30 at 13:51 -0400, Tom Lane wrote:
>
>
> Huh? Vacuum doesn't block writes.
>
> regards, tom lane
>
Of course, you are correct. I was thinking of Vacuum full, which is
recommended for use when you're deleting the majority of rows in a
table.
http://www.postgresql.org/docs/8.1/interactive/sql-vacuum.html
-Mark
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Database size Vs performance degradation
2008-07-30 12:09 Database size Vs performance degradation Dave North <DNorth@signiant.com>
2008-07-30 14:57 ` Re: Database size Vs performance degradation Valentin Bogdanov <valiouk@yahoo.co.uk>
2008-07-30 15:02 ` Re: Database size Vs performance degradation Dave North <DNorth@signiant.com>
2008-07-30 15:32 ` Re: Database size Vs performance degradation Craig James <craig_james@emolecules.com>
2008-07-30 16:16 ` Re: Database size Vs performance degradation Matthew Wakeling <matthew@flymine.org>
2008-07-30 16:49 ` Re: Database size Vs performance degradation Mark Roberts <mailing_lists@pandapocket.com>
2008-07-30 17:51 ` Re: Database size Vs performance degradation Tom Lane <tgl@sss.pgh.pa.us>
2008-07-30 18:04 ` Re: Database size Vs performance degradation Mark Roberts <mailing_lists@pandapocket.com>
@ 2008-07-31 17:49 ` Andrzej Zawadzki <zawadaa@wp.pl>
2008-08-01 11:03 ` Re: Database size Vs performance degradation Matthew Wakeling <matthew@flymine.org>
0 siblings, 1 reply; 23+ messages in thread
From: Andrzej Zawadzki @ 2008-07-31 17:49 UTC (permalink / raw)
To: pgsql-performance
Mark Roberts wrote:
> On Wed, 2008-07-30 at 13:51 -0400, Tom Lane wrote:
>
>> Huh? Vacuum doesn't block writes.
>>
>> regards, tom lane
>>
>>
>
> Of course, you are correct. I was thinking of Vacuum full, which is
> recommended for use when you're deleting the majority of rows in a
> table.
>
> http://www.postgresql.org/docs/8.1/interactive/sql-vacuum.html
>
Maybe I'm wrong but if this "bulk insert and delete" process is cyclical
then You don't need vacuum full.
Released tuples will fill up again with fresh data next day - after
regular vacuum.
I have such situation at work. Size of database on disk is 60GB and is
stable.
--
Andrzej Zawadzki
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Database size Vs performance degradation
2008-07-30 12:09 Database size Vs performance degradation Dave North <DNorth@signiant.com>
2008-07-30 14:57 ` Re: Database size Vs performance degradation Valentin Bogdanov <valiouk@yahoo.co.uk>
2008-07-30 15:02 ` Re: Database size Vs performance degradation Dave North <DNorth@signiant.com>
2008-07-30 15:32 ` Re: Database size Vs performance degradation Craig James <craig_james@emolecules.com>
2008-07-30 16:16 ` Re: Database size Vs performance degradation Matthew Wakeling <matthew@flymine.org>
2008-07-30 16:49 ` Re: Database size Vs performance degradation Mark Roberts <mailing_lists@pandapocket.com>
2008-07-30 17:51 ` Re: Database size Vs performance degradation Tom Lane <tgl@sss.pgh.pa.us>
2008-07-30 18:04 ` Re: Database size Vs performance degradation Mark Roberts <mailing_lists@pandapocket.com>
2008-07-31 17:49 ` Re: Database size Vs performance degradation Andrzej Zawadzki <zawadaa@wp.pl>
@ 2008-08-01 11:03 ` Matthew Wakeling <matthew@flymine.org>
2008-08-03 23:51 ` Re: Database size Vs performance degradation Fernando Ike <fike@midstorm.org>
0 siblings, 1 reply; 23+ messages in thread
From: Matthew Wakeling @ 2008-08-01 11:03 UTC (permalink / raw)
To: pgsql-performance
On Thu, 31 Jul 2008, Andrzej Zawadzki wrote:
> Maybe I'm wrong but if this "bulk insert and delete" process is cyclical then
> You don't need vacuum full.
> Released tuples will fill up again with fresh data next day - after regular
> vacuum.
Yes, a regular manual vacuum will prevent the table from growing more than
it needs to. However, a vacuum full is required to actually reduce the
size of the table from 7.5G to 2.7G if that hasn't been done on the
production system already.
Matthew
--
It's one of those irregular verbs - "I have an independent mind," "You are
an eccentric," "He is round the twist."
-- Bernard Woolly, Yes Prime Minister
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Database size Vs performance degradation
2008-07-30 12:09 Database size Vs performance degradation Dave North <DNorth@signiant.com>
2008-07-30 14:57 ` Re: Database size Vs performance degradation Valentin Bogdanov <valiouk@yahoo.co.uk>
2008-07-30 15:02 ` Re: Database size Vs performance degradation Dave North <DNorth@signiant.com>
2008-07-30 15:32 ` Re: Database size Vs performance degradation Craig James <craig_james@emolecules.com>
2008-07-30 16:16 ` Re: Database size Vs performance degradation Matthew Wakeling <matthew@flymine.org>
2008-07-30 16:49 ` Re: Database size Vs performance degradation Mark Roberts <mailing_lists@pandapocket.com>
2008-07-30 17:51 ` Re: Database size Vs performance degradation Tom Lane <tgl@sss.pgh.pa.us>
2008-07-30 18:04 ` Re: Database size Vs performance degradation Mark Roberts <mailing_lists@pandapocket.com>
2008-07-31 17:49 ` Re: Database size Vs performance degradation Andrzej Zawadzki <zawadaa@wp.pl>
2008-08-01 11:03 ` Re: Database size Vs performance degradation Matthew Wakeling <matthew@flymine.org>
@ 2008-08-03 23:51 ` Fernando Ike <fike@midstorm.org>
0 siblings, 0 replies; 23+ messages in thread
From: Fernando Ike @ 2008-08-03 23:51 UTC (permalink / raw)
To: Matthew Wakeling <matthew@flymine.org>; +Cc: pgsql-performance
2008/8/1 Matthew Wakeling <matthew@flymine.org>:
> On Thu, 31 Jul 2008, Andrzej Zawadzki wrote:
>>
>> Maybe I'm wrong but if this "bulk insert and delete" process is cyclical
>> then You don't need vacuum full.
>> Released tuples will fill up again with fresh data next day - after
>> regular vacuum.
>
> Yes, a regular manual vacuum will prevent the table from growing more than
> it needs to. However, a vacuum full is required to actually reduce the size
> of the table from 7.5G to 2.7G if that hasn't been done on the production
> system already.
One good possibility is use pg8.3 for fix problem. Enable
Autovacuum+HOT was won a significant performance compared with 8.2 and
minor versions. :)
Kind Regards,
--
Fernando Ike
http://www.midstorm.org/~fike/weblog
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Database size Vs performance degradation
2008-07-30 12:09 Database size Vs performance degradation Dave North <DNorth@signiant.com>
2008-07-30 14:57 ` Re: Database size Vs performance degradation Valentin Bogdanov <valiouk@yahoo.co.uk>
2008-07-30 15:02 ` Re: Database size Vs performance degradation Dave North <DNorth@signiant.com>
@ 2008-07-30 15:46 ` Mark Roberts <mailing_lists@pandapocket.com>
1 sibling, 0 replies; 23+ messages in thread
From: Mark Roberts @ 2008-07-30 15:46 UTC (permalink / raw)
To: Dave North <DNorth@signiant.com>; +Cc: pgsql-performance
On Wed, 2008-07-30 at 10:02 -0500, Dave North wrote:
> Thank you for the suggestion..much appreciated. Alas, I don't think
> this will be possible without a change to the application but it's a
> good idea nonetheless.
Affirmative, Dave. I read you.
If I were in your situation (not having access/desire to change the base
application), I'd write a sql script that does something like this:
- Create __new_table__ from old_table # Read lock on old table
- Rename old_table to __old_table__ # Access Exclusive Lock
- Rename __new_table__ to old_table # Access Exclusive Lock
- Commit # Now the application can write to the new table
- Sync newly written changes to the new table (these would be written
between the creation and access exclusive lock).
- Drop/Vacuum full/Archive old_table
Well, it would at least let you get the benefits of the rename approach
without actually altering the application. Additionally, the
application's writes would only be blocked for the duration of the
rename itself.
This makes the assumption that these writes aren't strictly necessary
immediately (such as via a find or insert construct). If this
assumption is false, you would need to lock the table and block the
application from writing while you create the temporary table. This has
the advantage of not requiring the final sync step.
Sorry if all of this seems redundant, but best of luck!
-Mark
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Database size Vs performance degradation
2008-07-30 12:09 Database size Vs performance degradation Dave North <DNorth@signiant.com>
2008-07-30 14:57 ` Re: Database size Vs performance degradation Valentin Bogdanov <valiouk@yahoo.co.uk>
@ 2008-07-30 21:58 ` Miernik <public@public.miernik.name>
2008-07-30 22:14 ` Re: Database size Vs performance degradation Mark Roberts <mailing_lists@pandapocket.com>
1 sibling, 1 reply; 23+ messages in thread
From: Miernik @ 2008-07-30 21:58 UTC (permalink / raw)
To: pgsql-performance
Valentin Bogdanov <valiouk@yahoo.co.uk> wrote:
> I am guessing that you are using DELETE to remove the 75,000
> unimportant. Change your batch job to CREATE a new table consisting
> only of the 5,000 important. You can use "CREATE TABLE table_name AS
> select_statement" command. Then drop the old table. After that you can
> use ALTER TABLE to change the name of the new table to that of the old
> one.
I have a similar, but different situation, where I TRUNCATE a table with
60k rows every hour, and refill it with new rows. Would it be better
(concerning bloat) to just DROP the table every hour, and recreate it,
then to TRUNCATE it? Or does TRUNCATE take care of the boat as good as a
DROP and CREATE?
I am running 8.3.3 in a 48 MB RAM Xen, so performance matters much.
--
Miernik
http://miernik.name/
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Database size Vs performance degradation
2008-07-30 12:09 Database size Vs performance degradation Dave North <DNorth@signiant.com>
2008-07-30 14:57 ` Re: Database size Vs performance degradation Valentin Bogdanov <valiouk@yahoo.co.uk>
2008-07-30 21:58 ` Re: Database size Vs performance degradation Miernik <public@public.miernik.name>
@ 2008-07-30 22:14 ` Mark Roberts <mailing_lists@pandapocket.com>
0 siblings, 0 replies; 23+ messages in thread
From: Mark Roberts @ 2008-07-30 22:14 UTC (permalink / raw)
To: Miernik <public@public.miernik.name>; +Cc: pgsql-performance
On Wed, 2008-07-30 at 23:58 +0200, Miernik wrote:
> I have a similar, but different situation, where I TRUNCATE a table
> with
> 60k rows every hour, and refill it with new rows. Would it be better
> (concerning bloat) to just DROP the table every hour, and recreate it,
> then to TRUNCATE it? Or does TRUNCATE take care of the boat as good as
> a
> DROP and CREATE?
>
> I am running 8.3.3 in a 48 MB RAM Xen, so performance matters much.
I've successfully used truncate for this purpose (server 8.2.6):
-----------------------------
psql=> select pg_relation_size(oid) from pg_class where relname =
'asdf';
pg_relation_size
------------------
32768
(1 row)
Time: 0.597 ms
psql=> truncate asdf;
TRUNCATE TABLE
Time: 1.069 ms
psql=> select pg_relation_size(oid) from pg_class where relname =
'asdf';
pg_relation_size
------------------
0
(1 row)
-Mark
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Database size Vs performance degradation
2008-07-30 12:09 Database size Vs performance degradation Dave North <DNorth@signiant.com>
@ 2008-07-30 16:48 ` Greg Smith <gsmith@gregsmith.com>
2008-07-30 17:39 ` Re: Database size Vs performance degradation Dave North <DNorth@signiant.com>
3 siblings, 1 reply; 23+ messages in thread
From: Greg Smith @ 2008-07-30 16:48 UTC (permalink / raw)
To: Dave North <DNorth@signiant.com>; +Cc: pgsql-performance
On Wed, 30 Jul 2008, Dave North wrote:
> One observation I've made on the DB system is the disk I/O seems
> dreadfully slow...we're at around 75% I/O wait sometimes and the read
> rates seem quite slow (hdparm says around 2.2MB/sec - 20MB/sec for
> un-cached reads).
This is typically what happens when you are not buffering enough of the
right information in RAM, such that there are lots of small reads and
writes to the disk involve lots of seeking. You'll only get a couple of
MB/s out of a disk if it has to move all over the place to retreive the
blocks you asked for.
Setting shared_buffers too low makes this more likely to happen, because
PostgreSQL has to constantly read and write out random blocks to make
space to read new ones in its limited work area. The OS buffers some of
that, but not as well as if the database server has a bit more RAM for
itself because then the blocks it most uses won't leave that area.
> And if so, would I be better having a higher shared_buffers rather than
> relying so much on OS cache?
The main situation where making shared_buffers too high is a problem on
8.1 involves checkpoints writing out too much information at once. You
didn't mention changing checkpoint_segments on your system; if it's at its
default of 3, your system is likely continuously doing tiny checkpoints,
which might be another reason why you're seeing so much scattered seek
behavior above. Something >30 would be more appropriate for
checkpoint_segments on your server.
I'd suggest re-tuning as follows:
1) Increase shared_buffers to 10,000, test. Things should be a bit
faster.
2) Increase checkpoint_segments to 30, test. What you want to watch for
here whether there are periods where the server seems to freeze for a
couple of seconds. That's a "checkpoint spike". If this happens, reduce
checkpoint_segments to some sort of middle ground; some people never get
above 10 before it's a problem.
3) Increase shared_buffers in larger chunks, as long as you don't see any
problematic spikes you might usefully keep going until it's set to at
least 100,000 before improvements level off.
> I spent several hours reading info on this list and other places and
> it's highly inconclusive about having high or low shared buffs Vs
> letting the OS disk cache handle it.
A lot of the material floating around the 'net was written circa
PostgreSQL 8.0 or earlier, and you need to ignore any advice in this area
from those articles. I think if you rescan everything with that filter in
place you'll find its not so inconclusive that increasing shared_buffers
is a win, so long as it doesn't trigger checkpoint spikes (on your
platform at least, there are still Windows issues). Check out my "Inside
the PostgreSQL Buffer Cache" presentation at
http://www.westnet.com/~gsmith/content/postgresql for an excess of detail
on this topic. Unfortunately the usage_count recommendations given there
are impractical for use on 8.1 because pg_buffercache doesn't include that
info, but the general "shared_buffers vs. OS cache" theory and suggestions
apply.
The other parameter I hope you're setting correctly for your system is
effective_cache_size, which should be at least 2GB for your server (exact
sizing depends on how much RAM is leftover after the Tomcat app is
running).
All this is something to consider in parallel with the vacuum
investigation you're doing. It looks like your autovacuum isn't anywhere
close to aggressive enough for your workload, which is not unusual at all
for 8.1, and that may actually be the majority if your problem.
--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Database size Vs performance degradation
2008-07-30 12:09 Database size Vs performance degradation Dave North <DNorth@signiant.com>
2008-07-30 16:48 ` Re: Database size Vs performance degradation Greg Smith <gsmith@gregsmith.com>
@ 2008-07-30 17:39 ` Dave North <DNorth@signiant.com>
2008-07-30 17:51 ` Re: Database size Vs performance degradation Guillaume Lelarge <guillaume@lelarge.info>
0 siblings, 1 reply; 23+ messages in thread
From: Dave North @ 2008-07-30 17:39 UTC (permalink / raw)
To: pgsql-performance
> -----Original Message-----
> From: Greg Smith [mailto:gsmith@gregsmith.com]
> Sent: July 30, 2008 12:48 PM
> To: Dave North
> Cc: pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] Database size Vs performance degradation
>
> On Wed, 30 Jul 2008, Dave North wrote:
>
> > One observation I've made on the DB system is the disk I/O seems
> > dreadfully slow...we're at around 75% I/O wait sometimes
> and the read
> > rates seem quite slow (hdparm says around 2.2MB/sec - 20MB/sec for
> > un-cached reads).
>
> This is typically what happens when you are not buffering
> enough of the right information in RAM, such that there are
> lots of small reads and writes to the disk involve lots of
> seeking. You'll only get a couple of MB/s out of a disk if
> it has to move all over the place to retreive the blocks you
> asked for.
I could totally see that except on another identical server, the MAX
rate I was able to get under no load was 20MB/sec which just seems
awfully low for 10K rpm disks to me (but granted, I'm not a performance
analysis expert by any stretch)
>
> Setting shared_buffers too low makes this more likely to
> happen, because PostgreSQL has to constantly read and write
> out random blocks to make space to read new ones in its
> limited work area. The OS buffers some of that, but not as
> well as if the database server has a bit more RAM for itself
> because then the blocks it most uses won't leave that area.
OK, this makes sense that a "specialist" cache will provide more
benefits that a "general" cache. Got it.
>
> > And if so, would I be better having a higher shared_buffers rather
> > than relying so much on OS cache?
>
> The main situation where making shared_buffers too high is a
> problem on
> 8.1 involves checkpoints writing out too much information at
> once. You didn't mention changing checkpoint_segments on
> your system; if it's at its default of 3, your system is
> likely continuously doing tiny checkpoints, which might be
> another reason why you're seeing so much scattered seek
> behavior above. Something >30 would be more appropriate for
> checkpoint_segments on your server.
It appears ours is currently set to 12 but this is something I'll have a
play with as well.
>
> I'd suggest re-tuning as follows:
>
> 1) Increase shared_buffers to 10,000, test. Things should be
> a bit faster.
>
> 2) Increase checkpoint_segments to 30, test. What you want
> to watch for here whether there are periods where the server
> seems to freeze for a couple of seconds. That's a
> "checkpoint spike". If this happens, reduce
> checkpoint_segments to some sort of middle ground; some
> people never get above 10 before it's a problem.
>
> 3) Increase shared_buffers in larger chunks, as long as you
> don't see any problematic spikes you might usefully keep
> going until it's set to at least 100,000 before improvements
> level off.
Do you happen to know if these are "reload" or "restart" tunable
parameters? I think I've read somewhere before that they are restart
parameters (assuming I've set SHMMAX high enough of course)
>
> > I spent several hours reading info on this list and other
> places and
> > it's highly inconclusive about having high or low shared buffs Vs
> > letting the OS disk cache handle it.
>
<SNIP good reading info
>
> The other parameter I hope you're setting correctly for your
> system is effective_cache_size, which should be at least 2GB
> for your server (exact sizing depends on how much RAM is
> leftover after the Tomcat app is running).
Now, this is interesting. I'm seeing just from top and vmstat, that the
OS cache is around 2-3GB pretty consistently with everything running
under full load. So it seems I should be able to pretty safely set this
to 2GB as you suggest.
>
> All this is something to consider in parallel with the vacuum
> investigation you're doing. It looks like your autovacuum
> isn't anywhere close to aggressive enough for your workload,
> which is not unusual at all for 8.1, and that may actually be
> the majority if your problem.
Yeah, I've pretty well convinced myself that it is. We have 2 tables
that see this add/remove pattern where we add something like 100,000
rows per table and then delete around 75,000 per night...what I've just
done is added enties into pg_autovacuum to change the vac_scale_factor
down to 0.2 for both of these tables. By my calcs this will lower the
vac threshold for these tables from 221,000 to 111,000 tuples each.
Even this may be too high with max_fsm_pages at 400,000 but I can go
lower if needed. As per other threads, my only real metric to measure
this is the overall database size since the better AV messaging is an
8.3 enhancement.
I'm starting with just changing the autovac parameters and see how that
affects things. I'm reluctant to change multiple parameters in one shot
(although they all make sense logically) just in case one goes awry ;)
I have to say, I've learnt a whole load from you folks here this
morning...very enlightening. I'm now moving on to your site Greg! :)
Cheers
Dave
>
> --
> * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com
> Baltimore, MD
>
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: Database size Vs performance degradation
2008-07-30 12:09 Database size Vs performance degradation Dave North <DNorth@signiant.com>
2008-07-30 16:48 ` Re: Database size Vs performance degradation Greg Smith <gsmith@gregsmith.com>
2008-07-30 17:39 ` Re: Database size Vs performance degradation Dave North <DNorth@signiant.com>
@ 2008-07-30 17:51 ` Guillaume Lelarge <guillaume@lelarge.info>
0 siblings, 0 replies; 23+ messages in thread
From: Guillaume Lelarge @ 2008-07-30 17:51 UTC (permalink / raw)
To: Dave North <DNorth@signiant.com>; +Cc: pgsql-performance
Dave North a écrit :
> [...]
>> I'd suggest re-tuning as follows:
>>
>> 1) Increase shared_buffers to 10,000, test. Things should be
>> a bit faster.
>>
>> 2) Increase checkpoint_segments to 30, test. What you want
>> to watch for here whether there are periods where the server
>> seems to freeze for a couple of seconds. That's a
>> "checkpoint spike". If this happens, reduce
>> checkpoint_segments to some sort of middle ground; some
>> people never get above 10 before it's a problem.
>>
>> 3) Increase shared_buffers in larger chunks, as long as you
>> don't see any problematic spikes you might usefully keep
>> going until it's set to at least 100,000 before improvements
>> level off.
>
> Do you happen to know if these are "reload" or "restart" tunable
> parameters? I think I've read somewhere before that they are restart
> parameters (assuming I've set SHMMAX high enough of course)
>
shared_buffers and checkpoint_segments both need a restart.
> [...]
> I have to say, I've learnt a whole load from you folks here this
> morning...very enlightening. I'm now moving on to your site Greg! :)
>
There's much to learn from Greg's site. I was kinda impressed by all the
good articles in it.
--
Guillaume.
http://www.postgresqlfr.org
http://dalibo.com
^ permalink raw reply [nested|flat] 23+ messages in thread
end of thread, other threads:[~2008-08-03 23:51 UTC | newest]
Thread overview: 23+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-07-30 12:09 Database size Vs performance degradation Dave North <DNorth@signiant.com>
2008-07-30 12:28 ` Richard Huxton <dev@archonet.com>
2008-07-30 12:41 ` Dave North <DNorth@signiant.com>
2008-07-30 15:05 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-07-30 17:18 ` Richard Huxton <dev@archonet.com>
2008-07-30 12:36 ` Matthew Wakeling <matthew@flymine.org>
2008-07-30 12:43 ` Dave North <DNorth@signiant.com>
2008-07-30 14:57 ` Valentin Bogdanov <valiouk@yahoo.co.uk>
2008-07-30 15:02 ` Dave North <DNorth@signiant.com>
2008-07-30 15:32 ` Craig James <craig_james@emolecules.com>
2008-07-30 16:16 ` Matthew Wakeling <matthew@flymine.org>
2008-07-30 16:49 ` Mark Roberts <mailing_lists@pandapocket.com>
2008-07-30 17:51 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-07-30 18:04 ` Mark Roberts <mailing_lists@pandapocket.com>
2008-07-31 17:49 ` Andrzej Zawadzki <zawadaa@wp.pl>
2008-08-01 11:03 ` Matthew Wakeling <matthew@flymine.org>
2008-08-03 23:51 ` Fernando Ike <fike@midstorm.org>
2008-07-30 15:46 ` Mark Roberts <mailing_lists@pandapocket.com>
2008-07-30 21:58 ` Miernik <public@public.miernik.name>
2008-07-30 22:14 ` Mark Roberts <mailing_lists@pandapocket.com>
2008-07-30 16:48 ` Greg Smith <gsmith@gregsmith.com>
2008-07-30 17:39 ` Dave North <DNorth@signiant.com>
2008-07-30 17:51 ` Guillaume Lelarge <guillaume@lelarge.info>
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