gmane.comp.db.postgresql.performance

Re: statistics target for columns in unique constraint?

Kevin Grittner — 2013-05-20T14:12:20


Since the statistics are just a random sampling and generally not
completely up-to-date, they really can't be used for anything other
than *estimating* relative costs in order to try to pick the best
plan.  Once a plan is chosen, its execution time is not influenced
by the statistics.  A higher statistics target can increase
planning time.  In a complex query with many joins and many indexes
on the referenced tables, the increase in planning time can be
significant.  I have seen cases where blindly increasing the
default statistics target resulted in planning time which was
longer than run time -- without any increase in plan quality.

Generally when something is configurable, it's because there can be
benefit to adjusting it.  If there was a single setting which could
not be materially improved upon for some cases, we wouldn't expose
a configuration option.  This is something which is not only
globally adjustable, you can override the setting for individual
columns -- again, we don't go to the trouble of supporting that
without a good reason.

Re: Hardware suggestions for maximum read performance

Scott Marlowe — 2013-05-20T03:57:32

This++ plus cluster on that index if you can.

Re: Reliability with RAID 10 SSD and Streaming Replication

Greg Smith — 2013-05-20T03:10:08

If it can keep up with replay from the faster master, that sounds like a 
decent backup.  Make sure you setup all write caches very carefully on 
that system, because it's going to be your best hope to come back up 
quickly after a real crash.

Any vendor that pushes Samsung 840 drives for database use should be 
ashamed of themselves.  Those drives are turning into the new 
incarnation of what we saw with the Intel X25-E/X-25-M:  they're very 
popular, but any system built with them will corrupt itself on the first 
failure.   I expect to see a new spike in people needing data recovery 
help after losing their Samsung 840 based servers start soon.


That's a useful plan.  Just make sure you ship new base backups fairly 
often.  If you have to fall back to that copy of the data, you'll need 
to replay anything that's happened since the last base backup happened. 
  That can easily result in a week of downtime if you're only shipping 
backups once per month, for example.

Re: Reliability with RAID 10 SSD and Streaming Replication

Greg Smith — 2013-05-20T03:00:59

That's not true at all.  Any decent RAID controller will have an option 
to stop write-back caching when the battery is bad.  Things will slow 
badly when that happens, but there is zero data risk from a short-term 
BBU failure.  The only serious risk with a good BBU setup are that 
you'll have a power failure lasting so long that the battery runs down 
before the cache can be flushed to disk.

Re: Deleting Rows From Large Tables

Greg Smith — 2013-05-20T02:55:28

I've had jobs like this where we ended up making the batch size cover 
only 4 hours at a time.  Once you've looked at the EXPLAIN plans for the 
row selection criteria and they're reasonable, dropping the period 
that's deleted per pass is really the only thing you can do.  Do some 
DELETEs, then pause to let the disk cache clear; repeat.

The other useful thing to do here is get very aggressive about settings 
for shared_buffers, checkpoint_segments, and checkpoint_timeout.  I'll 
normally push for settings like 8GB/256/15 minutes when doing this sort 
of thing.  The usual situation with a checkpoint every 5 minutes may not 
be feasible when you've got this type of work going on in the background.

Re: Hardware suggestions for maximum read performance

Greg Smith — 2013-05-20T02:44:16

Have you tried putting an index by datavalue on this table?  Once you've 
done that, then changing random_page_cost will make using that index 
look less expensive.  Sorting chews through a good bit of CPU time, and 
that's where all of your runtime is being spent at--once you increase 
work_mem up very high that is.


You're trying to fix a fundamental design issue with hardware.  That 
usually doesn't go well.  Once you get a box big enough to hold the 
whole database in RAM, beyond that the differences between server 
systems are relatively small.

Re: Reliability with RAID 10 SSD and Streaming Replication

Cuong Hoang — 2013-05-20T02:41:32

Thanks for suggestion Tomas. We're about to set up WAL backup to Amazon S3.
I think this should cover all of our bases. At least for the moment,
SAS-based standby seems to keep up with the master because that's its sole
purpose. We're not sending queries to the hot standby. We also consider
switching the hot standby to fast failover as you suggested. I guess for
now we should stick to streaming replication because the slave is still
keeping up with the master.

