pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPSQL performance - TPS
9+ messages / 6 participants
[nested] [flat]
* PSQL performance - TPS
@ 2019-08-01 03:10 Shital A <brightuser2019@gmail.com>
2019-08-01 05:15 ` Re: PSQL performance - TPS Gavin Flower <GavinFlower@archidevsys.co.nz>
2019-08-01 17:21 ` Re: PSQL performance - TPS Andres Freund <andres@anarazel.de>
2019-08-02 11:04 ` Re: PSQL performance - TPS Imre Samu <pella.samu@gmail.com>
0 siblings, 3 replies; 9+ messages in thread
From: Shital A @ 2019-08-01 03:10 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
Hello,
We are working on development of an application with postgresql 9.6 as
backend. Application as a whole is expected to give an throughput of 100k
transactions per sec. The transactions are received by DB from component
firing DMLs in ad-hoc fashion i.e. the commits are fired after random
numbers of transaction like 2,3,4. There is no bulk loading of records. DB
should have HA setup in active passive streaming replication. We are doing
a test setup on a 8-core machine having 16 GB RAM. Actual HW will be
better.
Need help in:
1. On this env(8core cpu, 16GB) what is the TPS that we can expect? We have
tested with a simple Java code firing insert and commit in a loop on a
simple table with one column. We get 1200 rows per sec. If we increase
threads RPS decrease.
2. We have tuned some DB params like shared_buffers, sync_commit off, are
there any other pointers to tune DB params?
Thanks.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: PSQL performance - TPS
2019-08-01 03:10 PSQL performance - TPS Shital A <brightuser2019@gmail.com>
@ 2019-08-01 05:15 ` Gavin Flower <GavinFlower@archidevsys.co.nz>
2019-08-01 05:18 ` Re: PSQL performance - TPS Shital A <brightuser2019@gmail.com>
2 siblings, 1 reply; 9+ messages in thread
From: Gavin Flower @ 2019-08-01 05:15 UTC (permalink / raw)
To: Shital A <brightuser2019@gmail.com>; pgsql-performance@lists.postgresql.org
On 01/08/2019 15:10, Shital A wrote:
> Hello,
>
> We are working on development of an application with postgresql 9.6 as
> backend. Application as a whole is expected to give an throughput of
> 100k transactions per sec. The transactions are received by DB from
> component firing DMLs in ad-hoc fashion i.e. the commits are fired
> after random numbers of transaction like 2,3,4. There is no bulk
> loading of records. DB should have HA setup in active passive
> streaming replication. We are doing a test setup on a 8-core machine
> having 16 GB RAM. Actual HW will be better.
>
> Need help in:
> 1. On this env(8core cpu, 16GB) what is the TPS that we can expect? We
> have tested with a simple Java code firing insert and commit in a loop
> on a simple table with one column. We get 1200 rows per sec. If we
> increase threads RPS decrease.
>
> 2. We have tuned some DB params like shared_buffers, sync_commit off,
> are there any other pointers to tune DB params?
>
>
> Thanks.
Curious, why not use a more up-to-date version of Postgres, such 11.4?
As more recent versions tend to run faster and to be better optimised!
You also need to specify the operating system! Hopefully you are
running a Linux or Unix O/S!
Cheers,
Gavin
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: PSQL performance - TPS
2019-08-01 03:10 PSQL performance - TPS Shital A <brightuser2019@gmail.com>
2019-08-01 05:15 ` Re: PSQL performance - TPS Gavin Flower <GavinFlower@archidevsys.co.nz>
@ 2019-08-01 05:18 ` Shital A <brightuser2019@gmail.com>
0 siblings, 0 replies; 9+ messages in thread
From: Shital A @ 2019-08-01 05:18 UTC (permalink / raw)
To: Gavin Flower <GavinFlower@archidevsys.co.nz>; +Cc: pgsql-performance@lists.postgresql.org
Hello,
Version 9.6 is used because the components interacting with DB support this
version. OS is RHEL 7.6.
Thanks!
On Thu, 1 Aug 2019, 10:45 Gavin Flower, <GavinFlower@archidevsys.co.nz>
wrote:
> On 01/08/2019 15:10, Shital A wrote:
> > Hello,
> >
> > We are working on development of an application with postgresql 9.6 as
> > backend. Application as a whole is expected to give an throughput of
> > 100k transactions per sec. The transactions are received by DB from
> > component firing DMLs in ad-hoc fashion i.e. the commits are fired
> > after random numbers of transaction like 2,3,4. There is no bulk
> > loading of records. DB should have HA setup in active passive
> > streaming replication. We are doing a test setup on a 8-core machine
> > having 16 GB RAM. Actual HW will be better.
> >
> > Need help in:
> > 1. On this env(8core cpu, 16GB) what is the TPS that we can expect? We
> > have tested with a simple Java code firing insert and commit in a loop
> > on a simple table with one column. We get 1200 rows per sec. If we
> > increase threads RPS decrease.
> >
> > 2. We have tuned some DB params like shared_buffers, sync_commit off,
> > are there any other pointers to tune DB params?
> >
> >
> > Thanks.
>
> Curious, why not use a more up-to-date version of Postgres, such 11.4?
> As more recent versions tend to run faster and to be better optimised!
>
> You also need to specify the operating system! Hopefully you are
> running a Linux or Unix O/S!
>
>
> Cheers,
> Gavin
>
>
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: PSQL performance - TPS
2019-08-01 03:10 PSQL performance - TPS Shital A <brightuser2019@gmail.com>
@ 2019-08-01 17:21 ` Andres Freund <andres@anarazel.de>
2019-08-01 18:06 ` Re: PSQL performance - TPS Purav Chovatia <puravc@gmail.com>
2 siblings, 1 reply; 9+ messages in thread
From: Andres Freund @ 2019-08-01 17:21 UTC (permalink / raw)
To: Shital A <brightuser2019@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org
Hi,
On 2019-08-01 08:40:53 +0530, Shital A wrote:
> Need help in:
> 1. On this env(8core cpu, 16GB) what is the TPS that we can expect? We have
> tested with a simple Java code firing insert and commit in a loop on a
> simple table with one column. We get 1200 rows per sec. If we increase
> threads RPS decrease.
>
> 2. We have tuned some DB params like shared_buffers, sync_commit off, are
> there any other pointers to tune DB params?
If you've set synchronous_commit = off, and you still get only 1200
transactions/sec, something else is off. Are you sure you set that?
Are your clients in the same datacenter as your database? Otherwise it
could be that you're mostly seeing latency effects.
Greetings,
Andres Freund
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: PSQL performance - TPS
2019-08-01 03:10 PSQL performance - TPS Shital A <brightuser2019@gmail.com>
2019-08-01 17:21 ` Re: PSQL performance - TPS Andres Freund <andres@anarazel.de>
@ 2019-08-01 18:06 ` Purav Chovatia <puravc@gmail.com>
2019-08-01 18:14 ` Re: PSQL performance - TPS Andres Freund <andres@anarazel.de>
0 siblings, 1 reply; 9+ messages in thread
From: Purav Chovatia @ 2019-08-01 18:06 UTC (permalink / raw)
To: Andres Freund <andres@anarazel.de>; +Cc: Shital A <brightuser2019@gmail.com>; pgsql-performance@lists.postgresql.org
I am not very surprised with these results. However, what’s the disk type?
That can matter quite a bit.
On Thu, 1 Aug 2019 at 10:51 PM, Andres Freund <andres@anarazel.de> wrote:
> Hi,
>
> On 2019-08-01 08:40:53 +0530, Shital A wrote:
> > Need help in:
> > 1. On this env(8core cpu, 16GB) what is the TPS that we can expect? We
> have
> > tested with a simple Java code firing insert and commit in a loop on a
> > simple table with one column. We get 1200 rows per sec. If we increase
> > threads RPS decrease.
> >
> > 2. We have tuned some DB params like shared_buffers, sync_commit off, are
> > there any other pointers to tune DB params?
>
> If you've set synchronous_commit = off, and you still get only 1200
> transactions/sec, something else is off. Are you sure you set that?
>
> Are your clients in the same datacenter as your database? Otherwise it
> could be that you're mostly seeing latency effects.
>
> Greetings,
>
> Andres Freund
>
>
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: PSQL performance - TPS
2019-08-01 03:10 PSQL performance - TPS Shital A <brightuser2019@gmail.com>
2019-08-01 17:21 ` Re: PSQL performance - TPS Andres Freund <andres@anarazel.de>
2019-08-01 18:06 ` Re: PSQL performance - TPS Purav Chovatia <puravc@gmail.com>
@ 2019-08-01 18:14 ` Andres Freund <andres@anarazel.de>
2019-08-01 18:27 ` Re: PSQL performance - TPS Rick Otten <rottenwindfish@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Andres Freund @ 2019-08-01 18:14 UTC (permalink / raw)
To: Purav Chovatia <puravc@gmail.com>; +Cc: Shital A <brightuser2019@gmail.com>; pgsql-performance@lists.postgresql.org
Hi,
On 2019-08-01 23:36:33 +0530, Purav Chovatia wrote:
> > If you've set synchronous_commit = off, and you still get only 1200
> > transactions/sec, something else is off. Are you sure you set that?
> I am not very surprised with these results. However, what’s the disk type?
> That can matter quite a bit.
Why aren't you surprised? I can easily get 20k+ write transactions/sec
on my laptop, with synchronous_commit=off. With appropriate
shared_buffers and other settings, the disk speed shouldn't matter that
much for in insertion mostly workload.
Greetings,
Andres Freund
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: PSQL performance - TPS
2019-08-01 03:10 PSQL performance - TPS Shital A <brightuser2019@gmail.com>
2019-08-01 17:21 ` Re: PSQL performance - TPS Andres Freund <andres@anarazel.de>
2019-08-01 18:06 ` Re: PSQL performance - TPS Purav Chovatia <puravc@gmail.com>
2019-08-01 18:14 ` Re: PSQL performance - TPS Andres Freund <andres@anarazel.de>
@ 2019-08-01 18:27 ` Rick Otten <rottenwindfish@gmail.com>
2019-08-02 04:59 ` Re: PSQL performance - TPS Shital A <brightuser2019@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Rick Otten @ 2019-08-01 18:27 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
On Thu, Aug 1, 2019 at 2:15 PM Andres Freund <andres@anarazel.de> wrote:
> Hi,
>
> On 2019-08-01 23:36:33 +0530, Purav Chovatia wrote:
> > > If you've set synchronous_commit = off, and you still get only 1200
> > > transactions/sec, something else is off. Are you sure you set that?
> > I am not very surprised with these results. However, what’s the disk
> type?
> > That can matter quite a bit.
>
>
Also a reminder that you should have a connection pooler in front of your
database such as PGBouncer. If you are churning a lot of connections you
could be hurting your throughput.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: PSQL performance - TPS
2019-08-01 03:10 PSQL performance - TPS Shital A <brightuser2019@gmail.com>
2019-08-01 17:21 ` Re: PSQL performance - TPS Andres Freund <andres@anarazel.de>
2019-08-01 18:06 ` Re: PSQL performance - TPS Purav Chovatia <puravc@gmail.com>
2019-08-01 18:14 ` Re: PSQL performance - TPS Andres Freund <andres@anarazel.de>
2019-08-01 18:27 ` Re: PSQL performance - TPS Rick Otten <rottenwindfish@gmail.com>
@ 2019-08-02 04:59 ` Shital A <brightuser2019@gmail.com>
0 siblings, 0 replies; 9+ messages in thread
From: Shital A @ 2019-08-02 04:59 UTC (permalink / raw)
To: Rick Otten <rottenwindfish@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org
On Thu, 1 Aug 2019, 23:58 Rick Otten, <rottenwindfish@gmail.com> wrote:
>
>
> On Thu, Aug 1, 2019 at 2:15 PM Andres Freund <andres@anarazel.de> wrote:
>
>> Hi,
>>
>> On 2019-08-01 23:36:33 +0530, Purav Chovatia wrote:
>> > > If you've set synchronous_commit = off, and you still get only 1200
>> > > transactions/sec, something else is off. Are you sure you set that?
>> > I am not very surprised with these results. However, what’s the disk
>> type?
>> > That can matter quite a bit.
>>
>>
> Also a reminder that you should have a connection pooler in front of your
> database such as PGBouncer. If you are churning a lot of connections you
> could be hurting your throughput.
>
>
>
Hello,
Yes, synchronous_commit is off on primary and standby.
Primary, standby and clients are in same datacentre.
Shared_buffers set to 25% of RAM , no much improvement if this is increased.
Other params set are:
Effective_cache_size 12GB
Maintainance_work_mem 1GB
Walk_buffers 16MB
Effective_io_concurrency 200
Work_mem 5242kB
Min_wal_size 2GB
Max_wal_size 4GB
Max_worker_processes 8
Max_parallel_workers_per_gather 8
Checkpoint_completion_target 0.9
Random_page_cost 1.1
We have not configured connection pooler. Number of coonections are under
20 for this testing.
@Rick, 20k TPS on your system - is it with batching
Want to know what configuration we are missing to achieve higher TPS. We
are testing inserts on a simple table with just one text column.
Thanks !
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: PSQL performance - TPS
2019-08-01 03:10 PSQL performance - TPS Shital A <brightuser2019@gmail.com>
@ 2019-08-02 11:04 ` Imre Samu <pella.samu@gmail.com>
2 siblings, 0 replies; 9+ messages in thread
From: Imre Samu @ 2019-08-02 11:04 UTC (permalink / raw)
To: Shital A <brightuser2019@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org
> Application as a whole is expected to give an throughput of 100k
transactions per sec.
> On this env(8core cpu, 16GB) what is the TPS that we can expect?
as a reference - maybe you can reuse/adapt the "TechEmpower Framework
Benchmarks" tests - and compare your PG9.6+hardware results.
The new TechEmpower Framework Benchmarks [2019-07-09 Round 18]
* reference numbers:
https://www.techempower.com/benchmarks/#section=data-r18&hw=ph&test=update
* source code: https://github.com/TechEmpower/FrameworkBenchmarks
* PG11 config:
https://github.com/TechEmpower/FrameworkBenchmarks/blob/master/toolset/databases/postgres/postgresql...
* java frameworks:
https://github.com/TechEmpower/FrameworkBenchmarks/tree/master/frameworks/Java
> We have tested with a simple Java code firing insert
As I see - There are lot of java framework - and sometimes 10x difference
in performance :
https://www.techempower.com/benchmarks/#section=data-r18&hw=ph&test=update
"Responses per second at 20 updates per request, Dell R440 Xeon Gold + 10
GbE"
( "Intel Xeon Gold 5120 CPU (14c28t) , 32 GB of memory, and an enterprise
SSD. Dedicated Cisco 10-gigabit Ethernet switch")
* java + PG11 results: low:126 -> high:21807
"Responses per second at 20 updates per request, Azure D3v2 instances"
* java + PG11 results: low:329 -> high:2975
best,
Imre
Shital A <brightuser2019@gmail.com> ezt írta (időpont: 2019. aug. 1., Cs,
5:11):
> Hello,
>
> We are working on development of an application with postgresql 9.6 as
> backend. Application as a whole is expected to give an throughput of 100k
> transactions per sec. The transactions are received by DB from component
> firing DMLs in ad-hoc fashion i.e. the commits are fired after random
> numbers of transaction like 2,3,4. There is no bulk loading of records. DB
> should have HA setup in active passive streaming replication. We are doing
> a test setup on a 8-core machine having 16 GB RAM. Actual HW will be
> better.
>
> Need help in:
> 1. On this env(8core cpu, 16GB) what is the TPS that we can expect? We
> have tested with a simple Java code firing insert and commit in a loop on a
> simple table with one column. We get 1200 rows per sec. If we increase
> threads RPS decrease.
>
> 2. We have tuned some DB params like shared_buffers, sync_commit off, are
> there any other pointers to tune DB params?
>
>
> Thanks.
>
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2019-08-02 11:04 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-08-01 03:10 PSQL performance - TPS Shital A <brightuser2019@gmail.com>
2019-08-01 05:15 ` Gavin Flower <GavinFlower@archidevsys.co.nz>
2019-08-01 05:18 ` Shital A <brightuser2019@gmail.com>
2019-08-01 17:21 ` Andres Freund <andres@anarazel.de>
2019-08-01 18:06 ` Purav Chovatia <puravc@gmail.com>
2019-08-01 18:14 ` Andres Freund <andres@anarazel.de>
2019-08-01 18:27 ` Rick Otten <rottenwindfish@gmail.com>
2019-08-02 04:59 ` Shital A <brightuser2019@gmail.com>
2019-08-02 11:04 ` Imre Samu <pella.samu@gmail.com>
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