How to find all SQLs executed by a transaction id?
Hello All, I am working on workload testing on a PostgreSQL database. Use case: Run workload of 5000 to 11000 transactions and a transaction should have Inserts, Selects, Updates and Selects I am using HammerDB, an open source tool to generate work load, and my question here is how to generate workload metrics by transactions per second. Question: Is there a way to get a metrics of queries executed by transactions and the execution times of each SQL with a transaction? Thanks, Ravi
significant jump in sql statement timing for on server vs a remote connection
Hi all; We are debugging a sql performance issue. We have a sql file with 50,000 simple select statements in it. If I run the file locally it completes in less than 15sec. If I force the local connection to be a tcp/ip connection via psql -h and I get approximately the same results, 15 - 16sec. However if we move the file to another server in the same network and run with a psql -h then it runs for more than 10min. Are there any postgres specific issues / settings / connection overhead we should look at? Or is this simply a network issue and fully outside the scope of the postgres database? FYI: postgresql 13 1.5TB of RAM 512GB of buffer_pool 10GB of work_mem Thanks in advance
Re: significant jump in sql statement timing for on server vs a remote connection
On Tue, Apr 19, 2022 at 03:00:09PM -0600, Sbob wrote: > We are debugging a sql performance issue. We have a sql file with 50,000 > simple select statements in it. If I run the file locally it completes in > less than 15sec. If I force the local connection to be a tcp/ip connection > via psql -h and I get approximately the same results, 15 - 16sec. > > > However if we move the file to another server in the same network and run > with a psql -h then it runs for more than 10min. Are there any postgres > specific issues / settings / connection overhead we should look at? Or is > this simply a network issue and fully outside the scope of the postgres > database? What OS ? What kind of authentication are you using ? Is there a connection pooler involved ? Did you try like that ? Did you test how long it takes to run 10k empty statements locally vs remotely ? time yes 'SELECT;' |head - |psql ... >/dev/null -- Justin
Re: significant jump in sql statement timing for on server vs a remote connection
On Tue, Apr 19, 2022 at 5:00 PM Sbob wrote: > > However if we move the file to another server in the same network and > run with a psql -h then it runs for more than 10min. What is the ping time? Packet loss? You can't take for granted that the network is good and fast just because they are on the same LAN. Cheers, Jeff
