PostgreSQL performance scaling

Ivan Voras ivoras at freebsd.org
Tue Nov 23 00:51:36 UTC 2010


On 11/23/10 01:26, Ivan Voras wrote:
> On 11/22/10 17:37, David Xu wrote:
>> Mark Felder wrote:
>>> I recommend posting this on the Postgres performance list, too.
>>>
>>>
>>>
>>>
>>> Regards,
>>>
>>>
>>> Mark
>>
>> I think if PostgreSQL uses semaphore for inter-process locking,
>> it might be a good idea to use POSIX semaphore exits in our head
>> branch, the new POSIX semaphore implementation now supports
>> process-shared, and is more light weight than SYSV semaphore,
>> if there is no contention, a process need not enter kernel to
>> acquire/release a lock. Note that I have just fixed a bug in head
>> branch. However RELENG_8 does not support process-shared semaphore
>> yet.
>
> Another thing might be that, despite that they appear to try to avoid
> it, they possibly have a large number of processes hanging on the same
> semaphore, leading to thundering herd problem.
>
> There already is code for POSIX semaphores in PostgreSQL. It requires
> some manual fiddling with the configuration to enable
> (USE_UNNAMED_POSIX_SEMAPHORES).
>
> However, I've just tried it on 9-CURRENT and it doesn't work:
>
> Nov 23 01:23:02 biggie postgres[1515]: [1-1] FATAL: sem_init failed: No
> space left on device

Ok, I've found the p1003_1b.sem_nsems_max sysctl.

It seems to help when used instead of sysv semaphores, but very little:

sysv semaphores:

-c#    result
4    33549
8    64864
12    79491
16    79887
20    66957
24    52576
28    50406
32    49491
40    45535
50    39499
75    29415

posix semaphores:

16	79125
20	70061
24	55620

After 20 clients, sys time goes sharply up like before

  procs      memory      page                    disks     faults 
   cpu
  r b w     avm    fre   flt  re  pi  po    fr  sr mf0 mf1   in   sy 
cs us sy id
27 32 0  11887M  3250M 62442   0   0   0     0   0   0   0   10 255078 
109047 18 73 10
30 32 0  11887M  3162M 58165   0   0   0    12   0   0   1    7 272540 
114416 17 75  9
29 32 0  11887M  3105M 57487   0   0   0     0   0   0   0    8 279475 
117891 15 75 10
16 31 0  11887M  3063M 59215   0   0   0     0   0   0   0    6 295342 
121090 16 70 13


and the overall behaviour is similar - the processes spend a lot of time 
in "sbwait" and "ksem" states.



More information about the freebsd-performance mailing list