Btw, after switching to SSD, performance improves vastly. IO utilisation
drops from 100% to 6% in peak periods. That's an order of magnitude faster!

Cheers,
Cuong


On Mon, May 20, 2013 at 8:34 AM, Tomas Vondra <tv< at >fuzzy.cz> wrote:

Re: Reliability with RAID 10 SSD and Streaming Replication

Tomas Vondra — 2013-05-19T22:34:50

Do you really need a running standby for fast failover? What about doing
plain WAL archiging? I'd definitely consider that, because even if you
setup a SAS-based replica, you can't use it for production as it does no
handle the load.

I think you could setup WAL archiving and in case of crash just use the
base backup and replay the WAL from the archive.

This means the SAS-based system is purely for WAL archiving, i.e.
performs only sequential writes which should not be a big deal.

The recovery will be performed on the SSD system, which should handle it
fine. If you need faster recovery, you may perform it incrementally on
the SAS system (it will take some time, but it won't influence the
master). You might do that daily or something like that.

The only problem with this is that this is file based, and could mean
lag (up to 16MB or archive_timeout). But this should not be problem if
you place the WAL on SAS drives with controller. If you use RAID, you
should be perfectly fine.

So this is what I'd suggest:

  1) use SSD for data files, SAS RAID1 for WAL on the master
  2) setup WAL archiving (base backup + archive on SAS system)
  3) update the base backup daily (incremental recovery)
  4) in case of crash, keep WAL from the archive and pg_xlog on the
     SAS RAID (on master)


Tomas


On 17.5.2013 02:21, Cuong Hoang wrote:

Re: Reliability with RAID 10 SSD and Streaming Replication

Tomas Vondra — 2013-05-19T22:15:23

The master waits for reception of the data, not writing them to the
disks. It will have to write them eventually (and that might cause
issues), but I'm not really sure it's that simple.


Don't forget the slave does not perform all the I/O (searching for the
row etc.). It's difficult to say how much this will save, though.

Tomas

Re: Deleting Rows From Large Tables

Greg Spiegelberg — 2013-05-19T21:14:07

Rob,

I'm going to make half of the list cringe at this suggestion though I have
used it successfully.

If you can guarantee the table will not be vacuumed during this cleanup or
rows you want deleted updated, I would suggest using the ctid column to
facilitate the delete.  Using the simple transaction below, I have
witnessed a DELETE move much more quickly than one using a PK or any other
column with an index.

BEGIN;
SELECT ctid INTO TEMP TABLE ctids_to_be deleted FROM my_big_table WHERE *delete
criteria*;
DELETE FROM my_big_table bt USING ctids_to_be_deleted dels WHERE bt.ctid =
dels.ctid;
COMMIT;

HTH.
-Greg


On Fri, May 17, 2013 at 5:26 AM, Rob Emery <re-pgsql< at >codeweavers.net> wrote:

Re: Deleting Rows From Large Tables

Jeff Janes — 2013-05-19T20:36:50


How many Cores do you have?  I think the Dell 2950 could have anywhere from
1 to 8.

Pick a smaller number of rows to delete, and run it with "explain analyze"
to see what it is going on.  I would say to use "explain (analyze,
buffers)" with track_io_timing on, but those don't exist back in 8.4.

Perhaps this would be a good excuse to upgrade!

If I run a sustained (more than about 5 minutes) delete it'll have a



Do you know why?  Can you identify the affected queries from those other
services and run explain analyze on them?







If your server is sized only to do its typical workload, then any
substantial extra work load is going to cause problems.  Trying to delete 1
day's work in a few seconds stills seems like it is very likely excessive.
 Why not jump all the way down to 5 minutes, or limit it to a certain
number of rows from table a, say 100 per unit?  If you start large and work
your way down, you will often be working in the dark because you won't have
the patience to let the large ones run to completion, slowing down the
whole system.  If you start at the bottom and work up, you will always know
where you are as the previous one ran to completion and you have the
timings from it.

How fast do you need to clean this up?  If it took months to get into the
situation, can't you take a few weeks to get out of it?




I'd probably add a "LIMIT 100" in there.  Then you can set created_at to
the final time point desired, rather than trying to increment it each time
and deciding how much to increment.



