pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedHeavy inserts load wile querying...
3+ messages / 2 participants
[nested] [flat]
* Heavy inserts load wile querying...
@ 2012-07-24 13:22 Ioannis Anagnostopoulos <ioannis@anatec.com>
2012-07-24 14:30 ` Re: Heavy inserts load wile querying... Craig James <cjames@emolecules.com>
0 siblings, 1 reply; 3+ messages in thread
From: Ioannis Anagnostopoulos @ 2012-07-24 13:22 UTC (permalink / raw)
To: pgsql-performance
Hello,
The Postres 9.0 database we use gets about 20K inserts per minute. As
long as you don't query at the same time the database is copying fine.
However long running queries seems to delay so much the db that the
application server buffers the incoming data as it cannot insert them
fast enough. The server has 4 HD. One is used for archive, past static
tables, the second is the index of the current live tables and the third
is the current data. The fourth is the OS.
The serve specs are:
Intel(R) Xeon(R) CPU W3520 @ 2.67GHz
4 cores
18GB Ram
Do you think that this work load is high that requires an upgrade to
cluster or RAID 10 to cope with it?
Kind Regards
Yiannis
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Heavy inserts load wile querying...
2012-07-24 13:22 Heavy inserts load wile querying... Ioannis Anagnostopoulos <ioannis@anatec.com>
@ 2012-07-24 14:30 ` Craig James <cjames@emolecules.com>
2012-07-24 14:42 ` Re: Heavy inserts load wile querying... Ioannis Anagnostopoulos <ioannis@anatec.com>
0 siblings, 1 reply; 3+ messages in thread
From: Craig James @ 2012-07-24 14:30 UTC (permalink / raw)
To: Ioannis Anagnostopoulos <ioannis@anatec.com>; +Cc: pgsql-performance
On Tue, Jul 24, 2012 at 6:22 AM, Ioannis Anagnostopoulos <ioannis@anatec.com
> wrote:
> Hello,
> The Postres 9.0 database we use gets about 20K inserts per minute. As long
> as you don't query at the same time the database is copying fine. However
> long running queries seems to delay so much the db that the application
> server buffers the incoming data as it cannot insert them fast enough. The
> server has 4 HD. One is used for archive, past static tables, the second is
> the index of the current live tables and the third is the current data. The
> fourth is the OS.
>
> The serve specs are:
> Intel(R) Xeon(R) CPU W3520 @ 2.67GHz
> 4 cores
> 18GB Ram
>
> Do you think that this work load is high that requires an upgrade to
> cluster or RAID 10 to cope with it?
>
You need to learn more about what exactly is your bottleneck ... memory,
CPU, or I/O. That said, I suspect you'd be way better off with this
hardware if you built a single software RAID 10 array and put everything on
it.
Right now, the backup disk and the OS disk are sitting idle most of the
time. With a RAID10 array, you'd at least double, maybe quadruple your
I/O. And if you added a battery-backed RAID controller, you'd have a
pretty fast system.
Craig
>
> Kind Regards
> Yiannis
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.**
> org <pgsql-performance@postgresql.org>)
> To make changes to your subscription:
> http://www.postgresql.org/**mailpref/pgsql-performance<http://www.postgresql.org/mailpref/pgsql-p...;
>
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Heavy inserts load wile querying...
2012-07-24 13:22 Heavy inserts load wile querying... Ioannis Anagnostopoulos <ioannis@anatec.com>
2012-07-24 14:30 ` Re: Heavy inserts load wile querying... Craig James <cjames@emolecules.com>
@ 2012-07-24 14:42 ` Ioannis Anagnostopoulos <ioannis@anatec.com>
0 siblings, 0 replies; 3+ messages in thread
From: Ioannis Anagnostopoulos @ 2012-07-24 14:42 UTC (permalink / raw)
To: Craig James <cjames@emolecules.com>; +Cc: pgsql-performance
On 24/07/2012 15:30, Craig James wrote:
>
>
> On Tue, Jul 24, 2012 at 6:22 AM, Ioannis Anagnostopoulos
> <ioannis@anatec.com <mailto:ioannis@anatec.com>> wrote:
>
> Hello,
> The Postres 9.0 database we use gets about 20K inserts per minute.
> As long as you don't query at the same time the database is
> copying fine. However long running queries seems to delay so much
> the db that the application server buffers the incoming data as it
> cannot insert them fast enough. The server has 4 HD. One is used
> for archive, past static tables, the second is the index of the
> current live tables and the third is the current data. The fourth
> is the OS.
>
> The serve specs are:
> Intel(R) Xeon(R) CPU W3520 @ 2.67GHz
> 4 cores
> 18GB Ram
>
> Do you think that this work load is high that requires an upgrade
> to cluster or RAID 10 to cope with it?
>
>
> You need to learn more about what exactly is your bottleneck ...
> memory, CPU, or I/O. That said, I suspect you'd be way better off
> with this hardware if you built a single software RAID 10 array and
> put everything on it.
>
> Right now, the backup disk and the OS disk are sitting idle most of
> the time. With a RAID10 array, you'd at least double, maybe quadruple
> your I/O. And if you added a battery-backed RAID controller, you'd
> have a pretty fast system.
>
> Craig
>
>
> Kind Regards
> Yiannis
>
> --
> Sent via pgsql-performance mailing list
> (pgsql-performance@postgresql.org
> <mailto:pgsql-performance@postgresql.org>)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
>
I can only assume that it is an i/o issue. At last this is what I can
read from iostat:
Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s
avgrq-sz avgqu-sz await svctm %util
sda 0.00 277.50 0.00 20.00 0.00 2344.00
117.20 0.09 2.25 4.50 9.00
sdb 1.00 0.50 207.50 4.50 45228.00 33.50
213.50 2.40 11.34 4.13 87.50
sdc 0.00 0.00 29.50 0.00 4916.00 0.00
166.64 0.11 3.73 1.36 4.00
sdd 0.00 0.00 4.00 179.50 96.00 3010.00
16.93 141.25 828.77 5.45 100.00
avg-cpu: %user %nice %system %iowait %steal %idle
7.60 0.00 2.08 46.45 0.00 43.87
Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s
avgrq-sz avgqu-sz await svctm %util
sda 0.00 61.50 0.00 28.00 0.00 704.00
25.14 0.04 3.04 1.43 4.00
sdb 2.00 0.00 90.50 162.00 19560.00 2992.00
89.31 78.92 194.26 3.76 95.00
sdc 0.00 0.00 10.50 0.00 2160.00 0.00
205.71 0.02 1.90 1.90 2.00
sdd 0.00 0.00 1.50 318.50 24.00 5347.00
16.78 134.72 572.81 3.12 100.00
Where sdb is the data disk and sdd is the index disk. "Top" hardly
reports anything more than 10% per postgress process ever, while when
the query is running, these numbers on iostat are consistatnly high. At
least I can identify my buffering the moment that index hits 100% util.
Is there any other way that I can identify bottlenecks in a more
positive way?
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2012-07-24 14:42 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-07-24 13:22 Heavy inserts load wile querying... Ioannis Anagnostopoulos <ioannis@anatec.com>
2012-07-24 14:30 ` Craig James <cjames@emolecules.com>
2012-07-24 14:42 ` Ioannis Anagnostopoulos <ioannis@anatec.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