[PERFORM] Checkpointing question

[Strange, John W]

During heavy writes times we get the checkpoint too often error, what's the 
real knock down effect of checkpointing too often?  The documents don't really 
say what is wrong with checkpointing too often, does it cause block, io 
contention, etc, etc?  From my understanding it's just IO contention, but I 
wanted to make sure.

14.4.6. Increase checkpoint_segments
Temporarily increasing the 
checkpoint_segments
 configuration variable can also make large data loads faster. This is because 
loading a large amount of data into PostgreSQL will cause checkpoints to occur 
more often than the normal checkpoint frequency (specified by the 
checkpoint_timeout configuration variable). Whenever a checkpoint occurs, all 
dirty pages must be flushed to disk. By increasing checkpoint_segments 
temporarily during bulk data loads, the number of checkpoints that are required 
can be reduced.


- John

[ 2011-02-15 09:34:30.549 GMT ]  :4d404b6e.47ef LOG:  checkpoint starting: xlog
[ 2011-02-15 09:34:43.656 GMT ]  :4d404b6e.47ef LOG:  checkpoint complete: 
wrote 36135 buffers (0.4%); 0 transaction log file(s) added, 0 removed, 12 
recycled; write=13.101 s, sync=0.000 s, total=13.107 s
[ 2011-02-15 09:34:57.090 GMT ]  :4d404b6e.47ef LOG:  checkpoints are occurring 
too frequently (27 seconds apart)
[ 2011-02-15 09:34:57.090 GMT ]  :4d404b6e.47ef HINT:  Consider increasing the 
configuration parameter "checkpoint_segments".
[ 2011-02-15 09:34:57.090 GMT ]  :4d404b6e.47ef LOG:  checkpoint starting: xlog
[ 2011-02-15 09:35:11.492 GMT ]  :4d404b6e.47ef LOG:  checkpoint complete: 
wrote 54634 buffers (0.7%); 0 transaction log file(s) added, 0 removed, 30 
recycled; write=14.290 s, sync=0.000 s, total=14.401 s
[ 2011-02-15 09:35:25.496 GMT ]  :4d404b6e.47ef LOG:  checkpoints are occurring 
too frequently (28 seconds apart)
[ 2011-02-15 09:35:25.496 GMT ]  :4d404b6e.47ef HINT:  Consider increasing the 
configuration parameter "checkpoint_segments".
[ 2011-02-15 09:35:25.496 GMT ]  :4d404b6e.47ef LOG:  checkpoint starting: xlog
[ 2011-02-15 09:35:39.688 GMT ]  :4d404b6e.47ef LOG:  checkpoint complete: 
wrote 39352 buffers (0.5%); 0 transaction log file(s) added, 0 removed, 30 
recycled; write=14.185 s, sync=0.000 s, total=14.192 s
[ 2011-02-15 09:35:53.417 GMT ]  :4d404b6e.47ef LOG:  checkpoints are occurring 
too frequently (28 seconds apart)
[ 2011-02-15 09:35:53.417 GMT ]  :4d404b6e.47ef HINT:  Consider increasing the 
configuration parameter "checkpoint_segments".
[ 2011-02-15 09:35:53.417 GMT ]  :4d404b6e.47ef LOG:  checkpoint starting: xlog
[ 2011-02-15 09:36:09.059 GMT ]  :4d404b6e.47ef LOG:  checkpoint complete: 
wrote 48803 buffers (0.6%); 0 transaction log file(s) added, 0 removed, 30 
recycled; write=15.408 s, sync=0.000 s, total=15.641 s






This communication is for informational purposes only. It is not
intended as an offer or solicitation for the purchase or sale of
any financial instrument or as an official confirmation of any
transaction. All market prices, data and other information are not
warranted as to completeness or accuracy and are subject to change
without notice. Any comments or statements made herein do not
necessarily reflect those of JPMorgan Chase & Co., its subsidiaries
and affiliates.

This transmission may contain information that is privileged,
confidential, legally privileged, and/or exempt from disclosure
under applicable law. If you are not the intended recipient, you
are hereby notified that any disclosure, copying, distribution, or
use of the information contained herein (including any reliance
thereon) is STRICTLY PROHIBITED. Although this transmission and any
attachments are believed to be free of any virus or other defect
that might affect any computer system into which it is received and
opened, it is the responsibility of the recipient to ensure that it
is virus free and no responsibility is accepted by JPMorgan Chase &
Co., its subsidiaries and affiliates, as applicable, for any loss
or damage arising in any way from its use. If you received this
transmission in error, please immediately contact the sender and
destroy the material in its entirety, whether in electronic or hard
copy format. Thank you.

