Re: IO related waits

On 9/17/24 12:34, veem v wrote:
>
> On Tue, 17 Sept 2024 at 21:24, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com
> <mailto:adrian(dot)klaver(at)aklaver(dot)com>> wrote:
>
>
> Which means you need to on Flink end:
>
> 1) Use Flink async I/O .
>
> 2) Find a client that supports async or fake it by using multiple
> synchronous clients.
>
> On Postgres end there is this:
>
> https://www.postgresql.org/docs/current/wal-async-commit.html
> <https://www.postgresql.org/docs/current/wal-async-commit.html>
>
> That will return a success signal to the client quicker if
> synchronous_commit is set to off. Though the point of the Flink async
> I/O is not to wait for the response before moving on, so I am not sure
> how much synchronous_commit = off would help.
>
>
>  Got it. So it means their suggestion was to set the asynch_io at flink
> level but not DB level, so that the application will not wait for the
> commit response from the database. But in that case , won't it overload
> the DB with more and more requests if database will keep doing the
> commit ( with synchronous_commit=ON)  and waiting for getting the
> response back from its storage for the WAL's to be flushed to the disk,
> while the application will not wait for its response back(for those
> inserts) and keep flooding the database with more and more incoming
> Insert requests?

My point is this is a multi-layer cake with layers:

1) Flink asycnc io

2) Database client async/sync

3) Postgres sync status.

That is a lot of moving parts and determining whether it is suitable is
going to require rigorous testing over a representative data load.

See more below.

>
> Additionally as I mentioned before, we see that from "pg_stat_database"
> from the column "xact_commit" , it's almost matching with the sum of
> "tup_inserted", "tup_updated", "tup_deleted" column. And also we
> verified in pg_stats_statements the  "calls" column is same as the
> "rows" column for the INSERT queries, so it means also we are inserting
> exactly same number of rows as the number of DB calls, so doesn't it
> suggest that we are doing row by row operations/dmls.
>
> Also after seeing above and asking application team to do the batch
> commit ,we are still seeing the similar figures from pg_stat_database
> and pg_stat_statements, so does it mean that we are looking into wrong
> stats? or the application code change has not been done accurately? and
> we see even when no inserts are running from the application side, we do
> see "xact_commit" keep increasing along with "tup_fetched" , why so?
>
> Finally we see in postgres here, even if we just write a DML statement
> it does commit that by default, until we explicitly put it in a
> "begin... end" block. Can that be the difference between how a "commit"
> gets handled in postgres vs other databases?

It does if autocommit is set in the client, that is common to other
databases also:

https://dev.mysql.com/doc/refman/8.4/en/commit.html

https://docs.oracle.com/en/database/oracle/developer-tools-for-vscode/getting-started/disabling-and-enabling-auto-commit.html

https://learn.microsoft.com/en-us/sql/t-sql/statements/set-implicit-transactions-transact-sql?view=sql-server-ver16

You probably need to take a closer look at the client/driver you are
using and the code that interacting with it.

In fact I would say you need to review the entire data transfer process
to see if there are performance gains that can be obtained without
adding an entirely new async component.

>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com