On Wed, Feb 20, 2013 at 3:44 PM, Josh Berkus j...@agliodbs.com wrote:
On 02/19/2013 07:15 PM, Scott Marlowe wrote:
On Tue, Feb 19, 2013 at 4:24 PM, Josh Berkus j...@agliodbs.com wrote:
... then you'll see checkpoint stalls and spread checkpoint will
actually make them worse by making the
From: Josh Berkus j...@agliodbs.com
To: Scott Marlowe scott.marl...@gmail.com
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 Fri, Feb 15, 2013 at 10:52 AM, Scott Marlowe scott.marl...@gmail.comwrote:
On Fri, Feb 15, 2013 at 11:26 AM, Josh Berkus j...@agliodbs.com 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
On Tue, Feb 26, 2013 at 2:30 PM, Jeff Janes jeff.ja...@gmail.com wrote:
On Fri, Feb 15, 2013 at 10:52 AM, Scott Marlowe scott.marl...@gmail.com
wrote:
On Fri, Feb 15, 2013 at 11:26 AM, Josh Berkus j...@agliodbs.com wrote:
On 02/14/2013 08:47 PM, Scott Marlowe wrote:
If you run your
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 any
On 02/19/2013 07:15 PM, Scott Marlowe wrote:
On Tue, Feb 19, 2013 at 4:24 PM, Josh Berkus j...@agliodbs.com 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
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 running
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
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
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 settings you'd
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
On Tue, Feb 19, 2013 at 4:24 PM, Josh Berkus j...@agliodbs.com 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
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.
--
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
On Mon, Feb 18, 2013 at 6:39 PM, Josh Berkus j...@agliodbs.com 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
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)
--
On Mon, Feb 18, 2013 at 5:39 PM, Josh Berkus j...@agliodbs.com 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
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
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,
On Fri, Feb 15, 2013 at 11:26 AM, Josh Berkus j...@agliodbs.com 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
, 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 d...@iqtell.com wrote:
Hello,
We upgraded from Ubuntu 11.04 to Ubuntu 12.04 and almost immediately
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
.
-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
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 there
...@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 used
-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
On 02/14/2013 12:41 PM, Dan
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 our
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 vs 3.2 and
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
(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
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 moment so can't dig
On Tue, Jan 22, 2013 at 1:41 AM, Andrew Dunstan and...@dunslane.net 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
On Tue, Jan 22, 2013 at 3:04 PM, Merlin Moncure mmonc...@gmail.com 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
On 01/22/2013 09:21 AM, rudi wrote:
On Tue, Jan 22, 2013 at 3:04 PM, Merlin Moncure mmonc...@gmail.com
mailto:mmonc...@gmail.com 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
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=5
Andrew Dunstan and...@dunslane.net 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
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
On Mon, Jan 21, 2013 at 9:05 AM, rudi rudol...@gmail.com 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
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
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
On Thu, Jun 14, 2012 at 11:15 AM, Siddharth Shah
siddharth.s...@elitecore.com 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
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
?
- Original Message -
From: Jeff Frost [EMAIL PROTECTED]
To: Neil Hepworth [EMAIL PROTECTED]
Cc: pgsql-performance@postgresql.org
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:
I am using version
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
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 set
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. delete ..
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
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
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 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
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!
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
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
53 matches
Mail list logo