Please refer to http://www.jpmorgan.com/pages/disclosures for
disclosures relating to European legal entities.

[PERFORM] LIMIT on partitioned-table!?

[Kim A. Brandt]

Hello list,

does `postgres (PostgreSQL) 8.4.5' use the LIMIT of a query when it is run on a 
partitioned-table or am I doing something wrong? It looks as if postgres queries 
all partitions and then LIMITing the records afterwards!? This results in a long 
(>3 minutes) running query. What can I do to optimise this?

The query could look like this:

EXPLAIN ANALYSE
SELECT *
FROM flexserver.unitstat
WHERE nodeid = 'abcd'
AND ts > '2010-01-01 00:00:00'
AND ts < '2011-02-15 15:00:00'
ORDER BY nodeid, ts
LIMIT 1000;

This is the `EXPLAIN ANALYSE'-output:

Limit  (cost=232195.49..232197.99 rows=1000 width=194) (actual 
time=205846.722..205852.218 rows=1000 loops=1)
  ->  Sort  (cost=232195.49..232498.26 rows=121108 width=194) (actual 
time=205846.717..205848.684 rows=1000 loops=1)
Sort Key: flexserver.unitstat.ts
Sort Method:  top-N heapsort  Memory: 314kB
->  Result  (cost=0.00..22.27 rows=121108 width=194) (actual 
time=444.969..205136.182 rows=203492 loops=1)
  ->  Append  (cost=0.00..22.27 rows=121108 width=194) 
(actual time=444.963..204236.800 rows=203492 loops=1)
->  Seq Scan on unitstat  (cost=0.00..14.90 rows=1 
width=258) (actual time=0.007..0.007 rows=0 loops=1)
  Filter: ((ts > '2010-01-01 00:00:00'::timestamp 
without time zone) AND (ts < '2011-02-15 15:00:00'::timestamp without time zone) 
AND ((nodeid)::text = 'abcd'::text))
->  Bitmap Heap Scan on unitstat_y2011m01 unitstat  
(cost=116.47..8097.17 rows=4189 width=194) (actual time=444.949..9900.002 
rows=5377 loops=1)
  Recheck Cond: ((nodeid)::text = 'abcd'::text)
  Filter: ((ts > '2010-01-01 00:00:00'::timestamp 
without time zone) AND (ts < '2011-02-15 15:00:00'::timestamp without time zone))
  ->  Bitmap Index Scan on 
idx_unitstat_y2011m01_nodeid_gps_ts  (cost=0.00..115.42 rows=4190 width=0) (actual 
time=426.599..426.599 rows=5377 loops=1)
Index Cond: ((nodeid)::text = 'abcd'::text)
->  Bitmap Heap Scan on unitstat_y2011m02 unitstat  
(cost=52.67..3689.16 rows=1906 width=194) (actual time=73.512..3211.698 rows=796 
loops=1)
  Recheck Cond: ((nodeid)::text = 'abcd'::text)
  Filter: ((ts > '2010-01-01 00:00:00'::timestamp 
without time zone) AND (ts < '2011-02-15 15:00:00'::timestamp without time zone))
  ->  Bitmap Index Scan on 
