Horton

Re: Improving connection scalability: GetSnapshotData()

Andres Freund <andres@anarazel.de>

View thread View on postgr.es
From: Andres Freund <andres@anarazel.de>
To: Konstantin Knizhnik <k.knizhnik@postgrespro.ru>
Cc: pgsql-hackers@lists.postgresql.org
Date: 2020-09-04T19:11:31Z
Lists: pgsql-hackers

Attachments

On 2020-09-04 11:53:04 -0700, Andres Freund wrote:
> There's a seperate benchmark that I found to be quite revealing that's
> far less dependent on scheduler behaviour. Run two pgbench instances:
> 
> 1) With a very simply script '\sleep 1s' or such, and many connections
>    (e.g. 100,1000,5000). That's to simulate connections that are
>    currently idle.
> 2) With a normal pgbench read only script, and low client counts.
> 
> Before the changes 2) shows a very sharp decline in performance when the
> count in 1) increases. Afterwards its pretty much linear.
> 
> I think this benchmark actually is much more real world oriented - due
> to latency and client side overheads it's very normal to have a large
> fraction of connections idle in read mostly OLTP workloads.
> 
> Here's the result on my workstation (2x Xeon Gold 5215 CPUs), testing
> 1f42d35a1d6144a23602b2c0bc7f97f3046cf890 against
> 07f32fcd23ac81898ed47f88beb569c631a2f223 which are the commits pre/post
> connection scalability changes.
> 
> I used fairly short pgbench runs (15s), and the numbers are the best of
> three runs. I also had emacs and mutt open - some noise to be
> expected. But I also gotta work ;)
> 
> | Idle Connections | Active Connections | TPS pre | TPS post |
> |-----------------:|-------------------:|--------:|---------:|
> |                0 |                  1 |   33599 |    33406 |
> |              100 |                  1 |   31088 |    33279 |
> |             1000 |                  1 |   29377 |    33434 |
> |             2500 |                  1 |   27050 |    33149 |
> |             5000 |                  1 |   21895 |    33903 |
> |            10000 |                  1 |   16034 |    33140 |
> |                0 |                 48 | 1042005 |  1125104 |
> |              100 |                 48 |  986731 |  1103584 |
> |             1000 |                 48 |  854230 |  1119043 |
> |             2500 |                 48 |  716624 |  1119353 |
> |             5000 |                 48 |  553657 |  1119476 |
> |            10000 |                 48 |  369845 |  1115740 |

Attached in graph form.

Greetings,

Andres Freund

Commits

  1. Try to unbreak 021_row_visibility.pl on mingw.

  2. Fix and test snapshot behavior on standby.

  3. Fix race condition in snapshot caching when 2PC is used.

  4. snapshot scalability: cache snapshots using a xact completion counter.

  5. Fix use of wrong index in ComputeXidHorizons().

  6. Make vacuum a bit more verbose to debug BF failure.

  7. snapshot scalability: Introduce dense array of in-progress xids.

  8. snapshot scalability: Move PGXACT->vacuumFlags to ProcGlobal->vacuumFlags.

  9. snapshot scalability: Move subxact info to ProcGlobal, remove PGXACT.

  10. snapshot scalability: Move PGXACT->xmin back to PGPROC.

  11. snapshot scalability: Don't compute global horizons while building snapshots.

  12. BRIN: Handle concurrent desummarization properly

  13. Track latest completed xid as a FullTransactionId.

  14. Rename VariableCacheData.nextFullXid to nextXid.

  15. snapshot scalability: Move delayChkpt from PGXACT to PGPROC.

  16. Report progress of CREATE INDEX operations