Re: [PERFORM] Survey: Max TPS you've ever seen
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 that will not do/help anything. What's the reasoning behind setting it to higher than 16MB? Do I have old information? Best regards, Martin -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Survey: Max TPS you've ever seen
For me 12000 tps until now 24 core, 150 Gb ram - 5 ssd raid 5 - Debian 7.8 - Postgres 9.3.5 ...with Postgres parameters customized: - checkpoint_segments 1000 - checkpoint_completion_target 0.9 - wal_buffers 256MB - shared_buffers 31 gb - max_connections 500 - effective_io_concurrency 15 ..and finally pgbench parameters - scale 350 - clients 300 - threads 30 - 60 seconds test run time Em 10/02/2015 22:32, Mark Kirkwood mark.kirkw...@catalyst.net.nz escreveu: 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 4 to 6 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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Survey: Max TPS you've ever seen
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 than that 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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Survey: Max TPS you've ever seen
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 RHEL6 servers, when moving from a stock RHEL6 kernel to an ELREPO 3.18 series kernel. That's what 10 years of kernel development will 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're using 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 (traditional pciE 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-85-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 against a 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 cache is the choke point for performance) - readahead settings (We *tripled* our read performance on large tables/transfers by changing this from the default value in 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 system sharing the same disk) - even things like raid stripe size and filesystem block size can have a small impact if you're going for absolute maximum TPS. However honestly all of this is probably dwarfed by the question of what you're doing with your database. If what you do doesn't actually look like pgbench activity (e.g. your server is mostly burning clock cycles on running ancient legacy pl/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 'fast memory' 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.kirkw...@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 4 to 6 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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Survey: Max TPS you've ever seen
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 gavinflo...@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
Re: [PERFORM] Survey: Max TPS you've ever seen
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 good indication 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 which isn'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 little web 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 124 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...@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 gavinflo...@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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Survey: Max TPS you've ever seen
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 4 to 6 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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] 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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
