pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Maximum insert per second
6+ messages / 4 participants
[nested] [flat]

* Maximum insert per second
@ 2011-07-12 13:36  Jenish Vyas <jenishvyas@gmail.com>
  0 siblings, 1 reply; 6+ messages in thread

From: Jenish Vyas @ 2011-07-12 13:36 UTC (permalink / raw)
  To: pgsql-admin

Hi All,

Thank you all for your previous advices. Now I am able to insert 1190- 1210
 records per seconds.

Now I just want to know by what extend I could stretch it.

What is the maximum insert I can achieve in one second?  What is the maximum
number of insert postgresql achieved so far?

Postgresql : 8.4.6
Os : Debian unstable version


Thanks & regards,
JENISH VYAS

^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Maximum insert per second
@ 2011-07-12 13:58  Kevin Grittner <Kevin.Grittner@wicourts.gov>
  parent: Jenish Vyas <jenishvyas@gmail.com>
  0 siblings, 1 reply; 6+ messages in thread

From: Kevin Grittner @ 2011-07-12 13:58 UTC (permalink / raw)
  To: Jenish Vyas <jenishvyas@gmail.com>; pgsql-admin

Jenish Vyas <jenishvyas@gmail.com> wrote:
 
> I am able to insert 1190- 1210 records per seconds.
> 
> Now I just want to know by what extend I could stretch it.
> 
> What is the maximum insert I can achieve in one second?  What is
> the maximum number of insert postgresql achieved so far?
 
I don't have hard numbers handy, and I know I don't have the fastest
hardware out there, but for bulk loads of narrow tables we typically
see tens of thousands of rows per second.  Of course that's with
"running with scissors" settings.  We turn off archiving,
autovacuum, fsync, full_page_writes, and synchronous_commit; and
COPY rows within the same transaction which creates the table,
before creating any indexes.  Then we build the indexes, VACUUM
FREEZE ANALYZE, and change back to a configuration which actually
preserves the data on a crash.
 
From memory, I would say on our larger servers we've probably seen
a max of something on the order of 50,000 rows per second on
relatively narrow tables, without factoring the index build and
vacuum times.
 
-Kevin



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Maximum insert per second
@ 2011-07-15 08:09  lst_hoe02@kwsoft.de
  parent: Kevin Grittner <Kevin.Grittner@wicourts.gov>
  0 siblings, 1 reply; 6+ messages in thread

From: lst_hoe02@kwsoft.de @ 2011-07-15 08:09 UTC (permalink / raw)
  To: pgsql-admin

Zitat von Kevin Grittner <Kevin.Grittner@wicourts.gov>:

> Jenish Vyas <jenishvyas@gmail.com> wrote:
>
>> I am able to insert 1190- 1210 records per seconds.
>>
>> Now I just want to know by what extend I could stretch it.
>>
>> What is the maximum insert I can achieve in one second?  What is
>> the maximum number of insert postgresql achieved so far?
>
> I don't have hard numbers handy, and I know I don't have the fastest
> hardware out there, but for bulk loads of narrow tables we typically
> see tens of thousands of rows per second.  Of course that's with
> "running with scissors" settings.  We turn off archiving,
> autovacuum, fsync, full_page_writes, and synchronous_commit; and
> COPY rows within the same transaction which creates the table,
> before creating any indexes.  Then we build the indexes, VACUUM
> FREEZE ANALYZE, and change back to a configuration which actually
> preserves the data on a crash.
>
> From memory, I would say on our larger servers we've probably seen
> a max of something on the order of 50,000 rows per second on
> relatively narrow tables, without factoring the index build and
> vacuum times.
>

Are there any numbers around for OLTP like systems? Would be nice to  
know what is possible with PostgreSQL in this case.

Regards

Andreas

Attachments:

  smime.p7s (application/pkcs7-signature, 5.9K) download

^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Maximum insert per second
@ 2011-07-15 09:39  Scott Marlowe <scott.marlowe@gmail.com>
  parent: lst_hoe02@kwsoft.de
  0 siblings, 1 reply; 6+ messages in thread

From: Scott Marlowe @ 2011-07-15 09:39 UTC (permalink / raw)
  To: lst_hoe02@kwsoft.de; +Cc: pgsql-admin

