[PERFORM] How does max_parallel_workers_per_gather change load averages?

[Ben Nachtrieb]

Hello,

This is my first question on this list.

How does max_parallel_workers_per_gather change Linux server load averages?

I have 2 cores and my max_parallel_workers_per_gather = 2 and 
max_worker_processes = 8, but my load averages are between 8 and 5 with 
scheduled at 1/189 to 5/195. Are these so high because I increased 
max_parallel_workers_per_gather? My understanding is that if my load averages 
are greater than my number of cores the system is overloaded. Should I think 
about it differently once I increase max_parallel_workers_per_gather? How 
should I think about it?

I am using postgres 9.6.

Re: [PERFORM] Possible to find disk IOs for a Query?

[Ben Chobot]

On Aug 31, 2016, at 3:01 PM, Bobby Mozumder  wrote:
> 
> Is it possible to find the number of disk IOs performed for a query?  EXPLAIN 
> ANALYZE looks like it shows number of sequential rows scanned, but not number 
> of IOs.  

Postgres knows the number of rows it will need to pull to do your query, but it 
has no way of knowing if a block not in its own cache can be satisfied via 
filesystem cache, or if it will fall through to disk read. If you are on linux, 
you might be able to tell the effectiveness of your filesystem cache via 
something like 
http://www.brendangregg.com/blog/2014-12-31/linux-page-cache-hit-ratio.html 


…but that's hardly going to show you something as granular as a per-query cost.

Re: [PERFORM] VACUUM VERBOSE ANALYZE taking long time to process.

[Ben Chobot]

On Sep 9, 2015, at 3:43 AM, anil7385  wrote:
> 
> Hi, 
> We have a table which consists of 3 millions of records and when we try to
> delete them and run VACUUM VERBOSE ANALYZE on it in production environment ,
> it takes 6/7 hours to process.

You make it sound like you are deleting all records. If that's true, why not 
TRUNCATE? It'll be a lot faster, and also leave you with no need to vacuum.

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Query planner not using indexes with JOIN query and OR clause

[Ben Hoyt]

 Try refactoring to:

 select ai.position, i.filename as image_filename, p.filename as
 panorama_filename
 from album_items ai
 left join image2 i on i.imageid = ai.image_id
 left join panoramas p on p.id = ai.panorama_id
 where i.filename in ('pano360--v471.jpg', 'pano360-2--v474.jpg')
 union all select ai.position, i.filename as image_filename, p.filename
 as panorama_filename
 from album_items ai
 left join image2 i on i.imageid = ai.image_id
 left join panoramas p on p.id = ai.panorama_id
 where  p.filename in ('pano360--v471', 'pano360-2--v474')

 ...and see if that helps.  Dealing with 'or' conditions is a general
 weakness of the planner that has gotten better over time but in some
 cases you have to boil it to 'union all'.


Yes, this definitely helps and the query performance goes back to normal,
thanks. It makes the code a bit more complicated, so not ideal, but
definitely works!

Thanks for the help. I don't how much you know about PostgreSQL internals
(I don't!), but what optimization would need to be in place for PostgreSQL
to be smarter about this query?

-Ben

[PERFORM] Query planner not using indexes with JOIN query and OR clause

[Ben Hoyt]

 NOT NULL,
  width integer,
  height integer,
  active boolean NOT NULL DEFAULT false,
  importid integer,
  timetaken timestamp without time zone,
  state integer NOT NULL DEFAULT 1,
  has_wide boolean NOT NULL DEFAULT false,
  type integer,
  document_id integer,
  property_id integer,
  CONSTRAINT image2_pkey PRIMARY KEY (imageid),
  CONSTRAINT fk_image2_hotelid FOREIGN KEY (hotelid) REFERENCES
content.hotel (hotelid),
  CONSTRAINT fk_image2_importid FOREIGN KEY (importid) REFERENCES
content.imageimport (importid),
  CONSTRAINT image2_document_id_fkey FOREIGN KEY (document_id) REFERENCES
content.documents (id),
  CONSTRAINT image2_property_id_fkey FOREIGN KEY (property_id) REFERENCES
content.properties (id),
  CONSTRAINT uq_image2_filename UNIQUE (filename)
);

CREATE INDEX fki_image2_property_id_fkey ON content.image2 (property_id);
CREATE INDEX image2_document_id_idx ON content.image2 (document_id);
CREATE INDEX image2_importid_idx ON content.image2 (importid);
CREATE INDEX ix_image2_hotelid ON content.image2 (hotelid);
CREATE INDEX ix_image2_imageid ON content.image2 (imageid);


CREATE TABLE content.panoramas
(
  id integer NOT NULL DEFAULT nextval('panoramas_id_seq'::regclass),
  hotel_id integer,
  filename text NOT NULL,
  folder text NOT NULL,
  import_id integer NOT NULL,
  active boolean NOT NULL DEFAULT false,
  state integer NOT NULL,
  num_images integer NOT NULL DEFAULT 0,
  type integer,
  hdr boolean NOT NULL DEFAULT false,
  has_preview boolean NOT NULL DEFAULT false,
  property_id integer,
  data json,
  previews_created boolean NOT NULL DEFAULT false,
  CONSTRAINT panoramas_pkey PRIMARY KEY (id),
  CONSTRAINT fk_panoramas_hotel_id FOREIGN KEY (hotel_id) REFERENCES
content.hotel (hotelid),
  CONSTRAINT fk_panoramas_import_id FOREIGN KEY (import_id) REFERENCES
content.imageimport (importid),
  CONSTRAINT panoramas_property_id_fkey FOREIGN KEY (property_id)
REFERENCES content.properties (id),
  CONSTRAINT panoramas_uq_filename UNIQUE (filename)
);

CREATE INDEX fki_panoramas_property_id_fkey ON content.panoramas
(property_id);
CREATE INDEX panoramas_hotel_id_idx ON content.panoramas (hotel_id);
CREATE INDEX panoramas_import_id_idx ON content.panoramas (import_id);
--

Thanks in advance,
Ben

Re: [PERFORM] Re: Adding foreign key constraint holds exclusive lock for too long (on production database)

[Ben Hoyt]

Thanks, Tom (and David and Josh).

 Well, apparently nobody who knows the code was paying attention, because
 that hasn't been true for some time.  ALTER TABLE ADD FOREIGN KEY will
 actually validate the constraint using a query constructed like this
 (cf RI_Initial_Check() in ri_triggers.c):

This was a very helpful pointer, and interesting to me, because I did
a quick look for the source that handled that but didn't find it (not
knowing the Postgres codebase at all). It was kinda weird to me at
first that the way it implements this is by building an SQL string and
then executing that -- at first I would have thought it'd call the
internal functions to do the job. But on second thoughts, this makes
total sense, as that way it gets all the advantages of the query
planner/optimizer for this too.

 It appears the possible explanations for Ben's problem are:

 1. For some reason this query is a lot slower than the one he came up
 with;

 2. The code isn't using this query but is falling back to a row-at-a-time
 check.

Anyway, it's definitely #1 that's happening, as I build the
RI_Initial_Check() query by hand, and it takes just as long as the ADD
CONSTRAINT.

I'll probably hack around it -- in fact, for now I've just dropped the
contraint entirely, as it's not really necessary on this table.

So I guess this is really a side effect of the quirky way we're
dumping and restoring only one schema, and dropping/re-adding
constraints on deployment because of this. Is this a really strange
thing to do -- deploying only one schema (the static data) and
dropping/re-adding constraints -- or are there better practices here?

Relatedly, what about best practices regarding inter-schema foreign keys?

-Ben


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Re: Adding foreign key constraint holds exclusive lock for too long (on production database)

[Ben Hoyt]

Hmm, weird -- now the RI_Initial_Check() query is much quicker (20s). We do
ANALYZE the data every few nights, so maybe that's what changed it. I'll
keep that in mind. -Ben


On Fri, Nov 1, 2013 at 3:19 AM, Tom Lane t...@sss.pgh.pa.us wrote:

 Ben Hoyt benh...@gmail.com writes:
  It appears the possible explanations for Ben's problem are:
  1. For some reason this query is a lot slower than the one he came up
  with;

  Anyway, it's definitely #1 that's happening, as I build the
  RI_Initial_Check() query by hand, and it takes just as long as the ADD
  CONSTRAINT.

 Huh.  Maybe an optimizer failing?  Could we see the full text of both
 queries and EXPLAIN ANALYZE results for them?

  So I guess this is really a side effect of the quirky way we're
  dumping and restoring only one schema, and dropping/re-adding
  constraints on deployment because of this. Is this a really strange
  thing to do -- deploying only one schema (the static data) and
  dropping/re-adding constraints -- or are there better practices here?

 Doesn't seem unreasonable.  One thought is that maybe you need to insert a
 manual ANALYZE after reloading the data?

 regards, tom lane

[PERFORM] Adding foreign key constraint holds exclusive lock for too long (on production database)

[Ben Hoyt]

Hi folks,

We're adding a foreign key constraint to a 20-million row table on our
production database, and it's taking about 7 minutes. Because it's an
ALTER TABLE, Postgres acquires an ACCESS EXCLUSIVE lock that prevents
any reads/writes (though this particular table is very write-heavy, so
even a read lock wouldn't help here).

For context: we do this whenever we deploy our site, because our
database is split across two schemas (live and content), and the
content schema we dump from our office database and restore into our
production database. To achieve this we restore it as contentnew
into the production db, then rename the content schema to
contentold and the contentnew schema to content.

This completes the actual deployment, however, now our live-to-content
foreign keys are pointing to contentold, so the final step is to go
through and drop all the live-to-content foreign keys and recreate
them (against the new content schema). Most of the tables are small
and re-adding the constraint is quick, except for this one table,
which is 20M rows and basically pauses our live website for 7 minutes.

A couple of questions about the ADD CONSTRAINT. The foreign key column
on the local table is indexed, and there are only ~50 unique values,
so the db *could* come up with the unique values pretty quickly and
then check them. Or, even if it needs to do a full scan of the 20M-big
table (ratesrequests) and join with the referenced table
(provider) on the foreign key, which is I think the most it should
have to do to check the foreign key, the following query only takes
~20s, not 7 minutes:

select p.name
from ratesrequests r
join provider p on r.providerid = p.providerid

I'm guessing the ADD CONSTRAINT logic bypasses some of the query
optimization used for SELECT queries. So I suppose my questions are:

1) Are there ways to speed up adding the constraint? Just speeding it
up a little bit won't really help -- for this purpose it'll need to be
an order of magnitude or so. I'm aware of a couple of possibilities:

a) Upgrade to Postgres 9.1 and use ADD CONSTRAINT NOT VALID. However,
this doesn't really help, as you need to run VALIDATE CONSTRAINT at
some later stage, which still grabs the exclusive lock.

b) Delete old rows from the table so it's not so big. Feels a bit
hacky just to fix this issue.

c) Get rid of this foreign key constraint entirely and just check it
in code when we insert. Pragmatic solution, but not ideal.

2) Is there a better way to do the content schema dump/restore that
avoids dropping and recreating the inter-schema foreign keys?

Other notes and research:

* We're running PostgreSQL 9.0.2, compiled by Visual C++ build 1500,
64-bit on 64-bit Windows Server 2008 SP1 (6.0.6001)
* The ratesrequests table has two text columns, one of which often
contains a few hundred to a couple of KB of data in the field. It is
added to rapidly. We regularly VACCUM ANALYZE it.
* As expected, the ADD CONSTRAINT has gotten slower over time as this
table grew. However -- I'm not 100% sure of this, but it seems to have
jumped recently (from 3-4 minutes to 7 minutes).
* 
http://www.postgresql.org/message-id/20030323112241.w14634-100...@megazone23.bigpanda.com
-- indicates that ADD CONSTRAINT isn't optimized as well as it could
be
* http://www.postgresql.org/message-id/51a11c97.90...@iol.ie --
indicates that the db ignores the index when add constraints

Thanks,
Ben.


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] incorrect row estimates for primary key join

[Ben]

On Jun 26, 2013, at 5:22 PM, Marcin Mańk wrote:

 On Wed, Jun 26, 2013 at 2:29 AM, Ben midfi...@gmail.com wrote:
 
 shouldn't an index scan definitely be fastest here?  you don't need to touch 
 the whole table or index.  maybe there something i have misconfigured here?
 
 
 How about you try increasing work_mem ? I think a hash join may be the
 best plan here, and it won't get chosen with low work_mem .

i will increase work_mem and experiment for the other queries, but the query 
which i was asking about in this particular question was looking up the single 
smallest key in the primary key index, which seems like it shouldn't need to 
touch more than one key, since it can just get the first one from an in-order 
index traversal.  of course with my earlier bigtable/jointable join question 
increasing work_mem makes a lot of sense.

best regards, ben



-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] incorrect row estimates for primary key join

[Ben]

hello --

thanks kevin for the tuning advice, i will answer your questions below and try 
different tuning configurations and report back.  but first allow me take a 
step back and ask a couple simple questions :

it seems to me that an equality join between two relations (call them A and B) 
using columns in relation B with a unique constraint should yield row estimates 
which are at most equal to the row estimates for relation A.  my questions are

1 - is this correct?

2 - does the postgresql planner implement this when generating row estimates?

it seems like if the answers to 1 and 2 are yes, then the row estimates for my 
join should always come back less or equal to the estimates for jointable, 
regardless of what the query plan is.  indeed this is what i find 
experimentally for smaller examples.  what is perplexing to me is why this is 
not true for this large table.  (the fact that the table size is greater than 
2^31 is probably a red herring but hasn't escaped my attention.)  while i do 
have a performance issue (i'd like for it to select the index scan) which might 
be solved by better configuration, that at the moment is a secondary question 
-- right now i'm interested in why the row estimates are off.

moving on to your remarks :

On Jun 25, 2013, at 6:20 AM, Kevin Grittner wrote:

 Ben midfi...@gmail.com wrote:
 
 PostgreSQL 9.1.1 on x86_64-suse-linux-gnu, compiled by gcc (SUSE Linux) 
 4.6.2, 64-bit
 
 Consider applying the latest bug fixes for 9.1 -- which would leave
 you showing 9.1.9.

i will bring it up with our ops people.  do you have any particular fixes in 
mind, or is this a (very sensible) blanket suggestion?

 default_statistics_target| 50| configuration file
 
 Why did you change this from the default of 100?

sorry, i do not know.  it is possible this was copied from the configuration of 
a different server, which is serving some very very large tables with gist 
indexes, where the statistics do not help the selectivity estimations much if 
at all (as far as i can tell gist indexes often use hard-coded selectivity 
estimates as opposed to using the statistics.)  in that case it is an oversight 
and i will correct it.  but i believe the statistics for the tables in question 
are close enough, and certainly do not explain the off row estimates in the 
query plan.

 effective_cache_size| 5632MB| configuration file
 
 How much RAM is on this machine?  What else is running on it? 
 (Normally people set this to 50% to 75% of total RAM.  Lower values
 discourage index usage in queries like your example.)

24GB.  i can up it to 12 or 16GB and report back.

 Do you get a different plan if you set cpu_tuple_cost = 0.03?  How
 about 0.05?  You can set this just for a single connection and run
 explain on the query to do a quick check.

setting cpu_tuple_cost to 0.03 or 0.05 has no effect on the choice of plan or 
the row estimates for the un-limited query or the limited query.

best regards, ben



-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] incorrect row estimates for primary key join

[Ben]

On Jun 25, 2013, at 4:36 PM, Tom Lane wrote:

 That seems intuitive, but some of the estimates need to be made
 before all such information is available.  Maybe we can do
 something about that some day
 Maybe someone else will jump in here with more details than I can
 provide (at least without hours digging in the source code).
 
 It does not attempt to match up query WHERE clauses with indexes during
 selectivity estimation, so the existence of a multi-column unique
 constraint wouldn't help it improve the estimate.

thanks tom, that answered my question.

 In the case at hand, I doubt that a better result rowcount estimate
 would have changed the planner's opinion of how to do the join.  The OP
 seems to be imagining that 2 million index probes into a large table
 would be cheap, but that's hardly true.  It's quite likely that the
 mergejoin actually is the best way to do the query.  If it isn't really
 best on his hardware, I would think that indicates a need for some
 tuning of the cost parameters.  Another thing that might be helpful for
 working with such large tables is increasing work_mem, to make hashes
 and sorts run faster.

i apologize if i seemed like i was presuming to know what the best query plan 
is.  i fully understand that the query planner sometimes makes unintuitive 
decisions which turn out to be for the best, having experienced it first hand 
many times.  since i've nudged my company to use postgresql (instead of 
mysql/sqlite), we've been very happy with it.  also, having tried my hand (and 
failing) at making good gist selectivity estimators, i think i've got a 
not-completely-ignorant 10,000 ft view of the trade-offs it tries to make, when 
sequential scans are better than repeated index lookups, et cetera.  i'm 
writing because i found this example, which shows yet another thing i don't 
understand about the query planner, and i am trying to learn better about it.

