Thread: Checkpoint_segments optimal value

On Thu, 17 Jul 2014 10:44:32 -0700
Prabhjot Sheena <prabhjot.sheena@rivalwatch.com> wrote:

> Hello All
>             i had a database load issue today and when i was checking logs
> i saw this message. I m running postgresql 8.3
>
> db::ts:2014-07-17 09:38:21 PDT::LOG:  checkpoints are occurring too
> frequently (26 seconds apart)
> db::ts:2014-07-17 09:38:21 PDT::HINT:  Consider increasing the
> configuration parameter "checkpoint_segments".
> db::ts:2014-07-17 09:38:21 PDT::LOG:  checkpoint starting: xlog

Is this the first time you've had the load problem?  How often does it
happen?  How often is that message in the logs?

The downside (as described in the docs) is that increasing the value will
cause PG to take longer to recover in the event of a crash.  That and that
you need enough disk space to hold the extra segments.

If the warning isn't happening too often, I would try increasing it only a
little and see if it helps.  If it's not enough you can then increase it some
more.  Various sources around the Internet suggest that you don't want to go
much larger than 256 for this (if only because it's uncommon to do so and is
probably indicative of other tuning that you need to do).  Unfortunatley, you
need to restart PG for the change to take effect, so you have to balance
experimenting with your tuning against how often you can get away with a server
restart.

If it's just that one time that you got that message, then you might want to
double checkpoint_segments to 64.  Anything more than that seems unjustified,
unless you're seeing the problem a lot more often than your email suggests.

If the problem is happening frequently, you're probably way better off
organizing an upgrade to PG 9.3 than fidgeting with a lot of tuning.  The
number of performance improvements from 8.3 to 9.3 is quite significant.

> pls suggest the optimal checkpoint_segment value i should set
>
> *My current values are this*
>
> checkpoint_segments = 32                # in logfile segments, min 1, 16MB
> each
> #checkpoint_timeout = 5min              # range 30s-1h
> #checkpoint_completion_target = 0.5     # checkpoint target duration, 0.0 -
> 1.0
> #checkpoint_warning = 30s               # 0 is off
>
> *Current Memory values*
>
> # - Memory -
>
> effective_cache_size = 8GB
>
> shared_buffers = 1024MB                 # min 128kB or max_connections*16kB
>                                         # (change requires restart)
> temp_buffers = 64MB                     # min 800kB
> #max_prepared_transactions = 5          # can be 0 or more
>                                         # (change requires restart)
>
> work_mem = 512MB                                # min 64kB
> maintenance_work_mem = 2048MB           # min 1MB     !!! increased to 1GB
> 10/1/2010 mrostron (to assist autovacuum workers)
> #max_stack_depth = 2MB                  # min 100kB
>
> # - Free Space Map -
>
> max_fsm_pages = 809600                  # min max_fsm_relations*16, 6 bytes
> each
>                                         # (change requires restart)
> synchronous_commit = off
>
> #full_page_writes = on                  # recover from partial page writes
> #wal_buffers = 64kB                     # min 32kB
>                                         # (change requires restart)
> #wal_writer_delay = 200ms               # 1-10000 milliseconds
>
> #commit_delay = 0                       # range 0-100000, in microseconds
> #commit_siblings = 5                    # range 1-1000


--
Potentialtech <wmoran@potentialtech.com>

Potentialtech <wmoran@potentialtech.com> writes:
> If the warning isn't happening too often, I would try increasing it only a
> little and see if it helps.  If it's not enough you can then increase it some
> more.  Various sources around the Internet suggest that you don't want to go
> much larger than 256 for this (if only because it's uncommon to do so and is
> probably indicative of other tuning that you need to do).  Unfortunatley, you
> need to restart PG for the change to take effect, so you have to balance
> experimenting with your tuning against how often you can get away with a server
> restart.

Huh?  You don't need a restart, just a reload (SIGHUP) to change that.

            regards, tom lane

On Thu, 17 Jul 2014 14:06:28 -0400
Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Potentialtech <wmoran@potentialtech.com> writes:
> > If the warning isn't happening too often, I would try increasing it only a
> > little and see if it helps.  If it's not enough you can then increase it some
> > more.  Various sources around the Internet suggest that you don't want to go
> > much larger than 256 for this (if only because it's uncommon to do so and is
> > probably indicative of other tuning that you need to do).  Unfortunatley, you
> > need to restart PG for the change to take effect, so you have to balance
> > experimenting with your tuning against how often you can get away with a server
> > restart.
>
> Huh?  You don't need a restart, just a reload (SIGHUP) to change that.

Really?  Well, thanks for correcting me on that.

--
Potentialtech <wmoran@potentialtech.com>

On Thu, 17 Jul 2014 11:28:04 -0700
Prabhjot Sheena <prabhjot.sheena@rivalwatch.com> wrote:

