Based on my initial hunch that something resulting from all the ALTERS was
making PostgreSQL planner end up with bad plans, I tried a pg_dump and
pg_restore. Now the 'bad' query comes back in 70 seconds (compared to 20
minutes earlier) and the rewritten query still comes back in 2 seconds. So
we wi
On Mon, Sep 5, 2011 at 4:36 PM, Richard Shaw wrote:
> Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz
> avgqu-sz await svctm %util
> sda 1.00 143.00 523.50 108.00 8364.00 2008.00 16.42
> 2.78 4.41 1.56 98.35
> sda1 0.00 0
vmstat 1 and iostat -x output
Normal
procs ---memory-- ---swap-- -io --system--
-cpu--
r b swpd free buff cache si sobibo in cs us sy id wa st
3 0 2332 442428 73904 3128734400894200 7 5 85 3
0
4 1 2
On 5 Září 2011, 21:07, Andy Colson wrote:
> On 09/05/2011 01:45 PM, Scott Marlowe wrote:
>> On Mon, Sep 5, 2011 at 8:08 AM, Gerhard Wohlgenannt
>> wrote:
>>> Below please find the results of vmstat 2 over some periode of time ..
>>> with
>>> normal database / system load.
>>>
> 2 1 1344204 240924
On Mon, Sep 5, 2011 at 11:24 AM, Andres Freund wrote:
> On Monday, September 05, 2011 14:57:43 Richard Shaw wrote:
>> Autovacuum has been disabled and set to run manually via cron during a quiet
>> period and fsync has recently been turned off to gauge any real world
>> performance increase, there
On September 5, 2011, Richard Shaw wrote:
> Hi Andy,
>
> It's not a new issue no, It's a legacy system that is in no way ideal but
> is also not in a position to be overhauled. Indexes are correct, tables
> are up to 25 million rows.
>
> On startup, it hits CPU more than IO, I'll provide some a
On 09/05/2011 01:45 PM, Scott Marlowe wrote:
On Mon, Sep 5, 2011 at 8:08 AM, Gerhard Wohlgenannt wrote:
Below please find the results of vmstat 2 over some periode of time .. with
normal database / system load.
2 1 1344204 240924 104156 31462484 3500 1906 234 3687 4512 12 3 77 9
On Mon, Sep 5, 2011 at 8:08 AM, Gerhard Wohlgenannt wrote:
> Below please find the results of vmstat 2 over some periode of time .. with
> normal database / system load.
>
> 0 0 1344332 237196 104140 31468412 0 0 330 102 4322 7130 4 2
> 90 4
> 1 1 1344332 236708 104144 31469000
On Monday, September 05, 2011 14:57:43 Richard Shaw wrote:
> Autovacuum has been disabled and set to run manually via cron during a quiet
> period and fsync has recently been turned off to gauge any real world
> performance increase, there is battery backup on the raid card providing
> some level o
On 09/05/2011 08:57 AM, Richard Shaw wrote:
Hi Andy,
It's not a new issue no, It's a legacy system that is in no way ideal but is
also not in a position to be overhauled. Indexes are correct, tables are up to
25 million rows.
On startup, it hits CPU more than IO, I'll provide some additiona
hi,
thanks a lot for your help!
Dear list,
we are encountering serious performance problems with our database.
Queries which took around 100ms or less last week now take several
seconds.
The database runs on Ubuntu Server 10.4.3 (kernel: 2.6.32-33) on
hardware as follows:
8-core Intel Xeon CP
hi,
thanks a lot for your help!
Dear list,
we are encountering serious performance problems with our database.
Queries which took around 100ms or less last week now take several
seconds.
The database runs on Ubuntu Server 10.4.3 (kernel: 2.6.32-33) on
hardware as follows:
8-core Intel Xeon CP
On 09/05/2011 03:51 PM, Andy Colson wrote:
On 09/05/2011 02:48 AM, Tomas Vondra wrote:
On 3 Září 2011, 9:26, Gerhard Wohlgenannt wrote:
Dear list,
we are encountering serious performance problems with our database.
Queries which took around 100ms or less last week now take several
seconds.
On 09/05/2011 09:39 AM, Tomas Vondra wrote:
On 5 Září 2011, 16:08, Gerhard Wohlgenannt wrote:
Below please find the results of vmstat 2 over some periode of time ..
with normal database / system load.
What does a "normal load" mean? Does that mean a time when the queries are
slow?
Are you sur
I think that wal_segments are too low, try 30.
2011/9/5, Andy Colson :
> On 09/05/2011 05:28 AM, Richard Shaw wrote:
>>
>> Hi,
>>
>> I have a database server that's part of a web stack and is experiencing
>> prolonged load average spikes of up to 400+ when the db is restarted and
>> first accessed
On 5 Září 2011, 16:08, Gerhard Wohlgenannt wrote:
> Below please find the results of vmstat 2 over some periode of time ..
> with normal database / system load.
What does a "normal load" mean? Does that mean a time when the queries are
slow?
Are you sure the machine really has 48GB of RAM? Becaus
On 5 Září 2011, 15:51, Andy Colson wrote:
> On 09/05/2011 02:48 AM, Tomas Vondra wrote:
>> That seems a bit slow ... 27MB/s for writes and 41MB/s forreads is ait
>> slow with 8 drives.
>>
>> Tomas
>>
>>
>
> Agreed, that's really slow. A single SATA drive will get 60 MB/s. Did
> you run Bonnie whi
Hi Andy,
It's not a new issue no, It's a legacy system that is in no way ideal but is
also not in a position to be overhauled. Indexes are correct, tables are up to
25 million rows.
On startup, it hits CPU more than IO, I'll provide some additional stats after
I restart it tonight.
Serv
On 09/05/2011 02:48 AM, Tomas Vondra wrote:
On 3 Září 2011, 9:26, Gerhard Wohlgenannt wrote:
Dear list,
we are encountering serious performance problems with our database.
Queries which took around 100ms or less last week now take several
seconds.
Results of Bonnie++
Version 1.96 ---
On 09/05/2011 05:28 AM, Richard Shaw wrote:
Hi,
I have a database server that's part of a web stack and is experiencing
prolonged load average spikes of up to 400+ when the db is restarted and first
accessed by the other parts of the stack and has generally poor performance on
even simple se
On 5/09/2011 6:55 PM, Richard Shaw wrote:
Hi Craig,
Apologies, I should have made that clearer, I am using PgBouncer 1.4.1 in front
of Postgres and included the config at the bottom of my original mail
Ah, I see. The point still stands: your hardware can *not* efficiently
do work for 1000 con
Hi Craig,
Apologies, I should have made that clearer, I am using PgBouncer 1.4.1 in front
of Postgres and included the config at the bottom of my original mail
Regards
Richard
.
On 5 Sep 2011, at 11:49, Craig Ringer wrote:
> On 5/09/2011 6:28 PM, Richard Shaw wrote:
>> max_connecti
On 5/09/2011 6:28 PM, Richard Shaw wrote:
max_connections| 1000
Woah! No wonder you have "stampeding herd" problems after a DB or server
restart and are having performance issues.
When you have 1000 clients trying to do work at once, they'll all be
fighting over memory, di
Hi,
I have a database server that's part of a web stack and is experiencing
prolonged load average spikes of up to 400+ when the db is restarted and first
accessed by the other parts of the stack and has generally poor performance on
even simple select queries.
There are 30 DBs in total on t
On 3/09/2011 8:25 AM, C Pond wrote:
I'm running a labour-intensive series of queries on a medium-sized dataset
(~100,000 rows) with geometry objects and both gist and btree indices.
The queries are embedded in plpgsql, and have multiple updates, inserts and
deletes to the tables as well as mul
Hi all,
Thanks for the replies and sorry for the late response, I have been away
for a few days.
Concerning the performance: 1 ms per row seems slow knowing that the
entire database is less then 64MB and therefore should easily fit into
memory and the client (pgAdmin III) runs on the server.
I a
On 3 Září 2011, 9:26, Gerhard Wohlgenannt wrote:
> Dear list,
>
> we are encountering serious performance problems with our database.
> Queries which took around 100ms or less last week now take several
> seconds.
>
> The database runs on Ubuntu Server 10.4.3 (kernel: 2.6.32-33) on
> hardware as fo
Hi.
Autoexplain module allow to log plans and statistics of live queries. Try it.
2011/9/3, Gerhard Wohlgenannt :
> Dear list,
>
> we are encountering serious performance problems with our database.
> Queries which took around 100ms or less last week now take several seconds.
>
> The database runs
I'm running a labour-intensive series of queries on a medium-sized dataset
(~100,000 rows) with geometry objects and both gist and btree indices.
The queries are embedded in plpgsql, and have multiple updates, inserts and
deletes to the tables as well as multiple selects which require the indice
Dear list,
we are encountering serious performance problems with our database.
Queries which took around 100ms or less last week now take several seconds.
The database runs on Ubuntu Server 10.4.3 (kernel: 2.6.32-33) on
hardware as follows:
8-core Intel Xeon CPU with 2.83GHz
48 GB RAM
RAID 5
30 matches
Mail list logo
