Thread: Postgresql optimisation
Dear all, I need to optimize a database used by approx 10 people, I don't need to have the perfect config, simply to avoid stupid bottle necks and follow the best practices... The database is used from a web interface the whole work day with "normal" requests (nothing very special). And each morning huge tables are DELETED and all data is INSERTed new from a script. (Well, "huge" is very relative, it's only 400'000 records) For now, we only planned a VACUUM ANALYSE eacha night. But the database complained about checkpoint_segments (currently = 3) What should be changed first to improve speed ? * memory ? *??? Thanks a lot for any advice (I know there are plenty of archived discussions on this subject but it's always difficult to know what very important, and what's general as opposed to specific solutions) Have a nice day ! Denis
On Wed, Oct 28, 2009 at 12:11 PM, Denis BUCHER <dbucherml@hsolutions.ch> wrote:
again, if it is 8.3+ (and everyone here would advice you to run at least that version), try using pg_tune script to get best performance settings.
Dear all,
I need to optimize a database used by approx 10 people, I don't need to
have the perfect config, simply to avoid stupid bottle necks and follow
the best practices...
The database is used from a web interface the whole work day with
"normal" requests (nothing very special).
And each morning huge tables are DELETED and all data is INSERTed new
from a script. (Well, "huge" is very relative, it's only 400'000 records)
use truncate, to clear the tables.
For now, we only planned a VACUUM ANALYSE eacha night.
if it is 8.3+, don't , as autovacuum takes care of that.
But the database complained about checkpoint_segments (currently = 3)
depending on traffic, that's pretty low. You should increment it, beyond 12 if possible.
What should be changed first to improve speed ?
* memory ?
*???
Thanks a lot for any advice (I know there are plenty of archived
discussions on this subject but it's always difficult to know what very
important, and what's general as opposed to specific solutions)
again, if it is 8.3+ (and everyone here would advice you to run at least that version), try using pg_tune script to get best performance settings.
--
GJ
Grzegorz Jaśkiewicz a écrit : > > > On Wed, Oct 28, 2009 at 12:11 PM, Denis BUCHER <dbucherml@hsolutions.ch > <mailto:dbucherml@hsolutions.ch>> wrote: > > Dear all, > > I need to optimize a database used by approx 10 people, I don't need to > have the perfect config, simply to avoid stupid bottle necks and follow > the best practices... > > The database is used from a web interface the whole work day with > "normal" requests (nothing very special). > > And each morning huge tables are DELETED and all data is INSERTed new > from a script. (Well, "huge" is very relative, it's only 400'000 > records) > > use truncate, to clear the tables. Oh yes, instead of DELETE FROM table; ? Ok thanks for the tip > For now, we only planned a VACUUM ANALYSE eacha night. > > if it is 8.3+, don't , as autovacuum takes care of that. 8.1.17 > But the database complained about checkpoint_segments (currently = 3) > > depending on traffic, that's pretty low. You should increment it, beyond > 12 if possible. Ok no problem in increasing this value, to, let's say... 50 ? > What should be changed first to improve speed ? > * memory ? > *??? > Thanks a lot for any advice (I know there are plenty of archived > discussions on this subject but it's always difficult to know what very > important, and what's general as opposed to specific solutions) > > > again, if it is 8.3+ (and everyone here would advice you to run at least > that version), try using pg_tune script to get best performance settings. Ok, we will soon move it to a new server, it will be 8.3 then :-) And I will use pg_tune... Thanks a lot for your advices ! Denis
2009/10/28 Denis BUCHER <dbucherml@hsolutions.ch>
yes. This simply means, that in case of any failure (power outage, etc) - data log could be slightly older, but if you have busy DB on the other hand - low number here, means a lot of checkpoints written - which slows down performance. So it is a trade-off.
8.1 is pretty old. Go for 8.3 if you want something old enough (as in, stable-and-old-but-not-too-old). Or 8.4 if you are interested in newest features.
Grzegorz Jaśkiewicz a écrit :> <mailto:dbucherml@hsolutions.ch>> wrote:Oh yes, instead of DELETE FROM table; ? Ok thanks for the tip
>
> Dear all,
>
> I need to optimize a database used by approx 10 people, I don't need to
> have the perfect config, simply to avoid stupid bottle necks and follow
> the best practices...
>
> The database is used from a web interface the whole work day with
> "normal" requests (nothing very special).
>
> And each morning huge tables are DELETED and all data is INSERTed new
> from a script. (Well, "huge" is very relative, it's only 400'000
> records)
>
> use truncate, to clear the tables.8.1.17
> For now, we only planned a VACUUM ANALYSE eacha night.
>
> if it is 8.3+, don't , as autovacuum takes care of that.Ok no problem in increasing this value, to, let's say... 50 ?
> But the database complained about checkpoint_segments (currently = 3)
>
> depending on traffic, that's pretty low. You should increment it, beyond
> 12 if possible.
yes. This simply means, that in case of any failure (power outage, etc) - data log could be slightly older, but if you have busy DB on the other hand - low number here, means a lot of checkpoints written - which slows down performance. So it is a trade-off.
8.1 is pretty old. Go for 8.3 if you want something old enough (as in, stable-and-old-but-not-too-old). Or 8.4 if you are interested in newest features.
--
GJ
Denis BUCHER <dbucherml@hsolutions.ch> wrote: > And each morning ... all data is INSERTed new I recommend VACUUM ANALYZE of the table(s) after this step. Without that, the first query to read each tuple sets its hint bits and rewrites it, causing a surprising delay at unpredictable times (although heavier near the start of the day). -Kevin
Kevin Grittner a écrit : >> And each morning ... all data is INSERTed new > > I recommend VACUUM ANALYZE of the table(s) after this step. Without > that, the first query to read each tuple sets its hint bits and > rewrites it, causing a surprising delay at unpredictable times > (although heavier near the start of the day). Ok great, thanks for the advice, I added it at the end of the process... Denis
On Wed, 28 Oct 2009, Denis BUCHER wrote: > For now, we only planned a VACUUM ANALYSE eacha night. You really want to be on a later release than 8.1 for an app that is heavily deleting things every day. The answer to most VACUUM problems is "VACUUM more often, preferrably with autovacuum", and using 8.1 puts you into a position where that's not really practical. Also, 8.3 and 8.4 are much faster anyway. 8.4 in particular has a fix for a problem you're very likely to run into with this sort of workload (running out of max_fsm_pages when running VACUUM), so if you're going to upgrade I would highly recommend targeting 8.4 instead of an earlier version. > But the database complained about checkpoint_segments (currently = 3) > What should be changed first to improve speed ? http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server covers this parameter and some of the others you should be considering. If your goal is just to nail the major bottlenecks and get the configuration in the right neighborhood, you probably only need to consider the setting down to the work_mem section; the ones after that are more advanced than you probably need. -- * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
> -----Original Message----- > From: Denis BUCHER > > And each morning huge tables are DELETED and all data is > INSERTed new from a script. (Well, "huge" is very relative, > it's only 400'000 records) If you are deleting ALL rows in the tables, then I would suggest using TRUNCATE instead of DELETE. Truncate will be faster deleting and it will not accumulate dead tuples. Also if you switch to truncate then you should ANALYSE the tables after you finish inserting. Note that VACUUM ANALYSE is not necessary after a truncate/insert because there should be no dead tuples to vacuum. Dave
On Wed, 28 Oct 2009, Dave Dutcher wrote:
> Also if you switch to truncate then you should ANALYSE the tables after you
> finish inserting. Note that VACUUM ANALYSE is not necessary after a
> truncate/insert because there should be no dead tuples to vacuum.
Perhaps reading the other replies in the thread before replying yourself
might be advisable, because this previous reply directly contradicts you:
On Wed, 28 Oct 2009, Kevin Grittner wrote:
> I recommend VACUUM ANALYZE of the table(s) after this step. Without
> that, the first query to read each tuple sets its hint bits and
> rewrites it, causing a surprising delay at unpredictable times
> (although heavier near the start of the day).
There *is* a benefit of running VACUUM ANALYSE rather than just ANALYSE.
Matthew
--
I suppose some of you have done a Continuous Maths course. Yes? Continuous
Maths? <menacing stares from audience> Whoah, it was like that, was it!
-- Computer Science Lecturer
> From: Matthew Wakeling > > Perhaps reading the other replies in the thread before > replying yourself might be advisable, because this previous > reply directly contradicts you: > > On Wed, 28 Oct 2009, Kevin Grittner wrote: > > I recommend VACUUM ANALYZE of the table(s) after this step. Without > > that, the first query to read each tuple sets its hint bits and > > rewrites it, causing a surprising delay at unpredictable times > > (although heavier near the start of the day). > > There *is* a benefit of running VACUUM ANALYSE rather than > just ANALYSE. > > Matthew I did read the other replies first, I guess I just missed Kevin Grittner's somehow. I noticed several people were worried the OP had problems with bloat, which is why I suggested TRUNCATE if possible. That was my main point. I guess I made the other comment because I feel beginners with postgres quite often don't understand the difference between VACUUM and ANALYSE, and for large tables an ANALYSE alone can take much less time. I didn't think about hint bits because I've never noticed a big impact from them, but that is probably just because of my particular situation. Now that it has been pointed out to me I agree it is good advise for the OP to use VACUUM ANALSE. Dave
Hello Greg, Greg Smith a écrit : > On Wed, 28 Oct 2009, Denis BUCHER wrote: > >> For now, we only planned a VACUUM ANALYSE eacha night. > > You really want to be on a later release than 8.1 for an app that is > heavily deleting things every day. The answer to most VACUUM problems > is "VACUUM more often, preferrably with autovacuum", and using 8.1 puts > you into a position where that's not really practical. Also, 8.3 and > 8.4 are much faster anyway. Ok as the new server will be Debian and the latest stbale is 8.3 we'll be on 8.3 soon :-) > 8.4 in particular has a fix for a problem you're very likely to run into > with this sort of workload (running out of max_fsm_pages when running > VACUUM), so if you're going to upgrade I would highly recommend > targeting 8.4 instead of an earlier version. I got this problem already on 8.1, I just increased max_fsm_pages, is that OK ? >> But the database complained about checkpoint_segments (currently = 3) >> What should be changed first to improve speed ? > > http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server covers > this parameter and some of the others you should be considering. If > your goal is just to nail the major bottlenecks and get the > configuration in the right neighborhood, you probably only need to > consider the setting down to the work_mem section; the ones after that > are more advanced than you probably need. Ok I tried to change some parameters, we'll see what happens ;-) Thanks a lot for all your tips :-) Have a nice evening ! Denis