you've already answered my main question (whether or not unique constraints are 
used to help row estimation.)  there's a couple more issues which i don't quite 
understand :

1) when i give a hint to the query planner to not expect more than 
number-of-rows-in-jointable (via a limit), switches to a nested loop + index 
scan, but with the same row estimates.  i'll show the plan i had in the first 
email :

Limit  (cost=0.00..178452647.11 rows=250 width=28)
  -  Nested Loop  (cost=0.00..1306127545.35 rows=18297957 width=28)
-  Seq Scan on jointable  (cost=0.00..35867.11 rows=2066911 width=24)
-  Index Scan using bigtable_pkey on bigtable  (cost=0.00..631.88 
rows=1 width=28)
  Index Cond: ((id1 = jointable.id1) AND (id2 = jointable.id2) AND 
(id3 = jointable.id3) AND (id4 = jointable.id4) AND (id5 = jointable.id5))
(5 rows)

before, i was misreading this as saying the planner was going to execute the 
nested loop fully (rows=18 million), and then limit the results.  i am now 
reading it as saying that the inner nested loop will be short-circuited after 
it generates enough rows.  if this is true, it seems to imply that, in query 
plan with deeply nested inner nested loops, one should read the inner loop row 
estimates with a grain of salt, as there might be limits (potentially many 
levels outwards) which can short-circuit them.  am i wrong about this?

2) when doing the sort+merge join, it choses to sort bigtable rather than use 
an index scan.  i've tried to give hints by requesting the results come in 
primary key order, but it keeps sorting by a permutation of the primary key and 
then resorting the join results at the end.  so obviously the random seek cost 
dominates the sequential read + sort (which i find surprising, but again i am 
happy to be surprised.)  that seems fine for a query which is going to touch 
the whole table.  but i can't seem to come up with a query which would ever 
favor using an index scan.  for example this :

explain select * from bigtable order by (id1, id2, id3, id4, id5) limit 1;
 QUERY PLAN 


 Limit  (cost=91923132.04..91923132.04 rows=1 width=28)
   -  Sort  (cost=91923132.04..99842047.88 rows=3167566336 width=28)
 Sort Key: (ROW(id1, id2, id3, id4, id5))
 -  Seq Scan on bigtable  (cost=0.00..76085300.36 rows=3167566336 
width=28)
(4 rows)

(apologies bigtable has grown since i've first started this thread.)  shouldn't 
an index scan definitely be fastest here?  you don't need to touch the whole 
table or index.  maybe there something i have misconfigured here?

best regards, ben

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

[PERFORM] incorrect row estimates for primary key join

[Ben]

hello postgresql experts --

i have a strange row estimate problem, which unfortunately i have trouble 
reproducing for anything but very large tables which makes boiling down to a 
simple example hard.  i'm using version 9.1.1, all tables have had analyze run 
on them.

here's the example : i have a large table (billions of rows) with a five column 
primary key and a sixth value column.  for confidentiality purposes i can't 
reproduce the exact schema here, but it is essentially

create table bigtable (
  id1 integer not null,
  id2 date not null,
  id3 integer not null,
  id4 time not null,
  id5 integer not null,
  value real not null,
  primary key (id1, id2, id3, id4, id5)
);

for various reasons there is only one id1 in the table right now, though in the 
future there will be more; also the primary key was added after table creation 
with alter table, though that shouldn't matter.

i need to select out a somewhat arbitrary collection of rows out of bigtable.  
to do so i generate a temporary table 

create table jointable (
  id1 integer not null,
  id2 date not null,
  id3 integer not null,
  id4 time not null,
  id5 integer not null
);

and then perform a join against this table.

if jointable doesn't have many rows, the planner picks a nested loop over 
jointable and a primary key lookup on bigtable.  in the following, for 
expository purposes, jointable has 10 rows.  we can see the planner knows this.

explain select * from bigtable join jointable using (id1, id2, id3, id4, id5);

 Nested Loop  (cost=0.00..6321.03 rows=145 width=28)
   -  Seq Scan on jointable  (cost=0.00..1.10 rows=10 width=24)
   -  Index Scan using bigtable_pkey on bigtable  (cost=0.00..631.97 rows=1 
width=28)
 Index Cond: ((id1 = jointable.id1) AND (id2 = jointable.id2) AND (id3 
= jointable.id3) AND (id4 = jointable.id4) AND (vid = foo.vid))
(4 rows)

as you increase the number of rows in jointable, the planner switches to a sort 
+ merge.  in this case jointable has roughly 2 million rows.

 Merge Join  (cost=727807979.29..765482193.16 rows=18212633 width=28)
  Merge Cond: ((bigtable.id1 = jointabe.id1) AND (bigtable.id2 = jointable.id2) 
AND (bigtable.id3 = jointable.id3) AND (bigtable.id4 = bigtable.id4) AND 
(bigtable.id5 = bigtable.id5))
   -  Sort  (cost=727511795.16..735430711.00 rows=3167566336 width=28)
 Sort Key: bigtable.id3, bigtable.id1, bigtable.id2, bigtable.id4, 
bigtable.id5
 -  Seq Scan on bigtable  (cost=0.00..76085300.36 rows=3167566336 
width=28)
   -  Materialize  (cost=295064.70..305399.26 rows=2066911 width=24)
 -  Sort  (cost=295064.70..300231.98 rows=2066911 width=24)
   Sort Key: jointable.id3, jointable.id1, jointable.id2, 
jointable.id4, jointable.id5
   -  Seq Scan on jointable  (cost=0.00..35867.11 rows=2066911 
width=24)
(9 rows)

the choice of sort + merge is really bad here, given the size of bigtable (3 
billion rows and counting.)

some questions :

1 - first off, why isn't the sort happening on the primary key, so that 
bigtable does not have to be sorted?

2 - more importantly, since we are joining on the primary key, shouldn't the 
row estimate coming out of the join be limited by the number of rows in 
jointable?

for example, it is strange to see that if i put in a non-limiting limit 
statement (something bigger than the number of rows in jointable) it switches 
back to a nested loop + index scan :

explain select * from bigtable join jointable using (id1, id2, id3, id4, id5) 
limit 250;

 Limit  (cost=0.00..178452647.11 rows=250 width=28)
   -  Nested Loop  (cost=0.00..1306127545.35 rows=18297957 width=28)
 -  Seq Scan on jointable  (cost=0.00..35867.11 rows=2066911 width=24)
 -  Index Scan using bigtable_pkey on bigtable  (cost=0.00..631.88 
rows=1 width=28)
   Index Cond: ((id1 = jointable.id1) AND (id2 = jointable.id2) AND 
(id3 = jointable.id3) AND (id4 = jointable.id4) AND (id5 = jointable.id5))
(5 rows)

am i not understanding the query planner, or is this a known issue in the query 
planner, or have i stumbled onto something amiss?  unfortunately any synthetic 
examples i was able to make (up to 10 million rows) did not exhibit this 
behavior, which makes it hard to test.

best regards, ben



-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] incorrect row estimates for primary key join

[Ben]

hello --

On Jun 24, 2013, at 4:23 PM, bricklen wrote:

 Is it safe to assume you ANALYZEd the jointable after creating it? (I assume 
 so, just checking)

yes, jointable was analyzed.  both tables were further analyzed after any 
changes.

 Can you post the output of:
 
 SELECT version();
 SELECT name, current_setting(name), source
 FROM pg_settings
 WHERE source NOT IN ('default', 'override');

  version
---
 PostgreSQL 9.1.1 on x86_64-suse-linux-gnu, compiled by gcc (SUSE Linux) 4.6.2, 
64-bit
(1 row)

 name |  current_setting   |source
--++--
 checkpoint_completion_target | 0.9| configuration file
 checkpoint_segments  | 16 | configuration file
 DateStyle| ISO, MDY   | configuration file
 default_statistics_target| 50 | configuration file
 default_text_search_config   | pg_catalog.english | configuration file
 effective_cache_size | 5632MB | configuration file
 lc_messages  | en_US.UTF-8| configuration file
 lc_monetary  | en_US.UTF-8| configuration file
 lc_numeric   | en_US.UTF-8| configuration file
 lc_time  | en_US.UTF-8| configuration file
 listen_addresses | *  | configuration file
 log_destination  | stderr | configuration file
 log_line_prefix  | %t %d %u   | configuration file
 log_timezone | US/Pacific | environment variable
 logging_collector| on | configuration file
 maintenance_work_mem | 480MB  | configuration file
 max_connections  | 300| configuration file
 max_stack_depth  | 2MB| environment variable
 max_wal_senders  | 3  | configuration file
 search_path  | public | user
 shared_buffers   | 1920MB | configuration file
 TimeZone | US/Pacific | environment variable
 wal_buffers  | 8MB| configuration file
 wal_keep_segments| 128| configuration file
 wal_level| hot_standby| configuration file
 work_mem | 48MB   | configuration file
(26 rows)

hope this helps!

thanks, ben

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Segment best size

[Ben Chobot]

On Apr 12, 2013, at 9:45 AM, Rodrigo Barboza wrote:

 Hi guys.
 I compiled my postrges server (9.1.4) with default segment size (16MB).
 Should it be enough? Should I increase this size in compilation?

Unlike some default values in the configuration file, the compiled-in defaults 
work well for most people.

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Simple join doesn't use index

[Ben Chobot]

On Jan 29, 2013, at 6:24 AM, Alex Vinnik wrote:

 random_page_cost=1 might be not what you really want. 
 it would mean that random reads are as fast as as sequential reads, which 
 probably is true only for SSD
 What randon_page_cost would be more appropriate for EC2 EBS Provisioned 
 volume that can handle 2,000 IOPS? 

For EC2 Provisioned IOPS volumes - not standard EBS - random_page_cost=1 is 
exactly what you want.

[PERFORM] shared_buffers on ubuntu precise

[Ben Chobot]

On Nov 30, 2012, at 8:06 AM, Shaun Thomas wrote:

 I say that because you mentioned you're using Ubuntu 12.04, and we were
 having some problems with PG on that platform. With shared_buffers over
 4GB, it starts doing really weird things to the memory subsystem.
 Whatever it does causes the kernel to purge cache rather aggressively.
 We saw a 60% reduction in read IO by reducing shared_buffers to 4GB.
 Without as many reads, your writes should be much less disruptive.

Hm, this sounds like something we should look into. Before we start digging do 
you have more to share, or did you leave it with the huh, that's weird; this 
seems to fix it solution?

[PERFORM] index usage for min() vs. order by asc limit 1

[Ben Chobot]

I have two queries in PG 9.1. One uses an index like I would like, the other 
does not. Is this expected behavior? If so, is there any way around it? 


postgres=# explain analyze select min(id) from delayed_jobs where 
strand='sis_batch:account:15' group by strand;
QUERY PLAN
--
 GroupAggregate  (cost=0.00..8918.59 rows=66 width=29) (actual 
time=226.759..226.760 rows=1 loops=1)
   -  Seq Scan on delayed_jobs  (cost=0.00..8553.30 rows=72927 width=29) 
(actual time=0.014..169.941 rows=72268 loops=1)
 Filter: ((strand)::text = 'sis_batch:account:15'::text)
 Total runtime: 226.817 ms
(4 rows)

postgres=# explain analyze select id from delayed_jobs where 
strand='sis_batch:account:15' order by id limit 1;
   QUERY 
PLAN
-
 Limit  (cost=0.00..0.33 rows=1 width=8) (actual time=0.097..0.098 rows=1 
loops=1)
   -  Index Scan using index_delayed_jobs_on_strand on delayed_jobs  
(cost=0.00..24181.74 rows=72927 width=8) (actual time=0.095..0.095 rows=1 
loops=1)
 Index Cond: ((strand)::text = 'sis_batch:account:15'::text)
 Total runtime: 0.129 ms
(4 rows)


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] index usage for min() vs. order by asc limit 1

[Ben Chobot]

On Nov 17, 2011, at 5:20 PM, Steve Atkins wrote:
 
 I don't think you want the group by in that first query.

Heh, I tried to simply the example, but in reality that = becomes an in clause 
of multiple values. So the group by is needed.


 
 
 postgres=# explain analyze select min(id) from delayed_jobs where 
 strand='sis_batch:account:15' group by strand;
   QUERY PLAN
 --
 GroupAggregate  (cost=0.00..8918.59 rows=66 width=29) (actual 
 time=226.759..226.760 rows=1 loops=1)
  -  Seq Scan on delayed_jobs  (cost=0.00..8553.30 rows=72927 width=29) 
 (actual time=0.014..169.941 rows=72268 loops=1)
Filter: ((strand)::text = 'sis_batch:account:15'::text)
 Total runtime: 226.817 ms
 (4 rows)
 
 postgres=# explain analyze select id from delayed_jobs where 
 strand='sis_batch:account:15' order by id limit 1;
  QUERY 
 PLAN
 -
 Limit  (cost=0.00..0.33 rows=1 width=8) (actual time=0.097..0.098 rows=1 
 loops=1)
  -  Index Scan using index_delayed_jobs_on_strand on delayed_jobs  
 (cost=0.00..24181.74 rows=72927 width=8) (actual time=0.095..0.095 rows=1 
 loops=1)
Index Cond: ((strand)::text = 'sis_batch:account:15'::text)
 Total runtime: 0.129 ms
 (4 rows)
 
 
 -- 
 Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-performance
 
 
 -- 
 Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Large number of short lived connections - could a connection pool help?

[Ben Chobot]

On Nov 14, 2011, at 4:42 PM, Cody Caughlan wrote:

 We have anywhere from 60-80 background worker processes connecting to
 Postgres, performing a short task and then disconnecting. The lifetime
 of these tasks averages 1-3 seconds.

[snip]

 Is this something that I should look into or is it not much of an
 issue? Whats the best way to determine if I could benefit from using a
 connection pool?

Yes, this is precisely a kind of situation a connection pooler will help with. 
Not only with the the connection set up/tear down overhead, but also by using 
resources on your server better you probably don't actually have 60-80 
cores on your server, so reducing that number down to just a few that are 
actually working will the Postgres finish them faster to work on others. 
Basically, the queueing happens off the postgres server, letting postgres use 
the box with less interruptions. 

Now, is it a problem to not use a pooler? That depends on if it's causing you 
grief or not. But if you think you'll get more connection churn or larger 
numbers of workers, then a connection pooler will only help more.
-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] PostgreSQL-9.0 Monitoring System to improve performance

[Ben Ciceron]

doe sit include monitoring replicas ?

Cheers,
Ben-




