On 27 Listopad 2011, 10:45, Gaëtan Allart wrote:
> Actually, this is because I changed sort_mem to 4 Mb as asked by Robert.
>
> I removed this setting..
This is exactly the reason why it's recommended not to do any changes
until it's reasonably certain what is the caause.
Did increasing the work_
Actually, this is because I changed sort_mem to 4 Mb as asked by Robert.
I removed this setting..
Gaëtan
Le 26/11/11 18:58, « Tomas Vondra » a écrit :
>Dne 26.11.2011 18:08, Gaëtan Allart napsal(a):
>> UhmŠ
>>
>> I'm seeing dozens and dozens of temporary file creations in logs :
>>
>> LOG:
On 26.11.2011 19:08, Tom Lane wrote:
> Tomas Vondra writes:
>> Anyway the output is a bit strange. It's writing a lot of temp files
>> that are significantly smaller (about 3MB) than work_mem (128MB).
>
> The most obvious guess about what's doing that is a hash join that has
> a drastic overestim
Dne 26.11.2011 17:47, Gaëtan Allart napsal(a):
> Rahh :/
>
> It's getting worse and worse :/ Database has to be restarted every 2 hours
> causing much traffic loss :/
>
> As far as the server is concerned, it was running great 7 days ago and had
> been running like this for months. I really don't
Tomas Vondra writes:
> Anyway the output is a bit strange. It's writing a lot of temp files
> that are significantly smaller (about 3MB) than work_mem (128MB).
The most obvious guess about what's doing that is a hash join that has
a drastic overestimate of how many rows it has to hash, so that it
Dne 26.11.2011 18:08, Gaëtan Allart napsal(a):
> Uhm…
>
> I'm seeing dozens and dozens of temporary file creations in logs :
>
> LOG: temporary file: path "base/pgsql_tmp/pgsql_tmp15388.1399", size 23912
> LOG: temporary file: path "base/pgsql_tmp/pgsql_tmp15388.211", size
> 2761788
> LOG: tem
Uhm…
I'm seeing dozens and dozens of temporary file creations in logs :
LOG: temporary file: path "base/pgsql_tmp/pgsql_tmp15388.1425", size 25340
LOG: temporary file: path "base/pgsql_tmp/pgsql_tmp15388.195", size
2720340
LOG: temporary file: path "base/pgsql_tmp/pgsql_tmp13772.3495", size 24
Rahh :/
It's getting worse and worse :/ Database has to be restarted every 2 hours
causing much traffic loss :/
As far as the server is concerned, it was running great 7 days ago and had
been running like this for months. I really don't get why it suddenly went
"I/oing"Š
Here's the current post
Hello Robert,
I'm having around 30 active connections (average).
As far as disk layout is concerned, ever thing's on the same disk (raid 1
with 2 SSDs).
Gaëtan
Le 26/11/11 15:25, « Robert Treat » a écrit :
>On Fri, Nov 25, 2011 at 6:48 PM, Gaëtan Allart wrote:
>> Here are the latest checkpoin
On Fri, Nov 25, 2011 at 6:48 PM, Gaëtan Allart wrote:
> Here are the latest checkpoint logs :
>
> LOG: checkpoint complete: wrote 842 buffers (0.1%); 0 transaction log
> file(s) added, 0 removed, 0 recycled; write=168.970 s, sync=0.005 s,
> total=168.977 s
> LOG: checkpoint starting: time
> LOG:
On 26 Listopad 2011, 10:45, Gaëtan Allart wrote:
> A better view of iotop :
>
> TID PRIO USER DISK READ DISK WRITE SWAPIN IOCOMMAND
> 31875 be/4 postgres0.00 B/s 15.23 M/s 0.00 % 0.00 % postgres:
> database database 46.105.104.205(50228) SELECT
> 30985 be/4 postgres0.
A better view of iotop :
TID PRIO USER DISK READ DISK WRITE SWAPIN IOCOMMAND
16974 be/4 postgres 46.33 K/s0.00 B/s 0.00 % 7.21 % postgres:
database database 46.105.111.92(54930) idle
383 be/4 postgres7.72 K/s0.00 B/s 0.00 % 1.56 % postgres:
database database
Dne 26.11.2011 00:17, Cédric Villemain napsal(a):
> Le 25 novembre 2011 23:47, Gaëtan Allart a écrit :
>> Hello Tomas and Cédric,
>>
>> Right now, the server is not all right. Load is above 30 and queries are
>> slow like hell.
>>
>>
>> Here's the complete iotop. Note the 71 MB/s writes (apparentl
Here are the latest checkpoint logs :
LOG: checkpoint complete: wrote 842 buffers (0.1%); 0 transaction log
file(s) added, 0 removed, 0 recycled; write=168.970 s, sync=0.005 s,
total=168.977 s
LOG: checkpoint starting: time
LOG: checkpoint complete: wrote 318 buffers (0.0%); 0 transaction log
f
Le 25 novembre 2011 23:47, Gaëtan Allart a écrit :
> Hello Tomas and Cédric,
>
> Right now, the server is not all right. Load is above 30 and queries are
> slow like hell.
>
>
> Here's the complete iotop. Note the 71 MB/s writes (apparently on SELECT
> queries).
>
> Total DISK READ: 633.35 K/s | T
Hello Tomas and Cédric,
Right now, the server is not all right. Load is above 30 and queries are
slow like hell.
Here's the complete iotop. Note the 71 MB/s writes (apparently on SELECT
queries).
Total DISK READ: 633.35 K/s | Total DISK WRITE: 71.06 M/s
TID PRIO USER DISK READ DISK WRI
On 25 Listopad 2011, 12:43, Cédric Villemain wrote:
> Le 25 novembre 2011 11:25, Tomas Vondra a écrit :
>> On 24 Listopad 2011, 23:19, Cédric Villemain wrote:
>>>
>>> It seem you have an issue with your checkpoint syncing time, it is
>>> fixed in 9.1 and backported in 9.0 here :
>>> http://project
Le 25 novembre 2011 11:25, Tomas Vondra a écrit :
> On 24 Listopad 2011, 23:19, Cédric Villemain wrote:
>> Le 24 novembre 2011 20:38, Gaëtan Allart a écrit :
>>> Finally, it "crashed" againŠ :-(
>>>
>>> Here's the output of iotop while databased was inaccessible :
>>>
>>> 32361 be/4 postgres 0
On 24 Listopad 2011, 23:19, Cédric Villemain wrote:
> Le 24 novembre 2011 20:38, Gaëtan Allart a écrit :
>> Finally, it "crashed" againŠ :-(
>>
>> Here's the output of iotop while databased was inaccessible :
>>
>> 32361 be/4 postgres 0.00 B/s 0.00 B/s 0.00 % 99.99 % postgres:
>> mydb
>> my
Le 24 novembre 2011 20:38, Gaëtan Allart a écrit :
> Finally, it "crashed" againŠ :-(
>
> Here's the output of iotop while databased was inaccessible :
>
> 32361 be/4 postgres 0.00 B/s 0.00 B/s 0.00 % 99.99 % postgres: mydb
> mydb host(34847) idle
> 32244 be/4 postgres 163.48 K/s 0.00 B
Finally, it "crashed" againŠ :-(
Here's the output of iotop while databased was inaccessible :
32361 be/4 postgres0.00 B/s0.00 B/s 0.00 % 99.99 % postgres: mydb
mydb host(34847) idle
32244 be/4 postgres 163.48 K/s0.00 B/s 0.00 % 99.99 % postgres: mydb
mydb host(34660) SELECT
32045
Le 24 novembre 2011 17:02, Tomas Vondra a écrit :
> On 24 Listopad 2011, 16:39, Robert Treat wrote:
>> On Thu, Nov 24, 2011 at 9:09 AM, Tomas Vondra wrote:
>>> On 24 Listopad 2011, 14:51, Gaëtan Allart wrote:
Postgresql.conf :
max_connections = 50
shared_buffers = 12G
tem
Tomas,
I've enabled logging of checkpoints.
I'm waiting for the next i/o crisisŠ
Gaëtan
Le 24/11/11 17:02, « Tomas Vondra » a écrit :
>On 24 Listopad 2011, 16:39, Robert Treat wrote:
>> On Thu, Nov 24, 2011 at 9:09 AM, Tomas Vondra wrote:
>>> On 24 Listopad 2011, 14:51, Gaëtan Allart wrot
Hi Robert,
Thanks for your help as well.
You're right about checkpoints, it's running pretty good at start then
encounter heavy i/os.
I've changed theses settings and also reduced work_mem a little and
reduced effective_cache_size btw.
LOG: parameter "work_mem" changed to "96MB"
LOG: parameter
On 24 Listopad 2011, 16:39, Robert Treat wrote:
> On Thu, Nov 24, 2011 at 9:09 AM, Tomas Vondra wrote:
>> On 24 Listopad 2011, 14:51, Gaëtan Allart wrote:
>>> Postgresql.conf :
>>>
>>> max_connections = 50
>>> shared_buffers = 12G
>>> temp_buffers = 40MB
>>> work_mem = 128MB
>>> maintenance_work_m
On Thu, Nov 24, 2011 at 9:09 AM, Tomas Vondra wrote:
> On 24 Listopad 2011, 14:51, Gaëtan Allart wrote:
>> Hello everyone,
>>
>> I'm having some troubles with a Postgresql server.
>> We're using PG has a database backend for a very big website (lots of data
>> and much traffic).
>>
>> The issue :
On 24 Listopad 2011, 15:27, Gaëtan Allart wrote:
> Hi Thomas,
>
> I will be using iotop ;)
> Right now, most i/o come from "postgres: wal writer process".
What do you mean by "most I/O" - how much data is it writing? Is there a
vacuum running at the same time? What other processes are doing I/O?
Hi Thomas,
I will be using iotop ;)
Right now, most i/o come from "postgres: wal writer process".
-> effective_cache_size
Okay, I'll rise it to 32Gb.
-> fsync : changed to on ;)
-> seq_pages : i'll run tests. Thanks.
-> dirty :
cat /proc/sys/vm/dirty_ratio
20
cat /proc/sys/vm/dirty_backgrou
On 24 Listopad 2011, 14:51, Gaëtan Allart wrote:
> Hello everyone,
>
> I'm having some troubles with a Postgresql server.
> We're using PG has a database backend for a very big website (lots of data
> and much traffic).
>
> The issue : server suddenly (1H after restart) becomes slow (queries not
>
Hello everyone,
I'm having some troubles with a Postgresql server.
We're using PG has a database backend for a very big website (lots of data
and much traffic).
The issue : server suddenly (1H after restart) becomes slow (queries not
responding), load rises (>20 instead of 1), iowait rises (20 to
30 matches
Mail list logo
