Re: [PERFORM] High CPU usage buy low I/O wait

On Thu, Apr 11, 2013 at 04:30:54PM -0700, bing1221 wrote: > Our server is running postgresql 8.4.15. During day time the cpu usage always > around 80%, but it's not IO bound. The swap space is looking OK also. Also > we setup pgbadger and enable all logs to monitor the slow query but they all > fin

[PERFORM] High CPU usage buy low I/O wait

Our server is running postgresql 8.4.15. During day time the cpu usage always around 80%, but it's not IO bound. The swap space is looking OK also. Also we setup pgbadger and enable all logs to monitor the slow query but they all finished quick. Usually it has 60 incoming connections, and we have p

Re: [PERFORM] High CPU usage / load average after upgrading to Ubuntu 12.04

> From: Josh Berkus >To: Scott Marlowe >Cc: pgsql-performance@postgresql.org >Sent: Thursday, 21 February 2013, 3:14 >Subject: Re: [PERFORM] High CPU usage / load average after upgrading to Ubuntu >12.04 > > >> Sounds to me like your IO system is stalling on fs

Re: [PERFORM] High CPU usage / load average after upgrading to Ubuntu 12.04

On Wed, Feb 20, 2013 at 3:44 PM, Josh Berkus wrote: > On 02/19/2013 07:15 PM, Scott Marlowe wrote: >> On Tue, Feb 19, 2013 at 4:24 PM, Josh Berkus wrote: >>> ... then you'll see checkpoint "stalls" and spread checkpoint will >>> actually make them worse by making the stalls longer. >> >> Wait, if

Re: [PERFORM] High CPU usage / load average after upgrading to Ubuntu 12.04

On Tue, Feb 26, 2013 at 2:30 PM, Jeff Janes wrote: > On Fri, Feb 15, 2013 at 10:52 AM, Scott Marlowe > wrote: >> >> On Fri, Feb 15, 2013 at 11:26 AM, Josh Berkus wrote: >> > On 02/14/2013 08:47 PM, Scott Marlowe wrote: >> >> If you run your benchmarks for more than a few minutes I highly >> >> r

Re: [PERFORM] High CPU usage / load average after upgrading to Ubuntu 12.04

On Fri, Feb 15, 2013 at 10:52 AM, Scott Marlowe wrote: > On Fri, Feb 15, 2013 at 11:26 AM, Josh Berkus wrote: > > On 02/14/2013 08:47 PM, Scott Marlowe wrote: > >> If you run your benchmarks for more than a few minutes I highly > >> recommend enabling sysstat service data collection, then you can

Re: [PERFORM] High CPU usage / load average after upgrading to Ubuntu 12.04

On 02/20/13 19:14, Josh Berkus wrote: >> Sounds to me like your IO system is stalling on fsyncs or something >> like that. On machines with plenty of IO cranking up completion >> target usuall smooths things out. > It certainly seems like it does. However, I can't demonstrate the issue > using a

Re: [PERFORM] High CPU usage / load average after upgrading to Ubuntu 12.04

> Sounds to me like your IO system is stalling on fsyncs or something > like that. On machines with plenty of IO cranking up completion > target usuall smooths things out. It certainly seems like it does. However, I can't demonstrate the issue using any simpler tool than pgbench ... even runni

Re: [PERFORM] High CPU usage / load average after upgrading to Ubuntu 12.04

On 02/19/2013 07:15 PM, Scott Marlowe wrote: > On Tue, Feb 19, 2013 at 4:24 PM, Josh Berkus wrote: >> ... then you'll see checkpoint "stalls" and spread checkpoint will >> actually make them worse by making the stalls longer. > > Wait, if they're spread enough then there won't be a checkpoint, so

Re: [PERFORM] High CPU usage / load average after upgrading to Ubuntu 12.04

On Tue, Feb 19, 2013 at 4:24 PM, Josh Berkus wrote: > ... then you'll see checkpoint "stalls" and spread checkpoint will > actually make them worse by making the stalls longer. Wait, if they're spread enough then there won't be a checkpoint, so to speak. Are you saying that spreading them out me