On Fri, Oct 7, 2011 at 3:01 PM, Fernando Hevia fhe...@gmail.com wrote:
 pgwatch might also be worth taking a look
 at: http://www.cybertec.at/en/postgresql_products/pgwatch-cybertec-enterprise-postgresql-monitor
 Fernando.-
 On Fri, Sep 30, 2011 at 18:29, Bobby Dewitt bdew...@appriss.com wrote:

 EnterpriseDB now has Postgres Enterprise Manager

 (http://enterprisedb.com/products-services-training/products/postgres-enter
 prise-manager) that has some of the information that is being asked for.
 It has a hot table analysis report that shows the number of scans, rows
 read, etc.  Since much of the tool is using the pgAdmin code base, much of
 this is also available in pgAdmin but PEM will track the statistics at
 given intervals and show you trending graphs over time.  It's still a very
 new tool so I'm sure they are working to add new features and have been
 looking for enhancement suggestions.  Of course, it requires a service
 contract with them to use the tool, but it doesn't cost extra to add the
 tool if you already have a contract with them.  It does have a 45 day
 evaluation if you wanted to check it out.

 Hope that helps.
 Bobby

 On 9/30/11 7:53 AM, Gregg Jaskiewicz gryz...@gmail.com wrote:

 Looks like this is generally an area that can be targeted by some
 businesses. Or an open source enthusiast.
 One centre that captures all the information and produces a report
 based on it would be a great thing. Especially in cases like mine,
 where I have tens of postgresql installations on different hardware
 and with different use patterns (but schemas and queries are the
 same).


 --
 Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-performance



-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

[PERFORM] pg9 replication over WAN ?

[Ben Ciceron]

Hello,

Has any performance or evaluation done for pg9.x streaming replication
over WAN ?
How adequate is the protocol to push WALs over long distance ?
Any best practice tuning wal_* for WAN ?

Cheers,
Ben-

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] How can i get record by data block not by sql?

[Ben Chobot]

On Oct 3, 2011, at 6:52 AM, 姜头 wrote:

 How can i get record by data block not by sql?
  
 I want to read and write lots of data by data blocks and write record to a 
 appointed data block and read it.
 so i can form a disk-resident tree by recording the block address. But i 
 don't know  how to implement in postgresql.
 Is there system function can do this? 
 Can someone help me?? Thank you very very much1

It sounds like you should look into the COPY command, or, if you're 
adventurous, the pg_bulkload project. They might get you the speed you're 
after, if not quite the implementation. But if what you're really after is to 
manipulate the table files directly - and I'm not sure why that would be a goal 
in itself - then perhaps SQL isn't for you.
-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] array_except -- Find elements that are not common to both arrays

[Ben Chobot]

On Sep 30, 2011, at 12:07 PM, bricklen wrote:

 I've been informed that this type of operation is called symmetric
 difference[1], and can be represented by A ∆ B.  A couple of
 alternative names were proposed, array_symmetric_difference and
 array_xor.
 Does anyone have a preference for the name? I assume that this
 function might potentially be used by others now that it is in the pg
 lists, so might as well give it an appropriate name now.
 Is this something that could be written in C to make it faster (I don't know 
 C)

FWIW, speaking as somebody who has no need of this function, array_xor is a 
pretty clear name that indicates what's going to happen.
-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] postgres constraint triggers

[Ben Chobot]

On Sep 27, 2011, at 6:37 PM, Craig Ringer wrote:

 On 09/27/2011 12:54 PM, Ben Chobot wrote:
 
 My memory is fuzzy but as I recall, a possible downside to using
 deferred constraints was increased memory usage
 
 That's right. PostgreSQL doesn't currently support spilling of pending 
 constraint information to disk; it has to keep it in RAM, and with 
 sufficiently huge deferred updates/inserts/deletes it's possible for the 
 backend to run out of RAM to use.
 
 though I cannot see how at the moment.
 
 A list of which triggers to run, and on which tuples, must be maintained 
 until those triggers are fired. That list has to be kept somewhere.

Well when you put it like that, it's so obvious. :)
-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] postgres constraint triggers

[Ben Chobot]

On Sep 26, 2011, at 10:52 AM, Maria L. Wilson wrote:

 Our first try to solve this problem has been to convert these triggers into a 
 constraint trigger which allows for DEFERRABLE INITIALLY DEFERRED flags.  
 This, we are finding, is forcing the trigger function to run after the 
 triggering transaction is completed.  We believe this will fix our locking 
 problem and hopefully speed up our inserts again.
 
 Any comments or past experiences would certainly be helpful!

My memory is fuzzy but as I recall, a possible downside to using deferred 
constraints was increased memory usage, though I cannot see how at the moment. 
Regardless, I think the upshot is that they aren't without their cost but as 
long as you aren't doing massive transactions that cost is probably one that 
you can afford to pay without much worry. 

Re: [PERFORM] Benchmarking a large server

[Ben Chobot]

On May 9, 2011, at 1:32 PM, Chris Hoover wrote:

 1.3 TB Fusion IO (2 1.3 TB Fusion IO Duo cards in a raid 10)

Be careful here. What if the entire card hiccups, instead of just a device on 
it? (We've had that happen to us before.) Depending on how you've done your 
raid 10, either all your parity is gone or your data is.
-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

[PERFORM] Custom operator class costs

[Ben Beecher]

Hey!
I'm having some trouble optimizing a query that uses a custom operator class.
#Postgres has given me a solution for natural sort -
http://www.rhodiumtoad.org.uk/junk/naturalsort.sql

I'm trying to run it over a huge table - when running it on demand,
the data needs to be dumped to memory and sorted.

Sort  (cost=31299.83..31668.83 rows=369 width=31)
  Sort Key: name
  -  Seq Scan on solutions_textbookpage  (cost=0.00..25006.55
rows=369 width=31)
Filter: (active AND (textbook_id = 263))

That's obviously too slow. I've created an index using the custom
operator class, so I don't have to do the sort every time I try to
sort.

 Index Scan Backward using natural_page_name_textbook on
solutions_textbookpage  (cost=0.00..650.56 rows=371 width=31) (actual
time=0.061..0.962 rows=369 loops=1)
   Index Cond: (textbook_id = 263)
   Filter: active

Obviously a little faster!


The problem I'm having is that because operator classes have a low
cost estimation pg missestimates and tries to do the sort on demand
rather than using the index.

I can get pg to use the index by either jacking up cpu_operator_cost
or lowering random_page_cost. Is this the best way to do that, or is
there a smarter way to ensure that pg uses this index when I need it.

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [HACKERS] [PERFORM] Slow count(*) again...

[Ben Chobot]

On Feb 3, 2011, at 1:50 PM, Mladen Gogala wrote:

 So, I will have to go back on my decision to use Postgres and re-consider 
 MySQL? I will rather throw away the effort invested in studying Postgres than 
 to risk an unfixable application downtime.  I am not sure about the world 
 domination thing, though. Optimizer hints are a big feature that everybody 
 else has and Postgres does not have because of religious reasons.

As always, you should use the tool you consider best for the job. If you think 
MySQL as both a product and a community has a better chance of giving you what 
you want, then you should use MySQL.
-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Question: BlockSize 8192 with FusionIO

[Ben Chobot]

On Jan 4, 2011, at 8:48 AM, Merlin Moncure wrote:

 
 most flash drives, especially mlc flash, use huge blocks anyways on
 physical level.  the numbers claimed here
 (http://www.fusionio.com/products/iodrive/)  (141k write iops) are
 simply not believable without write buffering.  i didn't see any note
 of how fault tolerance is maintained through the buffer (anyone
 know?).

FusionIO buffers. They have capacitors onboard to protect against crashing and 
power failure. They passed our crash attempts to corrupt writes to them before 
we put them into production, for whatever that's worth, but they do take a long 
time to come back online after an unclean shutdown.
-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

[PERFORM] encourging bitmap AND

[Ben]

hello --

i have a schema similar to the following

create table foo (
 id integer not null,
 val integer not null,
 s integer not null,
 e integer not null
);

create index foo_s_idx on foo using btree (s);
create index foo_e_idx on foo using btree (e);

i want to do queries like

select * from foo where 150 between s and e;

this usually gives me index or bitmap scans on one of the indices, plus a 
filter for the other condition.  this is not terribly efficient as the table is 
large (billions of rows), but there should only be a few thousand rows with s  
k  e for any k.  the data is id, value, interval (s, e), with s  e, and e - s 
is small.

i am experimenting and would like to see the effect of using a bitmap index 
AND scan using both indices.  as far as i can tell, there are no easy ways to 
force or encourage this -- there are no switches like enable_seqscan and such 
which force the use of bitmap AND, and i don't know how to tell the query 
planner about the structure of the data (i don't think this is adequately 
captured in any of the statistics it generates, i would need multi-column 
statistics.)

any clues?

best regards, ben
-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] encourging bitmap AND

[Ben]

On Dec 23, 2010, at 12:52 PM, Tom Lane wrote:

 Ben midfi...@gmail.com writes:
 i have a schema similar to the following
 
 create index foo_s_idx on foo using btree (s);
 create index foo_e_idx on foo using btree (e);
 
 i want to do queries like
 
 select * from foo where 150 between s and e;
 
 That index structure is really entirely unsuited to what you want to do,
 so it's not surprising that the planner isn't impressed with the idea of
 a bitmap AND.
 
 I'd suggest setting up something involving a gist index over an
 interval-ish datatype.  The PERIOD datatype that Jeff Davis is fooling
 with would do what you want --- see
 http://pgfoundry.org/projects/temporal
 http://thoughts.j-davis.com/2009/11/08/temporal-keys-part-2/
 If you don't want any add-on parts involved, you could fake it by using
 a box or possibly lseg.

Thanks for the quick response.  I've already played a lot with the PERIOD 
datatype and GIST, it works pretty good, but I found that the lack of 
statistics and real selectivity functions hurt me.  I was experimenting with 
the two column setup as an alternative, but if you think this is a dead end 
I'll look elsewhere.

Best regards, Ben
-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] concurrent IO in postgres?

[Ben Chobot]

On Dec 23, 2010, at 11:58 AM, Andy wrote:

 
 Somewhat tangential to the current topics, I've heard that FusionIO uses 
 internal cache and hence is not crash-safe, and if the cache is turned off 
 performance will take a big hit. Is that your experience?

It does use an internal cache, but it also has onboard battery power. The 
driver needs to put its house in order when restarting after an unclean 
shutdown, however, and that can take up to 30 minutes per card.
-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] MySQL HandlerSocket - Is this possible in PG?

[Michael Ben-Nes]

I think this might be a game changing feature.
For the first time after 10 years I have reason to consider MySQL, as the
cost per performance in such scenario is amazing. Morever I wont have to run
it in single mod or loose other functionality by using this feautre. as I
can access the ordinary interface on port 3306 and the fast interface on
other port.

I wonder if PostgreSQL should replicate this functionality somehow. How can
I represent this idea to the developers? They will probably know if this
feature worth something.

Thanks,
Miki

--
Michael Ben-Nes - Internet Consultant and Director.
http://www.epoch.co.il - weaving the Net.
Cellular: 054-4848113
--


On Tue, Dec 21, 2010 at 11:07 PM, Merlin Moncure mmonc...@gmail.com wrote:

 On Tue, Dec 21, 2010 at 10:50 AM, Pavel Stehule pavel.steh...@gmail.com
 wrote:
  2010/12/21 Michael Ben-Nes mich...@epoch.co.il:
  Hi Pavel,
 
  Thanks for your quick answer. Can you please elaborate a bit more about
 the
  points bellow.
 
  On Tue, Dec 21, 2010 at 1:31 PM, Pavel Stehule pavel.steh...@gmail.com
 
  wrote:
 
  Hello
 
  you can emulate it now.
 
  a) try to do a simple stored procedure, where you can wrap your query
 
  Do you mean I should use PREPARE?
 
  yes
 
 
  b) use a FAST CALL API to call this procedure
 
  Currently I use PHP to access the DB which use libpq. Is that cosidered
 a
  fast call API ? if not, can you please refer me to the right info.
 
 
 
  sorry it is a fast-path interface
 
  http://www.postgresql.org/docs/8.1/static/libpq-fastpath.html
 
  but php hasn't a adequate API :(


 I don't think fastpath interface is going to get you there.  What they
 are doing with mysql is bypassing both the parser and the protocol.
 As soon as you use libpq, you've lost the battle...you can't see
 anywhere close to to that performance before you become network
 bottlenecked.

 If you want to see postgres doing this in action, you could fire up
 the database in single user mode and run raw queries against the
 backend.   Another way to do it is to hack tcop/postgres.c and inject
 protocol messages manually.  Right now, the only way to get that close
 to the metal using standard techniques is via SPI (plpgsql, etc).  A
 proper transaction free stored procedure implementation would open a
 lot of doors for fast query execution.

 merlin

 --
 Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-performance

[PERFORM] MySQL HandlerSocket - Is this possible in PG?

[Michael Ben-Nes]

Hi,

Just stumbled on the following post:
http://yoshinorimatsunobu.blogspot.com/2010/10/using-mysql-as-nosql-story-for.html

The post claim that MySQL can do more qps then MemCahed or any other NoSQL
when doing simple queries like: SELECT * FROM table WHERE id=num;

And I wonder if:

1. Currently, is it possbile to achive the same using PG 9.0.x
2. Is it possible at all?

It seems to me that if such gain is possible, PG should benefit from that
significantly when it comes to Key/Value queries.


Best,
Miki

--
Michael Ben-Nes - Internet Consultant and Director.
http://www.epoch.co.il - weaving the Net.
Cellular: 054-4848113
--

Re: [PERFORM] MySQL HandlerSocket - Is this possible in PG?

[Michael Ben-Nes]

Hi Pavel,

Thanks for your quick answer. Can you please elaborate a bit more about the
points bellow.

On Tue, Dec 21, 2010 at 1:31 PM, Pavel Stehule pavel.steh...@gmail.comwrote:

 Hello

 you can emulate it now.

 a) try to do a simple stored procedure, where you can wrap your query


Do you mean I should use PREPARE?

b) use a FAST CALL API to call this procedure


Currently I use PHP to access the DB which use libpq. Is that cosidered a
fast call API ? if not, can you please refer me to the right info.


 c) use a some pool tool for pooling and persisting sessions

PHP pg_pconnect command open a persistent PostgreSQL connection. Is it
enough or I better use PgPool2 or something similar?

Considering the points above, will I be able to get such high QPS from
PostgreSQL ? If so, it will be my pleasure to dump Reddis and work solely
with PG :)


Thanks,
Miki


 Regards

 Pavel Stehule

 2010/12/21 Michael Ben-Nes mich...@epoch.co.il:
  Hi,
 
  Just stumbled on the following post:
 
 http://yoshinorimatsunobu.blogspot.com/2010/10/using-mysql-as-nosql-story-for.html
 
  The post claim that MySQL can do more qps then MemCahed or any other
 NoSQL
  when doing simple queries like: SELECT * FROM table WHERE id=num;
 
  And I wonder if:
 
  1. Currently, is it possbile to achive the same using PG 9.0.x
  2. Is it possible at all?
 
  It seems to me that if such gain is possible, PG should benefit from that
  significantly when it comes to Key/Value queries.
 
 
  Best,
  Miki
 
 

--
Michael Ben-Nes - Internet Consultant and Director.
http://www.epoch.co.il - weaving the Net.
Cellular: 054-4848113
--

Re: [PERFORM] MVCC performance issue

[Ben Chobot]

On Nov 12, 2010, at 8:14 AM, Kyriacos Kyriacou wrote:

 We are still using PostgreSQL 8.2.4. We are running a 24x7 system and 
 database size is over 200Gb so upgrade is not an easy decision!

This is why we have slony, so you can slowly upgrade your 200Gb while you're 
live and then only suffer a minute or so of downtime while you switchover. Even 
if you only install slony for the point of the upgrade and then uninstall it 
after you're done, that seems well worth it to me rather than running on 8.2.4 
for a while.

Note there were some changes between 8.2 and 8.3 in regards to casting that 
might make you revisit your application.

[PERFORM] equivalent queries lead to different query plans for self-joins with group by?

[Ben]

  (cost=14.55..14.56 rows=1 width=16)   


   
 -  Bitmap Heap Scan on foo  (cost=4.33..14.51 rows=8 width=16)


  
   Recheck Cond: ((ts  '2010-11-11 00:00:00'::timestamp without 
time zone) AND (ts  '2010-11-13 00:00:00'::timestamp without time zone))   

 
   -  Bitmap Index Scan on foo_pkey  (cost=0.00..4.33 rows=8 
width=0)


 Index Cond: ((ts  '2010-11-11 00:00:00'::timestamp 
without time zone) AND (ts  '2010-11-13 00:00:00'::timestamp without time 
zone))  
  
   -  Bitmap Heap Scan on foo  (cost=4.31..14.45 rows=8 width=20)  


  
 Recheck Cond: (pg_temp_2.foo.ts = pg_temp_2.foo.ts)


  
 -  Bitmap Index Scan on foo_pkey  (cost=0.00..4.31 rows=8 width=0)


  
   Index Cond: (pg_temp_2.foo.ts = pg_temp_2.foo.ts)


  

i find this behavior curious.  my understanding is that both queries are 
equivalent, and i would expect that the query planner would be able to choose 
either of those plans.  this is important -- with the real data i'm working 
with, the table is very large, and the sequential scan is a killer. 

are these queries equivalent, or am i mistaken?  if the planner distinguishes 
between these plans, how do i ensure that where clause restrictions propagate 
(correctly) to subqueries?

best regards, ben


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] equivalent queries lead to different query plans for self-joins with group by?

[Ben]

appreciate the instant response.

 Well, arguably it's not doing the right thing either way --- you'd sort
 of like the inequalities to get pushed down into both of the join
 inputs, not just one of them.  PG doesn't make that deduction though;
 it can make such inferences for equalities, but inequalities are not
 optimized as much.

in my work i have replaced the query with a sql function + window :

create or replace function bar(timestamp, timestamp) returns setof foo
language 'sql' as $$
  select ts,
 id,
 val -
 (avg(val) over (partition by ts)) as val
  from foo
  where ts  $1
  and ts  $2
$$;

i was forced to use a sql function as opposed to a view because the query 
planner was unable to push down restrictions on ts inside the view subquery, 
which i've manually done in the function.  indeed,

explain select ts, id, val - (avg(val) over (partition by ts)) as val from foo 
where ts  '2009-10-20' and ts  '2009-10-21';

and

explain select * from (select ts, id, val - (avg(val) over (partition by ts)) 
as val from foo) as f where ts  '2009-10-20' and ts  '2009-10-21';

give different answers, despite being equivalent, but i understand it is hard 
to push things into subqueries in general.  in this case it is only legal 
because we partition by ts.

thanks again for the explanations!

best, ben


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] partitioning question 1

[Ben]

On Oct 29, 2010, at 7:38 AM, Igor Neyman wrote:

 is my intuition completely off on this?
 
 best regards, ben
 
 
 If your SELECT retrieves substantial amount of records, table scan could
 be more efficient than index access.
 
 Now, if while retrieving large amount of records WHERE clause of this
 SELECT still satisfies constraints on some partition(s), then obviously
 one (or few) partition scans will be more efficient than full table scan
 of non-partitioned table.
 
 So, yes partitioning provides performance improvements, not only
 maintenance convenience.

my impression was that a *clustered* index would give a lot of the same I/O 
benefits, in a more flexible way.  if you're clustered on the column in 
question, then an index scan for a range is much like a sequential scan over a 
partition (as far as i understand.)

b
-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

[PERFORM] partitioning question 1

[Ben]

hello --

my last email was apparently too long to respond to so i'll split it up into 
shorter pieces.  my first question :

my understanding of how range partitioning and constraint exclusion works leads 
me to believe that it does not buy any query performance that a clustered index 
doesn't already give you -- the advantages are all in maintainability.  an 
index is able to eliminate pages just as well as constraint exclusion is able 
to eliminate table partitions.  the I/O advantages of having queries target 
small subtables are the same as the I/O advantages of clustering the index : 
result pages in a small range are very close to each other on disk.

finally, since constraint exclusion isn't as flexible as indexing (i've seen 
old mailing list posts that say that constraint exclusion only works with 
static constants in where clauses, and only works with simple operators like , 
 which basically forces btree indexes when i want to use gist) it is indeed 
likely that partitioning can be slower than one big table with a clustered 
index.

is my intuition completely off on this?

best regards, ben
-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] partitioning question 1

[Ben]

thanks for the prompt response.  some comments / questions below :

On Oct 28, 2010, at 10:31 AM, Joshua D. Drake wrote:
 ...constraint exclusion is able to eliminate table partitions.  the I/O 
 advantages of having queries target small subtables are the same as the I/O 
 advantages of clustering the index : result pages in a small range are very 
 close to each other on disk.
 
 Not entirely true. One a clustered index will not stay clustered if you
 are still updating data that is in the partition. You shouldn't
 underestimate the benefit of smaller relations in terms of maintenance
 either.

in my situation, the update come in-order (it is timeseries data and the 
clustered index is on time.)  so the table should remain relatively clustered.  
updates also happen relatively infrequently (once a day in one batch.)  so it 
appears that we will continue to get the I/O benefits described above.

are there any other benefits which partitioning provides for query performance 
(as opposed to update performance) besides the ones which i have mentioned?


 Yes the constraints have to be static. Not sure about the operator
 question honestly.

this seems to severely restrict their usefulness -- our queries are data 
warehouse analytical -type  queries, so the constraints are usually data-driven 
(come from joining against other tables.)

 is my intuition completely off on this?
 
 You may actually want to look into expression indexes, not clustered
 ones.


what would expression indexes give me?

thanks and best regards, ben


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] partitioning question 1

[Ben]

On Oct 28, 2010, at 11:50 AM, Joshua D. Drake wrote:
 Yes the constraints have to be static. Not sure about the operator
 question honestly.
 
 this seems to severely restrict their usefulness -- our queries are data 
 warehouse analytical -type  queries, so the constraints are usually 
 data-driven (come from joining against other tables.)
 
 Well it does and it doesn't. Keep in mind that the constraint can be:
 
 date = '2010-10-01 and date = '2010-10-31'
 
 What it can't be is something that contains date_part() or extract() (as
 an example) 

i think we are talking about two different things here: the constraints on the 
table, and the where-clause constraints in a query which may or may not trigger 
constraint exclusion.  i understand that table constraints have to be constants 
-- it doesn't make much sense otherwise.  what i am wondering about is, will 
constraint exclusion be triggered for queries where the column that is being 
partitioned on is being constrained things that are not static constants, for 
instance, in a join.  (i'm pretty sure the answer is no, because i think 
constraint exclusion happens before real query planning.)  a concrete example :

create table foo (i integer not null, j float not null);
create table foo_1 (check ( i = 0 and i  10) ) inherits (foo);
create table foo_2 (check ( i = 10 and i  20) ) inherits (foo);
create table foo_3 (check ( i = 20 and i  30) ) inherits (foo);
etc..

create table bar (i integer not null, k float not null);

my understanding is that a query like

select * from foo, bar using (i);

can't use constraint exclusion, even if the histogram of i-values on table bar 
says they only live in the range 0-9, and so the query will touch all of the 
tables.  i think this is not favorable compared to a single foo table with a 
well-maintained btree index on i.

 is my intuition completely off on this?
 
 You may actually want to look into expression indexes, not clustered
 ones.
 
 Take a look at the docs:
 
 http://www.postgresql.org/docs/8.4/interactive/indexes-expressional.html
 
 It could be considered partitioning without breaking up the table,
 just the indexes.

do you mean partial indexes?  i have to confess to not understanding how this 
is relevant -- how could partial indexes give any advantage over a full 
clustered index?

b 
-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] partitioning question 1

[Ben]

On Oct 28, 2010, at 12:44 PM, Joshua D. Drake wrote:
 
 My tests show you are incorrect:
 
 
 part_test=# explain analyze select * from foo join bar using (i) where
 i=9;
QUERY
 PLAN
 --
 Nested Loop  (cost=34.26..106.76 rows=200 width=20) (actual
 time=0.004..0.004 rows=0 loops=1)
   -  Append  (cost=0.00..68.50 rows=20 width=12) (actual
 time=0.004..0.004 rows=0 loops=1)
 -  Seq Scan on foo  (cost=0.00..34.25 rows=10 width=12)
 (actual time=0.001..0.001 rows=0 loops=1)
   Filter: (i = 9)
 -  Seq Scan on foo_1 foo  (cost=0.00..34.25 rows=10 width=12)
 (actual time=0.000..0.000 rows=0 loops=1)
   Filter: (i = 9)
   -  Materialize  (cost=34.26..34.36 rows=10 width=12) (never
 executed)
 -  Seq Scan on bar  (cost=0.00..34.25 rows=10 width=12) (never
 executed)
   Filter: (i = 9)
 Total runtime: 0.032 ms
 (10 rows)

strange.  my tests don't agree with your tests :

create table foo (i integer not null, j float not null);
create table foo_1 ( check (i = 0 and i  10) ) inherits (foo);
create table foo_2 ( check (i = 10 and i  20) ) inherits (foo);
create table foo_3 ( check (i = 20 and i  30) ) inherits (foo);
create index foo_1_idx on foo_1 (i);
create index foo_2_idx on foo_2 (i);
create index foo_3_idx on foo_3 (i);
insert into foo_1 select generate_series, generate_series from 
generate_series(0,9);
insert into foo_2 select generate_series, generate_series from 
generate_series(10,19);
insert into foo_3 select generate_series, generate_series from 
generate_series(20,29);
create table bar (i integer not null, k float not null);
create index bar_idx on bar (i);
insert into bar select generate_series, -generate_series from 
generate_series(0,9);
vacuum analyze;
explain analyze select * from foo join bar using (i);

QUERY PLAN  
 
--
 Hash Join  (cost=1.23..42.29 rows=98 width=20) (actual time=0.056..0.118 
rows=10 loops=1)
   Hash Cond: (public.foo.i = bar.i)
   -  Append  (cost=0.00..32.70 rows=1970 width=12) (actual time=0.008..0.043 
rows=30 loops=1)
 -  Seq Scan on foo  (cost=0.00..29.40 rows=1940 width=12) (actual 
time=0.001..0.001 rows=0 loops=1)
 -  Seq Scan on foo_1 foo  (cost=0.00..1.10 rows=10 width=12) (actual 
time=0.005..0.008 rows=10 loops=1)
 -  Seq Scan on foo_2 foo  (cost=0.00..1.10 rows=10 width=12) (actual 
time=0.003..0.006 rows=10 loops=1)
 -  Seq Scan on foo_3 foo  (cost=0.00..1.10 rows=10 width=12) (actual 
time=0.003..0.006 rows=10 loops=1)
   -  Hash  (cost=1.10..1.10 rows=10 width=12) (actual time=0.025..0.025 
rows=10 loops=1)
 -  Seq Scan on bar  (cost=0.00..1.10 rows=10 width=12) (actual 
time=0.005..0.013 rows=10 loops=1)
 Total runtime: 0.205 ms
(10 rows)


i'm running pg 8.4.3 with constraint_exclusion=on (just to be safe.)

best, b
-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] partitioning question 1

[Ben]

whoops, didn't see the i=9 (linebreak! linebreak!)

nonetheless that is a static constant constraint on the column i, and i was 
asking if constraint exclusions would work for dynamic constraints (like those 
derived from a table joined against.)  so for example the bar table has only 
0-9 in its histogram for i, but constraint exclusion can't use that to 
eliminate tables foo_2 and foo_3.  this is precisely the kind of information an 
index can use via join selectivity.

i am not going to the pg conference, sorry to say.

b


On Oct 28, 2010, at 1:48 PM, Joshua D. Drake wrote:

 On Thu, 2010-10-28 at 12:59 -0700, Ben wrote:
 On Oct 28, 2010, at 12:44 PM, Joshua D. Drake wrote:
 
 My tests show you are incorrect:
 
 
 part_test=# explain analyze select * from foo join bar using (i) where
 i=9;
   QUERY
 PLAN
 --
 Nested Loop  (cost=34.26..106.76 rows=200 width=20) (actual
 time=0.004..0.004 rows=0 loops=1)
  -  Append  (cost=0.00..68.50 rows=20 width=12) (actual
 time=0.004..0.004 rows=0 loops=1)
-  Seq Scan on foo  (cost=0.00..34.25 rows=10 width=12)
 (actual time=0.001..0.001 rows=0 loops=1)
  Filter: (i = 9)
-  Seq Scan on foo_1 foo  (cost=0.00..34.25 rows=10 width=12)
 (actual time=0.000..0.000 rows=0 loops=1)
  Filter: (i = 9)
  -  Materialize  (cost=34.26..34.36 rows=10 width=12) (never
 executed)
-  Seq Scan on bar  (cost=0.00..34.25 rows=10 width=12) (never
 executed)
  Filter: (i = 9)
 Total runtime: 0.032 ms
 (10 rows)
 
 strange.  my tests don't agree with your tests :
 
 Do you have constraint_exclusion turned on? You should verify with show
 constraint_exclusion (I saw what you wrote below).
 
 JD
 
 P.S. Blatant plug, you coming to http://www.postgresqlconference.org ?
 
 
 -- 
 PostgreSQL.org Major Contributor
 Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
 Consulting, Training, Support, Custom Development, Engineering
 http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt
 


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] BBU Cache vs. spindles

[Ben Chobot]

On Oct 7, 2010, at 4:38 PM, Steve Crawford wrote:

 I'm weighing options for a new server. In addition to PostgreSQL, this 
 machine will handle some modest Samba and Rsync load.
 
 I will have enough RAM so the virtually all disk-read activity will be 
 cached. The average PostgreSQL read activity will be modest - a mix of 
 single-record and fairly large (reporting) result-sets. Writes will be modest 
 as well but will come in brief (1-5 second) bursts of individual inserts. The 
 rate of insert requests will hit 100-200/second for those brief bursts.
 
 So...
 
 Am I likely to be better off putting $$$ toward battery-backup on the RAID or 
 toward adding a second RAID-set and splitting off the WAL traffic? Or 
 something else?

A BBU is, what, $100 or so? Adding one seems a no-brainer to me. Dedicated WAL 
spindles are nice and all, but they're still spinning media. Raid card cache is 
wy faster, and while it's best at bursty writes, it sounds like bursty 
writes are precisely what you have.



-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Testing Sandforce SSD

[Ben Chobot]

On Jul 24, 2010, at 12:20 AM, Yeb Havinga wrote:

 The problem in this scenario is that even when the SSD would show not data 
 loss and the rotating disk would for a few times, a dozen tests without 
 failure isn't actually proof that the drive can write it's complete buffer to 
 disk after power failure.

Yes, this is always going to be the case with testing like this - you'll never 
be able to prove that it will always be safe. 
-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] performance on new linux box

[Ben Chobot]

On Jul 15, 2010, at 2:40 PM, Ryan Wexler wrote:

 On Thu, Jul 15, 2010 at 12:35 PM, Ben Chobot be...@silentmedia.com wrote:
 On Jul 15, 2010, at 9:30 AM, Scott Carey wrote:
 
  Many raid controllers are smart enough to always turn off write caching on 
  the drives, and also disable the feature on their own buffer without a 
  BBU. Add a BBU, and the cache on the controller starts getting used, but 
  *not* the cache on the drives.
 
  This does not make sense.
  Write caching on all hard drives in the last decade are safe because they 
  support a write cache flush command properly.  If the card is smart it 
  would issue the drive's write cache flush command to fulfill an fsync() or 
  barrier request with no BBU.
 
 You're missing the point. If the power dies suddenly, there's no time to 
 flush any cache anywhere. That's the entire point of the BBU - it keeps the 
 RAM powered up on the raid card. It doesn't keep the disks spinning long 
 enough to flush caches.
 --
 Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-performance
 
 So you are saying write caching is a dangerous proposition on a raid card 
 with or without BBU?


Er, no, sorry, I am not being very clear it seems. 


