[PERFORM] Re: Performance degradation from PostgreSQL 8.2.21 to PostgreSQL 9.3.2

[Albe Laurenz]

fx TATEISHI KOJI wrote:
> Issuing exactly the same query as PostgreSQL 8.2.21 and PostgreSQL 9.3.2 will 
> slow the
> response by 6.4 ms on average.
> What could be the cause?
> Measurement method is as follows.
> ・ PostgreSQL 8.2.21 installation
>  ★Measurement
> ・ Export DUMP of PostgreSQL 8.2.21
> ・ PostgreSQL 8.2.21 uninstallation
> ・ PostgreSQL 9.3.2 installation
> ・ Dump import
>  ★Measurement

It is impossible to answer this with certainty without
EXPLAIN (ANALYZE, BUFFERS) output, but my first guess is that
the statistics on the 9.3 installation are not up to date.

ANALYZE all involved tables, then try again and see if the
performance degradation has vanished.

If not, start studying the execution plans.

Are the parameters in postgresql.conf set the same?

Yours,
Laurenz Albe

-- 
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] vacuum analyze affecting query performance

[Albe Laurenz]

rverghese wrote:
> We are on Postgres 9.5, and have been running a daily vacuum analyze on the
> entire database since 8.2
> The data has grown exponentially since, and we are seeing that queries are
> now being significantly affected while the vacuum analyze runs. The query
> database is a Slony slave.
> So the question is, is this typical behavior and should we still be running
> a daily vacuum analyze on the database?

While VACUUM runs on tables, you can expect performance to get worse,
mostly because of contention for I/O resources (is that the case for you?).

Autovacuum has become *much* better since PostgreSQL 8.2.

If you cannot find a "quiet time" during which you can keep running your
daily VACUUM without causing problems, don't do it and go with autovacuum
by all means.

Autovacuum is less disruptive than normal VACUUM, it is designed to not
hog resources.

If the database is very busy and autovacuum has problems keeping up,
tune it to be more aggressive (and it will still be less disruptive
than a manual VACUUM).

Yours,
Laurenz Albe

-- 
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] Inappropriate inner table for nested loop join

[Albe Laurenz]

Akihiko Odaki wrote:
> On 2017-06-23 20:20, Albe Laurenz wrote:
>> You could either try to do something like
>>
>> SELECT *
>> FROM (SELECT "posts".*
>>FROM "posts"
>>   JOIN "follows" ON "follows"."target_account" = "posts"."account"
>>WHERE "follows"."owner_account" = $1
>>OFFSET 0) q
>> ORDER BY "posts"."timestamp"
>> LIMIT 100;
> 
> Now I wonder whether it actually sorted or not. As you said, I want to
> "find rows with the greatest 'timestamp', match with rows from 'posts'
> in a nested loop and stop as soon as it has found 100 matches".
> 
> However, it seems to query 100 records without any consideration for
> "timestamp", and then sorts them. That is not expected. Here is a
> abstract query plan:
> 
>   Limit
> ->  Sort
>   Sort Key: posts.id DESC
>   ->  Nested Loop
> ->  Seq Scan on follows
>   Filter: (owner_account = $1)
> ->  Index Scan using index_posts_on_account on posts
>   Index Cond: (account_id = follows.target_account)
> 
> index_posts_on_account is an obsolete index on "posts" and only for
> "account". So it does nothing for sorting "timestamp".

That should be fine.

It fetches all rows from "follows" that match the condition,
Then joins them will all matching rows from "posts", sorts the
result descending by "id" and returns the 100 rows with the largest
value for "id".

So you will get those 100 rows from "posts" with the largest "id"
that have a match in "follows" where the condition is fulfilled.

It is just a different plan to do the same thing that is more efficient
in your case.

Yours,
Laurenz Albe

-- 
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] Inappropriate inner table for nested loop join

[Albe Laurenz]

Akihiko Odaki wrote:
> On 2017-06-23 20:20, Albe Laurenz wrote:
>> You could either try to do something like
>>
>> SELECT *
>> FROM (SELECT "posts".*
>>FROM "posts"
>>   JOIN "follows" ON "follows"."target_account" = "posts"."account"
>>WHERE "follows"."owner_account" = $1
>>OFFSET 0) q
>> ORDER BY "posts"."timestamp"
>> LIMIT 100;
> 
> Now I wonder whether it actually sorted or not. As you said, I want to
> "find rows with the greatest 'timestamp', match with rows from 'posts'
> in a nested loop and stop as soon as it has found 100 matches".
> 
> However, it seems to query 100 records without any consideration for
> "timestamp", and then sorts them. That is not expected. Here is a
> abstract query plan:
> 
>   Limit
> ->  Sort
>   Sort Key: posts.id DESC
>   ->  Nested Loop
> ->  Seq Scan on follows
>   Filter: (owner_account = $1)
> ->  Index Scan using index_posts_on_account on posts
>   Index Cond: (account_id = follows.target_account)
> 
> index_posts_on_account is an obsolete index on "posts" and only for
> "account". So it does nothing for sorting "timestamp".

Yes, if you replace posts.timestamp with q.timestamp, it should
sort by that.

Could you send CREATE TABLE and CREATE INDEX statements so I can try it?

Yours,
Laurenz Albe

-- 
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] Inappropriate inner table for nested loop join

[Albe Laurenz]

Akihiko Odaki wrote:
> I am having a problem with nested loop join.
> 
> A database has 2 tables: "posts" and "follows".
> Table "posts" have two columns: "timestamp" and "account".
> Table "follows" have two columns: "target_account" and "owner_account".
> The database also has an index on "posts" ("account", "timestamp"), one
> on "posts"("timestamp") and on "follows" ("owner_account",
> "target_account").
> 
> Table "posts" is so big and have 10 million records.
> The number of Records with the same value for "owner_accounts" in table
> "follows" is about 100 by average.
> 
> I issue the following query:
> 
> SELECT "posts".*
>FROM "posts"
>JOIN "follows" ON "follows"."target_account" = "posts"."account"
>WHERE "follows"."owner_account" = $1
>ORDER BY "posts"."timestamp"
>LIMIT 100
> 
> That results in a nested loop join with table "posts" as the inner and
> "follows" as the outer, which queried for each loop. EXPlAIN ANALYZE
> says the actual number of rows queried from table "posts" is 500,000.
> This behavior is problematic.
> 
> For performance, it may be better to retrieve 100 records joined with a
> record in table "follows", and then to retrieve those whose
> "posts"."timestamp" is greater than the one of last record we already
> have, or 100 records, joined with another record in table "follows", and
> so on. It would end up querying 10,000 records from table "posts" at
> most. The number could be even smaller in some cases.
> 
> Now I have these tough questions:
> * Is the "ideal" operation I suggested possible for PostgreSQL?
> * If so, I think that could be achieved by letting PostgreSQL use
> "follows" as the inner in the loops. How could I achieve that?
> * Is there any other way to improve the performance of the query?