> Thanks i have changed that to 64 and reloaded it.
>
> When i had load issue today there was this exact same query that hits the
> db like 50 to 60 times from different machines in 3 to 4 minutes and was
> taking long time to execute and was holding up the database. i did recreate
> an index and it started performing better. My question is why it is not
> fetching the result from the memory since  its the same query that runs
> again and again.
>
> This is the actual query i m taking about:
>
> SELECT account.id, account.organization_id, run.application_id,
> work_unit.script, work_unit.id, work_unit.start_time, run.id,
> work_unit.priority  FROM work_unit, run, account  WHERE  work_unit.status =
> 3 AND work_unit.run_id = run.id AND work_unit.type != 1 AND run.status = 1
> AND run.account_id = account.id
>
> Pls suggest if i can do something to fix this

Provide a lot more information if you want anyone on the list to be able
to help: such as explain output while the problem is happening, and some
information about the makeup of the tables (column types/indexes/# rows).

Guessing, based on the little information you've provided, it's likely
that you have something else going on at the same time that you're not
aware of, and this particular query is only a symptom.  I'm saying that
because SELECTs don't generally create any WAL traffic, so there were
probably some INSERT/UPDATE/DELETE running at the same time that both
pushed those 3 tables out of memory and/or saturated disk activity to
the point that accessing everything becomes slow for a short while, and
it's just those queries that you noticed.

Are you making the mistake where you set log_min_duration to 1s and only
worry about queries that take longer than 1s?  Because I've seen (on
multiple occasions) where many 1000s of queries, each running less than
1s, are the actual cause of the problem.  pgBadger is particularly helpful
in tracking down situations like that.

> On Thu, Jul 17, 2014 at 11:06 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> > Potentialtech <wmoran@potentialtech.com> writes:
> > > If the warning isn't happening too often, I would try increasing it only
> > a
> > > little and see if it helps.  If it's not enough you can then increase it
> > some
> > > more.  Various sources around the Internet suggest that you don't want
> > to go
> > > much larger than 256 for this (if only because it's uncommon to do so
> > and is
> > > probably indicative of other tuning that you need to do).
> >  Unfortunatley, you
> > > need to restart PG for the change to take effect, so you have to balance
> > > experimenting with your tuning against how often you can get away with a
> > server
> > > restart.
> >
> > Huh?  You don't need a restart, just a reload (SIGHUP) to change that.
> >
> >                         regards, tom lane
> >


--
Potentialtech <wmoran@potentialtech.com>

Here is the explain plan. There query time went backup up to 2 to 3 minutes from 3 second  in just 2 hrs.  Can anyone suggest something on how to fix this or why this is happening

 explain SELECT account.id, account.organization_id, run.application_id, work_unit.script, work_unit.id, work_unit.start_time, run.id, work_unit.priority  FROM work_unit, run, account  WHERE  work_unit.status = 3 AND work_unit.run_id = run.id AND work_unit.type != 1 AND run.status = 1 AND run.account_id = account.id;

-- 
john r pierce                                      37N 122W
somewhere on the middle of the left coast

SELECT account.id, account.organization_id, run.application_id, work_unit.script, work_unit.id, work_unit.start_time, run.id, work_unit.priority  FROM work_unit, run, account  WHERE  work_unit.status = 3 AND work_unit.run_id = run.id AND work_unit.type != 1 AND run.status = 1 AND run.account_id = account.id

-- 
john r pierce                                      37N 122W
somewhere on the middle of the left coast

i just did explain analyze and currently database is running slow coz of the query

explain ANALYZE SELECT account.id, account.organization_id, run.application_id, work_unit.script, work_unit.id, work_unit.start_time, run.id, work_unit.priority  FROM work_unit, run, account  WHERE  work_unit.status = 3 AND work_unit.run_id = run.id AND work_unit.type != 1 AND run.status = 1 AND run.account_id = account.id;

-- 
john r pierce                                      37N 122W
somewhere on the middle of the left coast

On 7/17/2014 5:36 PM, Prabhjot Sheena wrote:
> The overall load of system is good. It is a vm but the other database
> is running normal and not doing much activity


other database?  is that on the same postgresql cluster?   or on a
seperate instance of the postgres server on the same VM?  or on a
seperate VM on the same host ?

what does `iostat -x 5` show ?   let it run a bunch of samples, then run
this slow query and get more samples.   pay attention to the disk
drive(s) the database is stored on.

high %utilization or await times would be a bad thing.

--
john r pierce                                      37N 122W
somewhere on the middle of the left coast

Prabhjot Sheena <prabhjot.sheena@rivalwatch.com> wrote:

> I m running postgresql 8.3

That is out of support.  If you are at all concerned about
performance, you would do well to upgrade to a recent and
supported major release.

http://www.postgresql.org/support/versioning/

Anyway, it is always a good idea to show the output of:

SELECT version();

> temp_buffers = 64MB