Using a cache for write caching is dangerous, unless you protect it with a 
battery. Caches on a raid card can be protected by a BBU, so, when you use a 
BBU, write caching on the raid card is safe. (Just don't read the firmware 
changelog for your raid card or you will always be paranoid.) If you don't have 
a BBU, many raid cards default to disabling caching. You can still enable it, 
but the card will often tell you it's a bad idea.

There are also caches on all your disk drives. Write caching there is always 
dangerous, which is why almost all raid cards always disable the hard drive 
write caching, with or without a BBU. I'm not even sure how many raid cards let 
you enable the write cache on a drive... hopefully, not many.

Re: [PERFORM] performance on new linux box

[Ben Chobot]

On Jul 15, 2010, at 8:16 PM, Scott Carey wrote:

 On Jul 15, 2010, at 12:35 PM, Ben Chobot wrote:
 
 On Jul 15, 2010, at 9:30 AM, Scott Carey wrote:
 
 Many raid controllers are smart enough to always turn off write caching on 
 the drives, and also disable the feature on their own buffer without a 
 BBU. Add a BBU, and the cache on the controller starts getting used, but 
 *not* the cache on the drives.
 
 This does not make sense.
 Write caching on all hard drives in the last decade are safe because they 
 support a write cache flush command properly.  If the card is smart it 
 would issue the drive's write cache flush command to fulfill an fsync() or 
 barrier request with no BBU.
 
 You're missing the point. If the power dies suddenly, there's no time to 
 flush any cache anywhere. That's the entire point of the BBU - it keeps the 
 RAM powered up on the raid card. It doesn't keep the disks spinning long 
 enough to flush caches.
 
 If the power dies suddenly, then the data that is in the OS RAM will also be 
 lost.  What about that? 
 
 Well it doesn't matter because the DB is only relying on data being persisted 
 to disk that it thinks has been persisted to disk via fsync().

Right, we agree that only what has been fsync()'d has a chance to be safe

 The data in the disk cache is the same thing as RAM.  As long as fsync() 
 works _properly_ which is true for any file system + disk combination with a 
 damn (not HFS+ on OSX, not FAT, not a few other things), then it will tell 
 the drive to flush its cache _before_ fsync() returns.  There is NO REASON 
 for a raid card to turn off a drive cache unless it does not trust the drive 
 cache.  In write-through mode, it should not return to the OS with a fsync, 
 direct write, or other the OS thinks this data is persisted now call until 
 it has flushed the disk cache.  That does not mean it has to turn off the 
 disk cache.

...and here you are also right in that a write-through write cache is safe, 
with or without a battery. A write-through cache is a win for things that don't 
often fsync, but my understanding is that with a database, you end up fsyncing 
all the time, which makes a write-through cache not worth very much. The only 
good way to get good *database* performance out of spinning media is with a 
write-back cache, and the only way to make that safe is to hook up a BBU.


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] performance on new linux box

[Ben Chobot]

On Jul 14, 2010, at 6:57 PM, Scott Carey wrote:

 But none of this explains why a 4-disk raid 10 is slower than a 1 disk 
 system.  If there is no write-back caching on the RAID, it should still be 
 similar to the one disk setup.

Many raid controllers are smart enough to always turn off write caching on the 
drives, and also disable the feature on their own buffer without a BBU. Add a 
BBU, and the cache on the controller starts getting used, but *not* the cache 
on the drives.

Take away the controller, and most OS's by default enable the write cache on 
the drive. You can turn it off if you want, but if you know how to do that, 
then you're probably also the same kind of person that would have purchased a 
raid card with a BBU.
-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] performance on new linux box

[Ben Chobot]

On Jul 15, 2010, at 12:40 PM, Ryan Wexler wrote:

 On Wed, Jul 14, 2010 at 7:50 PM, Ben Chobot be...@silentmedia.com wrote:
 On Jul 14, 2010, at 6:57 PM, Scott Carey wrote:
 
  But none of this explains why a 4-disk raid 10 is slower than a 1 disk 
  system.  If there is no write-back caching on the RAID, it should still be 
  similar to the one disk setup.
 
 Many raid controllers are smart enough to always turn off write caching on 
 the drives, and also disable the feature on their own buffer without a BBU. 
 Add a BBU, and the cache on the controller starts getting used, but *not* the 
 cache on the drives.
 
 Take away the controller, and most OS's by default enable the write cache on 
 the drive. You can turn it off if you want, but if you know how to do that, 
 then you're probably also the same kind of person that would have purchased a 
 raid card with a BBU.
 --
 Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-performance
 
 Ben I don't quite follow your message.   Could you spell it out a little 
 clearer for me?
 thanks
 -ryan


Most (all?) hard drives have cache built into them. Many raid cards have cache 
built into them. When the power dies, all the data in any cache is lost, which 
is why it's dangerous to use it for write caching. For that reason, you can 
attach a BBU to a raid card which keeps the cache alive until the power is 
restored (hopefully). But no hard drive I am aware of lets you attach a 
battery, so using a hard drive's cache for write caching will always be 
dangerous.

That's why many raid cards will always disable write caching on the hard drives 
themselves, and only enable write caching using their own memory when a BBU is 
installed. 

Does that make more sense?

Re: [PERFORM] performance on new linux box

[Ben Chobot]

On Jul 15, 2010, at 9:30 AM, Scott Carey wrote:

 Many raid controllers are smart enough to always turn off write caching on 
 the drives, and also disable the feature on their own buffer without a BBU. 
 Add a BBU, and the cache on the controller starts getting used, but *not* 
 the cache on the drives.
 
 This does not make sense.
 Write caching on all hard drives in the last decade are safe because they 
 support a write cache flush command properly.  If the card is smart it 
 would issue the drive's write cache flush command to fulfill an fsync() or 
 barrier request with no BBU.

You're missing the point. If the power dies suddenly, there's no time to flush 
any cache anywhere. That's the entire point of the BBU - it keeps the RAM 
powered up on the raid card. It doesn't keep the disks spinning long enough to 
flush caches.
-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] performance on new linux box

[Ben Chobot]

On Jul 8, 2010, at 12:37 PM, Ryan Wexler wrote:

 One thing I don't understand is why BBU will result in a huge performance 
 gain.  I thought BBU was all about power failures?

When you have a working BBU, the raid card can safely do write caching. Without 
it, many raid cards are good about turning off write caching on the disks and 
refusing to do it themselves. (Safety over performance.)
-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Architecting a database

[Ben Chobot]

On Jun 30, 2010, at 11:12 AM, t...@exquisiteimages.com wrote:

  I read a post
 earlier today that mentioned in passing that it was better to have a
 faster processor than more cores.

This really depends on your workload and how much you value latency vs. 
throughput. If you tend to have a lot of very simple queries, more cores = 
more throughput, and it may not matter much if your queries take 20ms or 30ms 
if you can be doing a dozen or two more of them concurrently in an AMD system 
than in an Intel one. On the other hand, if you have less clients, or more 
latency-sensitive clients, then fewer-but-faster cores is usually a win.

Either way, the amount of power you can get for your money is pretty impressive.
-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

[SPAM] Re: [PERFORM] shared_buffers advice

[Ben Chobot]

On May 24, 2010, at 4:25 AM, Konrad Garus wrote:

 Do shared_buffers duplicate contents of OS page cache? If so, how do I
 know if 25% RAM is the right value for me? Actually it would not seem
 to be true - the less redundancy the better.

You can look into the pg_buffercache contrib module. 

 Another question - is there a tool or built-in statistic that tells
 when/how often/how much a table is read from disk? I mean physical
 read, not poll from OS cache to shared_buffers.

Well, the pg_stat_* tables tell you how much logical IO is going on, but 
postgres has no way of knowing how effective the OS or disk controller caches 
are.
-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

[PERFORM] function performs differently with different values

[Ben Chobot]

I've got a sql language function which does a fairly simple select from a 
table. If I give it one value, it performs quickly (half a ms). If I give it 
another value, it does not (1.1 seconds). When I run the equivalent select 
outside of the function, both values perform roughly the same (even though one 
value returns 140k more rows, as expected). 

My understanding is that this generally happens because the plan should be 
different for the different values, but the first time the function is run it 
caches the plan for one of the values and will never use the appropriate plan 
for the second value. However, when I do an explain analyze of the raw sql for 
both values, I get the same plan. So my understanding must be wrong?

I suppose the other possibility is that the slower value is slower in a 
function because it's returning 140k more rows and the function has to deal 
with that additional data. but that seems far-fetched, given that each row 
is just an int.
-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Testing FusionIO

[Ben Chobot]

On Mar 17, 2010, at 7:41 AM, Brad Nicholson wrote:

 As an aside, some folks in our Systems Engineering department here did
 do some testing of FusionIO, and they found that the helper daemons were
 inefficient and placed a fair amount of load on the server.  That might
 be something to watch of for for those that are testing them.

As another anecdote, we have 4 of the 160GB cards in a 24-core Istanbul server. 
I don't know how efficient the helper daemons are, but they do take up about 
half of one core's cycles, regardless of how busy the box actually is. So that 
sounds bad until you take into account how much that one core costs, and 
compare it to how much it would cost to have the same amount of IOPs in a 
different form. 
-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Postgres DB maintainenance - vacuum and reindex

[Ben Chobot]

Autovacuum is your friend for minimal downtime. It is configurable to let you 
adjust how invasive it will be, and you can have different settings per table 
if you wish.

As for the reindex, why do you think you will be reindexing regularly?

On Mar 15, 2010, at 10:30 PM, Meena_Ramkumar wrote:

 
 How to run vacuumdb and reindex for Postgres DB in a non-stop server? Will it
 be made without shutting the server? If so, then what will be performance
 degradation percentage?
 -- 
 View this message in context: 
 http://old.nabble.com/Postgres-DB-maintainenance---vacuum-and-reindex-tp27913694p27913694.html
 Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
 
 
 -- 
 Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-performance


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Building multiple indexes concurrently

[Ben Chobot]

On Mar 16, 2010, at 6:04 PM, Rob Wultsch wrote:

 Lets say I have a large table bigTable to which I would like to add
 two btree indexes. Is there a more efficient way to create indexes
 than:
 CREATE INDEX idx_foo on bigTable (foo);
 CREATE INDEX idx_baz on bigTable (baz);
 Or
 CREATE INDEX CONCURRENTLY idx_foo on bigTable (foo);
 CREATE INDEX CONCURRENTLY idx_baz on bigTable (baz);
 
 Are there any particular performance optimizations that would be in
 play in such a scenario?
 
 At a minimum I assume that if both of the commands were started at
 about the same time they would each scan the table in the same
 direction and whichever creation was slower would benefit from most of
 the table data it needed being prepopulated in shared buffers. Is this
 the case?

That sounds reasonable to me. You might also look at upping your 
maintenance_work_mem for your session, as well.
-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] shared_buffers advice

[Ben Chobot]

On Mar 10, 2010, at 6:22 PM, Paul McGarry wrote:

 Hi there,
 
 I'm after a little bit of advice on the shared_buffers setting (I have
 read the various docs on/linked from the performance tuning wiki page,
 some very helpful stuff there so thanks to those people).
 
 I am setting up a 64bit Linux server running Postgresql 8.3, the
 server has 64gigs of memory and Postgres is the only major application
 running on it. (This server is to go alongside some existing 8.3
 servers, we will look at 8.4/9 migration later)
 
 I'm basically wondering how the postgresql cache (ie shared_buffers)
 and the OS page_cache interact. The general advice seems to be to
 assign 1/4 of RAM to shared buffers.
 
 I don't have a good knowledge of the internals but I'm wondering if
 this will effectively mean that roughly the same amount of RAM being
 used for the OS page cache will be used for redundantly caching
 something the Postgres is caching as well?
 
 IE when Postgres reads something from disk it will go into both the OS
 page cache and the Postgresql shared_buffers and the OS page cache
 copy is unlikely to be useful for anything.
 
 If that is the case what are the downsides to having less overlap
 between the caches, IE heavily favouring one or the other, such as
 allocating shared_buffers to a much larger percentage (such as 90-95%
 of expected 'free' memory).

Cache isn't all you have to worry about. There's also work_mem and the number 
of concurrent queries that you expect, and those may end up leaving you less 
than 25% of ram for shared_buffers - though probably not in your case. Also, 
I've read that 10GB is the upper end of where shared_buffers becomes useful, 
though I'm not entirely sure why. I think that rule of thumb has its roots in 
some heuristics around the double buffering effects you're asking about.

I *can* say a 10GB shared_buffer value is working well with my 128GB of 
RAM. whether or not it's optimal, I couldn't say without a lot of 
experimentation I can't afford to do right now. You might have a look at the 
pg_buffercache contrib module. It can tell you how utilized your shared buffers 
are.
-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Testing FusionIO

[Ben Chobot]

We've enjoyed our FusionIO drives very much. They can do 100k iops without 
breaking a sweat. Just make sure you shut them down cleanly - it can up to 30 
minutes per card to recover from a crash/plug pull test. 

I also have serious questions about their longevity and failure mode when the 
flash finally burns out. Our hardware guys claim they have overbuilt the amount 
of flash on the card to be able to do their heavy writes for 5 years, but I 
remain skeptical. 

On Mar 8, 2010, at 6:41 AM, Devrim GÜNDÜZ wrote:

 Hi,
 
 I have a FusionIO drive to test for a few days. I already ran iozone and
 bonnie++ against it. Does anyone have more suggestions for it?
 
 It is a single drive (unfortunately).
 
 Regards,
 -- 
 Devrim GÜNDÜZ
 PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer
 PostgreSQL RPM Repository: http://yum.pgrpms.org
 Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
 http://www.gunduz.org  Twitter: http://twitter.com/devrimgunduz


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Testing FusionIO

[Ben Chobot]

On Mar 8, 2010, at 12:50 PM, Greg Smith wrote:

 Ben Chobot wrote:
 We've enjoyed our FusionIO drives very much. They can do 100k iops without 
 breaking a sweat. Just make sure you shut them down cleanly - it can up to 
 30 minutes per card to recover from a crash/plug pull test.   
 
 Yeah...I got into an argument with Kenny Gorman over my concerns with how 
 they were handling durability issues on his blog, the reading I did about 
 them never left me satisfied Fusion was being completely straight with 
 everyone about this area:  http://www.kennygorman.com/wordpress/?p=398
 
 If it takes 30 minutes to recover, but it does recover, I guess that's better 
 than I feared was the case with them.  Thanks for reporting the plug pull 
 tests--I don't trust any report from anyone about new storage hardware that 
 doesn't include that little detail as part of the testing.  You're just 
 asking to have your data get lost without that basic due diligence, and I'm 
 sure not going to even buy eval hardware from a vendor that appears evasive 
 about it.  There's a reason I don't personally own any SSD hardware yet.

Of course, the plug pull test can never be conclusive, but we never lost any 
data the handful of times we did it. Normally we'd do it more, but with such a 
long reboot cycle

But from everything we can tell, FusionIO does do reliability right.
-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] How to troubleshoot high mem usage by postgres?

[Ben Chobot]

On Feb 27, 2010, at 2:29 PM, Chris wrote:

 Hi, I'm having an issue where a postgres process is taking too much
 memory when performing many consecutive inserts and updates from a PHP

[snip]

In your postgresql.conf file, what are the settings for work_mem and 
shared_buffers?

Re: [PERFORM] bgwriter, checkpoints, curious (seeing delays)

[Ben Chobot]

On Feb 26, 2010, at 11:23 AM, Tory M Blue wrote:

 On Fri, Feb 26, 2010 at 5:09 AM, Kevin Grittner
 kevin.gritt...@wicourts.gov wrote:
 Tory M Blue  wrote:
 
 2010-02-25 22:53:13 PST LOG: checkpoint starting: time
 2010-02-25 22:53:17 PST postgres postgres [local] LOG: unexpected
 EOF on client connection
 2010-02-25 22:55:43 PST LOG: checkpoint complete: wrote 34155
 buffers (17.8%); 0 transaction log file(s) added, 0 removed, 15
 recycled; write=150.045 s, sync=0.000 s, total=150.046 s
 
 Did that unexpected EOF correspond to a connection attempt that gave
 up based on time?
 
 -Kevin
 
 Kevin
 
 Good question, I'm unclear what that was. I mean it's a LOG, so not a
 client connection, that really kind of confused me. I don't normally
 see EOF of client and an EOF on client from local, that's really
 really weird

