pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feed Re[2]: [PERFORM] Re[2]: [PERFORM] SMP on a heavy loaded database
3+ messages / 1 participants
[nested] [flat]
* Re[2]: [PERFORM] Re[2]: [PERFORM] SMP on a heavy loaded database
@ 2013-01-04 15:56 ` nobody nowhere <devnull@mail.ua>
2 siblings, 0 replies; 3+ messages in thread
From: nobody nowhere @ 2013-01-04 15:56 UTC (permalink / raw)
To: Charles Gomes <charlesrg@outlook.com>; +Cc: pgsql-performance
Пятница, 4 января 2013, 9:47 -05:00 от Charles Gomes <charlesrg@outlook.com>:
>________________________________
>> From: devnull@mail.ua
>> To: pgsql-performance@postgresql.org
>> Subject: [PERFORM] Re[2]: [PERFORM] SMP on a heavy loaded database
>> Date: Fri, 4 Jan 2013 18:41:25 +0400
>>
>>
>>
>>
>> Пятница, 4 января 2013, 0:42 -07:00 от Scott Marlowe
>> < scott.marlowe@gmail.com >:
>> On Thu, Jan 3, 2013 at 4:45 PM, nobody nowhere
>> <devnull@mail.ua< http://sentmsg?compose&To=devnull%40mail.ua >> wrote:
>> > Centos 5.X kernel 2.6.18-274
>> > pgsql-9.1 from pgdg-91-centos.repo
>> > relatively small database 3.2Gb
>> > Lot of insert, update, delete.
>> >
>> > I see non balanced _User_ usage on 14 CPU, exclusively assigned to
>> the hardware raid controller.
>> > What I'm doing wrong, and is it possible somehow to fix?
>> >
>> > Thanks in advance.
>> >
>> > Andrew.
>> >
>> > # top -d 10.00 -b -n 2 -U postgres -c
>> >
>> > top - 23:18:19 up 453 days, 57 min, 3 users, load average: 0.55, 0.47, 0.42
>> > Tasks: 453 total, 1 running, 452 sleeping, 0 stopped, 0 zombie
>> > Cpu0 : 0.6%us, 0.1%sy, 0.0%ni, 99.3%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
>> > Cpu1 : 0.0%us, 0.0%sy, 0.0%ni,100.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
>> > Cpu2 : 0.0%us, 0.0%sy, 0.0%ni,100.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
>> > Cpu3 : 1.2%us, 0.1%sy, 0.0%ni, 98.7%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
>> > Cpu4 : 2.6%us, 0.4%sy, 0.0%ni, 96.8%id, 0.0%wa, 0.0%hi, 0.2%si, 0.0%st
>> > Cpu5 : 0.8%us, 0.0%sy, 0.0%ni, 99.2%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
>> > Cpu6 : 5.4%us, 0.2%sy, 0.0%ni, 94.2%id, 0.2%wa, 0.0%hi, 0.0%si, 0.0%st
>> > Cpu7 : 3.3%us, 0.4%sy, 0.0%ni, 96.1%id, 0.0%wa, 0.0%hi, 0.2%si, 0.0%st
>> > Cpu8 : 1.4%us, 0.3%sy, 0.0%ni, 98.2%id, 0.0%wa, 0.0%hi, 0.1%si, 0.0%st
>> > Cpu9 : 0.0%us, 0.1%sy, 0.0%ni, 99.9%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
>> > Cpu10 : 0.0%us, 0.1%sy, 0.0%ni, 99.9%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
>> > Cpu11 : 1.6%us, 0.6%sy, 0.0%ni, 97.4%id, 0.0%wa, 0.0%hi, 0.4%si, 0.0%st
>> > Cpu12 : 0.5%us, 0.1%sy, 0.0%ni, 99.4%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
>> > Cpu13 : 1.4%us, 0.2%sy, 0.0%ni, 98.4%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
>> > Cpu14 : 24.2%us, 0.8%sy, 0.0%ni, 74.5%id, 0.3%wa, 0.0%hi, 0.2%si, 0.0%st
>> > Cpu15 : 0.7%us, 0.1%sy, 0.0%ni, 99.0%id, 0.0%wa, 0.1%hi, 0.1%si, 0.0%st
>> > Mem: 16426540k total, 16356772k used, 69768k free, 215764k buffers
>> > Swap: 4194232k total, 145280k used, 4048952k free, 14434356k cached
>> >
>>
>> So how many concurrent users are accessing this db? pgsql assigns one
>> process on one core so to speak. It can't spread load for one user
>> over all cores.
>> 64 php Fast-cgi processes over the Unix socket and about 20-30 over tcp
>
>Are you running IRQ Balance ? The OS can pin process to the respective IRQ handler. I switch off IRQ Balance on any heavy loaded servers and statically assign IRQ's to processors using
echo 000X > /proc/irq/XX/smp_affinity
irqballance do not help to fix it..
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re[2]: [PERFORM] Re[2]: [PERFORM] SMP on a heavy loaded database
@ 2013-01-04 16:23 ` nobody nowhere <devnull@mail.ua>
2 siblings, 0 replies; 3+ messages in thread
From: nobody nowhere @ 2013-01-04 16:23 UTC (permalink / raw)
To: Claudio Freire <klaussfreire@gmail.com>; +Cc: pgsql-performance
Пятница, 4 января 2013, 11:52 -03:00 от Claudio Freire <klaussfreire@gmail.com>:
>On Fri, Jan 4, 2013 at 11:41 AM, nobody nowhere < devnull@mail.ua > wrote:
>> So how many concurrent users are accessing this db? pgsql assigns one
>> process on one core so to speak. It can't spread load for one user
>> over all cores.
>>
>> 64 php Fast-cgi processes over the Unix socket and about 20-30 over tcp
>
>I guess that means the server isn't dedicated to postgres...
>
>...have you checked which PID is using that core? Is it postgres-related?
How do I know it?
Only postgres on this server heavely use the Raid controller. PHP comletely in XCache. At night I'll try to change socket to tcp. May be it will help,
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re[2]: [PERFORM] Re[2]: [PERFORM] SMP on a heavy loaded database
@ 2013-01-05 08:37 ` nobody nowhere <devnull@mail.ua>
2 siblings, 0 replies; 3+ messages in thread
From: nobody nowhere @ 2013-01-05 08:37 UTC (permalink / raw)
To: Claudio Freire <klaussfreire@gmail.com>; +Cc: pgsql-performance
Пятница, 4 января 2013, 18:53 -03:00 от Claudio Freire <klaussfreire@gmail.com>:
>On Fri, Jan 4, 2013 at 6:38 PM, nobody nowhere < devnull@mail.ua > wrote:
>> On Fri, Jan 4, 2013 at 6:07 PM, nobody nowhere < devnull@mail.ua > wrote:
>>> 9092 postgres 16 0 4326m 41m 34m S 0.0 0.3 0:00.27 14 postgres: user
>>> user_db [local] idle
>>> 9098 postgres 16 0 4329m 203m 194m S 3.5 1.3 0:00.65 14 postgres: user
>>> user_db [local] idle
>>> 9099 postgres 16 0 4327m 45m 38m S 0.0 0.3 0:00.41 14 postgres: user
>>> user_db [local] idle
>>
>> That looks like pg has been pinned to CPU14. I don't think it's pg's
>> doing. All I can think of is: check scheduler tweaks, numa, and pg's
>> initscript. Just in case it's being pinned explicitly.
>>
>> Not pinned.
>> Forks with tcp connection use other CPU. I just add connections pool and
>> change socket to tcp
>
>How interesting. It must be a peculiarity of unix sockets. I know unix
>sockets have close to no buffering, task-switching to the consumer
>instead of buffering. Perhaps what you're experiencing here is this
>"optimization" effect. It's probably not harmful at all. The OS will
>switch to another CPU if the need arises. It's not socket problem.
Ths same result when I change php fast-cgi connection to tcp,
Remote clients over tcp use insert-delete. Just data collection. Nothing more.
Locally php its lot of PL data processing functions. It's PL problem !!
>
>
>Have you done any stress testing? Is there any actual performance impact?
On my experience stress testing and real production perfomance usually absolutely different. :)
No application development going together with business growing. We just add functional to the system step by step.
For a last couple month we just grow up quickly and I decide to check performance :(
>
>
>--
>Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
>To make changes to your subscription:
>http://www.postgresql.org/mailpref/pgsql-performance
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2013-01-05 08:37 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-01-04 15:56 ` nobody nowhere <devnull@mail.ua>
2013-01-04 16:23 ` nobody nowhere <devnull@mail.ua>
2013-01-05 08:37 ` nobody nowhere <devnull@mail.ua>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox