Обсуждение: Survey: Max TPS you've ever seen

On 10/02/15 08:30, Luis Antonio Dias de Sá Junior wrote:
> Hi,
>
> A survay: with pgbench using TPS-B, what is the maximum TPS you're
> ever seen?
>
> For me: 12000 TPS.
>
> --
> Luis Antonio Dias de Sá Junior
Important to specify:

 1. O/S
 2. version of PostgreSQL
 3. PostgreSQL configuration
 4. hardware configuration
 5. anything else that might affect performance

I suspect that Linux will out perform Microsoft on the same hardware,
and optimum configuration for both O/S's...


Cheers,
Gavin

I'd suggest you run it on a large ramdisk with fsync turned off on a 32 core computer, see what you get, that will be a
goodindication of a maximum. 

Keep in mind though that 'postgres' with fsync (vs. without) is such a different creature that the comparison isn't
meaningful. 
Similarly 'postgres' on volatile backing store vs. non-volatile isn't really a meaningful comparison.

There's also a question here about the 't' in TPS. If you have no fsync and volatile storage, are you really doing
'transactions'?Depending on the definition you take, a transaction may have some sense of 'reliability' or atomicity
whichisn't reflected well in a ramdisk/no-fsync benchmark.  

It's probably not ideal to fill a mailing list with numbers that have no meaning attached to them, so why not set up a
littleweb database or Google doc to record max TPS and how it was achieved? 

For example, imagine I tell you that the highest I've achieved is 1240000 tps. How does it help you if I say that?

Graeme Bell

On 10 Feb 2015, at 11:48, Luis Antonio Dias de Sá Junior <luisjunior.sa@gmail.com> wrote:

> No problem with this. If anyone want to specify more details.
>
> But I want to know how far postgres can go. No matter OS or other variables.
>
> Gavin, you got more than 12000 TPS?
>
> 2015-02-09 19:29 GMT-02:00 Gavin Flower <GavinFlower@archidevsys.co.nz>:
> On 10/02/15 08:30, Luis Antonio Dias de Sá Junior wrote:
> Hi,
>
> A survay: with pgbench using TPS-B, what is the maximum TPS you're ever seen?
>
> For me: 12000 TPS.
>
> --
> Luis Antonio Dias de Sá Junior
> Important to specify:
>
> 1. O/S
> 2. version of PostgreSQL
> 3. PostgreSQL configuration
> 4. hardware configuration
> 5. anything else that might affect performance
>
> I suspect that Linux will out perform Microsoft on the same hardware, and optimum configuration for both O/S's...
>
>
> Cheers,
> Gavin
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
>
>
> --
> Luis Antonio Dias de Sá Junior

On 10/02/15 10:29, Gavin Flower wrote:
> On 10/02/15 08:30, Luis Antonio Dias de Sá Junior wrote:
>> Hi,
>>
>> A survay: with pgbench using TPS-B, what is the maximum TPS you're
>> ever seen?
>>
>> For me: 12000 TPS.
>>
>> --
>> Luis Antonio Dias de Sá Junior
> Important to specify:
>
> 1. O/S
> 2. version of PostgreSQL
> 3. PostgreSQL configuration
> 4. hardware configuration
> 5. anything else that might affect performance
>
> I suspect that Linux will out perform Microsoft on the same hardware,
> and optimum configuration for both O/S's...
>


Yes, exactly - and also the pgbench parameters:

- scale
- number of clients
- number of threads
- statement options (prepared or simple etc)
- length of test

We've managed to get 40000 to 60000 TPS on some pretty serious hardware:

- 60 core, 1 TB ram
- 16 SSD + 4 PCIe SSD storage
- Ubuntu 14.04
- Postgres 9.4 (beta and rc)

...with Postgres parameters customized:

- checkpoint_segments 1920
- checkpoint_completion_target 0.8
- wal_buffers  256MB
- wal_sync_method open_datasync
- shared_buffers 10GB
- max_connections 600
- effective_io_concurrency 10

..and finally pgbench parameters

- scale 2000
- clients 32, 64, 128, 256 (best results at 32 and 64 generally)
- threads = 1/2 client number
- prepared option
- 10 minute test run time

Points to note, we did *not* disable fsync or prevent buffers being
actually written (common dirty tricks in benchmarks). However, as others
have remarked - raw numbers mean little. Pgbench is very useful for
testing how tuning configurations are helping (or not) for a particular
hardware and software setup, but is less useful for answering the
question "how many TPS can postgres do"...

Regards

Mark

Hi all!

> - checkpoint_segments 1000
> - checkpoint_completion_target 0.9
> - wal_buffers  256MB
> - shared_buffers 31 gb
> - max_connections 500

I see that some of you are using wal_buffers = 256MB.
I was under the impression that Postgres will not benefit from higher value than the segment size, i.e. 16MB. More than
thatwill not do/help anything.
 

What's the reasoning behind setting it to higher than 16MB? Do I have old information?

Best regards, Martin

>> 1. O/S


Under "O/S", don't forget to mention linux kernel version.