idx_unitstat_y2011m02_nodeid_gps_ts  (cost=0.00..52.20 rows=1906 width=0) (actual 
time=55.458..55.458 rows=796 loops=1)
Index Cond: ((nodeid)::text = 'abcd'::text)
->  Index Scan using 
fki_unitstat_y2010m02_nodeid_ts_fkey on unitstat_y2010m02 unitstat  
(cost=0.00..10179.11 rows=5257 width=193) (actual time=39.531..11660.741 rows=6524 
loops=1)
  Index Cond: (((nodeid)::text = 'abcd'::text) AND (ts 
> '2010-01-01 00:00:00'::timestamp without time zone) AND (ts < '2011-02-15 
15:00:00'::timestamp without time zone))
->  Index Scan using 
fki_unitstat_y2010m01_nodeid_ts_fkey on unitstat_y2010m01 unitstat  
(cost=0.00..10324.31 rows=5358 width=193) (actual time=38.255..9808.237 rows=7128 
loops=1)
  Index Cond: (((nodeid)::text = 'abcd'::text) AND (ts 
> '2010-01-01 00:00:00'::timestamp without time zone) AND (ts < '2011-02-15 
15:00:00'::timestamp without time zone))
->  Bitmap Heap Scan on unitstat_y2010m11 unitstat  
(cost=586.92..39314.99 rows=21965 width=195) (actual time=1417.528..26090.404 
rows=24464 loops=1)
  Recheck Cond: ((nodeid)::text = 'abcd'::text)
  Filter: ((ts > '2010-01-01 00:00:00'::timestamp 
without time zone) AND (ts < '2011-02-15 15:00:00'::timestamp without time zone))
  ->  Bitmap Index Scan on 
idx_unitstat_y2010m11_nodeid_gps_ts  (cost=0.00..581.43 rows=21970 width=0) 
(actual time=1400.898..1400.898 rows=24464 loops=1)
Index Cond: ((nodeid)::text = 'abcd'::text)
->  Bitmap Heap Scan on unitstat_y2010m12 unitstat  
(cost=128.72..9050.29 rows=4683 width=194) (actual time=238.679..7472.936 
rows=2014 loops=1)
  Recheck Cond: ((nodeid)::text = 'abcd'::text)
  Filter: ((ts > '2010-01-01 00:00:00'::timestamp 
without time zone) AND (ts < '2011-02-15 15:00:00'::timestamp without time zone))
  ->  Bitmap Index Scan on 
idx_unitstat_y2010m12_nodeid_gps_ts  (cost=0.00..127.55 rows=4684 width=0) (actual 
time=225.009..225.009 rows=2014 loops=1)
Index Cond: ((nodeid)::text = 'abcd'

Re: [PERFORM] LIMIT on partitioned-table!?

[Shaun Thomas]

On 02/15/2011 08:23 AM, Kim A. Brandt wrote:


does `postgres (PostgreSQL) 8.4.5' use the LIMIT of a query when it
is run on a partitioned-table or am I doing something wrong? It looks
as if postgres queries all partitions and then LIMITing the records
afterwards!? This results in a long (>3 minutes) running query. What
can I do to optimise this?


Make sure you have constraint_exclusion set to 'on' in your config. 
Also, what are your checks for your partitions? You've got a pretty wide 
range in your 'ts' checks, so if you're using them as your partition 
definition, you're not helping yourself.


The main issue might just be that you've used an order clause. LIMIT 
1000 or not, even if it can restrict the result set based on your CHECK 
criteria, it'll still need to select every matching row from every 
matched partition, order the results, and chop off the first 1000.


--
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604
312-676-8870
stho...@peak6.com

__

See  http://www.peak6.com/email_disclaimer.php
for terms and conditions related to this email

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

Re: [PERFORM] Checkpointing question

[Kevin Grittner]

"Strange, John W"  wrote:
 
> During heavy writes times we get the checkpoint too often error,
> what's the real knock down effect of checkpointing too often?
 
The main concern is that it may cause an increase in disk writes,
possibly to the point of causing blocking while waiting for the
disk.
 
Generally bigger checkpoint_segments settings improve performance,
especially as shared_buffers is increased.  There are some
counter-examples, particularly during bulk loads, which haven't
really been explained:
 
http://archives.postgresql.org/pgsql-hackers/2010-04/msg00848.php
 
That makes this an area where careful testing of your real workload
with different settings can be important, at least if you're trying
to wring that last ounce of performance out of your server..
 
-Kevin

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

[PERFORM] high user cpu, massive SELECTs, no io waiting problem

[Thomas Pöhler]

Hi list,

 

first time for me here, hope you're not dealing too severely with me regarding 
guidelines. Giving my best.

 

We are running PostgreSQL 8.4.4 on x86_64-unknown-linux-gnu, compiled by GCC 
gcc (Debian 4.3.2-1.1) 4.3.2, 64-bit on a Supermicro SuperServer 8026B-6RF.

This version is downloaded from postgresql.org and selfcompiled, running for 
over a year now. The Server has 128 GB RAM and Four Intel® Xeon® X7550 with 64 
logical cores.

Operating System is "Linux database1 2.6.32-bpo.5-amd64 #1 SMP Mon Dec 13 
17:10:39 UTC 2010 x86_64 GNU/Linux".

 

The System boots through iscsi over a Qlogic QLE4062C HBA. Pgdata and xlog is 
logged in over iscsi HBA too. We tried en and disabling jumbo frames. Makes no 
difference.

We are using a DELL Equallogic SAN Backend with SAS drives.

 

Postgres is used as  backend for a high performance website. We are using nginx 
with php-fastcgi and memcached.

 

Since a few weeks we have really strange peaks on this system. User CPU is 
increasing up to 100% and we have lots of SELECTs running. 

There is no iowait at this time, only high user cpu and we don't know where 
this is coming from. It seems like this is only happening under certain 
circumstances.

 

We can solve this problem by simply removing the load from the website by 
delivering an offline page. We let database calm down for a while and then 
slowly throttling users.

 

See ganglia: http://dl.dropbox.com/u/183323/CPUloadprobsdb1.jpg

 

Has someone made similar experiences? Perhaps there is some issue between 
Postgres 8.4.4 and kernel 2.6.32?

 

Thank in advance

Thomas

 

 

 

-- 

Turtle Entertainment GmbH

Thomas Pöhler, Manager IT Operations

Siegburger Str. 189

50679 Cologne

Germany

fon. +49 221 880449-331

fax. +49 221 880449-399

http://www.turtle-entertainment.com/

http://www.esl.eu/

http://www.consoles.net/

Managing Director: Ralf Reichert

Register Court: Local Court Cologne, HRB 36678

 

Re: [PERFORM] high user cpu, massive SELECTs, no io waiting problem

[Scott Marlowe]

On Tue, Feb 15, 2011 at 10:19 AM, Thomas Pöhler
 wrote:
> Since a few weeks we have really strange peaks on this system. User CPU is
> increasing up to 100% and we have lots of SELECTs running.

Are you using pooling of some kind, or do you have LOTS of connections?

> There is no iowait at this time, only high user cpu and we don’t know where
> this is coming from. It seems like this is only happening under certain
> circumstances.

run htop and look for red.  if youi've got lots of red bar on each CPU
but no io wait then it's waiting for memory access.  Most of these
multi-core machines will be memory read / write speed bound.  Pooling
will help relieve some of that memory bandwidth load, but might not be
enough to eliminate it.

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

Re: [PERFORM] high user cpu, massive SELECTs, no io waiting problem

[Kevin Grittner]

Thomas Pöhler wrote:
 
> we have lots of SELECTs running.
 
How many?
 
Could you show your postgresql.conf file, with all comments removed?
 
What does vmstat 1 (or similar) show at baseline and during your
problem episodes?
 
-Kevin

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

[PERFORM] pg_dumpall affecting performance

[Mark Mikulec]

Hello,

I was under the impression that pg_dumpall didn't affect database
performance when dumping while the db is live. However I have evidence to
the contrary now - queries that are run during the pg_dumpall time take 10
to a 100 times longer to execute than normal while pg_dumpall is running.
The strange thing is that this started after my database grew by about 25%
after a large influx of data due to user load. I'm wonder if there is a
tipping
point or a config setting I need to change now that the db is larger that
is
causing all this to happen.

Thanks,
  Mark


-- 
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_dumpall affecting performance

[Plugge, Joe R.]

I was always under the impression that pg_dump and pg_dumpall cause all data to 
be read in to the buffers and then out, (of course squeezing out whatever may 
be active).  That is the big advantage to using PITR backups and using a tar or 
cpio method of backing up active containers and shipping off to another system, 
disk or api to tape system.

-Original Message-
From: pgsql-performance-ow...@postgresql.org 
[mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of Mark Mikulec
Sent: Tuesday, February 15, 2011 12:41 PM
To: pgsql-performance@postgresql.org
Subject: [PERFORM] pg_dumpall affecting performance

Hello,

I was under the impression that pg_dumpall didn't affect database performance 
when dumping while the db is live. However I have evidence to the contrary now 
- queries that are run during the pg_dumpall time take 10 to a 100 times longer 
to execute than normal while pg_dumpall is running.
The strange thing is that this started after my database grew by about 25% 
after a large influx of data due to user load. I'm wonder if there is a tipping 
point or a config setting I need to change now that the db is larger that is 
causing all this to happen.

Thanks,
  Mark


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

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

Re: [PERFORM] pg_dumpall affecting performance

[Steve Crawford]

On 02/15/2011 10:41 AM, Mark Mikulec wrote:

Hello,

I was under the impression that pg_dumpall didn't affect database
performance when dumping while the db is live. However I have evidence to
the contrary now - queries that are run during the pg_dumpall time take 10
to a 100 times longer to execute than normal while pg_dumpall is running.
The strange thing is that this started after my database grew by about 25%
after a large influx of data due to user load. I'm wonder if there is a
tipping
point or a config setting I need to change now that the db is larger that
is
causing all this to happen.

Don't know where that impression came from. It is true that you can 
continue to *use* your database normally while running a dump but you 
are reading the entire database and either transmitting it over the 
network or writing it to a local drive so it shouldn't be surprising 
that performance is impacted.


There are tipping points - one big one is when you move from having all 
your data in RAM to needing to read disk. And it can be a whopper. If 
all your information, through PG or OS caching is in RAM then your dumps 
may run very quickly. The moment you cross the point that things don't 
quite fit you can see a sharp decline.


Consider a least-recently-used algorithm and a very simplistic scenario. 
You read the "start" data. It isn't cached so you go to disk *and* you 
put those blocks into cache pushing others than you would need later out 
of cache. This continues and you potentially end up having to read 
everything from disk plus incur the overhead of checking and updating 
the cache. Meanwhile, the data you needed for your query may have been 
pushed out of cache so there is more contention for disk.


Admittedly an over-simplified example but you see the problem.

Cheers,
Steve


--
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 user cpu, massive SELECTs, no io waiting problem

[Strange, John W]

You have also run analyze verbose, and checked to make sure you don't have a 
ton of bloated indexes?

- check the process with strace -p PID
- check the diskIO with iostat, not vmstat
- run analyze verbose, and possible reindex the database, or cluster the larger 
tables.
- dump from pg_stat_activity, and check what the largest objects are based on 
relpages from pg_class.
- check index scans/table scans from pg_statio tables if you have 
track_activities on in the .conf file.

- John

From: pgsql-performance-ow...@postgresql.org 
[mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of Thomas Pöhler
Sent: 15 February 2011 17:19
To: pgsql-performance@postgresql.org
Cc: Felix Feinhals; Verteiler_A-Team; Björn Metzdorf
Subject: [PERFORM] high user cpu, massive SELECTs, no io waiting problem

Hi list,

first time for me here, hope you're not dealing too severely with me regarding 
guidelines. Giving my best.

We are running PostgreSQL 8.4.4 on x86_64-unknown-linux-gnu, compiled by GCC 
gcc (Debian 4.3.2-1.1) 4.3.2, 64-bit on a Supermicro SuperServer 8026B-6RF.
This version is downloaded from postgresql.org and selfcompiled, running for 
over a year now. The Server has 128 GB RAM and Four Intel® Xeon® X7550 with 64 
logical cores.
Operating System is "Linux database1 2.6.32-bpo.5-amd64 #1 SMP Mon Dec 13 
17:10:39 UTC 2010 x86_64 GNU/Linux".

The System boots through iscsi over a Qlogic QLE4062C HBA. Pgdata and xlog is 
logged in over iscsi HBA too. We tried en and disabling jumbo frames. Makes no 
difference.
We are using a DELL Equallogic SAN Backend with SAS drives.

Postgres is used as  backend for a high performance website. We are using nginx 
with php-fastcgi and memcached.

Since a few weeks we have really strange peaks on this system. User CPU is 
increasing up to 100% and we have lots of SELECTs running.
There is no iowait at this time, only high user cpu and we don't know where 
this is coming from. It seems like this is only happening under certain 
circumstances.

We can solve this problem by simply removing the load from the website by 
delivering an offline page. We let database calm down for a while and then 
slowly throttling users.

See ganglia: http://dl.dropbox.com/u/183323/CPUloadprobsdb1.jpg

Has someone made similar experiences? Perhaps there is some issue between 
Postgres 8.4.4 and kernel 2.6.32?

Thank in advance
Thomas



--
Turtle Entertainment GmbH
Thomas Pöhler, Manager IT Operations
Siegburger Str. 189
50679 Cologne
Germany
fon. +49 221 880449-331
fax. +49 221 880449-399
http://www.turtle-entertainment.com/
http://www.esl.eu/
http://www.consoles.net/
Managing Director: Ralf Reichert
Register Court: Local Court Cologne, HRB 36678



This communication is for informational purposes only. It is not
intended as an offer or solicitation for the purchase or sale of
any financial instrument or as an official confirmation of any
transaction. All market prices, data and other information are not
warranted as to completeness or accuracy and are subject to change
without notice. Any comments or statements made herein do not
necessarily reflect those of JPMorgan Chase & Co., its subsidiaries
and affiliates.

This transmission may contain information that is privileged,
confidential, legally privileged, and/or exempt from disclosure
under applicable law. If you are not the intended recipient, you
are hereby notified that any disclosure, copying, distribution, or
use of the information contained herein (including any reliance
thereon) is STRICTLY PROHIBITED. Although this transmission and any
attachments are believed to be free of any virus or other defect
that might affect any computer system into which it is received and
opened, it is the responsibility of the recipient to ensure that it
is virus free and no responsibility is accepted by JPMorgan Chase &
Co., its subsidiaries and affiliates, as applicable, for any loss
or damage arising in any way from its use. If you received this
transmission in error, please immediately contact the sender and
destroy the material in its entirety, whether in electronic or hard
copy format. Thank you.

Please refer to http://www.jpmorgan.com/pages/disclosures for
disclosures relating to European legal entities.

Re: [PERFORM] pg_dumpall affecting performance

[Kevin Grittner]

Mark Mikulec  wrote:
 
> The strange thing is that this started after my database grew by
> about 25% after a large influx of data due to user load
 
In addition to the issues already mentioned, there is the fact that
to maintain consistency an entire database must be dumped in a
single database transaction with one snapshot.  This means that
garbage collection can't run, which may lead to bloat under some
circumstances.  This may be why your database grew by 25%.  If that
bloat is concentrated in a small number of tables, you may want to
schedule aggressive maintenance (like CLUSTER) on those tables.
 
One other factor which can affect running applications is the table
locks which the dump must hold.
 
You might want to look into PITR backup techniques, or streaming
replication on 9.0
 
-Kevin

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

Re: [PERFORM] LIMIT on partitioned-table!?

[Kim A. Brandt]

Thank you Shaun,

removing the ORDER BY worked. But I am afraid to ask this. How can I order by 
partition? It seams that the planner has picked a random(!?) order of partition 
to select from. The returned records, from the selected partition, are 
correctly sorted bythe index though.

On 2011-02-15 15:49, Shaun Thomas wrote:

On 02/15/2011 08:23 AM, Kim A. Brandt wrote:


does `postgres (PostgreSQL) 8.4.5' use the LIMIT of a query when it
is run on a partitioned-table or am I doing something wrong? It looks
as if postgres queries all partitions and then LIMITing the records
afterwards!? This results in a long (>3 minutes) running query. What
can I do to optimise this?


Make sure you have constraint_exclusion set to 'on' in your config. Also, what 
are your checks for your partitions? You've got a pretty wide range in your 
'ts' checks, so if you're using them as your partition definition, you're not 
helping yourself.


The parameter `constraint_exclusion' is set to `partition'. Postgres is on 
FreeBSD.

My checks (if I understand you right) are as follows:

CREATE TABLE flexserver.unitstat_y2011m02
(
  ts timestamp without time zone NOT NULL,
  nodeid character varying(10) NOT NULL,
  gps_ts timestamp without time zone NOT NULL,
  ...
  CONSTRAINT unitstat_y2011m02_ts_check CHECK (ts >= '2011-02-01 
00:00:00'::timestamp without time zone AND ts < '2011-03-01 00:00:00'::timestamp 
without time zone)
)
INHERITS (flexserver.unitstat);

Each partition is constrained to one month.

About the wide range, I am aware of that. This probably has to change anyway!? 
So the current (and probably final solution) is to use a narrower search range. 
Thank you for the hint.


The main issue might just be that you've used an order clause. LIMIT 1000 or 
not, even if it can restrict the result set based on your CHECK criteria, it'll 
still need to select every matching row from every matched partition, order the 
results, and chop off the first 1000.


That was it. Just how can one order by partition if one would do a wide range 
search over multiple partitions?

The new query and EXPLAIN ANALYSE-output is:

SELECT *
FROM flexserver.unitstat
WHERE nodeid = 'abcd'
AND ts > '2010-01-01 00:00:00'
AND ts < '2011-02-15 15:00:00'
--ORDER BY nodeid, ts
LIMIT 1000;


Limit  (cost=0.00..1862.46 rows=1000 width=194) (actual time=2.569..18.948 
rows=1000 loops=1)
  ->  Result  (cost=0.00..225611.08 rows=121136 width=194) (actual 
time=2.566..15.412 rows=1000 loops=1)
->  Append  (cost=0.00..225611.08 rows=121136 width=194) (actual 
time=2.558..11.243 rows=1000 loops=1)
  ->  Seq Scan on unitstat  (cost=0.00..14.90 rows=1 width=258) 
(actual time=0.003..0.003 rows=0 loops=1)
Filter: ((ts > '2010-01-01 00:00:00'::timestamp without 
time zone) AND (ts < '2011-02-15 15:00:00'::timestamp without time zone) AND 
((nodeid)::text = 'abcd'::text))
  ->  Bitmap Heap Scan on unitstat_y2011m01 unitstat  
(cost=116.47..8097.17 rows=4189 width=194) (actual time=2.550..7.701 rows=1000 
loops=1)
Recheck Cond: ((nodeid)::text = 'abcd'::text)
Filter: ((ts > '2010-01-01 00:00:00'::timestamp without 
time zone) AND (ts < '2011-02-15 15:00:00'::timestamp without time zone))
->  Bitmap Index Scan on 
idx_unitstat_y2011m01_nodeid_gps_ts  (cost=0.00..115.42 rows=4190 width=0) (actual 
time=1.706..1.706 rows=5377 loops=1)
  Index Cond: ((nodeid)::text = 'abcd'::text)
  ->  Bitmap Heap Scan on unitstat_y2011m02 unitstat  
(cost=52.92..3744.97 rows=1934 width=194) (never executed)
Recheck Cond: ((nodeid)::text = 'abcd'::text)
Filter: ((ts > '2010-01-01 00:00:00'::timestamp without 
time zone) AND (ts < '2011-02-15 15:00:00'::timestamp without time zone))
->  Bitmap Index Scan on 
idx_unitstat_y2011m02_nodeid_gps_ts  (cost=0.00..52.44 rows=1935 width=0) (never 
executed)
  Index Cond: ((nodeid)::text = 'abcd'::text)
  ->  Index Scan using fki_unitstat_y2010m02_nodeid_ts_fkey on 
unitstat_y2010m02 unitstat  (cost=0.00..10179.11 rows=5257 width=193) (never 
executed)
Index Cond: (((nodeid)::text = 'abcd'::text) AND (ts > 
'2010-01-01 00:00:00'::timestamp without time zone) AND (ts < '2011-02-15 
15:00:00'::timestamp without time zone))
  ->  Index Scan using fki_unitstat_y2010m01_nodeid_ts_fkey on 
unitstat_y2010m01 unitstat  (cost=0.00..10324.31 rows=5358 width=193) (never 
executed)
Index Cond: (((nodeid)::text = 'abcd'::text) AND (ts > 
'2010-01-01 00:00:00'::timestamp without time zone) AND (ts < '2011-02-15 
15:00:00'::timestamp without time zone))
  ->  Bitmap Heap Scan on unitstat_y2010m11 unitstat  
(cost=58

Re: [PERFORM] high user cpu, massive SELECTs, no io waiting problem

[marcin mank]

On Tue, Feb 15, 2011 at 6:19 PM, Thomas Pöhler
 wrote:
> Hi list,
>
> See ganglia: http://dl.dropbox.com/u/183323/CPUloadprobsdb1.jpg
>

What is the bottom graph? queries/minute? Looks like Your database is
just getting hammered.
Maybe there is a really badly coded page somewhere (a query for each
user or something similar)?

Greetings
Marcin Mańk

-- 
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] LIMIT on partitioned-table!?

[Marti Raudsepp]

On Tue, Feb 15, 2011 at 21:33, Kim A. Brandt  wrote:
> removing the ORDER BY worked. But I am afraid to ask this. How can I order
> by partition? It seams that the planner has picked a random(!?) order of
> partition to select from. The returned records, from the selected partition,
> are correctly sorted bythe index though.

If a single query accesses more than one partition, PostgreSQL
currently cannot read the values in index-sorted order. Hence with
ORDER BY and LIMIT, PostgreSQL cannot return *any* results before it
has read all matching rows and then sorted them. Adding a LIMIT
doesn't help much. Your only bet is to reduce the number of matched
rows, or make sure that you only access a single partition.

Increasing work_mem may speed up the sort step if you're hitting the
disk (EXPLAIN ANALYZE VERBOSE will tell you whether that's the case).

This will change in PostgreSQL 9.1 which has a new Merge Append plan node.

Regards,
Marti

-- 
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 user cpu, massive SELECTs, no io waiting problem

[Ivan Voras]

On 15/02/2011 18:19, Thomas Pöhler wrote:

Hi list,

first time for me here, hope you’re not dealing too severely with me
regarding guidelines. Giving my best.

We are running PostgreSQL 8.4.4 on x86_64-unknown-linux-gnu, compiled by
GCC gcc (Debian 4.3.2-1.1) 4.3.2, 64-bit on a Supermicro SuperServer
8026B-6RF.

This version is downloaded from postgresql.org and selfcompiled, running
for over a year now. The Server has 128 GB RAM and Four Intel® Xeon®
X7550 with 64 logical cores.


So, 64 logical cores total.


Operating System is “Linux database1 2.6.32-bpo.5-amd64 #1 SMP Mon Dec
13 17:10:39 UTC 2010 x86_64 GNU/Linux”.

The System boots through iscsi over a Qlogic QLE4062C HBA. Pgdata and
xlog is logged in over iscsi HBA too. We tried en and disabling jumbo
frames. Makes no difference.


Are you using 10 Gbit/s Ethernet for iSCSI? Regular 1 Gbit/s Ethernet 
might be too slow for you.



Since a few weeks we have really strange peaks on this system. User CPU
is increasing up to 100% and we have lots of SELECTs running.



See ganglia: http://dl.dropbox.com/u/183323/CPUloadprobsdb1.jpg

Has someone made similar experiences? Perhaps there is some issue
between Postgres 8.4.4 and kernel 2.6.32?


From your graph it looks like the number of active processes (I'm 
assuming they are PostgreSQL processes) is going out of control.


There is an old problem (which I've encountered so I'm replying but it 
may or may not be in your case) in which PostgreSQL starts behaving 
badly even for SELECT queries if the number of simultaneous queries 
exceeds the number of logical CPUs. To test this, I'd recommend setting 
up a utility like pgpool-II (http://pgpool.projects.postgresql.org/) in 
front of the database to try and limit the number of active connections 
to nearly 64 (maybe you can have good results with 80 or 100).


You might also experiment with pgsql.max_links setting of PHP but IIRC 
PHP will just refuse more connections than that instead of waiting for 
them (but maybe your application can spin-wait for them, possibly while 
also using usleep()).




--
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 user cpu, massive SELECTs, no io waiting problem

[Scott Marlowe]

On Tue, Feb 15, 2011 at 6:00 PM, Ivan Voras  wrote:
> There is an old problem (which I've encountered so I'm replying but it may
> or may not be in your case) in which PostgreSQL starts behaving badly even
> for SELECT queries if the number of simultaneous queries exceeds the number
> of logical CPUs.

Note that this is a problem for most RDBMS engines, not just
postgresql.  The performance drop off isn't too bad, but the total
number of connections times even a doubling of response time results
in a slow server.

> To test this, I'd recommend setting up a utility like
> pgpool-II (http://pgpool.projects.postgresql.org/) in front of the database
> to try and limit the number of active connections to nearly 64 (maybe you
> can have good results with 80 or 100).

pgpool IS the answer for most of these issues.

> You might also experiment with pgsql.max_links setting of PHP but IIRC PHP
> will just refuse more connections than that instead of waiting for them (but
> maybe your application can spin-wait for them, possibly while also using
> usleep()).

That setting is PER PROCESS so it might not help that much.

http://www.php.net/manual/en/pgsql.configuration.php#ini.pgsql.max-links

-- 
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] LIMIT on partitioned-table!?

[Kim A. Brandt]

Thank you Marti,

I will go with the ``reduced number of matched rows'' and naturally be waiting 
for postgres 9.1 expectantly.


Kind regards,

Kim



On 2011-02-15 22:13, Marti Raudsepp wrote:

On Tue, Feb 15, 2011 at 21:33, Kim A. Brandt  wrote:

removing the ORDER BY worked. But I am afraid to ask this. How can I order
by partition? It seams that the planner has picked a random(!?) order of
partition to select from. The returned records, from the selected partition,
are correctly sorted bythe index though.


If a single query accesses more than one partition, PostgreSQL
currently cannot read the values in index-sorted order. Hence with
ORDER BY and LIMIT, PostgreSQL cannot return *any* results before it
has read all matching rows and then sorted them. Adding a LIMIT
doesn't help much. Your only bet is to reduce the number of matched
rows, or make sure that you only access a single partition.

Increasing work_mem may speed up the sort step if you're hitting the
disk (EXPLAIN ANALYZE VERBOSE will tell you whether that's the case).

This will change in PostgreSQL 9.1 which has a new Merge Append plan node.

Regards,
Marti


--
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 user cpu, massive SELECTs, no io waiting problem

[Greg Smith]

Kevin Grittner wrote:

Could you show your postgresql.conf file, with all comments removed



I just added a sample query to provide the data we always want here 
without people having to edit their config files, by querying 
pg_settings for it, to http://wiki.postgresql.org/wiki/Server_Configuration


I already updated http://wiki.postgresql.org/wiki/SlowQueryQuestions and 
http://wiki.postgresql.org/wiki/Guide_to_reporting_problems to mention 
this too.


--
Greg Smith   2ndQuadrant USg...@2ndquadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books


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