pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feed Re[2]: [PERFORM] SMP on a heavy loaded database
7+ messages / 3 participants
[nested] [flat]
* Re[2]: [PERFORM] SMP on a heavy loaded database
@ 2013-01-04 14:41 nobody nowhere <devnull@mail.ua>
0 siblings, 6 replies; 7+ messages in thread
From: nobody nowhere @ 2013-01-04 14:41 UTC (permalink / raw)
To: pgsql-performance
Пятница, 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 > 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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Re[2]: [PERFORM] SMP on a heavy loaded database
@ 2013-01-04 14:47 Charles Gomes <charlesrg@outlook.com>
parent: nobody nowhere <devnull@mail.ua>
5 siblings, 0 replies; 7+ messages in thread
From: Charles Gomes @ 2013-01-04 14:47 UTC (permalink / raw)
To: nobody nowhere <devnull@mail.ua>; pgsql-performance
________________________________
> 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.
--
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] 7+ messages in thread
* Re: Re[2]: [PERFORM] SMP on a heavy loaded database
@ 2013-01-04 14:52 Claudio Freire <klaussfreire@gmail.com>
parent: nobody nowhere <devnull@mail.ua>
5 siblings, 0 replies; 7+ messages in thread
From: Claudio Freire @ 2013-01-04 14:52 UTC (permalink / raw)
To: nobody nowhere <devnull@mail.ua>; +Cc: pgsql-performance
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?
--
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] 7+ messages in thread
* Re: Re[2]: [PERFORM] SMP on a heavy loaded database
@ 2013-01-04 16:43 Claudio Freire <klaussfreire@gmail.com>
parent: nobody nowhere <devnull@mail.ua>
5 siblings, 0 replies; 7+ messages in thread
From: Claudio Freire @ 2013-01-04 16:43 UTC (permalink / raw)
To: nobody nowhere <devnull@mail.ua>; +Cc: pgsql-performance
On Fri, Jan 4, 2013 at 1:23 PM, nobody nowhere <devnull@mail.ua> wrote:
>
> ...have you checked which PID is using that core? Is it postgres-related?
>
> How do I know it?
An unfiltered top or ps might give you a clue. You could also try
iotop, php does hit the filesystem (sessions stored in disk), and if
it's on the same partition as postgres, postgres' fsyncs might cause
it to flush to disk quite heavily.
--
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] 7+ messages in thread
* Re: Re[2]: [PERFORM] SMP on a heavy loaded database
@ 2013-01-04 19:04 Claudio Freire <klaussfreire@gmail.com>
parent: nobody nowhere <devnull@mail.ua>
5 siblings, 0 replies; 7+ messages in thread
From: Claudio Freire @ 2013-01-04 19:04 UTC (permalink / raw)
To: nobody nowhere <devnull@mail.ua>; +Cc: pgsql-performance
On Fri, Jan 4, 2013 at 3:38 PM, nobody nowhere <devnull@mail.ua> wrote:
>
> An unfiltered top or ps might give you a clue. You could also try
>
> Look at letter on the topic start.
It's filtered by -u postgres, so you can't see apache there.
> iotop, php does hit the filesystem (sessions stored in disk), and if
> it's on the same partition as postgres, postgres' fsyncs might cause
> it to flush to disk quite heavily.
>
> The question was "which PID is using that core?"
> Can you using top or iotop certanly answer on this question? I can't.
If you see some process hogging CPU/IO in a way that's consistent with
CPU14, then you have a candidate. I don't see much in that iotop,
except the 640k/s writes in pg's writer, which isn't much at all
unless you have a seriously underpowered/broken system. If all fails,
you can look for processes with high accumulated cputime, like the
"monitor" ones there on the first top (though it doesn't say much,
since that top is incomplete), or the walsender. Without the ability
to compare against all other processes, none of that means much - but
once you do, you can inspect those processes more closely.
Oh... and you can also tell top to show the "last used processor". I
guess I should have said this first ;-)
--
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] 7+ messages in thread
* Re: Re[2]: [PERFORM] SMP on a heavy loaded database
@ 2013-01-04 21:20 Claudio Freire <klaussfreire@gmail.com>
parent: nobody nowhere <devnull@mail.ua>
5 siblings, 0 replies; 7+ messages in thread
From: Claudio Freire @ 2013-01-04 21:20 UTC (permalink / raw)
To: nobody nowhere <devnull@mail.ua>; +Cc: pgsql-performance
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.
--
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] 7+ messages in thread
* Re: Re[2]: [PERFORM] SMP on a heavy loaded database
@ 2013-01-04 21:53 Claudio Freire <klaussfreire@gmail.com>
parent: nobody nowhere <devnull@mail.ua>
5 siblings, 0 replies; 7+ messages in thread
From: Claudio Freire @ 2013-01-04 21:53 UTC (permalink / raw)
To: nobody nowhere <devnull@mail.ua>; +Cc: pgsql-performance
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.
Have you done any stress testing? Is there any actual performance impact?
--
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] 7+ messages in thread
end of thread, other threads:[~2013-01-04 21:53 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-01-04 14:41 Re[2]: [PERFORM] SMP on a heavy loaded database nobody nowhere <devnull@mail.ua>
2013-01-04 14:47 ` Charles Gomes <charlesrg@outlook.com>
2013-01-04 14:52 ` Claudio Freire <klaussfreire@gmail.com>
2013-01-04 16:43 ` Claudio Freire <klaussfreire@gmail.com>
2013-01-04 19:04 ` Claudio Freire <klaussfreire@gmail.com>
2013-01-04 21:20 ` Claudio Freire <klaussfreire@gmail.com>
2013-01-04 21:53 ` Claudio Freire <klaussfreire@gmail.com>
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