Re: [PERFORM] High CPU usage / load average after upgrading to Ubuntu 12.04

On 20/02/13 12:24, Josh Berkus wrote: NM, I tested lowering dirty_background_ratio, and it didn't help, because checkpoints are kicking in before pdflush ever gets there. So the issue seems to be that if you have this combination of factors: 1. large RAM 2. many/fast CPUs 3. a database which f

Re: [PERFORM] High CPU usage / load average after upgrading to Ubuntu 12.04

On 02/19/2013 09:51 AM, Josh Berkus wrote: > On 02/18/2013 08:28 PM, Mark Kirkwood wrote: >> Might be worth looking at your vm.dirty_ratio, vm.dirty_background_ratio >> and friends settings. We managed to choke up a system with 16x SSD by >> leaving them at their defaults... > > Yeah? Any setting

Re: [PERFORM] High CPU usage / load average after upgrading to Ubuntu 12.04

On 20/02/13 06:51, Josh Berkus wrote: On 02/18/2013 08:28 PM, Mark Kirkwood wrote: Might be worth looking at your vm.dirty_ratio, vm.dirty_background_ratio and friends settings. We managed to choke up a system with 16x SSD by leaving them at their defaults... Yeah? Any settings you'd recommend

Re: [PERFORM] High CPU usage / load average after upgrading to Ubuntu 12.04

On 02/18/2013 08:28 PM, Mark Kirkwood wrote: > Might be worth looking at your vm.dirty_ratio, vm.dirty_background_ratio > and friends settings. We managed to choke up a system with 16x SSD by > leaving them at their defaults... Yeah? Any settings you'd recommend specifically? What did you use on

Re: [PERFORM] High CPU usage / load average after upgrading to Ubuntu 12.04

On 19/02/13 13:39, Josh Berkus wrote: Scott, So do you have generally slow IO, or is it fsync behavior etc? All tests except pgBench show this system as superfast. Bonnie++ and DD tests are good (200 to 300mb/s), and test_fsync shows 14K/second. Basically it has no issues until checkpoint kic

Re: [PERFORM] High CPU usage / load average after upgrading to Ubuntu 12.04

On Mon, Feb 18, 2013 at 5:39 PM, Josh Berkus wrote: > Scott, > >> So do you have generally slow IO, or is it fsync behavior etc? > > All tests except pgBench show this system as superfast. Bonnie++ and DD > tests are good (200 to 300mb/s), and test_fsync shows 14K/second. > Basically it has no is

Re: [PERFORM] High CPU usage / load average after upgrading to Ubuntu 12.04

> Did you try turning barriers on or off *manually* (explicitly)? With > LSI and barriers *on* and ext4 I had less-optimal performance. With > Linux MD or (some) 3Ware configurations I had no performance hit. They're off in fstab. /dev/sdd1 on /data type xfs (rw,noatime,nodiratime,nobarrier) -

Re: [PERFORM] High CPU usage / load average after upgrading to Ubuntu 12.04

On Mon, Feb 18, 2013 at 6:39 PM, Josh Berkus wrote: > Scott, > >> So do you have generally slow IO, or is it fsync behavior etc? > > All tests except pgBench show this system as superfast. Bonnie++ and DD > tests are good (200 to 300mb/s), and test_fsync shows 14K/second. > Basically it has no is

Re: [PERFORM] High CPU usage / load average after upgrading to Ubuntu 12.04

Scott, > So do you have generally slow IO, or is it fsync behavior etc? All tests except pgBench show this system as superfast. Bonnie++ and DD tests are good (200 to 300mb/s), and test_fsync shows 14K/second. Basically it has no issues until checkpoint kicks in, at which time the entire system

Re: [PERFORM] High CPU usage / load average after upgrading to Ubuntu 12.04

So, our drop in performance is now clearly due to pathological OS behavior during checkpoints. Still trying to pin down what's going on, but it's not system load; it's clearly related to the IO system. Anyone else see this? I'm getting it both on 3.2 and 3.4. We're using LSI Megaraid. -- Jos

Re: [PERFORM] High CPU usage / load average after upgrading to Ubuntu 12.04

On Fri, Feb 15, 2013 at 11:26 AM, Josh Berkus wrote: > On 02/14/2013 08:47 PM, Scott Marlowe wrote: >> If you run your benchmarks for more than a few minutes I highly >> recommend enabling sysstat service data collection, then you can look >> at it after the fact with sar. VERY useful stuff both

Re: [PERFORM] High CPU usage / load average after upgrading to Ubuntu 12.04

On 02/14/2013 08:47 PM, Scott Marlowe wrote: > If you run your benchmarks for more than a few minutes I highly > recommend enabling sysstat service data collection, then you can look > at it after the fact with sar. VERY useful stuff both for > benchmarking and post mortem on live servers. Well,

Re: [PERFORM] High CPU usage / load average after upgrading to Ubuntu 12.04

pgsql-performance-ow...@postgresql.org > [mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of Josh Berkus > Sent: Thursday, February 14, 2013 6:58 PM > To: pgsql-performance@postgresql.org > Subject: Re: [PERFORM] High CPU usage / load average after upgrading to > Ubuntu 12.04 &

Re: [PERFORM] High CPU usage / load average after upgrading to Ubuntu 12.04

...@postgresql.org [mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of Josh Berkus Sent: Thursday, February 14, 2013 6:58 PM To: pgsql-performance@postgresql.org Subject: Re: [PERFORM] High CPU usage / load average after upgrading to Ubuntu 12.04 On 02/14/2013 12:41 PM, Dan Kogan wrote: > We u

Re: [PERFORM] High CPU usage / load average after upgrading to Ubuntu 12.04

On 02/14/2013 12:41 PM, Dan Kogan wrote: > We used scale factor of 3600. > Yeah, maybe other people see similar load average, we were not sure. > However, we saw a clear difference right after the upgrade. > We are trying to determine whether it makes sense for us to go to 11.04 or > maybe the

Re: [PERFORM] High CPU usage / load average after upgrading to Ubuntu 12.04

. -Original Message- From: pgsql-performance-ow...@postgresql.org [mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of Josh Berkus Sent: Thursday, February 14, 2013 1:38 PM To: pgsql-performance@postgresql.org Subject: Re: [PERFORM] High CPU usage / load average after upgrading to Ubuntu 12.04

Re: [PERFORM] High CPU usage / load average after upgrading to Ubuntu 12.04

On 02/13/2013 05:30 PM, Dan Kogan wrote: > Just to be clear - I was describing the current situation in our production. > > We were running pgbench on different Ununtu versions today. I don’t have > 12.04 setup at the moment, but I do have 12.10, which seems to be performing > about the same as

Re: [PERFORM] High CPU usage / load average after upgrading to Ubuntu 12.04

, 2013 9:08 AM To: Dan Kogan Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] High CPU usage / load average after upgrading to Ubuntu 12.04 On Tue, Feb 12, 2013 at 11:25 AM, Dan Kogan wrote: > Hello, > > > > We upgraded from Ubuntu 11.04 to Ubuntu 12.04 and almost immediate

Re: [PERFORM] High CPU usage / load average after upgrading to Ubuntu 12.04

On Tue, Feb 12, 2013 at 11:25 AM, Dan Kogan wrote: > Hello, > > > > We upgraded from Ubuntu 11.04 to Ubuntu 12.04 and almost immediately > obeserved increased CPU usage and significantly higher load average on our > database server. > > At the time we were on Postgres 9.0.5. We decided to upgrade

Re: [PERFORM] High CPU usage / load average after upgrading to Ubuntu 12.04

Of Josh Berkus Sent: Wednesday, February 13, 2013 7:26 PM To: pgsql-performance@postgresql.org Subject: Re: [PERFORM] High CPU usage / load average after upgrading to Ubuntu 12.04 On 02/13/2013 11:24 AM, Josh Berkus wrote: > On 02/12/2013 05:28 PM, Dan Kogan wrote: >> Hi Will, >>

Re: [PERFORM] High CPU usage / load average after upgrading to Ubuntu 12.04

On 02/13/2013 11:24 AM, Josh Berkus wrote: > On 02/12/2013 05:28 PM, Dan Kogan wrote: >> Hi Will, >> >> Yes, I think we've seen some discussions on that. Our servers our hosted on >> Amazon Ec2 and upgrading the kernel does not seem so straight forward. >> We did a benchmark using pgbench on 3.5

Re: [PERFORM] High CPU usage / load average after upgrading to Ubuntu 12.04

On 02/12/2013 05:28 PM, Dan Kogan wrote: > Hi Will, > > Yes, I think we've seen some discussions on that. Our servers our hosted on > Amazon Ec2 and upgrading the kernel does not seem so straight forward. > We did a benchmark using pgbench on 3.5 vs 3.2 and saw an improvement. > Unfortunately

Re: [PERFORM] High CPU usage / load average after upgrading to Ubuntu 12.04

y 12, 2013 5:20 PM To: Dan Kogan; pgsql-performance@postgresql.org Subject: Re: [PERFORM] High CPU usage / load average after upgrading to Ubuntu 12.04 Hey Dan, If I recall correctly there were some discussions on here related to performance issues with the 3.2 kernel. I'm away at the mome

Re: [PERFORM] High CPU usage / load average after upgrading to Ubuntu 12.04

(or even tried) with the 9.0 jdbc driver against 9.2 server? Dan From: Eric Haertel [mailto:eric.haer...@groupon.com] Sent: Tuesday, February 12, 2013 12:52 PM To: Dan Kogan Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] High CPU usage / load average after upgrading to Ubuntu 12.04

[PERFORM] High CPU usage / load average after upgrading to Ubuntu 12.04

Hello, We upgraded from Ubuntu 11.04 to Ubuntu 12.04 and almost immediately obeserved increased CPU usage and significantly higher load average on our database server. At the time we were on Postgres 9.0.5. We decided to upgrade to Postgres 9.2 to see if that resolves the issue, but unfortunat

Re: [PERFORM] High CPU usage after partitioning

Andrew Dunstan writes: > On 01/22/2013 09:21 AM, rudi wrote: >> The query is pretty simple and standard, the behaviour (and the plan) >> is totally different when it comes to a partitioned table. >> >> Partioned table query => explain analyze SELECT "sb_logs".* FROM >> "sb_logs" WHERE (device

Re: [PERFORM] High CPU usage after partitioning

In PG 9.2 I’m getting “Index Only Scan Backward” for every partition in the first part of execution plan, when looking for MAX in partitioned table on a similar query: "-> Index Only Scan Backward using pk_cycle_200610 on gp_cycle_200610 gp_cycle (cost=0.00..8.34 rows=

Re: [PERFORM] High CPU usage after partitioning

On Tue, Jan 22, 2013 at 3:46 PM, Andrew Dunstan wrote: > The query is pretty simple and standard, the behaviour (and the plan) is > totally different when it comes to a partitioned table. > >> >> Partioned table query => explain analyze SELECT "sb_logs".* FROM >> "sb_logs" WHERE (device_id = 90

Re: [PERFORM] High CPU usage after partitioning

On 01/22/2013 09:21 AM, rudi wrote: On Tue, Jan 22, 2013 at 3:04 PM, Merlin Moncure > wrote: let's see the query -- it's probably written in such a way so as to not be able to be optimized through CE. The query is pretty simple and standard, the behaviour (a

Re: [PERFORM] High CPU usage after partitioning

On Tue, Jan 22, 2013 at 3:04 PM, Merlin Moncure wrote: > let's see the query -- it's probably written in such a way so as to > not be able to be optimized through CE. > > The query is pretty simple and standard, the behaviour (and the plan) is totally different when it comes to a partitioned tabl

Re: [PERFORM] High CPU usage after partitioning

On Tue, Jan 22, 2013 at 7:34 AM, rudi wrote: > Every query has been carefully optimized, child tables are indexed. The > table(s) has a UNIQUE index on ("date_taken", "device_id") and "date_taken" > is the partitioning column (one partition per year). > There are few well known access path to this

Re: [PERFORM] High CPU usage after partitioning

On Tue, Jan 22, 2013 at 1:41 AM, Andrew Dunstan wrote: > > On 01/21/2013 10:05 AM, rudi wrote: > >> Hello, >> >> I'm running postgresl 9.0. After partitioning a big table, CPU usage >> raised from average 5-10% to average 70-80%. >> >> - the table contains about 20.000.000 rows >> - partitions ar

Re: [PERFORM] High CPU usage after partitioning

On 01/21/2013 10:05 AM, rudi wrote: Hello, I'm running postgresl 9.0. After partitioning a big table, CPU usage raised from average 5-10% to average 70-80%. - the table contains about 20.000.000 rows - partitions are selected using a trigger, based on an indexed field, a date (IF date_taken

Re: [PERFORM] High CPU usage after partitioning

Merlin Moncure wrote: >> I'm running postgresl 9.0. After partitioning a big table, CPU >> usage raised from average 5-10% to average 70-80%. > First thing that jumps to mind is you have some seq-scan heavy > plans that were not seq-scan before. Make sure that all indexes are defined for each pa

Re: [PERFORM] High CPU usage after partitioning

On Mon, Jan 21, 2013 at 9:05 AM, rudi wrote: > Hello, > > I'm running postgresl 9.0. After partitioning a big table, CPU usage raised > from average 5-10% to average 70-80%. > > - the table contains about 20.000.000 rows > - partitions are selected using a trigger, based on an indexed field, a dat

[PERFORM] High CPU usage after partitioning

Hello, I'm running postgresl 9.0. After partitioning a big table, CPU usage raised from average 5-10% to average 70-80%. - the table contains about 20.000.000 rows - partitions are selected using a trigger, based on an indexed field, a date (IF date_taken >= x AND date_taken < y) - I created 5 pa

Re: [PERFORM] High CPU Usage

On Thu, Jun 14, 2012 at 11:15 AM, Siddharth Shah wrote: > I have around 1000 schema in database, Each schema having similar data > structure with different data > Each schema has few tables which never updates (Read only table) and other > tables rewrites almost everyday so I prefer to TRUNCATE th

[PERFORM] High CPU Usage

Hello, I have around 1000 schema in database, Each schema having similar data structure with different data Each schema has few tables which never updates (Read only table) and other tables rewrites almost everyday so I prefer to TRUNCATE those tables and restores with new data Now facing is

Re: [PERFORM] High CPU Usage - PostgreSQL 7.3

On Wed, 12 Jul 2006, Neil Hepworth wrote: Yes, it was the same DB so, yes 8.1 gives roughly a four fold improvement (assuming hardware and OS differences aren't that significant - I'd expect the Linux version to be faster if anything); which certainly ain't bad! :) Good idea for the vacuumdb -a

Re: [PERFORM] High CPU Usage - PostgreSQL 7.3

ew version ? - Original Message - From: "Jeff Frost" <[EMAIL PROTECTED]> To: "Neil Hepworth" <[EMAIL PROTECTED]> Cc: Sent: Wednesday, July 12, 2006 10:27 AM Subject: Re: [PERFORM] High CPU Usage - PostgreSQL 7.3 On Wed, 12 Jul 2006, Neil Hepworth wrote:

Re: [PERFORM] High CPU Usage - PostgreSQL 7.3

Yes, it was the same DB so, yes 8.1 gives roughly a four fold improvement (assuming hardware and OS differences aren't that significant - I'd expect the Linux version to be faster if anything); which certainly ain't bad! :)   Good idea for the vacuumdb -a -v on the laptop, I re imported the databa

Re: [PERFORM] High CPU Usage - PostgreSQL 7.3

On Tue, 11 Jul 2006, Jeff Frost wrote: On Wed, 12 Jul 2006, Neil Hepworth wrote: You might also want to turn on autovacuum and see if that helps. What's your disk subsystem like? In fact, what's the entire DB server hardware like? By the way, how big does the temp table get? If it's large

Re: [PERFORM] High CPU Usage - PostgreSQL 7.3

On Wed, 12 Jul 2006, Neil Hepworth wrote: I am using version PostgreSQL 7.3.10 (RPM: postgresql73-rhel21-7.3.10-2). Unfortunately vacuumdb -a -v does not give the FSM info at the end (need a newer version of postgres for that). Running the same queries on 8.1 reduces the time taken to about 16

Re: [PERFORM] High CPU Usage - PostgreSQL 7.3

Thanks for the tip; I'll try that when we initially upgrade, hopefully soon.   Neil  On 12/07/06, Bruno Wolff III <[EMAIL PROTECTED]> wrote: On Mon, Jul 10, 2006 at 17:55:38 +1000,Neil Hepworth <[EMAIL PROTECTED] > wrote:>> running on our server (obviously we need to update certain queries,> e.g. d

Re: [PERFORM] High CPU Usage - PostgreSQL 7.3

On Mon, Jul 10, 2006 at 17:55:38 +1000, Neil Hepworth <[EMAIL PROTECTED]> wrote: > > running on our server (obviously we need to update certain queries, > e.g. delete .. using.. and test with 8.1 first) - I will be pushing > for an upgrade as soon as possible. And the fsync=false is a You can

Re: [PERFORM] High CPU Usage - PostgreSQL 7.3

On Sun, 2006-07-09 at 19:52, Neil Hepworth wrote: > Hi, > > I am running PostgreSQL 7.3 on a Linux box (RHEL 2.1 - Xeon 2.8GHz > with 1GB of RAM) and seeing very high CPU usage (normally over 90%) > when I am running the following queries, and the queries take a long > time to return; over an hour

Re: [PERFORM] High CPU Usage - PostgreSQL 7.3

On Mon, 10 Jul 2006, Neil Hepworth wrote: I should also explain that I run through these queries on multiple tables and with some slightly different parameters for the "consolidation" so I run through those 3 queries (or similar) 9 times and this takes a total of about 2 hours, with high CPU u

Re: [PERFORM] High CPU Usage - PostgreSQL 7.3

I should also explain that I run through these queries on multiple tables and with some slightly different parameters for the "consolidation" so I run through those 3 queries (or similar) 9 times and this takes a total of about 2 hours, with high CPU usage. And I am running the queries from a rem

Re: [PERFORM] High CPU Usage - PostgreSQL 7.3

Thanks for the reply. The database is vacuum analysed regularly and during my testing I tried running the vacuum analyse full immediately before the running through the set of queries (which does help a bit - reduces the time to about 80% but is is still over an hour, with basically 100% CPU). I

Re: [PERFORM] High CPU Usage - PostgreSQL 7.3

On Mon, 10 Jul 2006, Neil Hepworth wrote: I am running PostgreSQL 7.3 on a Linux box (RHEL 2.1 - Xeon 2.8GHz with 1GB of RAM) and seeing very high CPU usage (normally over 90%) when I am running the following queries, and the queries take a long time to return; over an hour! First off, when is

[PERFORM] High CPU Usage - PostgreSQL 7.3

Hi, I am running PostgreSQL 7.3 on a Linux box (RHEL 2.1 - Xeon 2.8GHz with 1GB of RAM) and seeing very high CPU usage (normally over 90%) when I am running the following queries, and the queries take a long time to return; over an hour! CREATE TEMPORARY TABLE fttemp1600384653 AS SELECT * FROM f