We see that from our monitoring software testing port 5432.

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] moving pg_xlog -- yeah, it's worth it!

[Ben Chobot]

On Feb 23, 2010, at 2:23 PM, Kevin Grittner wrote:

 
 Here are the values from our two largest and busiest systems (where
 we found the pg_xlog placement to matter so much).  It looks to me
 like a more aggressive bgwriter would help, yes?
 
 cir= select * from pg_stat_bgwriter ;
 -[ RECORD 1 ]--+
 checkpoints_timed  | 125996
 checkpoints_req| 16932
 buffers_checkpoint | 342972024
 buffers_clean  | 343634920
 maxwritten_clean   | 9928
 buffers_backend| 575589056
 buffers_alloc  | 52397855471
 
 cir= select * from pg_stat_bgwriter ;
 -[ RECORD 1 ]--+
 checkpoints_timed  | 125992
 checkpoints_req| 16840
 buffers_checkpoint | 260358442
 buffers_clean  | 474768152
 maxwritten_clean   | 9778
 buffers_backend| 565837397
 buffers_alloc  | 71463873477
 
 Current settings:
 
 bgwriter_delay = '200ms'
 bgwriter_lru_maxpages = 1000
 bgwriter_lru_multiplier = 4
 
 Any suggestions on how far to push it?

I don't know how far to push it, but you could start by reducing the delay time 
and observe how that affects performance.

Re: [PERFORM] disk space usage unexpected

[Ben Chobot]

On Feb 15, 2010, at 1:25 PM, Rose Zhou wrote:

 Thanks Ben:
  
 I will adjust the auto vacuum parameters. It is on now, maybe not frequently 
 enough.
 How to get the disk space back to OS? Will a Vacuum Full Verbose get the disk 
 space back to OS?
  
  

Yes, but it might bloat your indexes. Do you actually need to get your disk 
space back? If you did, would the database just eat it up again after more 
activity?

[PERFORM] another 8.1-8.4 regression

[Ben Chobot]

I'm having problems with another one of my queries after moving from 8.1.19 to 
8.4.2. On 8.1.19, the plan looked like this:

http://wood.silentmedia.com/bench/8119

That runs pretty well. On 8.4.2, the same query looks like this:

http://wood.silentmedia.com/bench/842_bad

If I turn off mergejoin and hashjoin, I can get 8.4.2 to spit out this:

http://wood.silentmedia.com/bench/842_better

...which it thinks is going to suck but which does not. 

The query and relevant table definitions are here:

http://wood.silentmedia.com/bench/query_and_definitions


Any suggestions? I'm guessing the problem is with the absurd over-estimation on 
the nested loop under the sort node, but I'm not sure why it's so bad. 

Re: [PERFORM] another 8.1-8.4 regression

[Ben Chobot]

On Feb 16, 2010, at 1:29 PM, Ben Chobot wrote:

 I'm having problems with another one of my queries after moving from 8.1.19 
 to 8.4.2. On 8.1.19, the plan looked like this:
 
 http://wood.silentmedia.com/bench/8119
 
 That runs pretty well. On 8.4.2, the same query looks like this:
 
 http://wood.silentmedia.com/bench/842_bad
 
 If I turn off mergejoin and hashjoin, I can get 8.4.2 to spit out this:
 
 http://wood.silentmedia.com/bench/842_better
 
 ...which it thinks is going to suck but which does not. 
 
 The query and relevant table definitions are here:
 
 http://wood.silentmedia.com/bench/query_and_definitions
 
 
 Any suggestions? I'm guessing the problem is with the absurd over-estimation 
 on the nested loop under the sort node, but I'm not sure why it's so bad. 


After looking at this some more, I'm pretty confused at both of 8.4.2's plans. 
They both have a Nested Loop node in them where the expected row count is a bit 
over 2 million, and yet the inner nodes have expected row counts of 1 and 152. 
I was under the impression that a nested loop between R and S would return no 
more than R*S?

Re: [PERFORM] 8.1 - 8.4 regression

[Ben Chobot]

On Feb 15, 2010, at 7:59 AM, Kevin Grittner wrote:

 Could you show the query, along with table definitions (including
 indexes)?

Oh, yeah, I suppose that would help. :)

http://wood.silentmedia.com/bench/query_and_definitions

(I'd paste them here for posterity but I speculate the reason my first few 
attempts to ask this question never went through were because of the size of 
the email.)
-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] 8.1 - 8.4 regression

[Ben Chobot]

Awesome, that did the trick. Thanks Tom! So I understand better, why is my case 
not the normal, better case?

(I assume the long-term fix is post-9.0, right?)

On Feb 15, 2010, at 9:26 AM, Tom Lane wrote:

 Ben Chobot be...@silentmedia.com writes:
 On Feb 15, 2010, at 7:59 AM, Kevin Grittner wrote:
 Could you show the query, along with table definitions (including
 indexes)?
 
 Oh, yeah, I suppose that would help. :)
 
 http://wood.silentmedia.com/bench/query_and_definitions
 
 It looks like the problem is that the EXISTS sub-query is getting
 converted into a join; which is usually a good thing but in this case it
 interferes with letting the users table not be scanned completely.
 The long-term fix for that is to support nestloop inner indexscans where
 the index key comes from more than one join level up, but making that
 happen isn't too easy.
 
 In the meantime, I think you could defeat the optimization by
 inserting LIMIT 1 in the EXISTS sub-query.
 
   regards, tom lane


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] disk space usage unexpected

[Ben Chobot]

On Feb 15, 2010, at 11:59 AM, Rose Zhou wrote:

 Good day,
 
 I have a PostgreSQL 8.4 database installed on WinXP x64 with very heavy
 writing and updating on a partitioned table. Sometimes within one minute,
 there are tens of file with size=1,048,576kb (such as
 filenode.1,filenode.2,...filenode.43) created in the database subdirectory
 within PGDATA/base. 
 
 This caused the disk space quickly used up. Is this expected?

It's expected if you're doing lots of inserts, and/or lots of updates or 
deletes without an appropriate amount of vacuuming.



-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

[PERFORM] 8.1 - 8.4 regression

[Ben Chobot]

(Apologies if this ends up coming through multiple times - my first attempts 
seem to have gotten stuck.)

We recently took the much needed step in moving from 8.1.19 to 8.4.2. We took 
the downtime opportunity to also massively upgrade our hardware. Overall, this 
has been the major improvement you would expect, but there is at least one 
query which has degraded in performance quite a bit. Here is the plan on 8.4.2:
http://wood.silentmedia.com/bench/842

Here is the very much less compact plan for the same query on 8.1.19:
http://wood.silentmedia.com/bench/8119

I think the problem might be that 8.1.19 likes to use a few indexes which 8.4.2 
doesn't seem to think would be worthwhile. Perhaps that's because on the new 
hardware almost everything fits into ram, but even so, it would be better if 
those indexes were used. The other differences I can think of are 
random_page_cost (2 on the new hardware vs. 2.5 on the old), a ten-fold 
increase in effective_cache_size, doubling work_mem from 8MB to 16MB, and that 
we analyze up to 100 samples per attribute on 8.4.2, while our 8.1.19 install 
does 10 at most. Still, the estimates for both plans seem fairly accurate, at 
least where there are differences in which indexes are getting used.

Everything has been analyzed recently, and given that 8.4.2 already has 10x 
more analysis samples than 8.1.19, I'm not sure what to do to coax it towards 
using those indexes.
-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

[PERFORM] hardware priority for an SSD database?

[Ben Chobot]

We're looking to upgrade our database hardware so that it can sustain  
us while we re-architect some of the more fundamental issues with our  
applications. The first thing to spend money on is usually disks, but  
our database currently lives almost entirely on flash storage, so  
that's already nice and fast. My question is, what we should spend  
money on next?


With most data stored in flash, does it still make sense to buy as  
much ram as possible? RAM is still faster than flash, but while it's  
cheap, it isn't free, and our database is a couple hundred GB in size.


We also have several hundred active sessions. Does it makes sense to  
sacrifice some memory speed and go with 4 6-core Istanbul processors?  
Or does it make more sense to limit ourselves to 2 4-core Nehalem  
sockets and get Intel's 1333 MHz DDR3 memory and faster cores?


Our queries are mostly simple, but we have a lot of them, and their  
locality tends to be low. FWIW, about half are selects.


Does anybody have any experience with these kinds of tradeoffs in the  
absence of spinning media? Any insight would be much appreciated. From  
the information I have right now, trying to figuring out how to  
optimally spend our budget feels like a shot in the dark.


Thanks!

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Load experimentation

[Ben Brehmer]

Hi Andy,

Load is chugging along. We've optimized our postgres conf as much as 
possible but are seeing the inevitable I/O bottleneck. I had the same 
thought as you (converting inserts into copy's) a while back but 
unfortunately each file has many inserts into many different tables. 
Potentially I could rip through this with a little MapReduce job on 
50-100 nodes, which is still something I might do.


One thought we are playing with was taking advantage of 4 x 414GB EBS 
devices in a RAID0 configuration. This would spread disk writes across 4 
block devices.


Right now I'm wrapping about 1500 inserts in a transaction block. Since 
its an I/O bottlenecks, COPY statements might not give me much advantage.


Its definitely a work in progress :)

Ben


On 09/12/2009 5:31 AM, Andy Colson wrote:

On 12/07/2009 12:12 PM, Ben Brehmer wrote:

Hello All,

I'm in the process of loading a massive amount of data (500 GB). After
some initial timings, I'm looking at 260 hours to load the entire 500GB.
10 days seems like an awfully long time so I'm searching for ways to
speed this up. The load is happening in the Amazon cloud (EC2), on a
m1.large instance:
-7.5 GB memory
-4 EC2 Compute Units (2 virtual cores with 2 EC2 Compute Units each)
-64-bit platform


So far I have modified my postgresql.conf file (PostgreSQL 8.1.3). The
modifications I have made are as follows:

shared_buffers = 786432
work_mem = 10240
maintenance_work_mem = 6291456
max_fsm_pages = 300
wal_buffers = 2048
checkpoint_segments = 200
checkpoint_timeout = 300
checkpoint_warning = 30
autovacuum = off


There are a variety of instance types available in the Amazon cloud
(http://aws.amazon.com/ec2/instance-types/), including high memory and
high CPU. High memory instance types come with 34GB or 68GB of memory.
High CPU instance types have a lot less memory (7GB max) but up to 8
virtual cores. I am more than willing to change to any of the other
instance types.

Also, there is nothing else happening on the loading server. It is
completely dedicated to the load.

Any advice would be greatly appreciated.

Thanks,

Ben



I'm kind of curious, how goes the load?  Is it done yet?  Still 
looking at days'n'days to finish?


I was thinking... If the .sql files are really nicely formatted, it 
would not be too hard to whip up a perl script to run as a filter to 
change the statements into copy's.


Each file would have to only fill one table, and only contain inserts, 
and all the insert statements would have to set the same fields.  (And 
I'm sure there could be other problems).


Also, just for the load, did you disable fsync?

-Andy



--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

[PERFORM] Load experimentation

[Ben Brehmer]

Hello All,

I'm in the process of loading a massive amount of data (500 GB). After 
some initial timings, I'm looking at 260 hours to load the entire 500GB. 
10 days seems like an awfully long time so I'm searching for ways to 
speed this up. The load is happening in the Amazon cloud (EC2), on a 
m1.large instance:

-7.5 GB memory
-4 EC2 Compute Units (2 virtual cores with 2 EC2 Compute Units each)
-64-bit platform


So far I have modified my postgresql.conf  file (PostgreSQL 8.1.3). The 
modifications I have made are as follows:


shared_buffers = 786432
work_mem = 10240
maintenance_work_mem = 6291456
max_fsm_pages = 300
wal_buffers = 2048
checkpoint_segments = 200
checkpoint_timeout = 300
checkpoint_warning = 30
autovacuum = off


There are a variety of instance types available in the Amazon cloud 
(http://aws.amazon.com/ec2/instance-types/), including high memory and 
high CPU. High memory instance types come with 34GB or 68GB of memory. 
High CPU instance types have a lot less memory (7GB max) but up to 8 
virtual cores. I am more than willing to change to any of the other 
instance types.


Also, there is nothing else happening on the loading server. It is 
completely dedicated to the load.


Any advice would be greatly appreciated.

Thanks,

Ben

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Load experimentation

[Ben Brehmer]

Kevin,

This is running on on x86_64-unknown-linux-gnu, compiled by GCC gcc 
(GCC) 4.1.2 20080704 (Red Hat 4.1.2-44)


Ben

On 07/12/2009 10:33 AM, Kevin Grittner wrote:

Ben Brehmerbenbreh...@gmail.com  wrote:

   

-7.5 GB memory
-4 EC2 Compute Units (2 virtual cores with 2 EC2 Compute Units
each)
-64-bit platform
 


What OS?

   

(PostgreSQL 8.1.3)
 


Why use such an antiquated, buggy version?  Newer versions are
faster.

-Kevin

   


--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Load experimentation

[Ben Brehmer]

Thanks for the quick responses. I will respond to all questions in one 
email:


By Loading data I am implying: psql -U postgres -d somedatabase -f 
sql_file.sql.  The sql_file.sql contains table creates and insert 
statements. There are no indexes present nor created during the load.


OS: x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20080704 
(Red Hat 4.1.2-44)


PostgreSQL: I will try upgrading to latest version.

COPY command: Unfortunately I'm stuck with INSERTS due to the nature 
this data was generated (Hadoop/MapReduce).


Transactions: Have started a second load process with chunks of 1000 
inserts wrapped in a transaction. Its dropped the load time for 1000 
inserts from 1 Hour to 7 minutes :)


Disk Setup: Using a single disk Amazon image for the destination 
(database). Source is coming from an EBS volume. I didn't think there 
were any disk options in Amazon?



Thanks!

Ben





On 07/12/2009 10:39 AM, Thom Brown wrote:
2009/12/7 Kevin Grittner kevin.gritt...@wicourts.gov 
mailto:kevin.gritt...@wicourts.gov


Ben Brehmer benbreh...@gmail.com mailto:benbreh...@gmail.com
wrote:

 -7.5 GB memory
 -4 EC2 Compute Units (2 virtual cores with 2 EC2 Compute Units
each)
 -64-bit platform

What OS?

 (PostgreSQL 8.1.3)

Why use such an antiquated, buggy version?  Newer versions are
faster.

-Kevin



I'd agree with trying to use the latest version you can.

How are you loading this data?  I'd make sure you haven't got any 
indices, primary keys, triggers or constraints on your tables before 
you begin the initial load, just add them after.  Also use either the 
COPY command for loading, or prepared transactions.  Individual insert 
commands will just take way too long.


Regards

Thom

Re: [PERFORM] Load experimentation

[Ben Brehmer]

Thanks for all the responses. I have one more thought;

Since my input data is split into about 200 files (3GB each), I could 
potentially spawn one load command for each file. What would be the 
maximum number of input connections Postgres can handle without bogging 
down? When I say 'input connection' I mean psql -U postgres -d dbname 
-f one_of_many_sql_files.


Thanks,
Ben



On 07/12/2009 12:59 PM, Greg Smith wrote:

Ben Brehmer wrote:
By Loading data I am implying: psql -U postgres -d somedatabase -f 
sql_file.sql.  The sql_file.sql contains table creates and insert 
statements. There are no indexes present nor created during the load.
COPY command: Unfortunately I'm stuck with INSERTS due to the nature 
this data was generated (Hadoop/MapReduce).
Your basic options here are to batch the INSERTs into bigger chunks, 
and/or to split your data file up so that it can be loaded by more 
than one process at a time.  There's some comments and links to more 
guidance here at http://wiki.postgresql.org/wiki/Bulk_Loading_and_Restores


--
Greg Smith2ndQuadrant   Baltimore, MD
PostgreSQL Training, Services and Support
g...@2ndquadrant.com   www.2ndQuadrant.com
   

Re: [PERFORM] RAID card recommendation

[Ben Chobot]

On Nov 24, 2009, at 9:23 AM, Matthew Wakeling wrote:

We're about to purchase a new server to store some of our old  
databases, and I was wondering if someone could advise me on a RAID  
card. We want to make a 6-drive SATA RAID array out of 2TB drives,  
and it will be RAID 5 or 6 because there will be zero write traffic.  
The priority is stuffing as much storage into a small 2U rack as  
possible, with performance less important. We will be running Debian  
Linux.


People have mentioned Areca as making good RAID controllers. We're  
looking at the Areca ARC-1220 PCI-Express x8 SATA II as a  
possibility. Does anyone have an opinion on whether it is a turkey  
or a star?


We've used that card and have been quite happy with it. Looking  
through the release notes for firmware upgrades can be pretty worrying  
(you needed to fix what?!), but we never experienced any problems  
ourselves, and its not like 3ware release notes are any different.


But the main benefits of a RAID card are a write cache and easy hot  
swap. It sounds like you don't need a write cache. Can you be happy  
with the kernel's hotswap ability?

Re: [PERFORM] Databases vs Schemas

[Ben Chobot]

Scott Otis wrote:


I am seeking advice on what the best setup for the following would be.

 

My company provides a hosted web calendaring solution for school 
districts.  For each school district we have a separate database.  
Each database has 57 tables.






Over the next couple of months we will be creating an instance of our 
solution for each public school district in the US which is around 
18,000. 




Why are you trying to keep all this information on one server? It seems 
like you have such perfectly independent silos of data, why not take the 
opportunity to scale out horizontally? It's usually a lot cheaper to buy 
4 machines of power x than one machine of power (4x).


--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] SQL select query becomes slow when using limit (with no offset)

[Devin Ben-Hur]

Robert Haas wrote:

On Mon, Aug 10, 2009 at 11:19 AM, Kevin Grittnerkevin.gritt...@wicourts.gov 
wrote:

(2)  Somehow use effective_cache_size in combination with some sort of
current activity metrics to dynamically adjust random access costs.
(I know, that one's total hand-waving, but it seems to have some
possibility of better modeling reality than what we currently do.)


I was disappointed when I learned that effective_cache_size doesn't get 
generally used to predict the likelihood of a buffer fetch requiring 
physical io.



Yeah, I gave a lightning talk on this at PGcon, but I haven't had time
to do anything with it.  There are a couple of problems.  One is that
you have to have a source for your current activity metrics.  Since a
lot of the pages of interest will be in the OS buffer pool rather than
PG shared buffers, there's no easy way to handle this


While there are portability concerns, mmap + mincore works across BSD, 
Linux, Solaris and will return a vector of file pages in the OS buffer 
pool.  So it's certainly possible that on supported systems, an activity 
monitor can have direct knowledge of OS caching effectiveness on a per 
relation/index basis.


--
-Devin

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Very big insert/join performance problem (bacula)

[Devin Ben-Hur]

Marc Cousin wrote:

Le Thursday 16 July 2009 22:07:25, Kevin Grittner a écrit :

Marc Cousin cousinm...@gmail.com wrote:

the hot parts of these 2 tables are extremely likely to be in the
database or linux cache (buffer hit rate was 97% in the example
provided). Moreover, the first two queries of the insert procedure
fill the cache for us...


Ok, so to sum it up, should I keep these values (I hate doing this :) ) ? 
Would there be a way to approximately evaluate them regarding to the expected 
buffer hit ratio of the query ?


