Обсуждение: PSQL performance - TPS

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

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

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