Thread: Postgresql TPS Bottleneck

<wakandavision 'at' outlook.com> writes:

> Optimally I would like to fully use the CPU and get about 3-4 times
> more TPS (if even possible).

Disclaimer: I'm really not a pg performance expert.
I don't understand your hope to fully use the CPU; if your
scenario is disk-limited, which may very well be the case, then
of course you cannot fully use the CPU. With synchronous commits
and fsync, the system is probably spending time just waiting for
the disks to report the writes completion. Are iostat/vmstat
showing a lot of IO-wait?
Also, if you can live with a few lost transactions in case of
server crash, synchronous_commit=off is very ok and provides a
lot of performance gain.

-- 
Guillaume Cottenceau

While setting these 2 parameters to off will make things go faster 
(especially for fsync), it is unrealistic to have these settings in a 
production environment, especiall fsync=off.  You might get by with 
synchronous_commit=off, but with fsync=off you could end up with 
corruption in your database.  synchronous_commit may not make anything 
go faster just change where the time is being spent.

Regards,
Michael Vitale


wakandavision@outlook.com wrote on 3/31/2022 7:50 AM:
> fsync = off
> synchronous_commit = off

On 3/31/22 13:50, wakandavision@outlook.com wrote:
> Hi everyone,
> 
> I am a bachelor's student and writing my thesis about the scaling and
> performance of an application. The application is using postgresql as a
> database but we can't scale any further currently as it seems postgres
> is hitting the limit.
> 
> With the application, as well as with pgbench, we don't get more than
> (max) 70k TPS on postgres. But the servers' resources are not utilized
> completely (more below).
> 
> I've tried many different configurations but none of them had any major
> performance impact (unless fsync and synchronous_commit = off).
> 
> This is the (custom) configuration I am using:
> 
> shared_buffers=65551953kB
> effective_cache_size=147491895kB
> huge_pages=on
> min_wal_size=20GB
> max_wal_size=200GB
> wal_buffers=1GB
> max_wal_senders=0
> archive_mode=off
> wal_level=minimal
> work_mem=2GB
> maintenance_work_mem=4GB
> checkpoint_completion_target=0.9
> checkpoint_timeout = 30min
> random_page_cost=1.1
> bgwriter_flush_after = 2MB
> effective_io_concurrency = 200
> # Disabled just for performance experiments
> fsync = off
> synchronous_commit = off
> full_page_writes = on
> max_worker_processes=64
> max_parallel_workers=64
> max_parallel_workers_per_gather=10
> max_parallel_maintenance_workers=12
> 
> The system is as follows:
> 
> * 64 Cores (Intel Xeon Gold 6130 (Skylake, 2.10GHz, 2 CPUs/node, 16
> cores/CPU))
> * 192 GiB RAM (12 * 16GiB DIMM DDR4 Synchronous Registered (Buffered)
> 2666 MHz (0.4 ns))
> * 2 * SSD SATA Samsung MZ7KM240HMHQ0D3 (one is used for the WAL and the
> other for the data)
> * 10 Gbps network link
> * OS: Debian 11
> * Postgres 13 from apt
> 
> (I've also written a stackoverflow post about it -
> https://stackoverflow.com/questions/71631348/postgresql-bottleneck-neither-cpu-network-nor-i-o
> <https://stackoverflow.com/questions/71631348/postgresql-bottleneck-neither-cpu-network-nor-i-o>
> )
> <https://stackoverflow.com/questions/71631348/postgresql-bottleneck-neither-cpu-network-nor-i-o>
>     
> performance - Postgresql bottleneck neither CPU, network nor I/O - Stack
> Overflow
> <https://stackoverflow.com/questions/71631348/postgresql-bottleneck-neither-cpu-network-nor-i-o>
> We are testing our application for performance, which is using
> Postgresql 13 as a database. It is very insert and update heavy and we
> cannot get more than 65k TPS on the database. But none of the m...
> stackoverflow.com
> 
> 
> 
> Below is just an example of the pgbench I ran:
> 
> pgbench -i -s 50 -U postgres -h <DB_HOST> -d <DB_NAME>
> pgbench -c 64 -j 32 -t 100000 -h <DB_HOST> -U postgres <DB_NAME>
> 

I'd bet you need to use "pgbench -N" because the regular transaction
updates the "branch" table, and you only have 50 branches. Which
probably means a lot of conflicts and locking. The other thing you might
try is "-M prepared" which saves time on query planning.

FWIW I really doubt "fsync=off" will give you any meaningful results.

Maybe try assessing the hardware capability first, using tools like fio
to measure IOPS with different workloads.

Then try pgbench with a single client, and finally increase the number
of clients and see how it behaves and compare it to what you expect.

In any case, every system has a bottleneck. You're clearly hitting one,
otherwise the numbers would go faster. Usually, it's either CPU bound,
in which case "perf top" might tell us more, or it's IO bound, in which
case try e.g. "iostat -x -k 1" or something.

regards

-- 
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Hi everyone,

I am a bachelor's student and writing my thesis about the scaling and

performance of an application. The application is using postgresql as a

database but we can't scale any further currently as it seems postgres

is hitting the limit.

With the application, as well as with pgbench, we don't get more than

(max) 70k TPS on postgres. But the servers' resources are not utilized

completely (more below).

I would try monitoring using "perf top" and "atop -d" to see what is going on on the system. Also, try using sar to figure out what's going on. Are you paging, waiting for I/O or having some other kind of bottleneck. Once you figure where is your system spending time, you can address the problem. In addition to that, analyze the log files with pgbadger to find out which queries are time consuming and try optimizing them.

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com