Re: PSQL performance - TPS
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
Re: PSQL performance - TPS
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 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 > > >
Re: PSQL performance - TPS
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
Re: PSQL performance - TPS
On Thu, Aug 1, 2019 at 2:15 PM Andres Freund 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.
Re: PSQL performance - TPS
On Thu, 1 Aug 2019, 23:58 Rick Otten, wrote: > > > On Thu, Aug 1, 2019 at 2:15 PM Andres Freund 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 ! >
