Re: Survey: Max TPS you've ever seen - Mailing list pgsql-performance

>> 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