cached_buffer_cost = 0.01
effective_page_cost =
  ((1 - expected_cache_hit_ratio) * standard_page_cost)
+ (expected_cache_hit_ratio   * cached_buffer_cost)

If your assumption is only about these queries in particular, rather 
than applicable across the board, you should set the page_costs just for 
this query and reset them or close the connection after.


--
-Devin

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Very big insert/join performance problem (bacula)

[Devin Ben-Hur]

Marc Cousin wrote:

This mail contains the asked plans :
Plan 1
around 1 million records to insert, seq_page_cost 1, random_page_cost 4



 -  Hash  (cost=425486.72..425486.72 rows=16746972 width=92) (actual 
time=23184.196..23184.196 rows=16732049 loops=1)
   -  Seq Scan on path  (cost=0.00..425486.72 rows=16746972 
width=92) (actual time=0.004..7318.850 rows=16732049 loops=1)



   -  Hash  (cost=1436976.15..1436976.15 rows=79104615 width=35) (actual 
time=210831.840..210831.840 rows=79094418 loops=1)
 -  Seq Scan on filename  (cost=0.00..1436976.15 rows=79104615 
width=35) (actual time=46.324..148887.662 rows=79094418 loops=1)


This doesn't address the cost driving plan question, but I think it's a 
bit puzzling that a seq scan of 17M 92-byte rows completes in 7 secs, 
while a seqscan of 79M 35-byte rows takes 149secs.  It's about 4:1 row 
ratio, less than 2:1 byte ratio, but a 20:1 time ratio.  Perhaps there's 
some terrible bloat on filename that's not present on path?  If that seq 
scan time on filename were proportionate to path this plan would 
complete about two minutes faster (making it only 6 times slower instead 
of 9 :).


--
-Devin

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] performance for high-volume log insertion

[Ben Chobot]

On Mon, 20 Apr 2009, da...@lang.hm wrote:

one huge advantage of putting the sql into the configuration is the ability 
to work around other users of the database.


+1 on this. We've always found tools much easier to work with when they 
could be adapted to our schema, as opposed to changing our process for the 
tool.


--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] scheduling autovacuum at lean hours only.

[Ben]

On Feb 11, 2009, at 6:57 AM, Rajesh Kumar Mallah wrote:


why is it not a good idea to give end users control over when they
want to run it ?


There's nothing stopping you from just turning off autovacuum and  
running vacuum manually. The point of autovacuum is to vacuum as  
needed.



--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] scheduling autovacuum at lean hours only.

[Ben Chobot]

On Wed, 11 Feb 2009, Scott Carey wrote:


On a large partitioned database, ordinary vacuum is a very very difficult 
option.

Most of the time on such a system, most tables are dormant with respect to 
writes and never need to be vacuumed.  A 'daily vacuum' is likely to take a 
full day to run on larger systems. Since ordinary vacuum can't be run on 
subsets of tables without explicitly naming them one at a time (can't just 
vacuum a schema, tablespace, or use a regex to match table names), good luck 
using it effectively if you have a few thousand tables in partitions.  You'll 
have to have application code or a script with knowledge of all the partition 
names and which are in need of an analyze/vacuum.

Autovacuum is good enough in recent versions to be tuned to have very low 
impact though.  If you have to, rather than stop and start it, just turn the 
delay or cost settings up and down during different parts of the day.  More 
than likely however, it will be able to keep up with a single set of settings.
In particular, rather than making the delay longer, make the appropriate cost 
larger -- page miss or page dirty affect how much I/O it will do, and page hit 
will mostly affect how much CPU it uses.

Perhaps a feature request is to have a version of the manual vacuum command 
that doesn't bother running on tables that autovacuum wouldn't touch due to 
insufficient data churn.  This way, at lean hours one can manually vacuum to 
help an autovacuum that was tuned for very low impact 'catch up'.
Also, if there was some way to make vacuum not stink so badly on tables that 
were just loaded with pg_load, where it causes huge write activity for tables 
that clearly have no bloat (I believe this is a hint bits thing?).


Oh, I agree with everything you say. I'm just pointing out that if you 
really do want control over when things get vacuumed (e.g. you have a 
mostly-read database 20 hours a day and then 4 hours of heavy churn at 
night) then you can still do that if you want.


--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Need help with 8.4 Performance Testing

[Ben Chobot]

On Tue, 9 Dec 2008, Robert Haas wrote:


I don't believe the thesis.  The gap between disk speeds and memory
speeds may narrow over time, but I doubt it's likely to disappear
altogether any time soon, and certainly not for all users.


I think the not for all users is the critical part. In 2 years, we may 
(or may not) start using SSD instead of traditional drives for new 
installs, but we certainly won't be throwing out our existing servers any 
time soon just because something (much) better is now available.


--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] How many updates and inserts

[Ben]

Have a look at the pg_stat_user_tables table.

On Fri, 11 Jul 2008, Campbell, Lance wrote:


PostgreSQL: 8.2

How can I identify how many inserts and updates are being done in a
given time frame for a database?



Thanks,





Lance Campbell

Project Manager/Software Architect

Web Services at Public Affairs

University of Illinois

217.333.0382

http://webservices.uiuc.edu

My e-mail address has changed to [EMAIL PROTECTED]






--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

[PERFORM] very slow left join

[Ben]

I've inherited an Oracle database that I'm porting to Postgres, and this 
has been going quite well until now. Unfortunately, I've found one view (a 
largish left join) that runs several orders of magnitude slower on 
Postgres than it did on Oracle.


= select version();
 version
--
 PostgreSQL 8.2.4 on x86_64-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.1.1 
20070105 (Red Hat 4.1.1-52)
(1 row)


After analyzing the database, the explain analyze output for the query is:

 Nested Loop Left Join  (cost=133.51..15846.99 rows=1 width=312) (actual 
time=109.131..550711.374 rows=1248 loops=1)
   Join Filter: (log.logkey = ln.logkey)
   -  Nested Loop  (cost=133.51..267.44 rows=1 width=306) (actual 
time=15.316..74.074 rows=1248 loops=1)
 -  Merge Join  (cost=133.51..267.16 rows=1 width=325) (actual 
time=15.300..60.332 rows=1248 loops=1)
   Merge Cond: (log.eventkey = e.eventkey)
   Join Filter: ((e.clientkey = log.clientkey) AND (e.premiseskey = 
log.premiseskey))
   -  Index Scan using log_eventkey_idx on log  
(cost=0.00..3732.14 rows=36547 width=167) (actual time=0.015..25.385 rows=36547 
loops=1)
 Filter: (logicaldel = 'N'::bpchar)
   -  Sort  (cost=133.51..135.00 rows=595 width=328) (actual 
time=15.185..16.379 rows=1248 loops=1)
 Sort Key: e.eventkey
 -  Hash Join  (cost=1.30..106.09 rows=595 width=328) 
(actual time=0.073..2.033 rows=1248 loops=1)
   Hash Cond: ((e.clientkey = p.clientkey) AND 
(e.premiseskey = p.premiseskey))
   -  Seq Scan on event e  (cost=0.00..89.48 rows=1248 
width=246) (actual time=0.005..0.481 rows=1248 loops=1)
   -  Hash  (cost=1.14..1.14 rows=11 width=82) (actual 
time=0.059..0.059 rows=11 loops=1)
 -  Seq Scan on premises p  (cost=0.00..1.14 
rows=11 width=82) (actual time=0.004..0.020 rows=11 loops=1)
   Filter: (logicaldel = 'N'::bpchar)
 -  Index Scan using severity_pk on severity s  (cost=0.00..0.27 
rows=1 width=49) (actual time=0.007..0.009 rows=1 loops=1248)
   Index Cond: (e.severitykey = s.severitykey)
   -  Seq Scan on lognote ln1  (cost=0.00..15552.67 rows=1195 width=175) 
(actual time=1.173..440.695 rows=1244 loops=1248)
 Filter: ((logicaldel = 'N'::bpchar) AND (subplan))
 SubPlan
   -  Limit  (cost=4.30..8.58 rows=1 width=34) (actual 
time=0.171..0.171 rows=1 loops=2982720)
 InitPlan
   -  GroupAggregate  (cost=0.00..4.30 rows=1 width=110) 
(actual time=0.089..0.089 rows=1 loops=2982720)
 -  Index Scan using lognote_pk on lognote 
(cost=0.00..4.28 rows=1 width=110) (actual time=0.086..0.087 rows=1 loops=2982720)
   Index Cond: ((clientkey = $0) AND (premiseskey = 
$1) AND (logkey = $2))
   Filter: ((logicaldel = 'N'::bpchar) AND 
((lognotetext ~~ '_%;%'::text) OR (lognotetext ~~ '_%has modified Respond 
Status to%'::text)))
 -  Index Scan using lognote_pk on lognote  (cost=0.00..4.28 
rows=1 width=34) (actual time=0.170..0.170 rows=1 loops=2982720)
   Index Cond: ((clientkey = $0) AND (premiseskey = $1) AND 
(logkey = $2))
   Filter: ((logicaldel = 'N'::bpchar) AND (lognotetime = 
$3))
 Total runtime: 550712.393 ms
(31 rows)


Either side of the left join runs quite fast independently. (The full 
query also runs well when made into an inner join, but that's not the 
logic I want.) The biggest difference between running each side 
indpendently and together in a left join is that this line in the plan for 
the right side of the left join:


-  Index Scan using lognote_pk on lognote (cost=0.00..4.28 rows=1 width=110) 
(actual time=0.086..0.087 rows=1 loops=2982720)

...becomes this line when run independantly:

-  Index Scan using lognote_pk on lognote  (cost=0.00..4.28 rows=1 width=110) 
(actual time=0.086..0.087 rows=1 loops=2390)

That's quite a few more loops in the left join. Am I right to think that 
it's looping so much because the analyzer is so far off when guessing the 
rows for the left side of the join (1 vs. 1248)? Or is there something 
else going on? I've tried bumping up analyze stats on a few columns, but 
I'm not too sure how to spot which columns it might help with and, sure 
enough, it didn't help.



The actual query:

select *
from
  (
select *
from
  event e,
  severity s,
  premises p,
  log
where
  p.clientkey = e.clientkey and
  p.premiseskey = e.premiseskey and
  p.logicaldel = 'N' and
  log.logicaldel = 'N' and
  e.clientkey = log.clientkey and
  e.premiseskey = log.premiseskey 

Re: [PERFORM] very slow left join

[Ben]

On Fri, 16 May 2008, Scott Marlowe wrote:


Just for giggles, try running the query like so:

set enable_nestloop = off;
explain analyze ...

and see what happens.  I'm guessing that the nested loops are bad choices here.


You guess correctly, sir! Doing so shaves 3 orders of magnitude off the 
runtime. That's nice. :) But that brings up the question of why postgres 
thinks nested loops are the way to go? It would be handy if I could make 
it guess correctly to begin with and didn't have to turn nested loops off 
each time I run this.




Table public.event
Column |Type |   Modifiers
+-+
 clientkey  | character(30)   | not null
 premiseskey| character(30)   | not null
 eventkey   | character(30)   | not null
 severitykey| character(30)   |


Do these really need to be character and not varchar?  varchar / text
are better optimized in pgsql, and character often need to be cast
anyway, so you might as well start with varchar.  Unless you REALLY
need padding in your db, avoid char(x).


Unfortuantely, the people who created this database made all keys 30 
character strings, and we're not near a place in our release cycle where 
we can fix that.


--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] very slow left join

[Ben]

On Fri, 16 May 2008, Scott Marlowe wrote:


Well, I'm guessing that you aren't in locale=C and that the text


Correct, I am not. And my understanding is that by moving to the C locale, 
I would loose utf8 validation, so I don't want to go there. Though, it's 
news to me that I would get any kind of select performance boost with 
locale=C. Why would it help?



functions in your query aren't indexed.  Try creating an index on them
something like:

create index abc_txtfield_func on mytable (substring(textfield,1,5));

etc and see if that helps.


It does not. :(

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] performance tools

[Ben Chobot]

Have you tried the archive search tool for this very mailing list?  
There's a wealth of information imparted all the time, and tuning is  
generally about knowledge of what's happening, not blindly following  
the output of some program.



On Mar 17, 2008, at 8:46 AM, sathiya psql wrote:


i thought many a replies will come... but only one..

common guys... it may be helping you in tuning your database  
indirectly, post that tool also, give some informations such as


Tool Name: Schemaspy
Open Source: YES
Database: Postgres
URL: http://sourceforge.net/projects/schemaspy/1
Following can be taken as optional..
Easily Installable: YES
Performance TUNING tool: Partially YES ( YES / NO / Partially Yes )
ER diagram tool: Yes / No
Query Analysis Tool: Yes / No

Probably other informations also

common start sharing...

[PERFORM] Wrong number of rows estimation by the planner

[Yonatan Ben-Nes]

Hi all,

When I'm doing an explain analyze to a query of mine I notice that the
number of estimated rows by the planner is a lot smaller then the actual
number of rows, I'm afraid that this make my queries slower.

A query for example is:

EXPLAIN ANALYZE
SELECT product_id,product_name
FROM product
WHERE product_keywords_vector @@ plainto_tsquery('default', 'black') AND
rank(product_keywords_vector, plainto_tsquery('default', 'black'))  0.4 AND
product_status = TRUE AND product_type = 'comparison'
ORDER BY ((product_buy_number * 4) + product_view_number + 1) *
rank(product_keywords_vector, plainto_tsquery('default', 'black')) DESC;

QUERY PLAN

 Sort  (cost=10543.67..10544.81 rows=455 width=297) (actual time=
1098.188..1104.606 rows=22248 loops=1)
   Sort Key: (product_buy_number * 4) + product_view_number) +
1))::double precision * rank(product_keywords_vector, '''black'''::tsquery))
   -  Bitmap Heap Scan on product  (cost=287.13..10523.59 rows=455
width=297) (actual time=50.496..1071.900 rows=22248 loops=1)
 Recheck Cond: (product_keywords_vector @@ '''black'''::tsquery)
 Filter: ((rank(product_keywords_vector, '''black'''::tsquery) 
0.4::double precision) AND product_status AND (product_type =
'comparison'::text))
 -  Bitmap Index Scan on product_product_keywords_vector  (cost=
0.00..287.02 rows=2688 width=0) (actual time=26.385..26.385 rows=72507
loops=1)
   Index Cond: (product_keywords_vector @@ '''black'''::tsquery)
 Total runtime: .507 ms