PostgreSQL`s plan is to use the index on "posts"."timestamp" to find the
rows with the lowest "timestamp", match with rows from "posts" in
a nested loop and stop as soon as it has found 100 matches.

Now it must be that the rows in "posts" that match with rows in "follows"
have high values of "timestamp".

PostgreSQL doesn't know that, because it has no estimates how
values correlate across tables, so it has to scan much more of the index
than it had expected to, and the query performs poorly.

You could either try to do something like

SELECT *
FROM (SELECT "posts".*
  FROM "posts"
 JOIN "follows" ON "follows"."target_account" = "posts"."account"
  WHERE "follows"."owner_account" = $1
  OFFSET 0) q
ORDER BY "posts"."timestamp"  
LIMIT 100;

Or you could frop the index on "posts"."timestamp" and see if that helps.

Yours,
Laurenz Albe

-- 
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] update from performance question

[Albe Laurenz]

Armand Pirvu wrote:
> Running 9.5.2
> 
> I have the following update and run into a bit of a trouble . I realize the 
> tables
> involved have quite some data but here goes
> 
> 
> UPDATE
> tf_transaction_item_person TRANS
> SET
> general_ledger_code = PURCH.general_ledger_code,
> general_ledger_code_desc = PURCH.general_ledger_code_desc,
> update_datetime = now()::timestamp(0)
> FROM
>tf_purchases_person PURCH
> WHERE
> PURCH.general_ledger_code != '' AND
> TRANS.purchased_log_id = PURCH.purchased_log_id AND
> TRANS.general_ledger_code != PURCH.general_ledger_code
> ;
[...]
>   Table "tf_transaction_item_person"
[...]
> Indexes:
> "tf_transaction_item_person_pkey" PRIMARY KEY, btree 
> (person_transaction_item_id)
> "tf_tip_idx" btree (client_id, update_datetime)
> "tf_tip_isdel_idx" btree (show_id, person_transaction_item_id)

You don't show EXPLAIN (ANALYZE, BUFFERS) output for the problematic query,
so it is difficult to say where the time is spent.

But since you say that the same query without the UPDATE also takes more than
a minute, the duration for the UPDATE is not outrageous.
It may well be that much of the time is spent updating the index
entries for the 3.5 million affected rows.

I don't know if dropping indexes for the duration of the query and recreating
them afterwards would be a net win, but you should consider it.

It may be that the only ways to improve performance would be general
things like faster I/O, higher max_wal_size setting, and, most of all,
enough RAM in the machine to contain the whole database.

Yours,
Laurenz Albe

-- 
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] Please help with a slow query: there are millions of records, what can we do?

[Albe Laurenz]

Pat Maddox wrote:
> I’ve been asked to help with a project dealing with slow queries. I’m brand 
> new to the project, so I
> have very little context. I’ve gathered as much information as I can.
> 
> I’ve put the schema, query, and explain info in gists to maintain their 
> formatting.
> 
> We are stumped with this slow query right now. I could really use some help 
> looking for ways to speed
> it up.

I don't know if the plan can be improved; it has to retrieve and sort 347014 
rows,
most of which are read from diak, so it will take some time.

One thing I notice is that some statistics seem to be bad (the estimate for
the index scan on "permissions" is off the mark), so maybe you can ANALYZE
both tables (perhaps with higher "default_statistics_target") and see if that
changes anything.

Is there any chance you could give the machine lots of RAM?
That would speed up the bitmap heap scan (but not the sort).

Yours,
Laurenz Albe

-- 
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] Chaotic query planning ?

[Albe Laurenz]

Philippe Ivaldi wrote:
> The explain analyze of the following code is https://explain.depesz.com/s/VhOv
>
> [OTHER CTEs - TRUNCATED CODE]
> SELECT
>   count(*)
> FROM dossier d
>   LEFT JOIN vp ON vp.dossier_id = d.id
>   LEFT JOIN affected_ccial ON affected_ccial.dossier_id = d.id
>   LEFT JOIN dm_bien ON dm_bien.dossier_id = d.id
>   LEFT JOIN rdv_r2 ON rdv_r2.dossier_id = d.id
>   LEFT JOIN rdv_ra ON rdv_ra.dossier_id = d.id
>   LEFT JOIN mandat_papier_non_recu ON mandat_papier_non_recu.dossier_id = d.id
>   LEFT JOIN annonce csite_annonce_enabled ON csite_annonce_enabled.dossier_id 
> = d.id
>   LEFT JOIN invalidated_estimation ON invalidated_estimation.dossier_id = d.id
>   LEFT JOIN num_mandat_reserved ON num_mandat_reserved.dossier_id = d.id
>   LEFT JOIN d_status ON d_status.dossier_id = d.id
> WHERE [...]
>
> [...]
> 
> If I permute the line
>   LEFT JOIN vp ON vp.dossier_id = d.id
> with
> LEFT JOIN affected_ccial ON affected_ccial.dossier_id = d.id
> 
> The explain analyze is https://explain.depesz.com/s/sKGW
> resulting in a total time of 798.693ms instead of 65,843.533ms
> 
> 1. Can somebody explain me why the second query is near 100 faster than the
> first one ?
> 
> 2. Is there a rule that suggest the best order of the statements JOIN ?
>I'd read this doc 
> https://www.postgresql.org/docs/9.6/static/explicit-joins.html
>but I don't see any logic join order in this case…
> 
> 3. Why the two queries are very fast when I remove the WHERE
> conditions ?
> 
> I can provide additional informations if needed.

You join more than 8 tables in your query, and 8 is the default
value for join_collapse_limit.

https://www.postgresql.org/docs/current/static/runtime-config-query.html#GUC-JOIN-COLLAPSE-LIMIT

In this case, PostgreSQL doesn't perform an exhaustive search of
the possible query plans, but joins them in the order provided.

Experiment with raising join_collapse_limit and from_collapse_limit to 11.

Alternatively, optimize the join order by hand and don't tune the parameters.

Yours,
Laurenz Albe


-- 
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 problems with postgres and null Values?

[Albe Laurenz]

Sven Kerkling wrote:
> This one ist the burden, running at least 100 seconds:
> 
> SELECT b.id, b.status
> FROM export b, masterNew mb
> WHERE mb.sperre IS NULL
>   AND mb.status IS NULL
>   AND b.id = mb.id
> LIMIT 100;
> 
> http://explain.depesz.com/s/eAqG

I think the problem is here:

Bitmap Index Scan on masterNew_2016_pi_idx (cost=0.00..5.34 rows=181 width=0) 
(actual time=805.225..805.225 rows=4,764,537 loops=1)

Perhaps you should ANALYZE "masterNew" to get better statistics.

Yours,
Laurenz Albe

-- 
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] using shared_buffers during seq_scan

[Albe Laurenz]

Artem Tomyuk wrote:
> Is  Postgres use shared_buffers during seq_scan?
> In what way i can optimize seq_scan on big tables?

If the estimated table size is less than a quarter of shared_buffers,
the whole table will be read to the shared buffers during a sequential scan.

If the table is larger than that, it is scanned using a ring
buffer of 256 KB inside the shared buffers, so only 256 KB of the
table end up in cache.

You can speed up all scans after the first one by having lots of RAM.
Even if you cannot set shared_buffers four times as big as the table,
you can profit from having a large operating system cache.

Yours,
Laurenz Albe

-- 
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] Hash join gets slower as work_mem increases?

[Albe Laurenz]

Tomas Vondra wrote:
> Yes, that's clearly the culprit here. In both cases we estimate here are
> only ~4000 tuples in the hash, and 9.3 sizes the hash table to have at
> most ~10 tuples per bucket (in a linked list).
> 
> However we actually get ~3M rows, so there will be ~3000 tuples per
> bucket, and that's extremely expensive to walk. The reason why 100MB is
> faster is that it's using 2 batches, thus making the lists "just" ~1500
> tuples long.
> 
> This is pretty much exactly the reason why I reworked hash joins in 9.5.
> I'd bet it's going to be ~20x faster on that version.

Thank you for the explanation!

Yours,
Laurenz Albe

-- 
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] Hash join gets slower as work_mem increases?

[Albe Laurenz]

Tomas Vondra wrote:
> On 01/29/2016 04:17 PM, Albe Laurenz wrote:
>> I have a query that runs *slower* if I increase work_mem.
>>
>> The execution plans are identical in both cases, except that a temp file
>> is used when work_mem is smaller.

>> What could be an explanation for this?
>> Is this known behaviour?
> 
> There is a bunch of possible causes for such behavior, but it's quite
> impossible to say if this is an example of one of them as you have not
> posted the interesting parts of the explain plan. Also, knowing
> PostgreSQL version would be useful.
> 
> I don't think the example you posted is due to exceeding on-CPU cache as
> that's just a few MBs per socket, so the smaller work_mem is
> significantly larger.
> 
> What I'd expect to be the issue here is under-estimate of the hash table
> size, resulting in too few buckets and thus long chains of tuples that
> need to be searched sequentially. Smaller work_mem values usually limit
> the length of those chains in favor of batching.
> 
> Please, post the whole explain plan - especially the info about number
> of buckets/batches and the Hash node details.

Thanks for looking at this.
Sorry, I forgot to mention that this is PostgreSQL 9.3.10.

I didn't post the whole plan since it is awfully long, I'll include hyperlinks
for the whole plan.

work_mem = '100MB' (http://explain.depesz.com/s/7b6a):

->  Hash Join  (cost=46738.74..285400.61 rows=292 width=8) (actual 
time=4296.986..106087.683 rows=187222 loops=1)
   Hash Cond: ("*SELECT* 1_2".postadresse_id = p.postadresse_id)
   Buffers: shared hit=1181177 dirtied=1, temp read=7232 written=7230
[...]
   ->  Hash  (cost=18044.92..18044.92 rows=4014 width=8) (actual 
time=4206.892..4206.892 rows=3096362 loops=1)
 Buckets: 1024  Batches: 2 (originally 1)  Memory Usage: 102401kB
 Buffers: shared hit=1134522 dirtied=1, temp written=5296

work_mem = '500MB' (http://explain.depesz.com/s/Cgkl):

->  Hash Join  (cost=46738.74..285400.61 rows=292 width=8) (actual 
time=3802.849..245970.049 rows=187222 loops=1)
   Hash Cond: ("*SELECT* 1_2".postadresse_id = p.postadresse_id)
   Buffers: shared hit=1181175 dirtied=111
[...]
   ->  Hash  (cost=18044.92..18044.92 rows=4014 width=8) (actual 
time=3709.584..3709.584 rows=3096360 loops=1)
 Buckets: 1024  Batches: 1  Memory Usage: 120952kB
 Buffers: shared hit=1134520 dirtied=111

Does that support your theory?

There is clearly an underestimate here, caused by correlated attributes, but
is that the cause for the bad performance with increased work_mem?

Yours,
Laurenz Albe

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

[PERFORM] Hash join gets slower as work_mem increases?

[Albe Laurenz]

I have a query that runs *slower* if I increase work_mem.

The execution plans are identical in both cases, except that a temp file
is used when work_mem is smaller.

The relevant lines of EXPLAIN ANALYZE output are:

With work_mem='100MB':
->  Hash Join  (cost=46738.74..285400.61 rows=292 width=8) (actual 
time=4296.986..106087.683 rows=187222 loops=1)
  Hash Cond: ("*SELECT* 1_2".postadresse_id = p.postadresse_id)
  Buffers: shared hit=1181177 dirtied=1, temp read=7232 written=7230

With work_mem='500MB':
->  Hash Join  (cost=46738.74..285400.61 rows=292 width=8) (actual 
time=3802.849..245970.049 rows=187222 loops=1)
  Hash Cond: ("*SELECT* 1_2".postadresse_id = p.postadresse_id)
  Buffers: shared hit=1181175 dirtied=111

I ran operf on both backends, and they look quite similar, except that the
number of samples is different (this is "opreport -c" output):

CPU: Intel Sandy Bridge microarchitecture, speed 2899.8 MHz (estimated)
Counted CPU_CLK_UNHALTED events (Clock cycles when not halted) with a unit mask 
of 0x00 (No unit mask) count 9
samples  %image name   symbol name
---
  112   0.0019  postgres ExecProcNode
  3020116  49.9904  postgres ExecScanHashBucket
  3021162  50.0077  postgres ExecHashJoin
3020116  92.8440  postgres ExecScanHashBucket
  3020116  49.9207  postgres ExecScanHashBucket [self]
  3020116  49.9207  postgres ExecScanHashBucket
  8190  0.1354  vmlinux  apic_timer_interrupt

What could be an explanation for this?
Is this known behaviour?

Yours,
Laurenz Albe

-- 
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] High Planning Time

[Albe Laurenz]

Phil S wrote:
> I am running Postgresql on a Windows Server 2008 server. I have noticed that 
> queries have very high
> planning times now and then. Planning times go down for the same query 
> immediately after the query
> runs the first time, but then go up again after if the query is not re-run 
> for 5 minutes or so.
> 
> I am not able to find any specific information in the documentation that 
> would explain the issue or
> explains how to address it, so am asking for advice here.
> 
> Here is an example.
> 
> explain analyze
> select * from message
> limit 1
> 
> "Limit  (cost=0.00..0.44 rows=1 width=1517) (actual time=0.009..0.009 rows=1 
> loops=1)"
> "  ->  Seq Scan on message  (cost=0.00..28205.48 rows=64448 width=1517) 
> (actual time=0.007..0.007
> rows=1 loops=1)"
> "Planning time: 3667.361 ms"
> "Execution time: 1.652 ms"
> 
> As you can see the query is simple and does not justify 3 seconds of planning 
> time. It would appear
> that there is an issue with my configuration but I am not able to find 
> anything that looks out of
> sorts in the query planning configuration variables. Any advice about what I 
> should be looking for to
> fix this would be appreciated.

This is odd.

Could you profile the backend during such a statement to see where the time is 
spent?

Yours,
Laurenz Albe

-- 
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] No index only scan on md5 index

[Albe Laurenz]

Adam Brusselback wrote:
> I appreciate the response Tom, and you are correct that the workaround would 
> not work in my case.
> 
> So no index expressions can return the their value without recomputing 
> without that work around?  I
> learn something new every day it seems.
> Thank you for the alternate method.

No, what Tom said is that the check whether an "index only scan" was feasible
or not does not consider expressions.

Yours,
Laurenz Albe

-- 
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] SELECT slows down on sixth execution

[Albe Laurenz]

Jonathan Rogers wrote:
>> Look at the EXPLAIN ANALYZE output for both the custom plan (one of the
>> first five executions) and the generic plan (the one used from the sixth
>> time on) and see if you can find and fix the cause for the misestimate.
> 
> Yes, I have been looking at both plans and can see where they diverge.
> How could I go about figuring out why Postgres fails to see the large
> difference in plan execution time? I use exactly the same parameters
> every time I execute the prepared statement, so how would Postgres come
> to think that those are not the norm?

PostgreSQL does not consider the actual query execution time, it only
compares its estimates for there general and the custom plan.
Also, it does not keep track of the parameter values you supply,
only of the average custom plan query cost estimate.

The problem is either that the planner underestimates the cost of
the generic plan or overestimates the cost of the custom plans.

If you look at the EXPLAIN ANALYZE outputs (probably with
http://explain.depesz.com ), are there any row count estimates that
differ significantly from reality?

Yours,
Laurenz Albe

-- 
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] SELECT slows down on sixth execution

[Albe Laurenz]

Jonathan Rogers wrote:
> I have a very complex SELECT for which I use PREPARE and then EXECUTE.
> The first five times I run "explain (analyze, buffers) execute ..." in
> psql, it takes about 1s. Starting with the sixth execution, the plan
> changes and execution time doubles or more. The slower plan is used from
> then on. If I DEALLOCATE the prepared statement and PREPARE again, the
> cycle is reset and I get five good executions again.
> 
> This behavior is utterly mystifying to me since I can see no reason for
> Postgres to change its plan after an arbitrary number of executions,
> especially for the worse. When I did the experiment on a development
> system, Postgres was doing nothing apart from the interactively executed
> statements. No data were inserted, no settings were changed and no other
> clients were active in any way. Is there some threshold for five or six
> executions of the same query?
> 
> Without delving into the plans themselves yet, what could possibly cause
> the prepared statement to be re-planned? I have seen the same behavior
> on Postgres 9.2.10 and 9.4.1.

You are encountering "custom plans", introduced in 9.2.

When a statement with parameters is executed, PostgreSQL will not only generate
a generic plan, but for the first 5 executions it will substitute the arguments
and generate and execute a custom plan for that.

After 5 executions, the cost of the generic plan is compared to the average
of the costs of the custom plans.  If the cost is less, the generic plan will
be used from that point on.  If the cost is more, a custom plan will be used.

So what you encounter is probably caused by bad estimates for either
the custom plan or the generic plan.

Look at the EXPLAIN ANALYZE output for both the custom plan (one of the
first five executions) and the generic plan (the one used from the sixth
time on) and see if you can find and fix the cause for the misestimate.

Other than that, you could stop using prepared statements, but that is
probably not the optimal solution.

Yours,
Laurenz Albe

-- 
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] Techniques to Avoid Temp Files

[Albe Laurenz]

Duane Murphy wrote:
> We are trying to improve performance by avoiding the temp file creation.
> 
> LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp8068.125071", size 
> 58988604
> STATEMENT: SELECT iiid.installed_item__id, item_detail.id, 
> item_detail.model_id, item_detail.type
> FROM installed_item__item_detail AS iiid
> INNER JOIN item_detail ON iiid.item_detail__id = item_detail.id
> INNER JOIN item ON (item.installed_item__id = iiid.installed_item__id )
> INNER JOIN model ON (item.id = model.item__id AND model.id = $1)

> What are the causes of temp file creation?

Operations like hash and sort that need more space than work_mem promises.

> What additional information can I gather in order have a better understanding 
> of how to improve this
> query?

It woul be really useful to see the result of "EXPLAIN (ANALYZE, BUFFERS) 
SELECT ..."
for your query.

But essentially the answer to avoid temporary files is always "increase 
work_mem".

Yours,
Laurenz Albe

-- 
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] [GENERAL] trigger Before or After

[Albe Laurenz]

avpro avpro wrote:
> in the pgsql documentation
> (http://www.postgresql.org/docs/9.1/static/sql-createtrigger.html)
> 
> 
> i haven't seen anything referring to: how is affected the data inserted in 
> the new table by a trigger
> Before Insert compared with a trigger After Insert? and anything related to 
> performance

In your example (the trigger updates a second table) it should make
no difference if the trigger is BEFORE or AFTER INSERT.

The difference is that in a BEFORE trigger you can modify the values that
will be inserted before the INSERT actually happens.

> I read somewhere (I don't find the link anymore) that if the trigger is After 
> Insert, the data
> available in the table LOG might not be available anymore to run the trigger. 
> is that correct? or I
> might understood wrong?

I don't quite understand.
You will have access to the OLD and NEW values in both BEFORE and AFTER 
triggers.
In an AFTER trigger, the table row has already been modified.

> what's the difference related to performance concerning a trigger Before 
> Insert compared with a
> trigger After Insert?

I don't think that there is a big difference, but you can easily test it:
Insert 10 rows with a BEFORE trigger on the table and compare the
time it takes to inserting 10 rows with an AFTER trigger.

Yours,
Laurenz Albe

-- 
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] pgtune + configurations with 9.3

[Albe Laurenz]

Tory M Blue wrote:
> I've got some beefy hardware but have some tables that are over 57GB raw and 
> end up at 140GB size
> after indexes are applied. One index creation took 7 hours today. So it's 
> time to dive in and see
> where i'm lacking and what I should be tweaking.
> 
> I looked at pgtune again today and the numbers it's spitting out took me 
> back, they are huge. From all
> historical conversations and attempts a few of these larger numbers netted 
> reduced performance vs
> better performance (but that was on older versions of Postgres).
> 
> So I come here today to seek out some type of affirmation that these numbers 
> look good and I should
> look at putting them into my config, staged and or in one fell swoop.
> 
> I will start at the same time migrating my config to the latest 9.3 
> template...
> 
> Postgres Version: 9.3.4, Slony 2.1.3 (migrating to 2.2).
> CentOS 6.x, 2.6.32-431.5.1.el6.x86_64
> Big HP Boxen.
> 
> 32 core, 256GB of Ram DB is roughly 175GB in size but many tables are 
> hundreds of millions of rows.
> 
> The pgtune configurations that were spit out based on the information above;
> 
> max_connections = 300

That's a lot, but equals what you currently have.
It is probably ok, but can have repercussions if used with large work_mem:
Every backend can allocate that much memory, maybe even several times for a 
complicated query.

> shared_buffers = 64GB

That seems a bit on the large side.
I would start with something like 4GB and run (realistic) performance tests, 
doubling the value each time.
See where you come out best.
You can use the pg_buffercache contrib to see how your shared buffers are used.

> effective_cache_size = 192GB

That should be all the memory in the machine that is available to PostgreSQL,
so on an exclusive database machine it could be even higher.

> work_mem = 223696kB

That looks ok, but performance testing wouldn't harm.
Ideally you log temporary file creation and have this parameter big enough so 
that
normal queries don't need temp files, but low enough so that the file system 
cache still has
some RAM left.

> maintenance_work_mem = 2GB

That's particularly helpful for your problem, index creation.

> checkpoint_segments = 32

Check.
You want checkpoints to be time triggered, so don't be afraid to go higher
if you get warnings unless a very short restore time is of paramount importance.

> checkpoint_completion_target = 0.7

Check.

> wal_buffers = 16MB

That's fine too, although with 9.3 you might as well leave it default.
With that much RAM it will be autotuned to the maximum anyway.

> default_statistics_target = 100

That's the default value.
Increase only if you get bad plans because of insufficient statistics.

Yours,
Laurenz Albe

-- 
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] autocommit (true/false) for more than 1 million records

[Albe Laurenz]

[about loadling large amounts of data]

Felipe Santos wrote:
> This might also help:
> http://www.postgresql.org/docs/9.1/static/populate.html
> 
> 
> Bulk load tables from text files in almost all RDMS are "log free" (Postgres' 
> COPY is one of them).
> 
> The reason is that the database doesn't need to waste resources by writing 
> the log because there's no
> risk of data loss. If the COPY operation fails, your data will still live in 
> the text files you're
> trying to bulk load from.

That is only true if the table was created in the same transaction as the COPY 
statement.

Otherwise it could be that recovery starts after CREATE TABLE but before COPY, 
and
it would have to recover the loaded data.

Yours,
Laurenz Albe

-- 
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] autocommit (true/false) for more than 1 million records

[Albe Laurenz]

Alex Goncharov wrote:
> Thank you, Kevin -- this is helpful.
> 
> But it still leaves questions for me.

>> Alex Goncharov  wrote:
> 
>>> The whole thing is aborted then, and the good 99 records are not
>>> making it into the target table.
>>
>> Right.  This is one reason people often batch such copies or check
>> the data very closely before copying in.
> 
> How do I decide, before starting a COPY data load, whether such a load
> protection ("complexity") makes sense ("is necessary")?
> 
> Clearly not needed for 1 MB of data in a realistic environment.
> 
> Clearly is needed for loading 1 TB in a realistic environment.
> 
> To put it differently: If I COPY 1 TB of data, what criteria should I
> use for choosing the size of the chunks to split the data into?
> 
> For INSERT-loading, for the database client interfaces offering the
> array mode, the performance difference between loading 100 or 1000
> rows at a time is usually negligible if any.  Therefore 100- and
> 1000-row's array sizes are both reasonable choices.
> 
> But what is a reasonable size for a COPY chunk?  It can't even be
> measured in rows.
> 
> Note, that if you have a 1 TB record-formatted file to load, you can't
> just split it in 1 MB chunks and feed them to COPY -- the file has to
> be split on the record boundaries.
> 
> So, splitting the data for COPY is not a trivial operation, and if
> such splitting can be avoided, a reasonable operator will avoid it.
> 
> But then again: when can it be avoided?

You don't need to split the data at all if you make sure that they are
correct.

If you cannot be certain, and you want to avoid having to restart a huge
load with corrected data, the batch size is pretty much a matter of taste:
How much overhead does it generate to split the data in N parts?
How much time are you ready to wait for (re)loading a single part?

You'll probably have to experiment to find a solution that fits you.

>>> My question is: Where are these 99 records have been living, on
>>> the database server, while the 100-th one hasn't come yet, and
>>> the need to throw the previous data accumulation away has not
>>> come yet?
>>
>> They will have been written into the table.  They do not become
>> visible to any other transaction until and unless the inserting
>> transaction successfully commits.  These slides may help:
>>
>> http://momjian.us/main/writings/pgsql/mvcc.pdf
> 
> Yeah, I know about the MVCC model...  The question is about the huge
> data storage to be reserved without a commitment while the load is not
> completed, about the size constrains in effect here.

I don't understand that question.

You need the space anyway to complete the load.
If the load fails, you simply reclaim the space (VACUUM) and reuse it.
There is no extra storage needed.

>>> There have to be some limits to the space and/or counts taken by
>>> the new, uncommitted, data, while the COPY operation is still in
>>> progress.  What are they?
>>
>> Primarily disk space for the table.
> 
> How can that be found?  Is "df /mount/point" the deciding factor? Or
> some 2^32 or 2^64 number?

Disk space can be measure with "df".

>> If you are not taking advantage of the "unlogged load" optimization,
>> you will have written Write Ahead Log (WAL) records, too -- which
>> (depending on your configuration) you may be archiving.  In that
>> case, you may need to be concerned about the archive space required.
> 
> "... may need to be concerned ..." if what?  Loading 1 MB? 1 GB? 1 TB?
> 
> If I am always concerned, and check something before a COPY, what
> should I be checking?  What are the "OK-to-proceed" criteria?

That means "you should consider", not "you should be worried".
Unless you are loading into a table created in the same transaction,
"redo" information will be generated and stored in "WAL files", which
end up in your WAL archive.

This needs extra storage, proportional to the storage necessary
for the data itself.

>> If you have foreign keys defined for the table, you may get into
>> trouble on the RAM used to track pending checks for those
>> constraints.  I would recommend adding any FKs after you are done
>> with the big bulk load.
> 
> I am curious about the simplest case where only the data storage is to
> be worried about. (As an aside: the CHECK and NOT NULL constrains are
> not a storage factor, right?)

Right.

>> PostgreSQL does *not* have a "rollback log" which will impose a
>> limit.
> 
> Something will though, right?  What would that be? The available disk
> space on a file system? (I would be surprised.)

You can find something on the limitations here:
http://wiki.postgresql.org/wiki/FAQ#What_is_the_maximum_size_for_a_row.2C_a_table.2C_and_a_database.3F

>>> Say, I am COPYing 100 TB of data and the bad records are close
>>> to the end of the feed -- how will this all error out?
>>
>> The rows will all be in the table, but not visible to any other
>> transaction.
> 
> I see.  How much data can I fit there while doing COPY?  Not 1 TB?

Re: [PERFORM] Blocking every 20 sec while mass copying.

[Albe Laurenz]

Please keep the list on CC: in your responses.

Benjamin Dugast wrote:
> 2014-07-18 13:11 GMT+02:00 Albe Laurenz :
>> This sounds a lot like checkpoint I/O spikes.
>>
>> Check with the database server log if the freezes coincide with checkpoints.
>>
>> You can increase checkpoint_segments when you load data to have them occur 
>> less often.
>>
>> If you are on Linux and you have a lot of memory, you might hit spikes 
>> because too
>> much dirty data are cached; check /proc/sys/vm/dirty_ratio and 
>> /proc/sys/dirty_background_ratio.

> The checkpoint_segments is set to 64 already
> 
> the dirty_ration was set by default to 10 i put it down to 5
> the dirty_background_ratio was set to 5 and I changed it to 2
> 
> There is less freezes but the insert is so slower than before.

That seems to indicate that my suspicion was right.

I would say that your I/O system is saturated.
Have you checked with "iostat -mNx 1"?

If you really cannot drop the indexes during loading, there's probably not much 
more
you can do to speed up the load.
You can try to increase checkpoint_segments beyond 64 and see if that buys you 
anything.

Tuning the file system write cache will not reduce the amount of I/O necessary, 
but it
should reduce the spikes (which is what I thought was your problem).

Yours,
Laurenz Albe

-- 
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] Blocking every 20 sec while mass copying.

[Albe Laurenz]

Benjamin Dugast wrote:
> I'm working on Postgres 9.3.4 for a project.
> 
> 
> We are using Scala, Akka and JDBC to insert data in the database, we have 
> around 25M insert to do
> which are basically lines from 5000 files. We issue a DELETE according to the 
> file (mandatory) and
> then a COPY each 1000 lines of that file.
> 
> DELETE request : DELETE FROM table WHERE field1 = ? AND field2 = ?;
> 
> COPY request : COPY table FROM STDIN WITH CSV
> 
> 
> We have indexes on our database that we can't delete to insert our data.
> 
> 
> When we insert the data there is some kind of freezes on the databases 
> between requests. Freezes occur
> about every 20 seconds.
> 
> 
> Here is a screenshot 
>  from
> yourkit.
> 
> 
> We tried different solutions:
> 
> 
> * 1 table to 5 tables to reduces lock contention
> * fillfactor on indexes
> * commit delay
> * fsync to off (that helped but we can't do this)
> 
> We mainly want to know why this is happening because it slowing the insert 
> too much for us.

This sounds a lot like checkpoint I/O spikes.

Check with the database server log if the freezes coincide with checkpoints.

You can increase checkpoint_segments when you load data to have them occur less 
often.

If you are on Linux and you have a lot of memory, you might hit spikes because 
too
much dirty data are cached; check /proc/sys/vm/dirty_ratio and 
/proc/sys/dirty_background_ratio.

Yours,
Laurenz Albe

-- 
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] 1 machine + master DB with postgres_fdw + multiple DB instances on different ports

[Albe Laurenz]

Gezeala M. Bacuño II wrote:
> Does anybody have a similar setup:
> 
> [a] 1 physical machine with half a terabyte of RAM, Xeon E7- 8837  @ 2.67GHz, 
> huge ZFS pools + ZIL +
> L2ARC
> [b] master DB pg9.3 postgres_fdw with read/write capabilities, with 
> tablespaces and WAL on separate
> zpools, archiving enabled (for zfs snapshots purposes), +17K tables, multi-TB 
> in size and growing
> [c] multiple DB instances listening on different ports or sockets on the same 
> machine with [b]
> (looking at 2 DB instances as of now which may increase later on)
> 
> 
> On the master DB there are several schemas with foreign tables located on any 
> of the [c] DB instance.
> postgres_fdw foreign server definitions and all table sequence are on the 
> master DB. Basically, I'm
> looking at any benefits in terms of decreasing the master DB scaling, size, 
> separate shared_buffers
> and separate writer processes per instance (to utilize more CPU?). I'm also 
> planning on relocating
> seldom accessed tables on [c] DBs. Am I on the right path on utilizing 
> foreign data wrappers this way?

You are very likely not going to gain anything that way.

Access to foreign tables is slower than access to local tables, and 
(particularly when joins are
involved) you will end up unnecessarily sending lots of data around between the 
databases.
So I'd expect performance to suffer.

In addition, all the database clusters will have to share the memory, so I 
don't see an
improvement over having everything in one database.
Since the size will stay the same, you are not going to save anything on 
backups either.

Depending on the workload and how you distribute the tables, it might be a win 
to
distribute a large database across several physical machines.

I would test any such setup for performance.

Yours,
Laurenz Albe

-- 
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] SELECT outage in semop

[Albe Laurenz]

Сурен Арустамян wrote:

> I'm using postgresql 9.3.4 on Red Hat Enterprise Linux Server release 6.5 
> (Santiago)
> 
> Linux 193-45-142-74 2.6.32-431.17.1.el6.x86_64 #1 SMP Fri Apr 11 17:27:00 EDT 
> 2014 x86_64 x86_64
> x86_64 GNU/Linux
> 
> Server specs:
> 4x Intel(R) Xeon(R) CPU E7- 4870  @ 2.40GHz (40 physical cores in total)
> 
> 
> 441 GB of RAM
> 
> I have a schema when multi process daemon is setted up on the system and each 
> process holds 1
> postgresql session.
> 
> Each process of this daemon run readonly queries over the database.
> In normal situation it at most 35 ms for queries but from time to time (at a 
> random point of time)
> each database session hanges in some very strange semop call. Here is a part 
> of the strace:

[...]

> 41733 20:15:09.682507 semop(393228, {{0, -1, 0}}, 1) = 0 <2.080439>

[...]

> You may see that semop took 2 seconds from the whole system call.
> Same semops could be find in other database sessions.
> 
> Could you point me how can i find

What is your PostgreSQL configuration?

Is your database workload read-only?
If not, could these be locks?
You could set log_lock_waits and see if anything is logged.

Anything noteworthy in the database server log?
How busy is the I/O system and the CPU when this happens?

Yours,
Laurenz Albe

-- 
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] NFS, file system cache and shared_buffers

[Albe Laurenz]

I wrote:
>Jeff Janes wrote:
 All that said, there has always been a recommendation of caution around
 using NFS as a backing store for PG, or any RDBMS..
>>>
>>> I know that Oracle recommends it - they even built an NFS client
>>> into their database server to make the most of it.
>>
>> Last I heard (which has been a while), Oracle supported specific brand named 
>> implementations of NFS,
>> and warned against any others on a data integrity basis.
>
> I couldn't find any detailed information, but it seems that only certain
> NFS devices are supported.

For the record: Oracle support told me that all NFS is supported on Linux,
regardless of the device.  "Supported" does not mean "recommended", of course.

Yours,
Laurenz Albe

-- 
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] NFS, file system cache and shared_buffers

[Albe Laurenz]

Jeff Janes wrote:
>>> All that said, there has always been a recommendation of caution around
>>> using NFS as a backing store for PG, or any RDBMS..
>> 
>>  I know that Oracle recommends it - they even built an NFS client
>>  into their database server to make the most of it.
> 
> Last I heard (which has been a while), Oracle supported specific brand named 
> implementations of NFS,
> and warned against any others on a data integrity basis.

I couldn't find any detailed information, but it seems that only certain
NFS devices are supported.

> Why would they implement their own client?  Did they have to do something 
> special in their client to
> make it safe?

I think it is mostly a performance issue.  Each backend mounts its own copy
of the data files it needs.

Yours,
Laurenz Albe

-- 
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] NFS, file system cache and shared_buffers

[Albe Laurenz]

John Melesky wrote:
>> I just learned that NFS does not use a file system cache on the client side.
> 
> That's ... incorrect. NFS is cache-capable. NFSv3 (I think? It may have been 
> v2) started sending
> metadata on file operations that was intended to allow for client-side 
> caches. NFSv4 added all sorts
> of stateful behavior which allows for much more aggressive caching.

What do you mean by "allows"?  Does it cache files in memory or not?
Do you need additional software?  Special configuration?

> Where did you read that you could not use caching with NFS?

I have it by hearsay from somebody who seemed knowledgable, and the
existence of CacheFS seemed to indicate it was true.

Yours,
Laurenz Albe

-- 
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] NFS, file system cache and shared_buffers

[Albe Laurenz]

Stephen Frost wrote:
> All that said, there has always been a recommendation of caution around
> using NFS as a backing store for PG, or any RDBMS..

I know that Oracle recommends it - they even built an NFS client
into their database server to make the most of it.

Yours,
Laurenz Albe

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

[PERFORM] NFS, file system cache and shared_buffers

[Albe Laurenz]

I just learned that NFS does not use a file system cache on the client side.

On the other hand, PostgreSQL relies on the file system cache for performance,
because beyond a certain amount of shared_buffers performance will suffer.

Together these things seem to indicate that you cannot get good performance
with a large database over NFS since you can leverage memory speed.

Now I wonder if there are any remedies (CacheFS?) and what experiences
people have made with the performance of large databases over NFS.

Yours,
Laurenz Albe

-- 
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] Revisiting disk layout on ZFS systems

[Albe Laurenz]

Karl Denninger wrote:
> I've been doing a bit of benchmarking and real-world performance
> testing, and have found some curious results.

[...]

> The odd thing is that I am getting better performance with a 128k record
> size on this application than I get with an 8k one!

[...]

> What I am curious about, however, is the xlog -- that appears to suffer
> pretty badly from 128k record size, although it compresses even
> more-materially; 1.94x (!)
> 
> The files in the xlog directory are large (16MB each) and thus "first
> blush" would be that having a larger record size for that storage area
> would help.  It appears that instead it hurts.

As has been explained, the access patterns for WAL are quite different.

For your experiment, I'd keep them on different file systems so that
you can tune them independently.

Yours,
Laurenz Albe

-- 
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] Batch update query performance

[Albe Laurenz]

Hans Drexler wrote:
> We are porting an application to PostgreSQL. The appplication already
> runs with DB2 (LUW version) and Oracle. One query in particular executes
> slower on Postgres than it does on other Database platforms, notably DB2
> LUW and Oracle. (Please understand, we are not comparing databases here,
> we are porting an application. We do not mean to start a flame war
> here).

[...]

> Postgres needs close to 50
> minutes to process the same query on the same data. Sometimes, Postgres
> needs more than 2 hours.
> 
> The application performs an update query on every row
> of the table. The exact SQL of this query is:
> 
> update t67cdi_nl_cmp_descr set is_grc_002='Y'

[...]

> We tried removing all indexes. That reduces the runtime to ~3 minutes.
> When we start to put indexes back, the run time of the query increases
> again with each index added.

Do I read that right that the duration of the update is reduced from
50 or 120 minutes to 3 when you drop all the indexes?

[...]

> Hypothesis
> we have tried many things to solve this problem ourselves, but to no
> avail so far. Our hypothesis is that
> the Postgres creates new records for all rows and then needs to update
> all 15 indexes to make them point to the new rows. There does not seem
> to be a way to avoid that.
> 
> Question:
> - Is our hypothesis correct?
> - Can the forum please advise us on possible ways to make the query
> faster?

Your hypothesis may be correct.
What you could do is to create the table will a fillfactor of 50 or less
before populating it.  Then 50% of the space will be left empty and
could be used to put the updated data on the same page as the original
data.  That way you could take advantage of HOT (heap only tuples)
which will avoid the need to update indexes that do not reference the
updated column.

If I count right, you have got 15 indexes on this table.
Maybe you could check if you need them all.

Yours,
Laurenz Albe

-- 
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] Why shared_buffers max is 8GB?

[Albe Laurenz]

desmodemone wrote:

> max is 1024mb.

That must be a typo.
It can surely be much higher.

Yours,
Laurenz Albe

-- 
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] Bytea(TOAST) vs large object facility(OID)

[Albe Laurenz]

kosalram Babu Chellappa wrote:
>  We have a requirement to store images/documents with an average size of 
> 1-2MB on PostgreSQL database.
> We have PostgreSQL 9.2.4 running on Red hat linux 64 bit. We decided to setup 
> a stand alone postgreSQL
> server without streaming replication to host the images/documents only. We 
> are new to postgreSQL and
> we heard a lot of conversation about using Bytea vs Large object facility. We 
> would be inserting and
> retrieving document as whole using java webservices call from hibernate/JPA 
> interface into postgreSQL
> database. Is there any performance benchmark when using ByteA vs Large object 
> facility? Is there a
> general guidance to use one of these?

I don't know anything about Hibernate, but since bytea is handled like
any other regular data type, it should not present a problem.
To handle large objects, you need to use the large object API of
PostgreSQL, which makes large objects different from other data types.

Second, large objects are stored in their own table, and the user table
only stores the object ID.  When a row in the user table is deleted, the
large object won't go away automatically; you'd have to write a trigger
or something like that.

The real advantage of large objects comes when they are big enough that
you don't want to hold the whole thing in memory, but rather read and write
them in chunks.

Since this is not the case in your setup, I think that bytea is better for you.

Going back a step, do you really want a database just to hold images and
documents?  That will be slower and more complicated than a simple file service,
which would be a better solution for that requirement.

Yours,
Laurenz Albe

-- 
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] Order By Clause, Slows Query Performance?

[Albe Laurenz]

monalee_dba wrote:
> Eg. SELECT col1, col2, col3,col10 FROM table1;
> 
> For above query If I didn't mention ORDER BY clause, then I want to know
> selected data will appear in which order by a query planner?
> 
> Because I have huge size table, and when I applied ORDER BY col1, col2..in
> query the
> performance is soo bad that I can't offred.
> What should I do ? Because my requirement is data with ordered column.

A B-Tree index may help with that:
http://www.postgresql.org/docs/current/static/indexes-ordering.html
Consider a multicolumn index.

Yours,
Laurenz Albe

-- 
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] AMD vs Intel

[Albe Laurenz]

Johan Loubser wrote:
> I am tasked with getting specs for a postgres database server for the
> core purpose of running moodle at our university.
> The main question is at the moment is 12core AMD or 6/8core (E Series)
> INTEL.
> 
> What would be the most in portend metric in planning an enterprise level
> server for moodle.

I know too little about hardware to give an answer, but there are a few
things to consider:

- The faster each individual core is, the faster an individual
  query will be processed (in-memory sorting, hash tables etc.).

- More cores will help concurrency, but maybe not a lot: often it is
  I/O bandwidth that is the limiting factor for concurrency.

I think the best thing would be to estimate the amount of data,
concurrent users and operations per second you expect.

There is the excellent book "PostgreSQL High Performance" by Greg Smith
that I would recommend to read.

Yours,
Laurenz Albe

-- 
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] DBT5 execution failed due to undefined symbol: PQescapeLiteral

[Albe Laurenz]

amul sul wrote:
> I am trying to run DBT5 to test performance of PG9.2.4,
> 
> But execution failed due to undefined symbol: PQescapeLiteral error in 
> /bh/bh.out
> 
> Full error as follow:
[...]
> BrokerageHouseMain: symbol lookup error: BrokerageHouseMain: undefined 
> symbol: PQescapeLiteral
> --
> 
> Environment :
> 
> CentOS 6.4(Final)
> PG installed using source code
> 
> DBT5 is cloned from http://github.com/petergeoghegan/dbt5.git
> also tried with http://git.code.sf.net/p/osdldbt/dbt repo with pg9.0
> 
> same error occur when used with PG9.0,PG9.1 PG9.3beta2

That is a problem on the client side.

I guess that your binary was built and linked with PostgreSQL client library 
(libpq)
from version 9.0 or later, but you are trying to run it with a libpq.so
from version 8.4 or earlier (where PQescapeLiteral was not defined).

In that case you should upgrade the PostgreSQL client.

Yours,
Laurenz Albe

-- 
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] Fillfactor in postgresql 9.2

[Albe Laurenz]

Niels Kristian Schjødt wrote:
> I am experiencing a similar issue as the one mentioned in this post
> http://stackoverflow.com/questions/3100072/postgresql-slow-on-a-large-table-with-arrays-and-lots-of-
> updates/3100232#3100232
> However the post is written for a 8.3 installation, so I'm wondering if the 
> fillfactor problem is
> still roughly the same in 9.2, and hence would have a similar effect when 
> adjusted?

Yes, lowering the fillfactor for a table will still
increase the chances of HOT updates, improving performance
and reducing the need for maintenance.

Yours,
Laurenz Albe

-- 
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] on disk and in memory

[Albe Laurenz]

Jayadevan M wrote:
> If a table takes 100 MB while on disk, approximately how much space will it 
> take in RAM/database
> buffer?

100 MB.  A block in memory has the same layout as a block on disk.

Yours,
Laurenz Albe

-- 
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] PG_XLOG 27028 files running out of space

[Albe Laurenz]

Tory M Blue wrote:
> My postgres db ran out of space. I have 27028 files in the pg_xlog directory. 
> I'm unclear what
> happened this has been running flawless for years. I do have archiving turned 
> on and run an archive
> command every 10 minutes.
> 
> I'm not sure how to go about cleaning this up, I got the DB back up, but I've 
> only got 6gb free on
> this drive and it's going to blow up, if I can't relieve some of the stress 
> from this directory over
> 220gb.

> Postgres 9.1.6
> slon 2.1.2

Are there any messages in the log file?
Are you sure that archiving works, i.e. do WAL files
show up in your archive location?

The most likely explanation for what you observe is that
archive_command returns a non-zero result (fails).
That would lead to a message in the log.

Yours,
Laurenz Albe


-- 
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] Analyze and default_statistics_target

[Albe Laurenz]

AJ Weber wrote:
> What is the unit-of-measure used for default_statistics_target?

Number of entries in pg_stats.histogram_bounds orpg_stats.most_common_vals.

Yours,
Laurenz Albe

-- 
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] Perform scan on Toast table

[Albe Laurenz]

classical_89 wrote:
> Hi everyone ,I have a question. I have a table with large data (i was used
> bytea datatype and insert a binary content to table ) so that Postgres help
> me get a TOAST table to storage out-of-line values   .
> Assume that my table is " tbl_test " and toast table oid is 16816
> 
> When i peform  EXPLAIN ANALYZE select  query on tbl_test ( EXPLAIN ANALYZE
> SELECT * FROM tbl_test).It show that  sequential scan was performed on
> tbl_test ,but when i check pg_toast table with this query :
> 
> 
> SELECT
>   relid,
>   schemaname,
>   relname,
>   seq_scan,
>   seq_tup_read,
>   idx_scan,
> FROM pg_stat_all_tables
> WHERE relid IN  ( SELECT oid
>   FROM pg_class
>   WHERE relkind = 't' ) AND relid = 16816
> 
> I saw that  seq_tup_read = 0 and the seq_scan is always is 1 .idx_scan is
> increase arcording to the number of query on tbl_test
> 
>  I was wordering : Do have a sequential scan perform on tbl_test and other
> index scan will be peforming on TOAST after this sequential scan ?
> Can you explain this dump question to me ,please ?

The entries in the TOAST table need not be in the same order
as the entries in the main table.  So if you'd fetch them
sequentially, you'd have to reorder them afterwards.

It seems logical that access via the TOAST index is cheaper.

Yours,
Laurenz Albe


-- 
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] pgsql_tmp( Temporary tablespace)

[Albe Laurenz]

suhas.basavaraj12 wrote:
> Can i delete the content of this folder. I have observed couple of
times ,
> this folder got cleaned automatically.

These files are in use and you should not delete them.
If you need them to go right now, cancel the queries that
create temporary files.

If there are any leftover when PostgreSQL is shut down
(don't know if that can happen), it should be safe to
delete them.

> Which backend process deletes  the data from this folder .Any Idea?

I'm not sure, but probably the one that created them.

Yours,
Laurenz Albe


-- 
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] pgsql_tmp( Temporary tablespace)

[Albe Laurenz]

suhas.basavaraj12 wrote:
> This folde( Temporary tablespace) is getting filled and size increases
in
> the day where there lots of sorting operations.But after some times
the data
> in the is deleted automatically .  Can any one explain what is going
on ?

Must be temporary files created by the sorting operations.
If a sort, hash or similar operation is estimated to need
more than work_mem if done in memory, data will be dumped
to disk instead.

If you want to avoid that, you need to increase work_mem
(but make sure you don't run out of memory).

Yours,
Laurenz Albe


-- 
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 strange query plan for my query

[Albe Laurenz]

David Popiashvili wrote:
> I have database with few hundred millions of rows. I'm running the
following query:
> 
> select * from "Payments" as p
> inner join "PaymentOrders" as po
> on po."Id" = p."PaymentOrderId"
> inner join "Users" as u
> On u."Id" = po."UserId"
> INNER JOIN "Roles" as r
> on u."RoleId" = r."Id"
> Where r."Name" = 'Moses'
> LIMIT 1000
> When the where clause finds a match in database, I get the result in
several milliseconds, but if I
> modify the query and specify a non-existent r."Name" in where clause,
it takes too much time to
> complete. I guess that PostgreSQL is doing a sequential scan on the
Payments table (which contains the
> most rows), comparing each row one by one.
> Isn't postgresql smart enough to check first if Roles table contains
any row with Name 'Moses'?
> 
> Roles table contains only 15 row, while Payments contains ~350
million.
> 
> I'm running PostgreSQL 9.2.1.

> Here'e explain analyse results: http://explain.depesz.com/s/7e7

Can you also show the plan for the good case?

Yours,
Laurenz Albe


-- 
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] PostreSQL v9.2 uses a lot of memory in Windows XP

[Albe Laurenz]

Wu Ming wrote:
> I had installed postgreSQL v9.2 in Windows XP SP3.
> 
> My PC specs:
> Processor: Pentium Dual Core 2.09 GHz
> RAM: 2GB
> 
> The postgreSQL is run as windows service (manual).
> 
> The problem is the postgreSQL service uses a lot of memory and lags
> the OS if running in long time (about 2 hours or more) so I had to
> restart the postgreSQL service everytime it happened. I never do any
> big querying process so far. I only ever run it for adempiere ERP
> software and a small struts 2 project.
> 
> See this screenshot link from the Process Explorer:
> 
> http://i45.tinypic.com/vr4t3b.png
> 
> You can see that there are a lot of threads spawned. Is the threads
> that caused the high memory usage?
> 
> Is there a way to decrease the memory usage?

Is the machine dedicated to PostgreSQL?

What did you set the following patameters to:

shared_buffers
max_connections
work_mem
maintenance_work_mem

You probably need to reduce some of these settings.

Yours,
Laurenz Albe


-- 
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] fast read of binary data

[Albe Laurenz]

Eildert Groeneveld wrote:
> I am currently implementing using a compressed binary storage scheme
> genotyping data. These are basically vectors of binary data which may be
> megabytes in size.
> 
> Our current implementation uses the data type bit varying.
> 
> What we want to do is very simple: we want to retrieve such records from
> the database and transfer it unaltered to the client which will do
> something (uncompressing) with it. As massive amounts of data are to be
> moved, speed is of great importance, precluding any to and fro
> conversions.
> 
> Our current implementation uses Perl DBI; we can retrieve the data ok,
> but apparently there is some converting going on.
> 
> Further, we would like to use ODBC from Fortran90 (wrapping the
> C-library)  for such transfers. However, all sorts funny things happen
> here which look like conversion issues.
> 
> In old fashioned network database some decade ago (in pre SQL times)
> this was no problem. Maybe there is someone here who knows the PG
> internals sufficiently well to give advice on how big blocks of memory
> (i.e. bit varying records) can between transferred UNALTERED between
> backend and clients.

Using the C API you can specify binary mode for your data, which
meand that they won't be converted.

I don't think you will be able to use this with DBI or ODBC,
but maybe binary corsors can help
(http://www.postgresql.org/docs/current/static/sql-declare.html),
but I don't know if DBI or ODBC handles them well.

If you can avoid DBI or ODBC, that would be best.

Yours,
Laurenz Albe

-- 
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 is not using

[Albe Laurenz]

K P Manoj wrote:
> Please find the details of table description
> 
> test=# \d xxx
>Table "public.xxx"
> Column|Type |
Modifiers
>
--+-+---
>  crawler_id   | bigint  |
>  effective_org| character varying(255)  |
>  reverse_pd   | character varying(255)  |
>  Indexes:
> "xxx_rev_pd_idx1" btree (reverse_pd)
> 
> 
> test =#\d tmp
>Table "public.tmp"
>Column   |  Type  | Modifiers
> ++---
>  id | bigint |
>  mdc_domain_reverse | character varying(255) |
> Indexes:
> "tmp_idx1" btree (mdc_domain_reverse)
> "tmp_txt_idx_mdc" btree (mdc_domain_reverse varchar_pattern_ops)
> 
> 
> test=# EXPLAIN   select xxx.* from xxx xxx where exists (select 1 from
tmp where mdc_domain_reverse
> like 'ttt' || '.%');
> QUERY PLAN
>

--
> 
>  Result  (cost=0.03..2249.94 rows=13591 width=3141)
>One-Time Filter: $0
>InitPlan 1 (returns $0)
>  ->  Index Only Scan using tmp_txt_idx_mdc on tmp
(cost=0.00..4.27 rows=144 width=0)
>Index Cond: ((mdc_domain_reverse ~>=~ 'ttt.'::text) AND
(mdc_domain_reverse ~<~
> 'ttt/'::text))
>Filter: ((mdc_domain_reverse)::text ~~ 'ttt.%'::text)
>->  Seq Scan on xxx  (cost=0.00..2249.91 rows=13591 width=3141)
> (7 rows)
> 
> saleshub=# EXPLAIN   select xxx.* from xxx xxx where exists (select 1
from tmp where
> mdc_domain_reverse like xxx.reverse_pd || '.%');
> QUERY PLAN
>

---
>  Nested Loop Semi Join  (cost=0.00..341301641.67 rows=68 width=3141)
>Join Filter: ((tmp.mdc_domain_reverse)::text ~~
((xxx.reverse_pd)::text || '.%'::text))
>->  Seq Scan on xxx  (cost=0.00..2249.91 rows=13591 width=3141)
>->  Materialize  (cost=0.00..31811.93 rows=1442062 width=18)
>  ->  Seq Scan on tmp  (cost=0.00..24601.62 rows=1442062
width=18)
> (5 rows)
> 
> 
> My question was any chance to use  query planner with above index ? or
i want to change the query ?

It looks like I understood you right, and my answer applies:

>   I don't really understand what your problem is, but if
>   you are complaining that no index is used for the LIKE
>   condition in the first query, you're out of luck:
> 
>   The planner has no way of knowing if the contents of
>   xxx.reverse_pd start with "%" or not.

There is no chance to have the index used with this query.

You'll have to change the query so that the LIKE pattern
starts with a constant.

Maybe in your case (few entries in "xxx") you could use a
PL/SQL function that dynamically generates a query for each
row in "xxx".

Yours,
Laurenz Albe


-- 
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 is not using

[Albe Laurenz]

K P Manoj wrote:
> I am facing query performance in one of my testing server.
> How i can create index with table column name ?
> EXPLAIN select xxx.* from xxx xxx where exists (select 1 from tmp
where mdc_domain_reverse like
> xxx.reverse_pd || '.%');
> QUERY PLAN
>

---
>  Nested Loop Semi Join  (cost=0.00..315085375.74 rows=63 width=3142)
>Join Filter: ((tmp.mdc_domain_reverse)::text ~~
((xxx.reverse_pd)::text || '.%'::text))
>->  Seq Scan on xxx  (cost=0.00..6276.47 rows=12547 width=3142)
>->  Materialize  (cost=0.00..31811.93 rows=1442062 width=17)
>  ->  Seq Scan on tmp  (cost=0.00..24601.62 rows=1442062
width=17)
> 
> saleshub=# EXPLAIN  create table tmp2 as select xxx.* from xxx xxx
where exists (select 1 from tmp
> where mdc_domain_reverse like 'moc.ytirucesspc%') ;
>
QUERY PLAN
>

--
> --
>  Result  (cost=0.06..6276.53 rows=12547 width=3142)
>One-Time Filter: $0
>InitPlan 1 (returns $0)
>  ->  Index Scan using tmp_txt_idx_mdc on tmp  (cost=0.00..8.53
rows=144 width=0)
>Index Cond: (((mdc_domain_reverse)::text ~>=~
'moc.ytirucesspc'::text) AND
> ((mdc_domain_reverse)::text ~<~ 'moc.ytirucesspd'::text))
>Filter: ((mdc_domain_reverse)::text ~~
'moc.ytirucesspc%'::text)
>->  Seq Scan on xxx  (cost=0.00..6276.47 rows=12547 width=3142)

I don't really understand what your problem is, but if
you are complaining that no index is used for the LIKE
condition in the first query, you're out of luck:

The planner has no way of knowing if the contents of
xxx.reverse_pd start with "%" or not.

Yours,
Laurenz Albe


-- 
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] help with too slow query

[Albe Laurenz]

Pedro Jiménez Pérez wrote:
> Sent: Friday, November 02, 2012 1:14 PM
> To: pgsql-performance@postgresql.org
> Subject: [PERFORM] help with too slow query
> 
> Hello,
>   I have this table definition:
> CREATE TABLE ism_floatvalues
> (
>   id_signal bigint NOT NULL, -- Indica la señal a la que pertenece este 
> valor. Clave foránea que
> referencia al campo id_signal de la tabla ism_signal.
>   time_stamp timestamp without time zone NOT NULL, -- Marca de tiempo que 
> indica fecha y hora
> correpondiente a este dato. Junto con id_signal forma la clave primaria de 
> esta tabla
>   var_value double precision, -- Almacena el valor concreto de la señal en la 
> marca de tiempo
> espeficicada.
>   CONSTRAINT ism_floatvalues_id_signal_fkey FOREIGN KEY (id_signal)
>   REFERENCES ism_signal (id_signal) MATCH SIMPLE
>   ON UPDATE NO ACTION ON DELETE CASCADE
> )
> WITH (
>   OIDS=FALSE
> );
> 
> CREATE INDEX ism_floatvalues_index_idsignal_timestamp
>   ON ism_floatvalues
>   USING btree
>   (id_signal, time_stamp DESC);
> 
> 
> 
> *
> 
> Then I run this query
> *
> EXPLAIN analyze
> select round(CAST(sum(var_value) AS numeric),2) as var_value, 
> date_trunc('month', time_stamp) as
> time_stamp , date_part('month',date_trunc('month', time_stamp)) as month,
> date_part('year',date_trunc('year', time_stamp)) as year from ism_floatvalues 
> where id_signal in
> (
> select id_signal from ism_signal where reference = 'EDCA' and id_source in
> (
> select id_source from ism_installation where id_installation in
> (select id_installation from ism_groupxinstallation where id_group = 101)
> )
> )
> and time_stamp > date_trunc('month', current_date - interval '11 months')
> group by date_trunc('month', time_stamp), month, year
> order by time_stamp
> 
> **
> And this is the result:
> **
> 
> "GroupAggregate  (cost=4766541.62..4884678.62 rows=39483 width=16) (actual 
> time=1302542.073..1302713.154 rows=10 loops=1)"
[...]
> "->  Hash Join  (cost=545.65..3203518.39 rows=9356201 width=16) 
> (actual time=458941.090..1302245.307 rows=9741 loops=1)"
> "  Hash Cond: (ism_floatvalues.id_signal = ism_signal.id_signal)"
> "  ->  Seq Scan on ism_floatvalues  (cost=0.00..2965077.57 
> rows=28817098 width=24) (actual time=453907.600..1002381.652 rows=29114105 
> loops=1)"
> "Filter: (time_stamp > date_trunc('month'::text, 
> (('now'::text)::date - '11 mons'::interval)))"
[...]

> This query is very slow as you can see, it took about 20 minutos to 
> complete Can someone help me
> to improve performance on this query??
> Regards.

This sequential scan takes the lion share of the time.

Are the 29 million rows selected in that scan a significant percentage
of the total rows?  If yes, then the sequential scan is the
most efficient way to get the result, and the only remedy is to get
faster I/O or to cache more of the table in RAM.

If the query needs to access a lot of rows to complete, it must
be slow.

Yours,
Laurenz Albe


-- 
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] Slow query, where am I going wrong?

[Albe Laurenz]

> But why? Is there a way to force the planner into this?

I don't know enough about the planner to answer the "why",
but the root of the problem seems to be the mis-estimate
for the join between test_result and recipe_version
(1348 instead of 21983 rows).

That makes the planner think that a nested loop join
would be cheaper, but it really is not.

I had hoped that improving statistics would improve that
estimate.

The only way to force the planner to do it that way is
to set enable_nestloop=off, but only for that one query.
And even that is a bad idea, because for different
constant values or when the table data change, a nested
loop join might actually be the best choice.

I don't know how to solve that problem.

Yours,
Laurenz Albe


-- 
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] Slow query, where am I going wrong?

[Albe Laurenz]

AndyG wrote:
>> Increasing the statistics for test_result.id_recipe_version
>> had no effect?

> I increased the statistics in steps up to 5000 (with vacuum analyse) -
Seems
> to be as good as it gets.
> 
> http://explain.depesz.com/s/z2a

Just out of curiosity, do you get a better plan with
enable_nestloop=off?
Not that I think it would be a good idea to change that
setting in general.

Yours,
Laurenz Albe


-- 
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] Slow query, where am I going wrong?

[Albe Laurenz]

AndyG wrote:
> A marginal improvement.
> 
> http://explain.depesz.com/s/y63

That's what I thought.

Increasing the statistics for test_result.id_recipe_version
had no effect?

> I am going to normalize the table some more before partitioning.

How do you think that partitioning will help?

Yours,
Laurenz Albe


-- 
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] Replaying 48 WAL files takes 80 minutes

[Albe Laurenz]

k...@rice.edu wrote:
>>> If you do not have good random io performance log replay is nearly
>>> unbearable.
>>>
>>> also, what io scheduler are you using? if it is cfq change that to
>>> deadline or noop.
>>> that can make a huge difference.
>>
>> We use the noop scheduler.
>> As I said, an identical system performed well in load tests.

> The load tests probably had the "important" data already cached.
Processing
> a WAL file would involve bringing all the data back into memory using
a
> random I/O pattern.

The database is way too big (1 TB) to fit into cache.

What are "all the data" that have to be brought back?
Surely only the database blocks that are modified by the WAL,
right?

Yours,
Laurenz Albe


-- 
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] Replaying 48 WAL files takes 80 minutes

[Albe Laurenz]

Heikki Linnakangas wrote:
>> Why does WAL replay read much more than it writes?
>> I thought that pretty much every block read during WAL
>> replay would also get dirtied and hence written out.
> 
> Not necessarily. If a block is modified and written out of the buffer
> cache before next checkpoint, the latest version of the block is
already
> on disk. On replay, the redo routine reads the block, sees that the
> change was applied, and does nothing.

True.  Could that account for 1000 times more reads than writes?

>> I wonder why the performance is good in the first few seconds.
>> Why should exactly the pages that I need in the beginning
>> happen to be in cache?
> 
> This is probably because of full_page_writes=on. When replay has a
full
> page image of a block, it doesn't need to read the old contents from
> disk. It can just blindly write the image to disk. Writing a block to
> disk also puts that block in the OS cache, so this also efficiently
> warms the cache from the WAL. Hence in the beginning of replay, you
just
> write a lot of full page images to the OS cache, which is fast, and
you
> only start reading from disk after you've filled up the OS cache. If
> this theory is true, you should see a pattern in the I/O stats, where
in
> the first seconds there is no I/O, but the CPU is 100% busy while it
> reads from WAL and writes out the pages to the OS cache. After the OS
> cache fills up with the dirty pages (up to dirty_ratio, on Linux), you
> will start to see a lot of writes. As the replay progresses, you will
> see more and more reads, as you start to get cache misses.

That makes sense to me.
Unfortunately I don't have statistics in the required resolution
to verify that.

Thanks for the explanations.

Yours,
Laurenz Albe


-- 
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] Request for help with slow query

[Albe Laurenz]

Sean Woolcock wrote:
> I have a large (3 million row) table called "tape" that represents
files,
> which I join to a small (100 row) table called "filesystem" that
represents
> filesystems.  I have a web interface that allows you to sort by a
number of
> fields in the tape table and view the results 100 at a time (using
LIMIT
> and OFFSET).
> 
> The data only changes hourly and I do a "vacuum analyze" after all
changes.

> An example query that's running slowly for me is:
> 
> select tape.volser,
>tape.path,
>tape.scratched,
>tape.size,
>extract(epoch from tape.last_write_date) as
last_write_date,
>extract(epoch from tape.last_access_date) as
last_access_date
> from tape
> inner join filesystem
> on (tape.filesystem_id = filesystem.id)
> order by last_write_date desc
> limit 100
> offset 100;
> 
> On Postgres 8.1.17 this takes about 60 seconds. I would like it to
be faster.

> Here's a depesz link with that output:
http://explain.depesz.com/s/AUR

I don't see anything obviously wrong there.

At least the sequential scan on "tape" is necessary.

> Things I've tried:
[...]
> 3. I ran the query against the same data in Postgres 9.1.6 rather
than 8.1.17
>using the same hardware and it was about 5 times faster (nice
work,
>whoever did that!).  Unfortunately upgrading is not an option,
so this
>is more of an anecdote. I would think the query could go much
faster
>in either environment with some optimization.

Can you post EXPLAIN ANALYZE for the query on 9.1.6?

Staying on 8.1 is not a good idea, but I guess you know that.

> Storage details (important for performance and corruption questions):
> Do you use a RAID controller?
> No.
> How many hard disks are connected to the system and what types are
they?
> We use a single Hitachi HDT72102 SATA drive (250GB) 7200 RPM.
> How are your disks arranged for storage?
> Postgres lives on the same 100GB ext3 partition as the OS.

I'd say that a query like this will always be disk bound.
Getting faster storage should help.

Yours,
Laurenz Albe


-- 
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] Replaying 48 WAL files takes 80 minutes

[Albe Laurenz]

>> On Mon, Oct 29, 2012 at 6:05 AM, Albe Laurenz
 wrote:
>>> I am configuring streaming replication with hot standby
>>> with PostgreSQL 9.1.3 on RHEL 6 (kernel 2.6.32-220.el6.x86_64).
>>> PostgreSQL was compiled from source.
>>>
>>> It works fine, except that starting the standby took for ever:
>>> it took the system more than 80 minutes to replay 48 WAL files
>>> and connect to the primary.
>>>
>>> Can anybody think of an explanation why it takes that long?

Jeff Janes wrote:
>> Could the slow log files be replaying into randomly scattered pages
>> which are not yet in RAM?
>>
>> Do you have sar or vmstat reports?

The sar reports from the time in question tell me that I read
about 350 MB/s and wrote less than 0.2 MB/s.  The disks were
fairly busy (around 90%).

Jeff Trout wrote:
> If you do not have good random io performance log replay is nearly
unbearable.
> 
> also, what io scheduler are you using? if it is cfq change that to
deadline or noop.
> that can make a huge difference.

We use the noop scheduler.
As I said, an identical system performed well in load tests.

The sar reports give credit to Jeff Janes' theory.
Why does WAL replay read much more than it writes?
I thought that pretty much every block read during WAL
replay would also get dirtied and hence written out.

I wonder why the performance is good in the first few seconds.
Why should exactly the pages that I need in the beginning
happen to be in cache?

And finally: are the numbers I observe (replay 48 files in 80
minutes) ok or is this terribly slow as it seems to me?

Yours,
Laurenz Albe


-- 
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] Slow query, where am I going wrong?

[Albe Laurenz]

Andy wrote:
> I have been pulling my hair out over the last few days trying to get
any useful performance out of the
> following
> painfully slow query.
> The query is JPA created, I've just cleaned the aliases to make it
more readable.
> Using 'distinct' or 'group by' deliver about the same results, but
'distinct' is marginally better.
> Hardware is pretty low end (a test box), but is mostly dedicated to
PostgreSQL.
> The box spec and configuration is included at the end of this post -
Some of the values have been
> changed just to see if
> things get better.
> Inserts have also become extremely slow. I was expecting a drop off
when the database grew out of
> memory, but not this much.
> 
> Am I really missing the target somewhere?
> Any help and or suggestions will be very much appreciated.
> 
> Best regards,
> 
> Andy.
> 
> http://explain.depesz.com/s/cfb

The estimate on the join between recipe_version and test_result is not
good.

Maybe things will improve if you increase the statistics on
test_result.id_recipe_version.

If that does not help, maybe the nested loop join that takes
all your time can be sped up with the following index:

CREATE INDEX any_name ON test_item (id_test_result, type);

But I would not expect much improvement there.

BTW, you seem to have an awful lot of indexes defined, some
of which seem redundant.

Yours,
Laurenz Albe


-- 
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] Replaying 48 WAL files takes 80 minutes

[Albe Laurenz]

Alvaro Herrera wrote:
>> I am configuring streaming replication with hot standby
>> with PostgreSQL 9.1.3 on RHEL 6 (kernel 2.6.32-220.el6.x86_64).
>> PostgreSQL was compiled from source.
>>
>> It works fine, except that starting the standby took for ever:
>> it took the system more than 80 minutes to replay 48 WAL files
>> and connect to the primary.
>>
>> Can anybody think of an explanation why it takes that long?
> 
> Can you do a quick xlogdump of those files?  Maybe there is something
> unusual (say particular types of GIN/GiST index updates) on the files
> that take longer.

There are no GIN and GiST indexes in this cluster.

Here's the output of "xlogdump -S" on one of the WAL files
that took over 4 minutes:

000101D100EF:

Unable to read continuation page?
 ** maybe continues to next segment **
---
TimeLineId: 1, LogId: 465, LogSegment: 239

Resource manager stats:
  [0]XLOG  : 2 records, 112 bytes (avg 56.0 bytes)
 checkpoint: 2, switch: 0, backup end: 0
  [1]Transaction: 427 records, 96512 bytes (avg 226.0 bytes)
 commit: 427, abort: 0
  [2]Storage   : 0 record, 0 byte (avg 0.0 byte)
  [3]CLOG  : 0 record, 0 byte (avg 0.0 byte)
  [4]Database  : 0 record, 0 byte (avg 0.0 byte)
  [5]Tablespace: 0 record, 0 byte (avg 0.0 byte)
  [6]MultiXact : 0 record, 0 byte (avg 0.0 byte)
  [7]RelMap: 0 record, 0 byte (avg 0.0 byte)
  [8]Standby   : 84 records, 1352 bytes (avg 16.1 bytes)
  [9]Heap2 : 325 records, 9340 bytes (avg 28.7 bytes)
  [10]Heap  : 7611 records, 4118483 bytes (avg 541.1 bytes)
 ins: 2498, upd/hot_upd: 409/2178, del: 2494
  [11]Btree : 3648 records, 120814 bytes (avg 33.1 bytes)
  [12]Hash  : 0 record, 0 byte (avg 0.0 byte)
  [13]Gin   : 0 record, 0 byte (avg 0.0 byte)
  [14]Gist  : 0 record, 0 byte (avg 0.0 byte)
  [15]Sequence  : 0 record, 0 byte (avg 0.0 byte)

Backup block stats: 2600 blocks, 11885880 bytes (avg 4571.5 bytes)

Yours,
Laurenz Albe


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

[PERFORM] Replaying 48 WAL files takes 80 minutes

[Albe Laurenz]

I am configuring streaming replication with hot standby
with PostgreSQL 9.1.3 on RHEL 6 (kernel 2.6.32-220.el6.x86_64).
PostgreSQL was compiled from source.

It works fine, except that starting the standby took for ever:
it took the system more than 80 minutes to replay 48 WAL files
and connect to the primary.

Can anybody think of an explanation why it takes that long?

This is decent hardware: 24 cores of AMD Opteron 6174, 128 GB RAM,
NetApp SAN attached with 8 GBit Fibrechannel (ext4 file system).
An identical system performed fine in performance tests.

Here is the log; I have edited it for readability:

2012-10-29 09:22:22.945  database system was interrupted; last known up
at 2012-10-26 01:11:59 CEST
2012-10-29 09:22:22.945  creating missing WAL directory
"pg_xlog/archive_status"
2012-10-29 09:22:22.947  entering standby mode
2012-10-29 09:22:23.434  restored log file "000101D100C4"
from archive
2012-10-29 09:22:23.453  redo starts at 1D1/C420
2012-10-29 09:22:25.847  restored log file "000101D100C5"
from archive
2012-10-29 09:22:27.457  restored log file "000101D100C6"
from archive
2012-10-29 09:22:28.946  restored log file "000101D100C7"
from archive
2012-10-29 09:22:30.421  restored log file "000101D100C8"
from archive
2012-10-29 09:22:31.243  restored log file "000101D100C9"
from archive
2012-10-29 09:22:32.194  restored log file "000101D100CA"
from archive
2012-10-29 09:22:33.169  restored log file "000101D100CB"
from archive
2012-10-29 09:22:33.565  restored log file "000101D100CC"
from archive
2012-10-29 09:23:35.451  restored log file "000101D100CD"
from archive

Everything is nice until here.
Replaying this WAL file suddenly takes 1.5 minutes instead
of mere seconds as before.

2012-10-29 09:24:54.761  restored log file "000101D100CE"
from archive
2012-10-29 09:27:23.013  restartpoint starting: time
2012-10-29 09:28:12.200  restartpoint complete: wrote 242 buffers
(0.0%);
 0 transaction log file(s) added, 0 removed, 0
recycled;
 write=48.987 s, sync=0.185 s, total=49.184 s;
 sync files=1096, longest=0.016 s, average=0.000
s
2012-10-29 09:28:12.206  recovery restart point at 1D1/CC618278
2012-10-29 09:28:31.226  restored log file "000101D100CF"
from archive

Again there is a difference of 2.5 minutes
between these WAL files, only 50 seconds of
which were spent in the restartpoint.

From here on it continues in quite the same vein.
Some WAL files are restored in seconds, but some take
more than 4 minutes.

I'll skip to the end of the log:

2012-10-29 10:37:53.809  restored log file "000101D100EF"
from archive
2012-10-29 10:38:53.194  restartpoint starting: time
2012-10-29 10:39:25.929  restartpoint complete: wrote 161 buffers
(0.0%);
 0 transaction log file(s) added, 0 removed, 0
recycled;
 write=32.661 s, sync=0.066 s, total=32.734 s;
 sync files=251, longest=0.003 s, average=0.000
s
2012-10-29 10:39:25.929  recovery restart point at 1D1/ED95C728
2012-10-29 10:42:56.153  restored log file "000101D100F0"
from archive
2012-10-29 10:43:53.062  restartpoint starting: time
2012-10-29 10:45:36.871  restored log file "000101D100F1"
from archive
2012-10-29 10:45:39.832  restartpoint complete: wrote 594 buffers
(0.0%);
 0 transaction log file(s) added, 0 removed, 0
recycled;
 write=106.666 s, sync=0.093 s, total=106.769 s;
 sync files=729, longest=0.004 s, average=0.000
s
2012-10-29 10:45:39.832  recovery restart point at 1D1/EF5D4340
2012-10-29 10:46:13.602  restored log file "000101D100F2"
from archive
2012-10-29 10:47:38.396  restored log file "000101D100F3"
from archive
2012-10-29 10:47:38.962  streaming replication successfully connected to
primary

I'd be happy if somebody could shed light on this.

Yours,
Laurenz Albe

PS: Here is the configuration:

 name | current_setting 
--+---
 version  | PostgreSQL 9.1.3 on
x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.6 20110731 (Red Hat
4.4.6-3), 64-bit
 archive_command  | gzip -1 <"%p" | tee
/POSTGRES/data/exchange/"%f".gz >/POSTGRES/data/backups/ELAK/"%f".gz
 archive_mode | on
 checkpoint_completion_target | 0.9
 checkpoint_segments  | 30
 client_encoding  | UTF8
 constraint_exclusion | off
 cursor_tuple_fraction| 1
 custom_variable_classes  | pg_stat_statements
 default_statistics_target| 1000
 effective_cache_size | 64GB
 hot_standby  | on
 lc_collate   | de_DE.UTF8
 lc_ctype | de_DE.UTF8
 listen_address

Re: [PERFORM] LIKE op with B-Tree Index?

[Albe Laurenz]

Sam Wong wrote:
> I am investigating a performance issue involved with LIKE '%'
> on an index in a complex query with joins.

> Q1.
> SELECT * FROM shipments WHERE shipment_id LIKE '12345678%'
>
> Q2.
> SELECT * FROM shipments WHERE shipment_id >= '12345678' AND
> shipment_id < '12345679'

[Q1 and Q2 have different row estimates]

Merlin wrote:
>> Right -- I didn't visualize it properly.  Still, you're asking
>> the server to infer that
>> since you're looking between to adjacent textual characters range
bounded
>> [) it convert the 'between' to a partial
>> string search.   That hold up logically but probably isn't worth
>> spending cycles to do, particularly in cases of non-ascii mappable
unicode
>> characters.

> Postgresql did that already. Refer to the analyze result of Q1 and Q2,
it
> gives
> "Index Cond: ((shipment_id >= '12345678'::text) AND (shipment_id <
> '12345679'::text))"
> (I also just realized they did it just now)
> 
> Yet, with additional Filter (ref Q1 analyze), it's surprisingly that
it
> estimates Q1 will have more rows that Q2.
> 
> FYI, I made a self-contained test case and submitted a bug #7610.

Did you try to increase the statistics for column "shipment_id"?

This will probably not make the difference go away, but
if the estimate gets better, it might be good enough for
the planner to pick the correct plan.

Yours,
Laurenz Albe


-- 
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/effective_cache_size on 96GB server

[Albe Laurenz]

Strahinja Kustudic wrote:
>> I have a Postgresql 9.1 dedicated server with 16 cores, 96GB RAM and RAID10 
>> 15K SCSI drives
>> which is runing Centos 6.2 x64. This server is mainly used for 
>> inserting/updating large amounts of
>> data via copy/insert/update commands, and seldom for running select queries.
>> 
>> Here are the relevant configuration parameters I changed:
>> 
>> shared_buffers = 10GB
>> effective_cache_size = 90GB
>> work_mem = 32MB
>> maintenance_work_mem = 512MB
>> checkpoint_segments = 64
>> checkpoint_completion_target = 0.8
>> 
>> My biggest concern are shared_buffers and effective_cache_size, should I 
>> increase shared_buffers
>> and decrease effective_cache_size? I read that values above 10GB for 
>> shared_buffers give lower
>> performance, than smaller amounts?
>> 
>> free is currently reporting (during the loading of data):
>> 
>> $ free -m
>>  total   used   free sharedbuffers cached
>> Mem: 96730  96418311  0 71  93120
>> -/+ buffers/cache:   3227  93502
>> Swap:21000 51  20949
>> 
>> So it did a little swapping, but only minor, still I should probably 
>> decrease shared_buffers so
>> there is no swapping at all.

> Hm, I just notices that shared_buffers + effective_cache_size = 100 > 96GB, 
> which can't be right.
> effective_cache_size should probably be 80GB.

I think you misunderstood effective_cache_size.
It does not influence memory usage, but query planning.
It gives the planner an idea of how much memory there is for caching
data, including the filesystem cache.

So a good value for effective_cache_size would be
total memory minus what the OS and others need minus what private
memory the PostgreSQL backends need.
The latter can be estimated as work_mem times max_connections.

To avoid swapping, consider setting vm.swappiness to 0 in
/etc/sysctl.conf.

10GB of shared_buffers is quite a lot.
If you can run realistic performance tests, start with a lower value
and increase until you cannot see a notable improvement.

Yours,
Laurenz Albe

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

[PERFORM] RE: [PERFORM] exponentia​l performanc​e decrease, problem with version postgres + RHEL?

[Albe Laurenz]

John Nash wrote:
> We have being doing some testing with an ISD transaction and we had
> some problems that we posted here.
> 
> The answers we got were very kind and useful but we couldn't solve the 
> problem.

Could you refer to the threads so that you don't get the same advice again?

> We have doing some investigations after this and we are thinking if is
> it possible that OS has something to do with this issue. I mean, we
> have two hosts, both of them with OS = Red Hat Enterprise Linux Server
> release 6.2 (Santiago)
> 
> But when doing "select * from version()" on the postgres shell we obtain:
> 
> sessions=# select * from version();
>version
> --
> 
>  PostgreSQL 9.1.3 on x86_64-unknown-linux-gnu, compiled by gcc (GCC)
> 4.4.6 20110731 (Red Hat 4.4.6-3), 64-bit
> (1 row)
> 
> We don't understand why in here it's written "(Red Hat 4.4.6-3)".
> 
> Is it possible that we have installed a postgres' version that it's
> not perfect for the OS?

It means that the PostgreSQL you are using was compiled with a
compiler that was compiled on RHEL4.  Shouldn't be a problem.

> But if this is a problem, why are we obtaining a normal perform on a
> host and an exponential performance decrease on another?
> 
> And how can we obtain a normal performance when launching the program
> which does the queries from another host (remote url) but when
> launching it in the same host we obtain this decrease on the
> performance?

Try to identify the bottleneck.
Is it disk I/O, CPU, memory or something else?

> name   |
> current_setting
> 
> --+---
> -
> --
>  version  | PostgreSQL 9.1.3 on
> x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.6 20110731 (Red
> Hat
>  4.4.6-3), 64-bit
>  archive_mode | off
>  client_encoding  | UTF8
>  fsync| on
>  lc_collate   | en_US.UTF-8
>  lc_ctype | en_US.UTF-8
>  listen_addresses | *
>  log_directory| pg_log
>  log_filename | postgresql-%a.log
>  log_rotation_age | 1d
>  log_rotation_size| 0
>  log_truncate_on_rotation | on
>  logging_collector| on
>  max_connections  | 100
>  max_stack_depth  | 2MB
>  port | 50008
>  server_encoding  | UTF8
>  shared_buffers   | 32MB

Now that sticks out as being pretty small.
Try 1/4 of the memory available for the database, but not
more than 2 GB.

>  synchronous_commit   | on
>  TimeZone | Europe/Madrid
>  wal_buffers  | 64kB

That's also pretty small.

>  wal_sync_method  | fsync
> (22 rows)

Yours,
Laurenz Albe

-- 
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] Spurious failure to obtain row lock possible in PG 9.1?

[Albe Laurenz]

henk de wit wrote:
> I'm using Postgres 9.1 on Debian Lenny and via a Java server (JBoss AS
6.1) I'm executing a simple
> "select ... for update" query:
> 
> 
> SELECT
> 
> importing
> 
> FROM
> 
> customer
> 
> WHERE
> 
> id = :customer_id
> 
> FOR UPDATE NOWAIT
> 
> 
> Once every 10 to 20 times Postgres fails to obtain the lock for no
apparent reason:
> 
> 18:22:18,285 WARN  [org.hibernate.util.JDBCExceptionReporter] SQL
Error: 0, SQLState: 55P03
> 18:22:18,285 ERROR [org.hibernate.util.JDBCExceptionReporter] ERROR:
could not obtain lock on row in
> relation "customer"
> 
> 
> I'm "pretty" sure there's really no other process that has the lock,
as I'm the only one on a test DB.
> If I execute the query immediately again, it does succeed in obtaining
the lock. I can however not
> reproduce this via e.g. PGAdmin.
> 
> 
> Is it possible or perhaps even known that PG has this behavior, or
should I look for the cause in the
> Java code? (I'm using Java EE"s entity manager to execute a native
query inside an EJB bean that lets
> a JDBC connection from a pool join a JTA transaction.)

There must be at least a second database connection that holds
locks on the objects you need.
Look in pg_stat_activity if you see other connections.

It is probably a race condition of some kind.

Turn on logging og connections and disconnections.
Set log_statement='all'

That way you should be able to see from the log entries
who issues what queries concurrently with you.

Yours,
Laurenz Albe


-- 
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] Cost of opening and closing an empty transaction

[Albe Laurenz]

Jon Leighton wrote:
> I'm one of the developers of the Ruby on Rails web framework.
> 
> In some situations, the framework generates an empty transaction
block.
> I.e. we sent a BEGIN and then later a COMMIT, with no other queries in
> the middle.
> 
> We currently can't avoid doing this, because a user *may* send queries
> inside the transaction.
> 
> I am considering the possibility of making the transaction lazy. So we
> would delay sending the BEGIN until we have the first query ready to
go.
> If that query never comes then neither BEGIN nor COMMIT would ever be
sent.
> 
> So my question is: is this a worthwhile optimisation to make? In
> particular, I am wondering whether empty transactions increase the
work
> the database has to do when there are several other connections open?
> I.e. does it cause contention?
> 
> If anyone has any insight about other database servers that would also
> be welcome.

The one thing that will be the same for all databases is that
saving the two client-server roud trips for BEGIN and COMMIT
is probably worth the effort if it happens often enough.

The question which resources an empty transaction consumes
is probably database specific; for PostgreSQL the expense is
not high, as far as I can tell.

Yours,
Laurenz Albe




-- 
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] add column with default value is very slow

[Albe Laurenz]

AI Rumman wrote:
> I execued the query:
> ALTER TABLE entity ADD COLUMN owner_type char(1) NOT NULL default 'U';
> 
> The db is stuck. The enity table has 2064740 records;
> 
> Watching locks:
[all locks are granted]

> Any idea for the db stuck?

To add the column, PostgreSQL has to modify all rows in the table.

But then 2064740 records is not very much, so it shouldn't take forever.

Do you see processor or I/O activity?

Yours,
Laurenz Albe


-- 
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] libpq or postgresql performance

[Albe Laurenz]

Aryan Ariel Rodriguez Chalas wrote:
> I'm working with an application that connects to a remote server database 
> using "libpq" library over
> internet, but making a simple query is really slow even though I've done 
> PostgreSQL Tunning and table
> being indexed, so I want to know:
> 
> -Why is postgresql or libpq that slow when working over internet?
> -What else should I do to solve this issue in addition of postgresql tunning?
> -Why if I connect to the remote server desktop (using RDP or any Remote 
> Desktop Application) and run
> the application using the same internet connection, it runs really fast when 
> making requests to
> postgresql; but if I run the application locally by connecting to the remote 
> postgresql server through
> "libpq", it's really slow?.

There are a million possible reasons; it would be a good
idea to trace at different levels to see where the time
is lost.

One thing that comes to mind and that is often the cause of
what you observe would be that there is a lot of traffic
between the database and the application, but little traffic
between the application and the user.

Yours,
Laurenz Albe

-- 
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] JDBC 5 million function insert returning Single Transaction Lock Access Exclusive Problem

[Albe Laurenz]

Eileen wrote:
> I have written some Java code which builds a postgresql function.
That function calls approximately 6
> INSERT statements with a RETURNING clause.  I recreate and re-run the
function about 900,000 times.  I
> use JDBC to execute these functions on postgresql 8.3 on Windows.
When I tried running this on a
> single Connection of Postgresql, it failed (some kind of memory
error).  So I split the JDBC
> connections up into chunks of 5000.  I reran and everything was fine.
It took about 1 hour to execute
> all the updates.
> 
> Since it took so long to perform the update, I wanted to prevent other
users from querying the data
> during that time.  So I read about the LOCK command.  It seemed like I
should LOCK all the tables in
> the database with an ACCESS EXCLUSIVE mode.  That would prevent anyone
from getting data while the
> database was making its updates.
> 
> Since a LOCK is only valid for 1 transaction, I set autocommit to
FALSE.  I also removed the code
> which chunked up the inserts.  I had read that a single transaction
ought to have better performance
> than committing after each insert, but that was clearly not what ended
up happening in my case.
> 
> In my case, a few problems occurred.  Number 1, the process ran at
least 8 hours and never finished.
> It did not finish because the hard drive was filled up.  After running
a manual vacuum (VACUUM FULL),
> no space was freed up.  I think this has cost me 20 GB of space.  Is
there any way to free this space
> up?  I even dropped the database to no avail.

Try to identify what files use the space.
Look at the size of directories.
Could it be that "archive_mode" is "on" and you ran out of space
for archived WALs?

When you drop a database, all files that belong to the database
are gone.

> Secondly, why did this process take over 8 hours to run?  While
reading the performance mailing list,
> it seems like recommendations are to run lots of INSERTS in a single
commit.  Is 5 million too many?
> Is redefining a function over and over inside a transaction a problem?
Does the RETURNING clause
> present a problem during a single transaction?

It would be interesting to know how the time was spent.
Were the CPUs busy? Were there locks?

Yours,
Laurenz Albe


-- 
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 about caching on full table scans

[Albe Laurenz]

Markus Innerebner wrote:
> I am doing some runtime experiments in my implementation, which is
computing multi-modal range queries
> for a query point (if you want to know details check the website:
www.isochrones.inf.unibz.it).
> The network is explored using Dijkstra Shortest Path algorithm that
starts from the query point and
> starts to explore all connected vertices until the time is exceeded.
> The network is stored on a postgres (postgis) database consisting of
vertices and edges.
> relation: edge(id int, source int, target int, length double, segment
geometry,..)
> 
> I have different implementations how the network is loaded in main
memory:
> approach 1: loads initially the entire edge table (full table scan) in
main memory and then starts to
> expand the network and doing some computation.
> approach 2: loads only the adjacent edges of the current expanded
vertex
> approach 3: loads junks using the euclidean distance upper bound
> 
> I have different datasets: 6000 tuples (small), 4,000,000 tuples
(large)
> 
> I repeat each experiment at least 10 times.
> When observing the runtime I realized following:
> - in the first iteration approach 1 takes long time, and its runtime
starts to perform better after
> each iteration:
> e.g. with large dataset
>   - iteration 1:   60.0s
>   - iteration 2:   40.7s
>   - iteration 3:   40,s
>   - iteration 4:   39.7s
>   - iteration 5:   39.5s
>   - iteration 6:   39.3s
>   - iteration 7:   40.0s
>   - iteration 8:   34.8s
>   - iteration 9:   39.1s
>   - iteration 10: 38.0s
> 
> In the other approaches I do not see that big difference.
> 
> I know that postgres (and OS) is caching that dataset. But is there a
way to force the database to
> remove that values from the cache?
> I also tried to perform after each iteration a scan on a dummy table
(executing it at least 10 times
> to force the optimized to keep that dummy data in main memory).
> But  I do not see any difference.
> 
> I thing the comparison is not right fair, if the caching in the main
memory approach brings that big
> advantage.
> 
> What can you as experts suggest me?

In your approach 1 to 3, what do you mean with "load into main memory"?
Do you
a) make sure that the data you talk about are in the PostgreSQL buffer
cache
or
b) retrieve the data from PostgreSQL and store it somewhere in your
application?

To clear PostgreSQL's cache, restart the server.
That should be a fast operation.
Since version 8.3, PostgreSQL is smart enough not to evict the
whole cache for a large sequential scan.

To flush the filesystem cache (from Linux 2.6.16 on), use
sync; echo 3 > /proc/sys/vm/drop_caches

Yours,
Laurenz Albe


-- 
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] Execution from java - slow

[Albe Laurenz]

Jayadevan M wrote:
> I have a plpgsql function that takes a few seconds (less than 5) when
executed  from psql. The same
> function, when invoked from java via a prepared statement takes a few
minutes. There are a few queries
> in the function. Out of these, the first query takes input parameters
for filtering the data. It is
> this query which takes a long time when the procedure is invoked from
java. To ensure that the query
> does use actual values (and not bind variables) for optimization, we
used
> 
> execute
> '
> select x.col_type_desc,x.acc_id,acc_svr from (.
> '
> using d_from_date,d_to_date
> 
> It did not help. Any suggestions? It is from_date and to_date on which
data gets filtered. We are
> using the same values for filtering, when we execute it from java/psql

Use the auto_explain contrib with
auto_explain.log_nested_statements=on
to see the statements that are really executed
and compare!

Yours,
Laurenz Albe


-- 
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 index issue

[Albe Laurenz]

codevally wrote:
> I have a question regarding PostgreSQL 9.1 indexing.
> 
> I am having a table and want to create a index for a column and I want
to
> store the data with time zone for that column. The questions are:
> 
> 1. Can I create a index for a column which store time stamp with time
zone.
> If can is there ant performance issues?
> 
> 2. Also I can store the time stamp value with zone as a long integer
value.
> If so what is the difference between the above step. Which one is
better.
> 
> Many Thanks.

If you didn't like the answer that you got to exactly the same
question on
http://archives.postgresql.org/message-id/CAOokBJGA56tiyLZGPf859fmLiZidp
p19Q5pPbT65Hwc4wORegg%40mail.gmail.com
why didn't you say so?

Yours,
Laurenz Albe

-- 
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 could select id=xx so slow?

[Albe Laurenz]

Yan Chunlu wrote:
> I have logged one day data and found the checkpoint is rather
frequently(detail:
> https://gist.github.com/3088338). Not sure if it is normal, but the
average time of checkpoint is
> about 100sec~200sec, it seems related with my settings:
> 
> 574 checkpoint_segments = 64
> 575 wal_keep_segments = 5000
> 
> I set checkpoint_segments as a very large value which is because
otherwise the slave server always can
> not follow the master, should I lower that value?

You mean, you set wal_keep_segments high for the standby, right?

wal_keep_segments has no impact on checkpoint frequency and intensity.

You are right that your checkpoint frequency is high. What is your value
of checkpoint_timeout?

You can increase the value of checkpoint_segments to decrease the
checkpoint frequence, but recovery will take longer then.

> or the slow query is about something else?  thanks!

I guess the question is how saturated the I/O system is during
checkpoints. But even if it is very busy, I find it hard to believe
that such a trivial statement can take extremely long.

Yours,
Laurenz Albe

-- 
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 db, 30 tables with number of rows < 100 (not huge) - the fastest way to clean each non-empty table and reset unique identifier column of empty ones.

[Albe Laurenz]

Stanislaw Pankevich wrote:
>>>  PostgreSQL db, 30 tables with number of rows < 100 (not huge) - the 
>>> fastest way to clean each
>>> non-empty table and reset unique identifier column of empty ones 
>>>
>>> I wonder, what is the fastest way to accomplish this kind of task in 
>>> PostgreSQL. I am interested in
>>> the fastest solutions ever possible.

>>> I need the fastest cleaning strategy for such case working on PostgreSQL 
>>> both 8 and 9.
>>>
>>> I see the following approaches:
>>>
>>> 1) Truncate each table. It is too slow, I think, especially for empty 
>>> tables.

>> Did you actually try it? That's the king's way to performance questions!
>> Truncating a single table is done in a matter of microseconds, particularly
>> if it is not big.
>> Do you have tens of thousands of tables?

> Actually, 10-100 tables.

>> You could of course run a SELECT 1 FROM table LIMIT 1, but again I don't
>> think that this will be considerably faster than just truncating the table.
> 
> Exactly this query is much faster, believe me. You can see my latest
> results on https://github.com/stanislaw/truncate-vs-count.

Ok, I believe you.

My quick tests showed that a sible truncate (including transaction and
client-server roundtrip via UNIX sockets takes some 10 to 30 milliseconds.

Multiply that with 100, and you end up with just a few seconds at most.
Or what did you measure?

I guess you run that deletion very often so that it is painful.

Still I think that the biggest performance gain is to be had by using
PostgreSQL's features (truncate several tables in one statement, ...).

Try to bend your Ruby framework!

Yours,
Laurenz Albe

-- 
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 db, 30 tables with number of rows < 100 (not huge) - the fastest way to clean each non-empty table and reset unique identifier column of empty ones.

[Albe Laurenz]

Stanislaw Pankevich wrote:
>  PostgreSQL db, 30 tables with number of rows < 100 (not huge) - the 
> fastest way to clean each
> non-empty table and reset unique identifier column of empty ones 
> 
> I wonder, what is the fastest way to accomplish this kind of task in 
> PostgreSQL. I am interested in
> the fastest solutions ever possible.

> I have following assumptions:
> 
> I have 30-100 tables. Let them be 30.
> 
> Half of the tables are empty.
> 
> Each non-empty table has, say, no more than 100 rows. By this I mean, 
> tables are NOT large.
> 
> I need an optional possibility to exclude 2 or 5 or N tables from this 
> procedure.
> 
> I cannot! use transactions.

Why? That would definitely speed up everything.

> I need the fastest cleaning strategy for such case working on PostgreSQL both 
> 8 and 9.
> 
> I see the following approaches:
> 
> 1) Truncate each table. It is too slow, I think, especially for empty tables.

Did you actually try it? That's the king's way to performance questions!
Truncating a single table is done in a matter of microseconds, particularly
if it is not big.
Do you have tens of thousands of tables?

> 2) Check each table for emptiness by more faster method, and then if it is 
> empty reset its unique
> identifier column (analog of AUTO_INCREMENT in MySQL) to initial state (1), 
> i.e to restore its
> last_value from sequence (the same AUTO_INCREMENT analog) back to 1, 
> otherwise run truncate on it.

That seems fragile an won't work everywhere.

What if the table has no primary key with a DEFAULT that uses a sequence?
What if it has such a key, but the DEFAULT was not used for an INSERT?
What if somebody manually reset the sequence?

Besides, how do you find out what the sequence for a table's primary key
is? With a SELECT, I guess. That SELECT is probably not faster than
a simple TRUNCATE.

> Also my guess was that EXISTS(SELECT something FROM TABLE) could somehow be 
> used to work good as one
> of the "check procedure" units, cleaning procedure should consist of, but 
> haven't accomplished it too.

You could of course run a SELECT 1 FROM table LIMIT 1, but again I don't
think that this will be considerably faster than just truncating the table.

> I would appreciate any hints on how this procedure could be accomplished in 
> PostgreSQL native way.
> 
> Thanks!
> 
> UPDATE:
> 
> I need all this to run unit and integration tests for Ruby or Ruby on Rails 
> projects. Each test should
> have a clean DB before it runs, or to do a cleanup after itself (so called 
> teardown). Transactions are
> very good, but they become unusable when running tests against particular 
> webdrivers, in my case the
> switch to truncation strategy is needed. Once I updated that with reference 
> to RoR, please do not post
> here the answers about "Obviously, you need DatabaseCleaner for PG" and so on 
> and so on.

I completely fail to understand what you talk about here.

Yours,
Laurenz Albe

-- 
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] Paged Query

[Albe Laurenz]

Hermann Matthes wrote:
> I want to implement a "paged Query" feature, where the user can enter
in
> a dialog, how much rows he want to see. After displaying the first
page
> of rows, he can can push a button to display the next/previous page.
> On database level I could user "limit" to implement this feature. My
> problem now is, that the user is not permitted to view all rows. For
> every row a permission check is performed and if permission is
granted,
> the row is added to the list of rows sent to the client.
> If for example the user has entered a page size of 50 and I use "limit
> 50" to only fetch 50 records, what should I do if he is only permitted
> to see 20 of these 50 records? There may be more records he can view.
> But if I don't use "limit", what happens if the query would return
> 5,000,000 rows? Would my result set contain 5,000,000 rows or would
the
> performance of the database go down?

Selecting all 500 rows would consume a lot of memory wherever
they are cached. Also, it might lead to bad response times (with
an appropriate LIMIT clause, the server can choose a plan that
returns the first few rows quickly).

I assume that there is some kind of ORDER BY involved, so that
the order of rows displayed is not random.

I have two ideas:
- Try to integrate the permission check in the query.
  It might be more efficient, and you could just use LIMIT
  and OFFSET like you intended.
- Select some more rows than you want to display on one page,
  perform the permission checks. Stop when you reach the end
  or have enough rows. Remember the sort key of the last row
  processed.
  When the next page is to be displayed, use the remembered
  sort key value to SELECT the next rows.

Yours,
Laurenz Albe

-- 
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 could select id=xx so slow?

[Albe Laurenz]

Yan Chunlu wrote:
> I have grabbed one day slow query log and analyzed it by pgfouine, to
my surprise, the slowest query
> is just a simple select statement:
> 
> select diggcontent_data_message.thing_id,
diggcontent_data_message.KEY,
> diggcontent_data_message.value, diggcontent_data_message.kind FROM
diggcontent_data_message WHERE
> diggcontent_data_message.thing_id = 3570882;
> 
> where thing_id is the primary key,  guess how long it takes?
> 
> 754.61 seconds!!
> 
> I tried explain analyze it and below is the result, which is very
fast:
> 
> explain analyze select diggcontent_data_message.thing_id,
diggcontent_data_message.KEY,
> diggcontent_data_message.value, diggcontent_data_message.kind FROM
diggcontent_data_message WHERE
> diggcontent_data_message.thing_id = 3570882;
>
QUERY PLAN
>

--
> -
>  Index Scan using idx_thing_id_diggcontent_data_message on
diggcontent_data_message  (cost=0.00..15.34
> rows=32 width=51) (actual time=0.080..0.096 rows=8 loops=1)
>Index Cond: (thing_id = 3570882)
>  Total runtime: 0.115 ms
> (3 rows)
> 
> so I wonder could this simple select is innocent and affected badly by
other queries? how could I find
> those queries that really slow down the database?

Are these by any chance the aggregated costs in pgFouine?
Could it be that the statement just ran very often and used that time in
total?

Other than that, it could have been blocked by something that takes an
exclusive lock on the table.

There are no ON SELECT DO INSTEAD rules or similar things on the table,
right?

Yours,
Laurenz Albe

-- 
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 tables

[Albe Laurenz]

Josh Berkus wrote:
> On 6/20/12 3:27 PM, Midge Brown wrote:
>> I need to move a postgres 9.0 database -- with tables, indexes, and wals 
>> associated with 16
>> tablespaces on 12 logical drives -- to an existing raid 10 drive in another 
>> volume on the same server.
>> Once I get the data off the initial 12 drives they will be reconfigured, at 
>> which point I'll need to
>> move everything from the 2nd volume to the aforementioned 12 logical drives 
>> on the first volume. This
>> is being done both to free up the 2nd volume and to better utilize raid 10.
>>
>> I checked around and found a way to create sql statements to alter the 
>> public tablespaces and
>> indexes, but I haven't found anything that provides information about moving 
>> the numerous associated
>> config files, log files, etc.
>>
>> ANY comments, suggestions, or direction to existing documentation would be 
>> greatly appreciated.

> 1. back everything up.
> 
> 2. create a bunch of directories on the RAID10 to match the existing
> tablespaces (they won't be mounts, but Postgres doesn't care about that).
> 
> 3. shut down postgres
> 
> 4. copy all your files to the new directories
> 
> 5. change your mount points which were in use by the old tablespaces to
> symlinks which point at the new diretories
> 
> 6. start postgres back up from the new location

Shouldn't you also

7. UPDATE spclocation in pg_tablespace ?

Yours,
Laurenz Albe

-- 
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] Tablespaces and query planning

[Albe Laurenz]

Cédric Villemain wrote:
> > - The statistics gathered by ANALYZE are independent of the tablespace
> >   containing the table.
> 
> yes.
> 
> > - The tablespace containing the table has no influence on query planning
> >   unless seq_page_cost or random_page_cost has been set on the
> > tablespace.
> 
> yes.
> 
> > - VACUUM ANALYZE does the same as VACUUM followed by ANALYZE.
> 
> no.
> it is fine grained, but in the diffs there is:
> 
>  VACUUM and ANALYSE do not update pg_class the same way for the
> reltuples/relpages: for ex VACUUM is accurate for index, and ANALYZE is fuzzy
> so if you issue a vacuum you have exact values, if you then run ANALYZE you
> may change them to be less precise.

Thanks for the confirmationsand the clarification.  I hadn't thought of the
statistical entries in pg_class.

Yours,
Laurenz Albe

-- 
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] Seqscan slowness and stored procedures

[Albe Laurenz]

Ivan Voras wrote:
> I have a SQL function (which I've pasted below) and while testing its
> code directly (outside a function), this is the "normal", default
plan:
> 
> http://explain.depesz.com/s/vfP (67 ms)
> 
> and this is the plain with enable_seqscan turned off:
> 
> http://explain.depesz.com/s/EFP (27 ms)
> 
> Disabling seqscan results in almost 2.5x faster execution.
> 
> However, when this code is wrapped in a function, the execution time
is
> closer to the second case (which is great, I'm not complaining):
> 
> edem=> explain analyze select * from document_content_top_voted(36);
> QUERY PLAN
>

--
> -
>  Function Scan on document_content_top_voted  (cost=0.25..10.25
> rows=1000 width=188) (actual time=20.644..20.821 rows=167 loops=1)
>  Total runtime: 21.236 ms
> (2 rows)
> 
> I assume that the difference between the function execution time and
the
> direct plan with seqscan disabled is due to SQL parsing and planning.

That cannot be, because SQL functions do not cache execution plans.

Did you take caching of table data in the buffer cache or the filesystem
cache into account?  Did you run your tests several times in a row and
were the actual execution times consistent?

> Since the plan is compiled-in for stored procedures, is the planner in
> that case already running under the assumption that seqscans must be
> disabled (or something to that effect)?
> 
> Would tweaking enable_seqscan and other planner functions during the
> CREATE FUNCTION have an effect on the stored plan?

No, but you can use the SET clause of CREATE FUNCTION to change
enable_seqscan for this function if you know that this is the right
thing.
But be aware that things might be different for other function arguments
or when the table data change, so this is normally considered a bad
idea.

> Do the functions need to be re-created when the database is fully
> populated, to adjust their stored plans with regards to new
selectivity
> situation on the indexes?

No. Even in PL/pgSQL, where plans are cached, this is only for the
lifetime of the database session.  The plan is generated when the
function is called for the first time in a database session.

Yours,
Laurenz Albe

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

[PERFORM] Tablespaces and query planning

[Albe Laurenz]

Could somebody confirm or refute the following statements, please?

- The statistics gathered by ANALYZE are independent of the tablespace
  containing the table.
- The tablespace containing the table has no influence on query planning
  unless seq_page_cost or random_page_cost has been set on the
tablespace.
- VACUUM ANALYZE does the same as VACUUM followed by ANALYZE.

Yours,
Laurenz Albe

-- 
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] Several optimization options (config/hardware)

[Albe Laurenz]

Martin Grotzke wrote:
>> You could try different kernel I/O elevators and see if that improves
>> something.
>>
>> I have made good experiences with elevator=deadline and
elevator=noop.

> Ok, great info.
> 
> I'm not sure at which device to look honestly to check the current
> configuration.
> 
> mount/fstab shows the device /dev/mapper/VG01-www for the relevant
> partition. When I check iostat high utilization is reported for the
> devices dm-4 and sda (showing nearly the same numbers for util
always),
> so I suspect that dm-4 is mapped on sda.

Use the option -N of "iostat" to see long device names.
You can use "lvm" to figure out the mapping.

> This is the current config:
> $ cat /sys/block/sda/queue/scheduler
> noop anticipatory deadline [cfq]
> $ cat /sys/block/dm-4/queue/scheduler
> none

Do you mean literal "none" or do you mean that the file is empty?

> Which of them should be changed?
> I'll discuss this also with our hosting provider next week, he'll know
> what has to be done.

I'd just add "elevator=deadline" to the kernel line in /etc/grub.conf
and reboot.  At least if it is a dedicated database machine.

But of course you want to change it on the fly first to test - not
knowing
the answer to your question, I would change it in both devices if I can.

Yours,
Laurenz Albe

-- 
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] Several optimization options (config/hardware)

[Albe Laurenz]

Martin Grotzke wrote:
> we want to see if we can gain better performance with our postgresql
> database. In the last year the amount of data growed from ~25G to now
> ~140G and we're currently developing a new feature that needs to get
> data faster from the database. The system is both read and write
heavy.
> 
> At first I want to give you an overview over the hardware, software
and
> configuration and the changes that I see we could check out. I'd be
very
> happy if you could review and tell if the one or the other is
nonsense.
> 
> Hardware:
> - CPU: 4x4 Cores Intel Xeon L5630  @ 2.13GHz
> - RAM: 64GB
> - RAID 1 (1+0) HP Company Smart Array G6 controllers, P410i
>   (I don't know the actual number of discs)
> - A single partition for data and wal-files
> 
> Software
> - RHEL 6, Kernel 2.6.32-220.4.1.el6.x86_64
> - postgresql90-server-9.0.6-1PGDG.rhel6.x86_64

You could try different kernel I/O elevators and see if that improves
something.

I have made good experiences with elevator=deadline and elevator=noop.

Yours,
Laurenz Albe

-- 
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] timing != log duration

[Albe Laurenz]

Rafael Martinez wrote:
> I am wondering why the time reported by \timing in psql is not the
same
> as the time reported by duration in the log file when log_duration or
> log_min_duration_statement are on?. I can not find any information
about
> this in the documentation.

\timing measures the time on the client, while the log contains the
duration
on the server side.  The client time includes the overhead for
transferring
data to and from the server.

Yours,
Laurenz Albe

-- 
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] SSL encryption makes bytea transfer slow

[Albe Laurenz]

Marti Raudsepp wrote:
>> Disabling OpenSSL compression in the source (which
>> is possible since OpenSSL 1.0.0) does not give me any performance
>> improvement.

> If it doesn't give you any performance improvement then you haven't
> disabled compression. Modern CPUs can easily saturate 1 GbitE with
> AES256-encrypted connections. Compression is usually the bottleneck,
> at 20-30 MB/s.

I finally managed to disable compression, and the performance
improvement is dramatic.

Now I have "only" 100% penalty for using SSL, as seen in this
oprofile report:

samples  %image name   symbol name
751  50.1670  libcrypto.so.1.0.0   /usr/lib64/libcrypto.so.1.0.0
594  39.6794  postgres hex_encode
835.5444  libc-2.12.so memcpy

I'll post to hackers and see if I can get this into core.

Yours,
Laurenz Albe

-- 
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] SSL encryption makes bytea transfer slow

[Albe Laurenz]

Marti Raudsepp wrote:
>> I can't get oprofile to run on this RHEL6 box, it doesn't record
>> anything, so all I can test is total query duration.

> Maybe this helps you with OProfile?
> 
> http://people.planetpostgresql.org/andrew/index.php?/archives/224-The-joy-of-Vx.html

Dang, you're right, I wasn't aware that I was on a virtual machine

Now it seems that you were right before as well, and I failed to
disable SSL compression. At any rate this is what oprofile gives me:

samples  %image name   symbol name
6754 83.7861  libz.so.1.2.3/lib64/libz.so.1.2.3
618   7.6665  libcrypto.so.1.0.0   /usr/lib64/libcrypto.so.1.0.0
534   6.6245  postgres hex_encode
951.1785  libc-2.12.so memcpy

Unfortunately there is hardly any documentation for OpenSSL, but I'll try to
figure out what I did wrong.

If I managed to disable compression, I think that would be good for pretty
much everybody who uses SSL with PostgreSQL.

Yours,
Laurez Albe

-- 
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] SSL encryption makes bytea transfer slow

[Albe Laurenz]

Heikki Linnakangas wrote:
 Disabling OpenSSL compression in the source (which
 is possible since OpenSSL 1.0.0) does not give me any performance
 improvement.

>>> If it doesn't give you any performance improvement then you haven't
>>> disabled compression. Modern CPUs can easily saturate 1 GbitE with
>>> AES256-encrypted connections. Compression is usually the bottleneck,
>>> at 20-30 MB/s.

>> Hmm, my knowledge of OpenSSL is so little that it is well possible that
>> I did it wrong. I have attached the small patch I used; can you see
>> where I went wrong?

> That only works with OpenSSL 1.0.0 - did you upgrade? I thought you were
> using 0.9.7a earlier.
> 
> FWIW, it would be better to test "#ifdef SSL_OP_NO_COMPRESSION"
> directly, rather than the version number.

Yes, I ran these tests with RHEL6 and OpenSSL 1.0.0.

I guess I have hit the wall here.

I can't get oprofile to run on this RHEL6 box, it doesn't record
anything, so all I can test is total query duration.

I tried to disable compression as above, but cannot verify that
I was successful.

I also tried different ciphers, but no matter what I did, the
duration on the server stayed pretty much the same, 4 to 5 times
more than without SSL.

Thanks everybody for the help.

Yours,
Laurenz Albe

-- 
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] SSL encryption makes bytea transfer slow

[Albe Laurenz]

Marti Raudsepp wrote:
>> Disabling OpenSSL compression in the source (which
>> is possible since OpenSSL 1.0.0) does not give me any performance
>> improvement.
> 
> If it doesn't give you any performance improvement then you haven't
> disabled compression. Modern CPUs can easily saturate 1 GbitE with
> AES256-encrypted connections. Compression is usually the bottleneck,
> at 20-30 MB/s.

Hmm, my knowledge of OpenSSL is so little that it is well possible that
I did it wrong. I have attached the small patch I used; can you see
where I went wrong?

Yours,
Laurenz Albe


ssl.patch
Description: ssl.patch

-- 
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] SSL encryption makes bytea transfer slow

[Albe Laurenz]

Merlin Moncure wrote:
 We selected a 30MB bytea with psql connected with
 "-h localhost" and found that it makes a huge
 difference whether we have SSL encryption on or off.

 Without SSL the SELECT finished in about a second,
 with SSL it took over 23 seconds (measured with
 \timing in psql).
 During that time, the CPU is 100% busy.
 All data are cached in memory.

 Is this difference as expected?

>>> I tried to reproduce that, but only saw about 4x difference in the
>>> timing, not 23x.

>>> oprofile suggests that all that overhead is coming from compression.
>>> Apparently SSL does compression automatically. Oprofile report of the
>>> above test case with SSL enabled:
[...]

>> Funny, I cannot see any calls to libz. On my system (RHEL 3, PostgreSQL
>> 8.4.8, openssl 0.9.7a) the oprofile reports of the server process look
>> like this:

>> samples  %           symbol name      image name
>> 5326     77.6611     (no symbol)      /lib/libcrypto.so.0.9.7a

> that's a pretty ancient crypto you got there...it may not compress by
> default.  Heikki's test data will compress super well which would
> totally skew performance testing to libz since the amount of data
> actually encrypted will be fairly tiny.  real world high entropy cases
> often show crypto as the worse offender in my experience.

I experimented some more on a recent system (RHEL6, OpenSSL 1.0.0-fips),
and it is as you say. Disabling OpenSSL compression in the source (which
is possible since OpenSSL 1.0.0) does not give me any performance
improvement.

Seems you pretty much have to live with at most 1/4 of the performance
if you want to SELECT large images using SSL.

Yours,
Laurenz Albe

-- 
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] SSL encryption makes bytea transfer slow

[Albe Laurenz]

Heikki Linnakangas wrote:
>> We selected a 30MB bytea with psql connected with
>> "-h localhost" and found that it makes a huge
>> difference whether we have SSL encryption on or off.
>>
>> Without SSL the SELECT finished in about a second,
>> with SSL it took over 23 seconds (measured with
>> \timing in psql).
>> During that time, the CPU is 100% busy.
>> All data are cached in memory.
>>
>> Is this difference as expected?

Thanks for looking at that.

> I tried to reproduce that, but only saw about 4x difference in the
> timing, not 23x.

I tried more tests on an idle server, and the factor I observe here is
3 or 4 as you say.  The original measurements were taken on a server
under load.

> oprofile suggests that all that overhead is coming from compression.
> Apparently SSL does compression automatically. Oprofile report of the
> above test case with SSL enabled:
> 
> samples  %image name   symbol name
> 2817774.4753  libz.so.1.2.3.4  /usr/lib/libz.so.1.2.3.4
> 1814  4.7946  postgres byteain
> 1459  3.8563  libc-2.13.so __memcpy_ssse3_back
> 1437  3.7982  libcrypto.so.0.9.8   /usr/lib/libcrypto.so.0.9.8
> 896   2.3682  postgres hex_encode
> 304   0.8035  vmlinux-3.0.0-1-amd64clear_page_c
> 271   0.7163  libc-2.13.so __strlen_sse42
> 222   0.5868  libssl.so.0.9.8  /usr/lib/libssl.so.0.9.8
> 
> And without:
> 
> samples  %image name   symbol name
> 1601 27.4144  postgres byteain
> 865  14.8116  postgres hex_encode
> 835  14.2979  libc-2.13.so __memcpy_ssse3_back
> 290   4.9658  vmlinux-3.0.0-1-amd64clear_page_c
> 280   4.7945  libc-2.13.so __strlen_sse42
> 184   3.1507  vmlinux-3.0.0-1-amd64page_fault
> 174   2.9795  vmlinux-3.0.0-1-amd64put_mems_allowed
> 
> 
> Maybe your data is very expensive to compress for some reason?

Funny, I cannot see any calls to libz. On my system (RHEL 3, PostgreSQL
8.4.8,
openssl 0.9.7a) the oprofile reports of the server process look like
this:

With SSL:

samples  %   symbol name  image name
5326 77.6611 (no symbol)  /lib/libcrypto.so.0.9.7a
755  11.009  byteaout
/magwien/postgres-8.4.8/bin/postgres
378  5.51181 __GI_memcpy  /lib/tls/libc-2.3.2.so
220  3.20793 printtup
/magwien/postgres-8.4.8/bin/postgres

Without SSL:

samples  %   symbol name  image name
765  55.8394 byteaout
/magwien/postgres-8.4.8/bin/postgres
293  21.3869 __GI_memcpy  /lib/tls/libc-2.3.2.so
220  16.0584 printtup
/magwien/postgres-8.4.8/bin/postgres


Could that still be compression?

The test I am running is:

$ psql "host=localhost sslmode=... dbname=test"
test=> \o /dev/null
test=> select val from images where id=2;
test=> \q

Yours,
Laurenz Albe

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

[PERFORM] SSL encryption makes bytea transfer slow

[Albe Laurenz]

We selected a 30MB bytea with psql connected with
"-h localhost" and found that it makes a huge
difference whether we have SSL encryption on or off.

Without SSL the SELECT finished in about a second,
with SSL it took over 23 seconds (measured with
\timing in psql).
During that time, the CPU is 100% busy.
All data are cached in memory.

Is this difference as expected?

Yours,
Laurenz Albe

-- 
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] default_statistics_target

[Albe Laurenz]

Carlo Stonebanks wrote:
> The whole topic of messing with stats makes my head spin but I am concerned 
> about some horridly performing queries that have had bad rows estimates and 
> others which always choose seq scans when indexes are available. Reading up 
> on how to improve planner estimates, I have seen references to 
> default_statistics_target being changed from the default of 10 to 100.
> 
> Our DB is large, with thousands of tables, but the core schema has about 100 
> tables and the typical row counts are in the millions of rows for the whole 
> table. We have been playing endless games with tuning this server - but with 
> all of the suggestions, I don't think the issue of changing 
> default_statistics_target has ever come up. Realizing that there is a 
> performance hit associated with ANALYZE, are there any other downsides to 
> increasing this value to 100, and is this a common setting for large DBs?

>From PostgreSQL 8.3 to 8.4, the default value for default_statistics_target
has changed from 10 to 100. I would take that as a very strong indication
that 100 is preceived to be a reasonable value by many knowlegdable people.

High values of that parameter are advisable if good performance of
nontrivial queries is the most important thing in your database
(like in a data warehouse) and the cost of ANALYZE is only secondary.

Yours,
Laurenz Albe

-- 
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] foreign key constraint lock behavour in postgresql

[Albe Laurenz]

I wrote:
> > One idea that occurs to me is that it might be possible to add to PG
> > some tuple lock modes that are intended to cover updates that don't
> > touch indexed columns.  So, say:
> > 
> > SHARED NONINDEX - conflicts only with EXCLUSIVE locks
> > SHARED - conflicts with EXCLUSIVE or EXCLUSIVE NONINDEX locks
> > EXCLUSIVE NONINDEX - conflicts with any lock except SHARED NONINDEX.
> > must have this level or higher to update tuple.
> > EXCLUSIVE - conflicts with any other lock.  must have this to update
> > any indexed column of a tuple.
> > 
> > Then a foreign key constraint could take a SHARED NONINDEX lock on the
> > target tuple, because any column that's the target of a foreign key
> > must be indexed; and so we don't care if the nonindexed columns get
> > updated under us.  I think. Also, I believe you'd also need to
> > duplicate any SHARED NONINDEX locks for any new versions of the tuple
> > that got created while the lock was held, which might be sticky.
> 
> That should work and improve concurrency in PostgreSQL!

Even more if EXCLUSIVE NONINDEX is also used for updates that
change indexed columns where the index is not UNIQUE.

Yours,
Laurenz Albe

-- 
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] Linux I/O tuning: CFQ vs. deadline

[Albe Laurenz]

Greg Smith wrote:
> Recently I've made a number of unsubstantiated claims that the deadline 
> scheduler on Linux does bad things compared to CFQ when running 
> real-world mixed I/O database tests.  Unfortunately every time I do one 
> of these I end up unable to release the results due to client 
> confidentiality issues.  However, I do keep an eye out for people who 
> run into the same issues in public benchmarks, and I just found one:  
> http://insights.oetiker.ch/linux/fsopbench/

That is interesting; particularly since I have made one quite different
experience in which deadline outperformed CFQ by a factor of approximately 4.

So I tried to look for differences, and I found two possible places:
- My test case was read-only, our production system is read-mostly.
- We did not have a RAID array, but a SAN box (with RAID inside).

The "noop" scheduler performed about as well as "deadline".
I wonder if the two differences above could explain the different
result.

Yours,
Laurenz Albe

-- 
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] foreign key constraint lock behavour in postgresql

[Albe Laurenz]

Robert Haas wrote:
[explanation of how Oracle locks on Updates involving foreign keys]
> 
> Yeah, that seems odd.  I assume they know what they're doing; they're
> Oracle, after all.  It does sound, too, like they have column level
> locks based on your comment about "an EXCLUSIVE lock on the modified
> column".  I doubt we're likely to implement such a thing, but who
> knows.

Sorry, that was a mistake. I meant "an EXCLUSIVE lock on the modified
row". Oracle works quite like PostgreSQL in locking modified rows.

> Another interesting point is that a statement that involves
> only tb_b can trigger a share lock on tb_a; presumably that means they
> know they need to take a share lock on every table that references the
> updated column, which seems like it could be fairly expensive in the
> worst case.

Yes, that's the only way Oracle's method makes sense, by taking out
a shared lock on every table that references the updated table.

It may be expensive, but as the example shows, it also allows concurrency
in a way that PostgreSQL doesn't, so maybe it's worth the pain.

On the other hand, Oracle has some problems that PostgreSQl doesn't.
If you run the following example, assuming the original setup of
wangyuxiang:

SESSION 2:
   BEGIN;
   UPDATE tb_b SET id='b2' WHERE id='b1';

SESSION 1:
   INSERT INTO tb_a (id,b_id) VALUES ('a1','b1');

SESSION 2:
   UPDATE tb_b SET id='b1' WHERE id='b2';
   COMMIT;

it will succeed just fine on PostgreSQL (with SESSION 1 blocking until
SESSION 2 COMMITs), but on Oracle it will cause a deadlock aborting
SESSION 1.

So, according the the principle of preservation of difficulties, both
implementations have their snags, and I wouldn't say that PostgreSQL
is worse off.

> One idea that occurs to me is that it might be possible to add to PG
> some tuple lock modes that are intended to cover updates that don't
> touch indexed columns.  So, say:
> 
> SHARED NONINDEX - conflicts only with EXCLUSIVE locks
> SHARED - conflicts with EXCLUSIVE or EXCLUSIVE NONINDEX locks
> EXCLUSIVE NONINDEX - conflicts with any lock except SHARED NONINDEX.
> must have this level or higher to update tuple.
> EXCLUSIVE - conflicts with any other lock.  must have this to update
> any indexed column of a tuple.
> 
> Then a foreign key constraint could take a SHARED NONINDEX lock on the
> target tuple, because any column that's the target of a foreign key
> must be indexed; and so we don't care if the nonindexed columns get
> updated under us.  I think. Also, I believe you'd also need to
> duplicate any SHARED NONINDEX locks for any new versions of the tuple
> that got created while the lock was held, which might be sticky.

That should work and improve concurrency in PostgreSQL!

Yours,
Laurenz Albe

-- 
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] foreign key constraint lock behavour in postgresql

[Albe Laurenz]

Robert Haas wrote:
> Just for kicks I tried this out and the behavior is as the OP
> describes: after a little poking around, it sees that the INSERT grabs
> a share-lock on the referenced row so that a concurrent update can't
> modify the referenced column.
> 
> It's not really clear how to get around this.  If it were possible to
> lock individual columns within a tuple, then the particular update
> above could be allowed since only the name is being changed.  Does
> anyone know what happens in Oracle if the update targets the id column
> rather than the name column?

I have investigated what Oracle (10.2) does in this situation.

First the original sample as posted by wangyuxiang:

insert into tb_a(id,b_id) values('a1','b1');

will place a ROW EXCLUSIVE lock on tb_a, an EXCLUSIVE lock
on the row that was inserted and a ROW SHARE lock on tb_b.
No lock on any row in the parent table is taken.

update tb_b set name='changed' where id='b1';

will place a ROW EXCLUSIVE lock on tb_b and an EXCLUSIVE
lock on the modified column.

Since ROW EXCLUSIVE and ROW SHARE do not conflict, both statements
will succeed.


Now to your question:

update tb_b set id='b2' where id='b1';

This will place a ROW EXCLUSIVE lock on tb_b, an EXCLUSIVE lock
on the updated row and a SHARE lock on tb_a.
This last lock is only held for the duration of the UPDATE statement
and *not* until the end of the transaction.

So this update will block, because the SHARE and the ROW EXCLUSIVE
lock on tb_a are incompatible.


So it seems that Oracle handles this quite differently.
I was particularly surprised that it uses locks that are not held
until end-of-transaction.

Yours,
Laurenz Albe

-- 
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] There is a statistic table?

[Albe Laurenz]

waldomiro wrote:
> I need to know how much the postgres is going to disk to get 
> blocks and how much it is going to cache? witch is the 
> statistic table and what is the field that indicates blocks 
> reads from the disk and the memory cache?

The view pg_statio_all_tables will show you the number of
disk reads and buffer hits per table.

There are other statistics views, see
http://www.postgresql.org/docs/8.4/static/monitoring-stats.html#MONITORING-STATS-VIEWS

> Another question is, what is the best memory configuration to 
> keep more data in cache? 

That's easy - the greater shared_buffers is, the more cache you have.

Another option is to choose shared_buffers not too large and let
the filesystem cache buffer the database for you.

Yours,
Laurenz Albe

-- 
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] Configuring Postgresql for writing BLOB at a high-rate

[Albe Laurenz]

SHIVESH WANGRUNGVICHAISRI wrote:
> The main question is: how do I configure Postgresql such that 
> it's most efficient for storing large BLOB at a high-rate?

Refering to what you wrote on the web site you quoted,
I would guess that neither tuning WAL nor tuning logging
will have much effect.

My guess would be that the system will be I/O bound from
writing the large objects to disk, so maybe tuning on the
operating system or hardware level might be most effective.

Notice the use of subjunctive mode in the above.
What you should do is: run your test and find out where
the bottleneck is. Are the disks very busy and do you see
significant I/O-wait? Then you're I/O bound.

In that case you could try tuning the I/O part of the kernel
(you didn't say which operating system) and - easiest of all -
get rid of that RAID-5 and get a RAID-1 of fast disks.

Yours,
Laurenz Albe

-- 
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] Concurrency issue under very heay loads

[Albe Laurenz]

Raji Sridar wrote:
> We use a typical counter within a transaction to generate 
> order sequence number and update the next sequence number. 
> This is a simple next counter - nothing fancy about it.  When 
> multiple clients are concurrently accessing this table and 
> updating it, under extermely heavy loads in the system 
> (stress testing), we find that the same order number is being 
> generated for multiple clients. Could this be a bug? Is there 
> a workaround? Please let me know.

Please show us your code!

Yours,
Laurenz Albe

-- 
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] embedded sql regression from 8.2.4 to 8.3.7

[Albe Laurenz]

Eric Haszlakiewicz wrote:
>> The log is misleading; the first statement is not really executed,
>> it is only prepared (parsed). If you set the log level to DEBUG2, it
>> will look like:
> 
> Yes, but it's still incurring the overhead of sending the message to the
> server, isn't it?

Yes.

>> Maybe it is the additional PREPARE that slows your program.
>> Are your queries complex enough that the PREPARE consumes
>> significant time?
> 
> No, the queries aren't complex, but we prepare and excute hundred of
> queries, so it seems like the overhead of the extra message sent to the
> server adds up.

I see.

>  I was hoping there was a way to work around this by
> having Postgres not send that prepare to the server, but given the
> "major protocol rewrite" phrase on that commit log message you pointed
> me at, I'm guessing that's not possible.

It looks like what is normally an advantage (having named prepared
statements that can be reused) makes things slower in your case, since
you do not use the prepared statement at all and only need it to
be able to use a cursor with dynamic SQL.

Yours,
Laurenz Albe

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