Do these to queries slow down other operations?  Or is it just the deletes?



How much time to do the 3 deletes take relative to each other and to the
inserts?

Cheers,

Jef

Re: PostgreSQL 9.2.3 performance problem caused Exclusive locks

jonranes — 2013-05-19T04:40:04

I am having the same exact problems.  I reduced shared buffers as that seems
to have done the trick for now in this thread.  If things improve I'll post
back and confirm.



--
View this message in context: http://postgresql.1045698.n5.nabble.com/PostgreSQL-9-2-3-performance-problem-caused-Exclusive-locks-tp5747909p5756113.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.

Re: Slow CTE Query

Sergey Konoplev — 2013-05-18T21:44:34

Rewrite it without CTE. Planner will have more freedom in this case.
Also I would try to use LEFT JOIN ... IS NULL technique instead of NOT
EXISTS.




--
Kind regards,
Sergey Konoplev
PostgreSQL Consultant and DBA

Profile: http://www.linkedin.com/in/grayhemp
Phone: USA +1 (415) 867-9984, Russia +7 (901) 903-0499, +7 (988) 888-1979
Skype: gray-hemp
Jabber: gray.ru< at >gmail.com

Slow CTE Query

Stefan Keller — 2013-05-18T19:54:45

Hi,

I'm experiencing a very slow CTE query (see below).

When I split the three aggregations into three separate views, its' decent
fast. So I think it's due to the planner.

Any ideas like reformulating the query?

These are the tables and views involved:
* Table promotion with start/end date and a region, and table
promo2mission (each 1 to dozen tupels).
* View  all_errors (more than 20'000 tubles, based on table errors
without tupels from table fix)
* Table error_type (7 tupels)

Here's the EXPLAIN ANALYZE log: http://explain.depesz.com/s/tbF

Yours, Stefan


CTE Query:

WITH aggregation1
     AS (SELECT p.id                   AS promo_id,
                p.startdate,
                p.enddate,
                p.geom                 AS promogeom,
                pm.error_type,
                pm.mission_extra_coins AS extra_coins
         FROM   (promotion p
                 join promo2mission pm
                   ON (( p.id = pm.promo_id )))
         WHERE  ( ( p.startdate <= Now() )
                  AND ( p.enddate >= Now() ) )),
     aggregation2
     AS (SELECT e.error_id     AS missionid,
                e.schemaid,
                t.TYPE,
                e.osm_id,
                e.osm_type,
                t.description  AS title,
                t.view_type,
                t.answer_placeholder,
                t.bug_question AS description,
                t.fix_koin_count,
                t.vote_koin_count,
                e.latitude,
                e.longitude,
                e.geom         AS missiongeom,
                e.txt1,
                e.txt2,
                e.txt3,
                e.txt4,
                e.txt5
         FROM   all_errors e,
                error_type t
         WHERE  ( ( e.error_type_id = t.error_type_id )
                  AND ( NOT ( EXISTS (SELECT 1
                                      FROM   fix f
                                      WHERE  ( ( ( ( f.error_id = e.error_id )
                                                   AND ( f.osm_id =
e.osm_id ) )
                                                 AND ( ( f.schemaid ) :: text =
                                                     ( e.schemaid ) :: text ) )
                                               AND ( ( f.complete
                                                       AND f.valid )
                                                      OR ( NOT
                f.complete ) ) )) ) ) )),
     aggregation3
     AS (SELECT ag2.missionid AS missionidtemp,
                ag1.promo_id,
                ag1.extra_coins
         FROM   (aggregation2 ag2
                 join aggregation1 ag1
                   ON (( ( ag2.TYPE ) :: text = ( ag1.error_type ) :: text )))
         WHERE  public._st_contains(ag1.promogeom, ag2.missiongeom))
SELECT ag2.missionid AS id,
       ag2.schemaid,
       ag2.TYPE,
       ag2.osm_id,
       ag2.osm_type,
       ag2.title,
       ag2.description,
       ag2.latitude,
       ag2.longitude,
       ag2.view_type,
       ag2.answer_placeholder,
       ag2.fix_koin_count,
       ag2.missiongeom,
       ag2.txt1,
       ag2.txt2,
       ag2.txt3,
       ag2.txt4,
       ag2.txt5,
       ag3.promo_id,
       ag3.extra_coins
