Movatterモバイル変換


[0]ホーム

URL:


Skip to content
DEV Community
Log in Create account

DEV Community

YugabyteDB profile imageFrits Hoogland
Frits Hoogland forYugabyteDB

Posted on • Edited on

     

Postgres first database connection latency

This post is about a lesser known 'feature' of postgres: the loading or creation of the pg_internal.init files (plural) that happens when logging on to a postgres database. Because YugabyteDBreuses the postgres source, YugabyteDB performs dealing with the pg_internal.init files identical to postgres.

Let me first talk about why I am performing this writeup. During testing I noticed the first connection to postgres taking more time than successive logons. This is easy to miss, because when I tested again, I didn't see it anymore. Then when the postmaster was restarted, the very first connection showed higher latency again. This means that in specific cases, which is if these deal with databases that are accessed just after postmaster startup, there might be extra work performed that increases logon latency. This is important for doing automated tests on newly build or created databases.

Before diving into the details: is this important for concurrent postgres/YugabyteDB access, and does it influence regular database activity: no(!)

The phenomenon of performing extra work to create the pg_internal.init files only happens with the very first backend to logon to a database on a given postmaster. So, why this writeup, where is this important for? This is important for latency testing, to understand this clear outlier.

Closely related to the latency differences for logging on: the first connection latency is influenced by the number of objects in the database, and can get significant with high (relative to processing power ~ > 1000) number of objects, where YugabyteDB is more sensitive to this than stock postgres, because YugabyteDB loads the catalog data from the master leader, whilst stock postgres loads it from local files or the buffer cache.

So, what is happening here?
During the logon to a database in postgres, the backend is created by forking the postmaster, and the backend builds caches in its private memory. To be more precise: each individual backend creates caches with the needed minimal catalog data into its private memory so it has just enough catalog data to function.

As part of the backend creation, it creates a cache called 'relcache' or relation cache, which contains relation descriptors.These descriptors are quite comprehensive, which is why the very first session to enter a database after postmaster startup will gather theglobal relcache information, and gather thelocal, database specific relcache information, andwrite these to a file in memory format. This way, any following session can use the files to load its relcache, without the need to perform the work to obtain all the information for each relation in the relcache.

Again: if you have a normal functioning postgres or YugabyteDB database cluster, this phenomenon should not influence normal, daily operations. However, in specific situations, such as carefully timing logon after builds, this is very important to understand, because the first logon will have a higher latency.

To see the effect of the pg_internal.init files creation, you can use the [connection-profiler project].(https://github.com/fritshoogland-yugabyte/connection-profiler.git).

install connection-profiler

In order to install connection-profiler, you need rust to compile the connection-profiler, and you need git to clone the connection-profiler project.

Get git and gcc

sudo yum install git gcc -y
Enter fullscreen modeExit fullscreen mode

Clone the connection-profiler repository

git clone https://github.com/fritshoogland-yugabyte/connection-profiler.git
Enter fullscreen modeExit fullscreen mode

Get rust

curl https://sh.rustup.rs -sSf | sh -s -- -y
Enter fullscreen modeExit fullscreen mode

Source the rust environment

source "$HOME/.cargo/env"
Enter fullscreen modeExit fullscreen mode

Go into the connection-profiler directory and build a production (release) binary:

cd connection-profilercargo build --release
Enter fullscreen modeExit fullscreen mode

Once finished, the executable is in thetarget/release directory with the nameconnection-profiler.

PostgreSQL

To test with postgres startup the postmaster. Like hopefully is clear from the description: do not attach with any client before the testing, or that client will have performed the initialisation of the pg_internal.init files.

Now run the connection-profiler by performing a number (5) connections, not executing any SQL:

[postgres@localhost connection-profiler]$ ./target/release/connection-profiler -u "user=postgres password=postgres port=5432 host=localhost" -q '' -n 5create_connection                              5001 us------------------------------------------------------------============================================================create_connection                              2724 us------------------------------------------------------------============================================================create_connection                              2523 us------------------------------------------------------------============================================================create_connection                              2365 us------------------------------------------------------------============================================================create_connection                              2380 us------------------------------------------------------------============================================================
Enter fullscreen modeExit fullscreen mode

Clearly, the first connection is slower than the other connections. If the same sequence of connecting 5 times is repeated, the first connection does not show the higher latency again:

[postgres@localhost connection-profiler]$ ./target/release/connection-profiler -u "user=postgres password=postgres port=5432 host=localhost" -q '' -n 5create_connection                              2602 us------------------------------------------------------------============================================================create_connection                              3074 us------------------------------------------------------------============================================================create_connection                              2799 us------------------------------------------------------------============================================================create_connection                              2319 us------------------------------------------------------------============================================================create_connection                              2503 us------------------------------------------------------------============================================================
Enter fullscreen modeExit fullscreen mode

YugabyteDB

YugabyteDB uses the postgres source-code, and there behaves identical. However, the principal difference of YugabyteDB versus postgres is that the storage is changed to networked sources. This gives a lot of flexibility and scalability, but currently means extra latency is introduced. This is how the latencies look like on a small, local virtual machine based cluster:

[vagrant@yb-1 connection-profiler]$ ./target/release/connection-profiler -u "user=yugabyte password=yugabyte port=5433 host=localhost" -q '' -n 5create_connection                            187654 us------------------------------------------------------------============================================================create_connection                             22404 us------------------------------------------------------------============================================================create_connection                             17495 us------------------------------------------------------------============================================================create_connection                             17815 us------------------------------------------------------------============================================================create_connection                             18213 us------------------------------------------------------------============================================================
Enter fullscreen modeExit fullscreen mode

Redoing the repeated connections gives more consistent response times:

[vagrant@yb-1 connection-profiler]$ ./target/release/connection-profiler -u "user=yugabyte password=yugabyte port=5433 host=localhost" -q '' -n 5------------------------------------------------------------============================================================create_connection                             18996 us------------------------------------------------------------============================================================create_connection                             19581 us------------------------------------------------------------============================================================create_connection                             21980 us------------------------------------------------------------============================================================create_connection                             21924 us------------------------------------------------------------============================================================
Enter fullscreen modeExit fullscreen mode

As indicated: the higher latency is something we are working on, on making it better, although it's not extremely far off of native postgres latency.

The first connection above which builds the pg_internal.init files will request catalog data to build these files, which is done to its proxy thread in its local tablet server, which then performs an RPC request to the current master leader for the actual catalog data.

Top comments(0)

Subscribe
pic
Create template

Templates let you quickly answer FAQs or store snippets for re-use.

Dismiss

Are you sure you want to hide this comment? It will become hidden in your post, but will still be visible via the comment'spermalink.

For further actions, you may consider blocking this person and/orreporting abuse

Open Source Distributed PostgreSQL

Join the Yugabyte Cloud today, and start using a distributed, PostgreSQL-compatible, DBaaS.

More fromYugabyteDB

DEV Community

We're a place where coders share, stay up-to-date and grow their careers.

Log in Create account

[8]ページ先頭

©2009-2025 Movatter.jp