(8 rows)

Here as I understand it, at the Bitmap Index Scan on
product_product_keywords_vector the planner estimate that it will retrieve
2688 rows but it actually retrieve 72507 rows and later at the Bitmap Heap
Scan on product it estimate 455 rows and retrieve 22248 rows.

I increased the statistics of the field which the
product_product_keywords_vector index is built on by doing:
ALTER TABLE product ALTER COLUMN product_keywords_vector SET STATISTICS
1000;
ANALYZE;
REINDEX INDEX product_product_keywords_vector;

But it didn't change a thing.

Any ideas?

Thanks in advance,
Yonatan Ben-Nes

Re: [PERFORM] hardware and For PostgreSQL

[Ben]

It would probably help you to spend some time browsing the archives of 
this list for questions similar to yours - you'll find quite a lot of 
consistent answers. In general, you'll find that:


- If you can fit your entire database into memory, you'll get the best
  performance.

- If you cannot (and most databases cannot) then you'll want to get the
  fastest disk system you can.

- For reads, RAID5 isn't so bad but for writes it's near the bottom of the
  options. RAID10 is not as efficient in terms of hardware, but if you
  want performance for both reads and writes, you want RAID10.

- Your RAID card also matters. Areca cards are expensive, and a lot of
  people consider them to be worth it.

- More procs tend to be better than faster procs, because more procs let
  you do more at once and databases tend to be i/o bound more than cpu
  bound.

- More or faster procs put more contention on the data, so getting more or
  better cpus just increases the need for faster disks or more ram.

- PG is 64 bit if you compile it to be so, or if you install a 64-bit
  binary package.

and all that said, application and schema design can play a far more 
important role in performance than hardware.



On Wed, 31 Oct 2007, Ketema Harris wrote:

I am trying to build a very Robust DB server that will support 1000+ 
concurrent users (all ready have seen max of 237 no pooling being used).  i 
have read so many articles now that I am just saturated.  I have a general 
idea but would like feedback from others.


I understand query tuning and table design play a large role in performance, 
but taking that factor away
and focusing on just hardware, what is the best hardware to get for Pg to 
work at the highest level

(meaning speed at returning results)?

How does pg utilize multiple processors?  The more the better?
Are queries spread across multiple processors?
Is Pg 64 bit?
If so what processors are recommended?

I read this : 
http://www.postgresql.org/files/documentation/books/aw_pgsql/hw_performance/node12.html
POSTGRESQL uses a multi-process model, meaning each database connection has 
its own Unix process. Because of this, all multi-cpu operating systems can 
spread multiple database connections among the available CPUs. However, if 
only a single database connection is active, it can only use one CPU. 
POSTGRESQL does not use multi-threading to allow a single process to use 
multiple CPUs.


Its pretty old (2003) but is it still accurate?  if this statement is 
accurate how would it affect connection pooling software like pg_pool?


RAM?  The more the merrier right? Understanding shmmax and the pg config file 
parameters for shared mem has to be adjusted to use it.
Disks?  standard Raid rules right?  1 for safety 5 for best mix of 
performance and safety?
Any preference of SCSI over SATA? What about using a High speed (fibre 
channel) mass storage device?


Who has built the biggest baddest Pg server out there and what do you use?

Thanks!





---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq



---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

Re: [PERFORM] Memory Settings....

[Ben]

You may find this informative:

http://www.powerpostgresql.com/Downloads/annotated_conf_80.html

On Mon, 22 Oct 2007, Lee Keel wrote:


I have a client server that is dedicated to being a Postgres 8.2.4 database
server for many websites.  This server will contain approximately 15
databases each containing between 40-100 tables.  Each database will have
approximately 7 web applications pulling data from it, but there will
probably be no more than 50 simultaneous requests.  The majority of the
tables will be very small tables around 1K in total size.  However, most of
the queries will be going to the other 10-15 tables that are in each
database that will contain postgis shapes.  These tables will range in size
from 50 to 730K rows and each row will range in size from a 2K to 3MB.  The
data will be truncated and reinserted as part of a nightly process but other
than that, there won't be many writes during the day.  I am trying to tune
this server to its maximum capacity.  I would appreciate any advice on any
of the settings that I should look at.  I have not changed any of the
settings before because I have never really needed to.  And even now, I have
not experienced any bad performance, I am simply trying to turn the track
before the train gets here.

Server Specification:
Windows 2003 Enterprise R2
Dual-Quad Core 2.33GHz
8GB RAM
263 GB HD (I am not 100% on drive speed, but I think it is 15K)


Thanks in advance,
Lee Keel



This email and any files transmitted with it are confidential and intended 
solely for the use of the individual or entity to whom they are addressed. If 
you have received this email in error please notify the sender. This message 
contains confidential information and is intended only for the individual 
named. If you are not the named addressee you should not disseminate, 
distribute or copy this e-mail.



---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

[PERFORM] quickly getting the top N rows

[Ben]

If I have this:

create table foo (bar int primary key);

...then in my ideal world, Postgres would be able to use that index on bar 
to help me with this:


select bar from foo order by bar desc limit 20;

But in my experience, PG8.2 is doing a full table scan on foo, then 
sorting it, then doing the limit. I have a more complex primary key, but I 
was hoping the same concept would still apply. Am I doing something wrong, 
or just expecting something that doesn't exist?


---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

Re: [PERFORM] quickly getting the top N rows

[Ben]

On Thu, 4 Oct 2007, Bill Moran wrote:


However, 2 guesses:
1) You never analyzed the table, thus PG has awful statistics and
  doesn't know how to pick a good plan.
2) You have so few rows in the table that a seq scan is actually
  faster than an index scan, which is why PG uses it instead.


No, the tables are recently analyzed and there are a couple hundred 
thousand rows in there. But I think I just figured it out it's a 
3-column index, and two columns of that index are the same for every row. 
When I drop those two columns from the ordering restriction, the index 
gets used and things speed up 5 orders of magnitude.


Maybe the planner is smart enough to think that if a column in the order 
by clause is identical for most rows, then using an index won't help 
but not smart enough to realize that if said column is at the *end* of the 
order by arguments, after columns which do sort quite well, then it should 
use an index after all.


---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [PERFORM] quickly getting the top N rows

[Ben]

On Thu, 4 Oct 2007, Tom Lane wrote:


You're being about as clear as mud here, except that you obviously lied
about what you were doing in your first message.  If you have a planner
problem, show us the *exact* query, the *exact* table definition, and
unfaked EXPLAIN ANALYZE output.


I didn't realize that simplification was viewed as so sinister, but 
thanks, I'll remember that in the future.


The table:
 Table public.log
 Column |Type |  Modifiers
+-+-
 clientkey  | character(30)   | not null
 premiseskey| character(30)   | not null
 logkey | character(30)   | not null
 logicaldel | character(1)| default 'N'::bpchar
 lockey | character(30)   |
 devlockey  | character(30)   |
 eventkey   | character(30)   |
 logshorttext   | character varying(255)  |
 logdesc| character varying(255)  |
 loguserkey | character(30)   |
 logassetkey| character(30)   |
 logresourcekey | character(30)   |
 logtime| timestamp without time zone |
 logip  | character varying(50)   |
 logarchived| character(1)|
 logarchivedate | timestamp without time zone |
 loghasvideo| character(1)|
 loghasaudio| character(1)|
 resvehiclekey  | character(30)   |
 synccreated| character(1)|
 logtypekey | character(30)   |
Indexes:
log_pkey PRIMARY KEY, btree (clientkey, premiseskey, logkey)
eventkey_idx btree (eventkey),
log_ak1 btree (clientkey, premiseskey, logtime, logkey)


The original, slow query:

explain analyze SELECT * FROM log WHERE clientkey in 
('450101')  AND premiseskey in 
('450101') and logicaldel = 'N'

ORDER BY logtime desc, logkey desc, clientkey desc, premiseskey desc LIMIT 20 
offset 0;

QUERY PLAN

 Limit  (cost=356402.58..356402.63 rows=20 width=563) (actual 
time=215858.481..215858.527 rows=20 loops=1)
   -  Sort  (cost=356402.58..357598.25 rows=478267 width=563) (actual 
time=215858.478..215858.498 rows=20 loops=1)
 Sort Key: logtime, logkey, clientkey, premiseskey
 -  Seq Scan on log  (cost=0.00..52061.67 rows=478267 width=563) 
(actual time=29.340..100043.313 rows=475669 loops=1)
   Filter: ((clientkey = '450101'::bpchar) 
AND (premiseskey = '450101'::bpchar) AND (logicaldel = 
'N'::bpchar))
 Total runtime: 262462.582 ms
(6 rows)


Every row in log has identical clientkey and premiseskey values, so if I 
just remove those columns from the order by clause, I get this far 
superior plan:


explain analyze SELECT * FROM log WHERE clientkey in 
('450101') AND premiseskey in

('450101') and logicaldel = 'N'
ORDER BY logtime desc, logkey desc LIMIT 20 offset 0;
QUERY PLAN
---
 Limit  (cost=0.00..12.33 rows=20 width=563) (actual time=0.047..0.105 rows=20 
loops=1)
   -  Index Scan Backward using log_ak1 on log  (cost=0.00..294735.70 
rows=478267 width=563) (actual time=0.044..0.076 rows=20 loops=1)
 Index Cond: ((clientkey = '450101'::bpchar) 
AND (premiseskey = '450101'::bpchar))
 Filter: (logicaldel = 'N'::bpchar)
 Total runtime: 0.165 ms
(5 rows)


...which made me to think that maybe postgres is not using log_ak1 in the 
former case because two of the columns in the order by match every row.


Unfortunately, in this case it's not an option to alter the query. I'm 
just trying to figure out an explaination.


---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [PERFORM] quickly getting the top N rows

[Ben]

On Thu, 4 Oct 2007, Simon Riggs wrote:


In the first query, Postgres cannot use the index because the sort order
of the index does not match the sort order of the query. When you change
the sort order of the query so that it matches that of the index, then
the index is used.

If you define your index on (logtime, logkey, clientkey, premiseskey)
rather than on (clientkey, premiseskey, logtime, logkey) you will have a
fast query. Yes, the column order matters.


I thought that might explain it, but then I'm surprised that it can still 
use an index when the first two columns of the index aren't in the query. 
Wouldn't that mean that it might have to walk the entire index to find 
matching rows?


unless it's smart enough to realize that the first two columns will 
match everything. Which would be cool.


---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [PERFORM] quickly getting the top N rows

[Ben]

On Thu, 4 Oct 2007, Tom Lane wrote:


There's some limited smarts in there about deciding that leading columns
of an index don't matter to the sort ordering if they're constrained to
just one value by the query.  But it doesn't catch the case you need,
which is that columns of an ORDER BY request are no-ops when they're
constrained to just one value.


Oh, no, that explains it perfectly, because that's precisely the case I 
have - I dropped the columns from the ordering, but not the where clause. 
Thanks, now I understand the current behavior.


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

[PERFORM] Poor Performance after Upgrade

[Ben Perrault]

Hi,

I recently inherited a very old (PostgreSQL 7.0.3) database, and have 
migrated it to 8.2.4 but have run into a performance issue.


Basically, I did a dump and import into the new database, vacuumed and 
created fresh indexes and everything is work great except the following 
type of query (and similar):


 SELECT tsr.stepId, tsr.testType, tsr.problemReportId, tsr.excpt, tcr.caseId
FROM   TestCaseRun tcr, TestStepRun tsr
WHERE  tcr.parentSN = 194813
AND(tsr.testType ''
OR  tsr.problemReportId  ''
OR  tsr.excpt'')
ANDtsr.parentSN =  tcr.recordSN

What used to take 250ms or so on the old database now takes between 55 and 
60 Seconds.


On the old database, the query plan looks like this:

Unique  (cost=13074.30..13078.36 rows=32 width=68)
  -  Sort  (cost=13074.30..13074.30 rows=324 width=68)
-  Nested Loop  (cost=0.00..13060.77 rows=324 width=68)
  -  Index Scan using parentsn_tcr_indx on testcaserun tcr 
(cost=0.00..444.83 rows=111 width=16)
  -  Index Scan using parentsn_tsr_indx on teststeprun tsr 
(cost=0.00..113.42 rows=27 width=52)


And on the new database it looks like this:

 Unique  (cost=206559152.10..206559157.14 rows=336 width=137)
   -  Sort  (cost=206559152.10..206559152.94 rows=336 width=137)
 Sort Key: tsr.stepid, tsr.testtype, tsr.problemreportid, 
tsr.excpt, tcr.caseid
 -  Nested Loop  (cost=1.00..106559138.00 rows=336 
width=137)
   -  Index Scan using parentsn_tcr_indx on testcaserun tcr 
(cost=0.00..17.00 rows=115 width=11)

 Index Cond: (parentsn = 186726)
   -  Index Scan using parentsn_tsr_indx on teststeprun tsr 
(cost=0.00..56089.00 rows=75747 width=134)

 Index Cond: (tsr.parentsn = tcr.recordsn)
 Filter: ((testtype  ''::text) OR 
((problemreportid)::text  ''::text) OR (excpt  ''::text))

(9 rows)

I'm fairly familiar with PostgreSQL, but I have no idea where to start in 
trying to trouble shoot this huge performance discrepancy. The hardware 
and OS are the same.


And the data size is exactly the same between the two, and the total data 
size is about 7.5GB, with the largest table (teststeprun mentioned above) 
being about 15 million rows.


Any pointers to where to start troubleshooting this or how to change the 
query to work better would be appreciated.


cheers and thanks,
Ben Perrault
Sr. Systems Consultant
Alcatel-Lucent Internetworking

---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at

   http://www.postgresql.org/about/donate