FROM   (aggregation2 ag2
        left join aggregation3 ag3
               ON (( ag2.missionid = ag3.missionidtemp )));

(unknown)

Stefan Keller — 2013-05-18T19:50:15

Hi,

I'm experiencing a very slow CTE query (see below).

When I split the three aggregationns into separate views, its' decent
fast. So I think it's due to the planner.

Any ideas how to reformulate the query?

These are the tables and views involved:
* Table promotion with start/end date and a region, and table
promo2mission (each 1 to dozen tupels).
* View  all_errors (more than 20'000 tubles, based on table errors
without tupels from table fix)
* Table error_type (7 tupels)

Here's the EXPLAIN ANALYZE log: http://explain.depesz.com/s/tbF

Yours, Stefan


CTE Query:

WITH aggregation1
     AS (SELECT p.id                   AS promo_id,
                p.startdate,
                p.enddate,
                p.geom                 AS promogeom,
                pm.error_type,
                pm.mission_extra_coins AS extra_coins
         FROM   (promotion p
                 join promo2mission pm
                   ON (( p.id = pm.promo_id )))
         WHERE  ( ( p.startdate <= Now() )
                  AND ( p.enddate >= Now() ) )),
     aggregation2
     AS (SELECT e.error_id     AS missionid,
                e.schemaid,
                t.TYPE,
                e.osm_id,
                e.osm_type,
                t.description  AS title,
                t.view_type,
                t.answer_placeholder,
                t.bug_question AS description,
                t.fix_koin_count,
                t.vote_koin_count,
                e.latitude,
                e.longitude,
                e.geom         AS missiongeom,
                e.txt1,
                e.txt2,
                e.txt3,
                e.txt4,
                e.txt5
         FROM   all_errors e,
                error_type t
         WHERE  ( ( e.error_type_id = t.error_type_id )
                  AND ( NOT ( EXISTS (SELECT 1
                                      FROM   fix f
                                      WHERE  ( ( ( ( f.error_id = e.error_id )
                                                   AND ( f.osm_id =
e.osm_id ) )
                                                 AND ( ( f.schemaid ) :: text =
                                                     ( e.schemaid ) :: text ) )
                                               AND ( ( f.complete
                                                       AND f.valid )
                                                      OR ( NOT
                f.complete ) ) )) ) ) )),
     aggregation3
     AS (SELECT ag2.missionid AS missionidtemp,
                ag1.promo_id,
                ag1.extra_coins
         FROM   (aggregation2 ag2
                 join aggregation1 ag1
                   ON (( ( ag2.TYPE ) :: text = ( ag1.error_type ) :: text )))
         WHERE  public._st_contains(ag1.promogeom, ag2.missiongeom))
SELECT ag2.missionid AS id,
       ag2.schemaid,
       ag2.TYPE,
       ag2.osm_id,
       ag2.osm_type,
       ag2.title,
       ag2.description,
       ag2.latitude,
       ag2.longitude,
       ag2.view_type,
       ag2.answer_placeholder,
       ag2.fix_koin_count,
       ag2.missiongeom,
       ag2.txt1,
       ag2.txt2,
       ag2.txt3,
       ag2.txt4,
       ag2.txt5,
       ag3.promo_id,
       ag3.extra_coins
FROM   (aggregation2 ag2
        left join aggregation3 ag3
               ON (( ag2.missionid = ag3.missionidtemp )));

Re: statistics target for columns in unique constraint?

ach — 2013-05-18T15:35:51

Thanks guys!  I'm gonna try tuning the statistics back down to 10 on that
table and see what that does to the insertion rates.  Oh and for Mark:  Not
to worry - i'd actually tuned the stats there up myself awhile ago in an
experiment to see if -that- would've sped insertions some; back before i'd
had enough mileage on postgres for it to have occurred to me that might just
have been useless ;-)

