Skip site navigation (1) Skip section navigation (2)

Re: Load experimentation

From: Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>
To: Ben Brehmer <benbrehmer(at)gmail(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org, Thom Brown <thombrown(at)gmail(dot)com>, Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov>, craig_james(at)emolecules(dot)com, kbuckham(at)applocation(dot)net, scott(dot)lists(at)enterprisedb(dot)com, Greg Smith <greg(at)2ndquadrant(dot)com>
Subject: Re: Load experimentation
Date: 2009-12-08 07:59:40
Message-ID: dcc563d10912072359l771d7b7et8a231266d4d7bf7e@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Tue, Dec 8, 2009 at 12:58 AM, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> wrote:
> On Tue, Dec 8, 2009 at 12:22 AM, Ben Brehmer <benbrehmer(at)gmail(dot)com> wrote:
>> Thanks for all the responses. I have one more thought;
>>
>> Since my input data is split into about 200 files (3GB each), I could
>> potentially spawn one load command for each file. What would be the maximum
>> number of input connections Postgres can handle without bogging down? When I
>> say 'input connection' I mean "psql -U postgres -d dbname -f
>> one_of_many_sql_files".
>
> This is VERY dependent on your IO capacity and number of cores.  My
> experience is that unless you're running on a decent number of disks,
> you'll run out of IO capacity first in most machines.  n pairs of
> mirrors in a RAID-10 can handle x input threads where x has some near
> linear relation to n.  Have 100 disks in a RAID-10 array?  You can
> surely handle dozens of load threads with no IO wait.  Have 4 disks in
> a RAID-10?  Maybe two to four load threads will max you out.  Once
> you're IO bound, adding more threads and more CPUs won't help, it'll
> hurt.  The only way to really know is to benchmark it, but i'd guess
> that about half as many import threads as mirror pairs in a RAID-10
> (or just drives if you're using RAID-0) would be a good place to start
> and work from there.

Note that if you start running out of CPU horsepower first the
degradation will be less harsh as you go past the knee in the
performance curve.    IO has a sharper knee than CPU.

In response to

pgsql-performance by date

Next:From: Dimitri FontaineDate: 2009-12-08 09:08:45
Subject: Re: Load experimentation
Previous:From: Scott MarloweDate: 2009-12-08 07:58:50
Subject: Re: Load experimentation

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group