We saw a MASSIVE increase in TPS (I think it was a doubling? Don't have the data to hand right now) on our multicore
RHEL6servers, when moving from a stock RHEL6 kernel to an ELREPO 3.18 series kernel. That's what 10 years of kernel
developmentwill do for you.  

> - 16 SSD + 4 PCIe SSD storage

Similarly, it's useful to specify

- exactly which drives were being used during the test  (PCIe and SATA SSDs perform pretty differently!). Similarly if
you'reusing e.g. a dell server with a ssd cache in front of the disks, remember to mention it.  

- Also exactly which PCI interface, now that there are different types of PCI attached SSD becoming available
(traditionalpciE SSD vs NVMe) with substantially different performance and overheads.  

(Performance junkies: Check out nvmE if you haven't heard of it)
   http://www.thessdreview.com/daily-news/latest-buzz/marvell-displays-88ss1094-nvme-ssd-controller-2-9gbs/

http://www.thessdreview.com/daily-news/latest-buzz/memblaze-pmc-collaborate-pblaze4-pcie-ssd-hyperscale-data-centers-3-2gbs-reads-850000-iops/

- Which firmware (some ssds exhibit noteable performance changes with firmware)

- which filesystem and filesystem options (try benchmarking with a fresh ext4 filesystem and nobarriers - then compare
againsta mostly full filesystem with barriers on an SSD. You should see quite a difference) 

- which RAID controller.  (Good luck if you're using an H710 with modern SSDs for example... the controller's write
cacheis the choke point for performance) 

- readahead settings (We *tripled* our read performance on large tables/transfers by changing this from the default
valuein linux up to around 16MB) 

- filesystem queue depth and scheduler ( e.g. shallow/deep queues on ssds and e.g. cfq vs. noop schedulers on ssds)

- if anything else is running on the same server/filesystem (e.g. background db activity, web servers etc, operating
systemsharing the same disk) 

- even things like raid stripe size and filesystem block size can have a small impact if you're going for absolute
maximumTPS.  

However honestly all of this is probably dwarfed by the question of what you're doing with your database. If what you
dodoesn't actually look like pgbench activity (e.g. your server is mostly burning clock cycles on running ancient
legacypl/sql code) then you're taking the wrong benchmark if you use pgbench.   


(Also, another note for performance junkies - some interesting news from the gaming world - spending extra money on
'fastmemory' is probably a waste in the current generation of computers) 

  http://www.anandtech.com/show/7364/memory-scaling-on-haswell/3

Graeme Bell

On 11 Feb 2015, at 01:31, Mark Kirkwood <mark.kirkwood@catalyst.net.nz> wrote:

> On 10/02/15 10:29, Gavin Flower wrote:
>> On 10/02/15 08:30, Luis Antonio Dias de Sá Junior wrote:
>>> Hi,
>>>
>>> A survay: with pgbench using TPS-B, what is the maximum TPS you're
>>> ever seen?
>>>
>>> For me: 12000 TPS.
>>>
>>> --
>>> Luis Antonio Dias de Sá Junior
>> Important to specify:
>>
>> 1. O/S
>> 2. version of PostgreSQL
>> 3. PostgreSQL configuration
>> 4. hardware configuration
>> 5. anything else that might affect performance
>>
>> I suspect that Linux will out perform Microsoft on the same hardware,
>> and optimum configuration for both O/S's...
>>
>
>
> Yes, exactly - and also the pgbench parameters:
>
> - scale
> - number of clients
> - number of threads
> - statement options (prepared or simple etc)
> - length of test
>
> We've managed to get 40000 to 60000 TPS on some pretty serious hardware:
>
> - 60 core, 1 TB ram
> - 16 SSD + 4 PCIe SSD storage
> - Ubuntu 14.04
> - Postgres 9.4 (beta and rc)
>
> ...with Postgres parameters customized:
>
> - checkpoint_segments 1920
> - checkpoint_completion_target 0.8
> - wal_buffers  256MB
> - wal_sync_method open_datasync
> - shared_buffers 10GB
> - max_connections 600
> - effective_io_concurrency 10
>
> ..and finally pgbench parameters
>
> - scale 2000
> - clients 32, 64, 128, 256 (best results at 32 and 64 generally)
> - threads = 1/2 client number
> - prepared option
> - 10 minute test run time
>
> Points to note, we did *not* disable fsync or prevent buffers being actually written (common dirty tricks in
benchmarks).However, as others have remarked - raw numbers mean little. Pgbench is very useful for testing how tuning
configurationsare helping (or not) for a particular hardware and software setup, but is less useful for answering the
question"how many TPS can postgres do"... 
>
> Regards
>
> Mark
>
>
>
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance

On 13/02/15 00:20, Gudmundsson Martin (mg) wrote:
> Hi all!
>
>> - checkpoint_segments 1000
>> - checkpoint_completion_target 0.9
>> - wal_buffers  256MB
>> - shared_buffers 31 gb
>> - max_connections 500
>
> I see that some of you are using wal_buffers = 256MB.
> I was under the impression that Postgres will not benefit from higher value than the segment size, i.e. 16MB. More
thanthat will not do/help anything. 
>
> What's the reasoning behind setting it to higher than 16MB? Do I have old information?
>
> Best regards, Martin
>

There was some discussion a while ago in which 32MB and 8MB both
demonstrated better performance than 16MB (probably related to the fact
the the default wal file size is 16MB). We just experimented further
with bigger values, and saw some improvement.

Cheers

Mark