One quick follow up since I'm expecting y'all might know:  Do the statistics
targets actually speed performance on an index search itself; the actual
lookup?  Or are the JUST to inform the planner towards the best pathway
decision?  In other words if I have statistics set to 1000, say, in one
case, and the planner chose the exact same path it would have if they'd just
been set to 100, would the lookup return faster when the stats were at 1000? 
Or would it actually take the same time either way?  My hunch is it's the
latter...




--
View this message in context: http://postgresql.1045698.n5.nabble.com/statistics-target-for-columns-in-unique-constraint-tp5755256p5756093.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.

Re: Deleting Rows From Large Tables

Vitalii Tymchyshyn — 2013-05-18T07:15:26

Analyze your temp tables after filling and before using!
17 трав. 2013 17:27, "Sékine Coulibaly" <scoulibaly< at >gmail.com> напис.

Re: Deleting Rows From Large Tables

Sékine Coulibaly — 2013-05-17T14:26:00

Oh, sorry, overlooked that part.
Maybe refreshing stats with VACUUM FULL ?


2013/5/17 Robert Emery <robertemery< at >codeweavers.net>

Re: Reliability with RAID 10 SSD and Streaming Replication

Merlin Moncure — 2013-05-17T13:19:17


whoops -- misread your post heh (you were suggesting to use classic
hard drives).  yeah, that might work but it only buys you so much
particuarly if there is a lot of random activity in the heap.

merlin

Re: Reliability with RAID 10 SSD and Streaming Replication

Merlin Moncure — 2013-05-17T13:17:52

That is not going to help.  Since the drives lie about fsync, upon a
power event you must assume the database is corrupt.  I think his
proposed configuration is the best bet (although I would strongly
consider putting SSD on the standby as well).   Personally, I think
non SSD drives are obsolete for database purposes and will not
recommend them for any configuration.  Ideally though, OP would be
using S3700 and we wouldn't be having this conversation.

merlin

Deleting Rows From Large Tables

Rob Emery — 2013-05-17T11:26:11

Hi All,

We've got 3 quite large tables that due to an unexpected surge in
usage (!) have grown to about 10GB each, with 72, 32 and 31 million
rows in. I've been tasked with cleaning out about half of them, the
problem I've got is that even deleting the first 1,000,000 rows seems
to take an unreasonable amount of time. Unfortunately this is on quite
an old server (Dell 2950 with a RAID-10 over 6 disks) running Postgres
8.4; which serves other things like our logging systems.

If I run a sustained (more than about 5 minutes) delete it'll have a
detrimental effect on the other services. I'm trying to batch up the
deletes into small chunks of approximately 1 month of data ; even this
seems to take too long, I originally reduced this down to a single
day's data and had the same problem. I can keep decreasing the size of
the window I'm deleting but I feel I must be doing something either
fundamentally wrong or over-complicating this enormously. I've
switched over to retrieving a list of IDs to delete, storing them in
temporary tables and deleting based on the primary keys on each of the
tables with something similar to this:

BEGIN TRANSACTION;

CREATE TEMPORARY TABLE table_a_ids_to_delete (id INT);
CREATE TEMPORARY TABLE table_b_ids_to_delete (id INT);

INSERT INTO table_a_ids_to_delete
    SELECT id FROM table_a WHERE purchased ='-infinity' AND created_at
< '2007-01-01T00:00:00';

INSERT INTO table_b_ids_to_delete
    SELECT table_b_id FROM table_a_table_b_xref
    INNER JOIN table_a_ids_to_delete ON (table_a_ids_to_delete.id =
table_a_table_b.quote_id);

DELETE FROM table_a_table_b_xref USING table_a_ids_to_delete
    WHERE table_a_table_b_xref.table_a_id = table_a_ids_to_delete.id;

DELETE FROM table_b USING table_b_ids_to_delete
    WHERE table_b.id = table_b_ids_to_delete.id;

DELETE FROM table_a USING table_a_ids_to_delete
    WHERE table_a.id =  table_a_ids_to_delete.id;

COMMIT;

There're indices on table_a on the queried columns, table_b's primary
key is it's id, and table_a_table_b_xref has an index on (table_a_id,
table_b_id). There're FK defined on the xref table, hence why I'm
deleting from it first.

Does anyone have any ideas as to what I can do to make the deletes any
faster? I'm running out of ideas!

Thanks in advance,

--
Rob Emery