You don't say what you have set for max_connections, but
temp_buffers is allocated at need *separately for each connection*
and once allocated to a connection it is never released until the
connection is closed.  So, hypothetically, if you had 100
connections open and they each had used temporary tables at some
time or other, even when all are idle they could have 6.4GB of
your 12GB of RAM tied up.

> work_mem = 512MB

Unlike temp_buffers, this one is released at the end of the
command (or earlier), but it is possible to have multiple
allocations for a single connection.  If you had 100 queries
active, each using one work_mem allocation, that would allocate
about 51.2GB of RAM, which looks like it is more than you have.  I
generally recommend setting max_connections only to what is needed
(with a small reserve) and setting work_mem to 25% of machine (or
VM) RAM divided by max_connections.

On the face of it, neither of the above seems to be causing
problems at the time you ran vmstat, but they worry me.

> max_fsm_pages = 809600

Since the EXPLAIN ANALYZE output you later posted show estimates
which were off by several orders of magnitude, quite possibly
leading to a suboptimal plan, you should probably run queries to
check for bloat and update statistics.  If you find badly bloated
tables you should run CLUSTER or VACUUM FULL on them to eliminate
bloat.  Any bloated indexes, and any indexed on bloated tables you
cleaned up using VACUUM FULL should be fixed with REINDEX.  (Once
you are on 9.0 or later it is no longer necessary to REINDEX a
table after using VACUUM FULL on it, although a regular VACUUM
after the VACUUM FULL is still important.)  If you find bloat you
should make autovacuum more aggressive or add VACUUM ANALYZE
crontab jobs, to prevent a recurrence.

You may want to look at increasing statistics targets, either
globally or on specific columns used for selection criteria.

After doing the above, or if you decide not to do any or all of it,
you should probably run EXPLAIN ANALYZE VERBOSE; (without
specifying a table name and without the FULL option) and review the
output.  The most important part of the output is the last few
lines, which in 8.3 might include warnings that suggest
configuration changes.

The above might help, but I think the biggest problem may be your
VM.  You show very low disk I/O numbers, but a pretty significant
fraction of the time waiting for I/O.  The previously-suggested
iostat output may help nail it down more specifically, but
basically you seem to have a big problem with bandwidth for storage
access.  It's pretty disturbing to see lines in vmstat output which
show zero disk in or out, but over 10% of CPU time waiting for
storage?!?

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 7/21/2014 1:51 PM, Kevin Grittner wrote:
> The above might help, but I think the biggest problem may be your
> VM.  You show very low disk I/O numbers, but a pretty significant
> fraction of the time waiting for I/O.  The previously-suggested
> iostat output may help nail it down more specifically, but
> basically you seem to have a big problem with bandwidth for storage
> access.  It's pretty disturbing to see lines in vmstat output which
> show zero disk in or out, but over 10% of CPU time waiting for
> storage?!?

that would suggest to me a large number of VMs sharing a single SATA
drive, or similar.



--
john r pierce                                      37N 122W
somewhere on the middle of the left coast

On 7/25/2014 5:58 PM, Prabhjot Sheena wrote:
> We do have i/o problem during heavy loads because we share mount point
> from netapp across different machines which we are getting rid in few
> weeks by moving stuff to Amazon aws and than IO issues will go away
> with fast iops

Netapp has to be some of the most expensive storage I've ever seen in a
datacenter and is frequently a source of performance problems.   NFS
isn't really appropriate for storing database data on, there's way too
many opportunites for fsync to get lost, more so if you've got a
virtualization layer in there too.





--
john r pierce                                      37N 122W
somewhere on the middle of the left coast

Prabhjot Sheena <prabhjot.sheena@rivalwatch.com> wrote:

> PostgreSQL 8.3.7 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 3.4.6 20060404 (Red Hat 3.4.6-9)

Running anything on that version is insane.  Not only has the 8.3
major release been out of support since February of 2013, but
you're missing about 4 years of fixes for serious bugs and security
issues since the last patch version of 8.3 before it went out of
support.  Any vacuum or planning problems you are seeing on that
release are very likely fixed on a supported version.

> This is what i did to improve query performance. i recreated all
> the indexes on work_unit table and have been running vacuum
> analyze through cron job 3 times a day on two tables that are in
> the query. The query performance is between 2 to 3 seconds now.

That's pretty solid evidence that you have been having problems
with bloat.  The most common cause of this is not setting
autovacuum to be aggressive enough.

> autovacuum_max_workers = 1

Vacuum of one big table could starve all other tables, resulting in
bloat.  If anything, this should be set larger than the default.
You might want to try setting it to somewhere in the 3 to 5 range,
but then watch for any long periods where all workers are busy.  If
you see that, you probably need more workers.  I would also boost
autovacuum_vacuum_cost_limit to maybe 500.

> The strange thing i noticed is that just today at one time query
> performance came down to under 1 second and started using this
> query plan

There's really no point hypothesizing about what might cause that
in such an old version with so many known bugs.  The best way to
improve performance would be to upgrade.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company