On Feb 12, 2014, at 18:43, =D0=91=D0=BE=D1=80=D0= =BE=D0=B4=D0=B8=D0=BD =D0=92=D0=BB=D0=B0=D0=B4=D0=B8=D0=BC=D0=B8=D1=80 <<= a href=3D"mailto:root@simply.name">root@simply.name> wrote:

root@rpopdb01e ~ # fgrep data /etc/fsta= b
UUID=3Df815fd3f-e4e4-43a6-a6a1-bce1203db3e0 /var/lib/pgsql/9.3/d= ata ext4 noatime,nodiratime 0 1
root@rpopdb01e ~ #

<= /div>
According to iostat the disks are not the bottleneck.
12.02.2014, =D0=B2 21:30, Ilya Kosmodemiansky <hydrobiont@gmail.com> =D0=BD=D0=B0=D0=BF= =D0=B8=D1=81=D0=B0=D0=BB(=D0=B0):

Hi Vladimir,

=
Just in case: how is your ext4 mount?

<= div>Best regards,
Ilya

On Feb 12, 2014, at 17:= 59, =D0=91=D0=BE=D1=80=D0=BE=D0=B4=D0=B8=D0=BD =D0=92=D0=BB=D0=B0=D0=B4=D0=B8= =D0=BC=D0=B8=D1=80 <root@simply.name<= /a>> wrote:

Hi all.

<= div>Today I have started getting errors like below in logs (seems that I hav= e not changed anything for last week). When it happens the db gets lots of c= onnections in state active, eats 100% cpu and clients get errors (due to tim= eout).

2014-02-12 15:44:24.562 MSK,"rpop","rp= opdb_p6",30061,"localhost:58350",52fb5e53.756d,1,"SELECT waiting",2014-02-12= 15:43:15 MSK,143/264877,1002850566,LOG,00000,"process 30061 still waiting f= or ExclusiveLock on extension of relation 26118 of database 24590 after 1000= .082 ms",,,,,"SQL statement ""insert into rpop.rpop_imap_uidls (folder_id, u= idl) values (i_folder_id, i_uidl)""

I have read several topics [1, 2, 3, 4] with similar probl= ems but haven't find a good solution. Below is some more diagnostics.
<= div>
I am running PostgreSQL 9.3.2 installed from RPM packages= on RHEL 6.4. Host is running with the following CPU (32 cores) and memory:<= /div>

root@rpopdb01e ~ # fgrep -m1 'model name' /pro= c/cpuinfo
model name : Intel(R) Xeon(R) CPU E5-2660 0 @ 2.20GHz
root@rpopdb01e ~ # free -m
&nb= sp; total used free &= nbsp; shared buffers cached
Mem: = 129028 123558 5469 &= nbsp; 0 135 &nb= sp; 119504
-/+ buffers/cache: 3918 &nb= sp; 125110
Swap: 16378 &n= bsp; 0 16378
root@rpopdb01e ~ #

PGDATA lives on RAID6 array of 8 ssd-disks wit= h ext4, iostat and atop say the disks are really free. Right now PGDATA take= s only 95G.
The settings changed in postgresql.conf are here [= 5].

When it happens the last query from here [6] sh= ows that almost all queries are waiting for ExclusiveLock, but they do a= simple insert.

(extend,26647,26825,,,,,= ,,) | 5459 | ExclusiveLock | 1 | (e= xtend,26647,26825,,,,,,,) | 8053 | ExclusiveLock | 5459,8053
= ;(extend,26647,26828,,,,,,,) | 5567 | ExclusiveLo= ck | 1 | (extend,26647,26828,,,,,,,) | 5490 | ExclusiveLock | 5= 567,5490
(extend,24584,25626,,,,,,,) | &= nbsp;5611 | ExclusiveLock | 1 | (extend,24584,25626,,,,,,,) | 3= 963 | ExclusiveLock | 5611,3963

I have severa= l databases running on one host with one postmaster process and Exclusi= veLock is being waited by many oids. I suppose the only common thing for all= of them is that they are bigger than others and they almost do not get upda= tes and deletes (only inserts and reads). Some more info about one of such t= ables is here [7].

I have tried to look at the sour= ce code (src/backend/access/heap/hio.c) to understand when the exclusive loc= k can be taken, but I could only read comments :) I have also examined FSM f= or this tables and their indexes and found that for most of them there are f= ree pages but there are, for example, such cases:

<= div>rpopdb_p0=3D# select count(*) from pg_freespace('rpop.rpop_uidl') where a= vail !=3D 0;
count
--------
115953=
(1 row)

rpopdb_p0=3D# select count(*) fr= om pg_freespace('rpop.pk_rpop_uidl') where avail !=3D 0;
cou= nt
-------
0
(1 row)
=

rpopdb_p0=3D# \dS+ rpop.rpop_uidl
&= nbsp; &= nbsp; Table "rpop.rpop_uidl"
Column | &n= bsp; Type | Mod= ifiers | Storage | Stats target | Description
--------+-----= -------------------+-----------+----------+--------------+-------------
popid | bigint &= nbsp; | not null | plain | &n= bsp; |
uidl | character varying(2= 00) | not null | extended | &= nbsp;|
Indexes:
"pk_rpop_uidl" PRIMARY KEY= , btree (popid, uidl)
Has OIDs: no

rpopdb= _p0=3D#

My questions are= :
1. Do we consume 100% cpu (in system) trying to get page from FS= M? Or does it happen during exclusive lock acquiring? How can I dig it?
2. How much space do we extend to the relation when we get exclusive l= ock on it?
3. Why extended page is not visible for other backends?=
4. Is there any possibility of situation where backend A got excl= usive lock on some relation to extend it. Then OS CPU scheduler made a conte= xt switch to backend B while backend B is waiting for exclusive lock on the s= ame relation. And so on for many backends.
5. (and the main questi= on) what can I do to get rid of such situations? It is a production cluster a= nd I do not have any ideas what to do with this situation :( Any help would b= e really appropriate.

[1] http://www.postgresql.org/message-id/8bca3aa10906011613l8ac2423h= 8153bbd2513dc159@mail.gmail.com
[2] http://pgsql.performance.narkive.com/IrkPbl3f/postgresq= l-9-2-3-performance-problem-caused-exclusive-locks
[3] h= ttp://www.postgresql.org/message-id/50A2C93E.9070607@dalibo.com
[4] http://www.postgresql.org= /message-id/CAL_0b1sypYeOyNkYNV95nNV2d+4jXTug3HkKF6FahfW7Gvgb_Q@mail.gmail.c= om
[5] http://pastebin.com/raw.php?i=3DBd40Vn6h
[6] http://wiki.po= stgresql.org/wiki/Lock_dependency_information
[7 http://pastebin.com/raw.php?i=3DeGr= tG524]

--
Vladimir