On Fri, Jul 15, 2011 at 2:09 AM,  <lst_hoe02@kwsoft.de> wrote:
> Zitat von Kevin Grittner <Kevin.Grittner@wicourts.gov>:
>
>> Jenish Vyas <jenishvyas@gmail.com> wrote:
>>
>>> I am able to insert 1190- 1210 records per seconds.
>>>
>>> Now I just want to know by what extend I could stretch it.
>>>
>>> What is the maximum insert I can achieve in one second?  What is
>>> the maximum number of insert postgresql achieved so far?
>>
>> I don't have hard numbers handy, and I know I don't have the fastest
>> hardware out there, but for bulk loads of narrow tables we typically
>> see tens of thousands of rows per second.  Of course that's with
>> "running with scissors" settings.  We turn off archiving,
>> autovacuum, fsync, full_page_writes, and synchronous_commit; and
>> COPY rows within the same transaction which creates the table,
>> before creating any indexes.  Then we build the indexes, VACUUM
>> FREEZE ANALYZE, and change back to a configuration which actually
>> preserves the data on a crash.
>>
>> From memory, I would say on our larger servers we've probably seen
>> a max of something on the order of 50,000 rows per second on
>> relatively narrow tables, without factoring the index build and
>> vacuum times.
>>
>
> Are there any numbers around for OLTP like systems? Would be nice to know
> what is possible with PostgreSQL in this case.

System: SuperMicro H8QG6
4xAMD Opteron(tm) Processor 6168
Total 48 cores
128G RAM
Areca 1680 w 512M battery backed cache
32 15k SAS 147Gig drives

pgbench -i -s 100
pgbench -c 48 -t 10000
tps = 7777.626762 (including connections establishing)
tps = 7808.976047 (excluding connections establishing)

If I run them for much longer, it'll drop down a bit and average about
4 to 5k sustained tps.



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Maximum insert per second
@ 2011-07-15 10:09  Scott Marlowe <scott.marlowe@gmail.com>
  parent: Scott Marlowe <scott.marlowe@gmail.com>
  0 siblings, 1 reply; 6+ messages in thread

From: Scott Marlowe @ 2011-07-15 10:09 UTC (permalink / raw)
  To: lst_hoe02@kwsoft.de; +Cc: pgsql-admin

On Fri, Jul 15, 2011 at 3:39 AM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
>
> System: SuperMicro H8QG6
> 4xAMD Opteron(tm) Processor 6168
> Total 48 cores
> 128G RAM
> Areca 1680 w 512M battery backed cache
> 32 15k SAS 147Gig drives
>
> pgbench -i -s 100
> pgbench -c 48 -t 10000
> tps = 7777.626762 (including connections establishing)
> tps = 7808.976047 (excluding connections establishing)
>
> If I run them for much longer, it'll drop down a bit and average about
> 4 to 5k sustained tps.

Correction, I just ran a much longer test that would have involved at
least 1 checkpoint and was still getting ~7800tps on this machine.

OS is Ubuntu 10.04.  Only non-stock settings are:

sysctl.conf:
vm.zone_reclaim_mode = 0
kernel.shmmax = 33554432000
kernel.shmall = 209715200
kernel.shmmni = 4096

/etc/rc.local:
swapoff -a



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Maximum insert per second
@ 2011-07-17 10:40  lst_hoe02@kwsoft.de
  parent: Scott Marlowe <scott.marlowe@gmail.com>
  0 siblings, 0 replies; 6+ messages in thread

From: lst_hoe02@kwsoft.de @ 2011-07-17 10:40 UTC (permalink / raw)
  To: pgsql-admin

Zitat von Scott Marlowe <scott.marlowe@gmail.com>:

> On Fri, Jul 15, 2011 at 3:39 AM, Scott Marlowe  
> <scott.marlowe@gmail.com> wrote:
>>
>> System: SuperMicro H8QG6
>> 4xAMD Opteron(tm) Processor 6168
>> Total 48 cores
>> 128G RAM
>> Areca 1680 w 512M battery backed cache
>> 32 15k SAS 147Gig drives
>>
>> pgbench -i -s 100
>> pgbench -c 48 -t 10000
>> tps = 7777.626762 (including connections establishing)
>> tps = 7808.976047 (excluding connections establishing)
>>
>> If I run them for much longer, it'll drop down a bit and average about
>> 4 to 5k sustained tps.
>
> Correction, I just ran a much longer test that would have involved at
> least 1 checkpoint and was still getting ~7800tps on this machine.
>
> OS is Ubuntu 10.04.  Only non-stock settings are:
>
> sysctl.conf:
> vm.zone_reclaim_mode = 0
> kernel.shmmax = 33554432000
> kernel.shmall = 209715200
> kernel.shmmni = 4096
>
> /etc/rc.local:
> swapoff -a
>

Thanks, very impressive.

Regards

Andreas

Attachments:

  smime.p7s (application/pkcs7-signature, 5.9K) download

^ permalink  raw  reply  [nested|flat] 6+ messages in thread


end of thread, other threads:[~2011-07-17 10:40 UTC | newest]

Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-07-12 13:36 Maximum insert per second Jenish Vyas <jenishvyas@gmail.com>
2011-07-12 13:58 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-07-15 08:09   ` lst_hoe02@kwsoft.de
2011-07-15 09:39     ` Scott Marlowe <scott.marlowe@gmail.com>
2011-07-15 10:09       ` Scott Marlowe <scott.marlowe@gmail.com>
2011-07-17 10:40         ` lst_hoe02@kwsoft.de

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