Perf improvements by NinoFloris · Pull Request #5245 · npgsql/npgsql

NinoFloris · 2023-08-31T13:38:26Z

Based on handler rework, splits out the non-essential perf improvements for easier reviewing.

# Conflicts: # src/Npgsql/NpgsqlCommand.cs

Improves code flow for the hot path

# Conflicts: # src/Npgsql/Internal/NpgsqlConnector.FrontendMessages.cs

vonzshik

Had a bit of free time, here's partial (16/33) review, will probably finish either this evening or tomorrow.

src/Npgsql/PreparedStatement.cs

src/Npgsql/NpgsqlDataReader.cs

src/Npgsql/BackendMessages/CommandCompleteMessage.cs

src/Npgsql/Internal/PgReader.cs

# Conflicts: # src/Npgsql/Internal/NpgsqlConnector.FrontendMessages.cs

# Conflicts: # src/Npgsql/Internal/NpgsqlReadBuffer.cs

# Conflicts: # src/Npgsql/Internal/PgReader.cs

# Conflicts: # src/Npgsql/NpgsqlDataReader.cs

…o entry

vonzshik

33/33, just left to refine it a bit.

src/Npgsql/MetricsReporter.cs

src/Npgsql/MultiplexingDataSource.cs

src/Npgsql/NpgsqlDataReader.cs

src/Npgsql/NpgsqlBatchCommand.cs

roji · 2023-11-02T18:00:29Z

And here are some perf numbers for fortunes/single_query platform:

Fortunes

db	fortunes-5c106023	fortunes-4b5b897a
Max CPU Usage (%)	36	37	+2.78%
Max Cores usage (%)	1,006	1,043	+3.68%
Max Working Set (MB)	54	54	0.00%
Build Time (ms)	1,233	1,271	+3.08%
Start Time (ms)	1,228	1,110	-9.61%
Published Size (KB)	368,923	368,923	0.00%

application	fortunes-5c106023	fortunes-4b5b897a
Max CPU Usage (%)	96	96	0.00%
Max Cores usage (%)	2,687	2,675	-0.45%
Max Working Set (MB)	585	587	+0.34%
Max Private Memory (MB)	1,332	1,346	+1.05%
Build Time (ms)	3,632	3,680	+1.32%
Start Time (ms)	1,904	2,000	+5.04%
Published Size (KB)	101,587	101,587	0.00%
Symbols Size (KB)	54	54	0.00%
.NET Core SDK Version	9.0.100-alpha.1.23552.4	9.0.100-alpha.1.23552.4

load	fortunes-5c106023	fortunes-4b5b897a
Max CPU Usage (%)	40	41	+2.50%
Max Cores usage (%)	1,116	1,151	+3.14%
Max Working Set (MB)	48	48	0.00%
Max Private Memory (MB)	363	363	0.00%
Build Time (ms)	4,983	3,423	-31.31%
Start Time (ms)	0	0
Published Size (KB)	73,211	73,211	0.00%
Symbols Size (KB)	0	0
.NET Core SDK Version	7.0.403	7.0.403
First Request (ms)	98	95	-3.06%
Requests/sec	479,772	497,363	+3.67%
Requests	7,243,924	7,510,167	+3.68%
Mean latency (ms)	1.13	1.07	-5.31%
Max latency (ms)	29.32	17.65	-39.80%
Bad responses	0	0
Socket errors	0	0
Read throughput (MB/s)	629.58	652.67	+3.67%
Latency 50th (ms)	1.00	0.96	-4.00%
Latency 75th (ms)	1.22	1.17	-4.10%
Latency 90th (ms)	1.53	1.49	-2.61%
Latency 99th (ms)	4.06	3.92	-3.45%

Single query

db	single_query-5c106023	single_query-4b5b897a
Max CPU Usage (%)	37	38	+2.70%
Max Cores usage (%)	1,043	1,077	+3.26%
Max Working Set (MB)	56	55	-1.79%
Build Time (ms)	1,261	1,293	+2.54%
Start Time (ms)	1,167	1,083	-7.20%
Published Size (KB)	368,923	368,923	0.00%

application	single_query-5c106023	single_query-4b5b897a
Max CPU Usage (%)	97	97	0.00%
Max Cores usage (%)	2,705	2,705	0.00%
Max Working Set (MB)	596	571	-4.19%
Max Private Memory (MB)	1,344	1,322	-1.64%
Build Time (ms)	3,470	3,303	-4.81%
Start Time (ms)	1,989	2,044	+2.77%
Published Size (KB)	101,587	101,587	0.00%
Symbols Size (KB)	54	54	0.00%
.NET Core SDK Version	9.0.100-alpha.1.23552.4	9.0.100-alpha.1.23552.4

load	single_query-5c106023	single_query-4b5b897a
Max CPU Usage (%)	40	41	+2.50%
Max Cores usage (%)	1,110	1,136	+2.34%
Max Working Set (MB)	49	48	-2.04%
Max Private Memory (MB)	363	363	0.00%
Build Time (ms)	3,324	3,411	+2.62%
Start Time (ms)	0	0
Published Size (KB)	73,211	73,211	0.00%
Symbols Size (KB)	0	0
.NET Core SDK Version	7.0.403	7.0.403
First Request (ms)	97	107	+10.31%
Requests/sec	499,078	508,344	+1.86%
Requests	7,535,737	7,675,807	+1.86%
Mean latency (ms)	1.06	1.04	-1.89%
Max latency (ms)	17.97	16.02	-10.85%
Bad responses	0	0
Socket errors	0	0
Read throughput (MB/s)	72.24	73.58	+1.85%
Latency 50th (ms)	0.99	0.97	-2.02%
Latency 75th (ms)	1.11	1.09	-1.80%
Latency 90th (ms)	1.26	1.24	-1.59%
Latency 99th (ms)	3.51	3.65	+3.99%

Fortunes, before the perf PR (5c10602)

crank --config https://raw.githubusercontent.com/aspnet/Benchmarks/main/scenarios/platform.benchmarks.yml --scenario fortunes --profile aspnet-citrine-lin --application.framework net9.0 --application.options.outputFiles C:\projects\npgsql\src\Npgsql\bin\Release\net8.0\Npgsql.dll --json c:\temp\benchmarks\FortunesPlatform\fortunes-5c106023.json

| db                   |         |
| -------------------- | ------- |
| Max CPU Usage (%)    | 36      |
| Max Cores usage (%)  | 1,006   |
| Max Working Set (MB) | 54      |
| Build Time (ms)      | 1,233   |
| Start Time (ms)      | 1,228   |
| Published Size (KB)  | 368,923 |


| application             |                                    |
| ----------------------- | ---------------------------------- |
| Max CPU Usage (%)       | 96                                 |
| Max Cores usage (%)     | 2,687                              |
| Max Working Set (MB)    | 585                                |
| Max Private Memory (MB) | 1,332                              |
| Build Time (ms)         | 3,632                              |
| Start Time (ms)         | 1,904                              |
| Published Size (KB)     | 101,587                            |
| Symbols Size (KB)       | 54                                 |
| .NET Core SDK Version   | 9.0.100-alpha.1.23552.4            |
| ASP.NET Core Version    | 9.0.0-alpha.1.23552.3+c90397541f23 |
| .NET Runtime Version    | 9.0.0-alpha.1.23552.2+b54e9d3d33ab |


| load                    |                     |
| ----------------------- | ------------------- |
| Max CPU Usage (%)       | 40                  |
| Max Cores usage (%)     | 1,116               |
| Max Working Set (MB)    | 48                  |
| Max Private Memory (MB) | 363                 |
| Build Time (ms)         | 4,983               |
| Start Time (ms)         | 0                   |
| Published Size (KB)     | 73,211              |
| Symbols Size (KB)       | 0                   |
| .NET Core SDK Version   | 7.0.403             |
| ASP.NET Core Version    | 7.0.13+d634f2bd1ad6 |
| .NET Runtime Version    | 7.0.13+3f73a2f186e0 |
| First Request (ms)      | 98                  |
| Requests/sec            | 479,772             |
| Requests                | 7,243,924           |
| Mean latency (ms)       | 1.13                |
| Max latency (ms)        | 29.32               |
| Bad responses           | 0                   |
| Socket errors           | 0                   |
| Read throughput (MB/s)  | 629.58              |
| Latency 50th (ms)       | 1.00                |
| Latency 75th (ms)       | 1.22                |
| Latency 90th (ms)       | 1.53                |
| Latency 99th (ms)       | 4.06                |

Fortunes, after the perf PR (4b5b897)

crank --config https://raw.githubusercontent.com/aspnet/Benchmarks/main/scenarios/platform.benchmarks.yml --scenario fortunes --profile aspnet-citrine-lin --application.framework net9.0 --application.options.outputFiles C:\projects\npgsql\src\Npgsql\bin\Release\net8.0\Npgsql.dll --json c:\temp\benchmarks\FortunesPlatform\fortunes-4b5b897a.json

| db                   |         |
| -------------------- | ------- |
| Max CPU Usage (%)    | 37      |
| Max Cores usage (%)  | 1,043   |
| Max Working Set (MB) | 54      |
| Build Time (ms)      | 1,271   |
| Start Time (ms)      | 1,110   |
| Published Size (KB)  | 368,923 |


| application             |                                    |
| ----------------------- | ---------------------------------- |
| Max CPU Usage (%)       | 96                                 |
| Max Cores usage (%)     | 2,675                              |
| Max Working Set (MB)    | 587                                |
| Max Private Memory (MB) | 1,346                              |
| Build Time (ms)         | 3,680                              |
| Start Time (ms)         | 2,000                              |
| Published Size (KB)     | 101,587                            |
| Symbols Size (KB)       | 54                                 |
| .NET Core SDK Version   | 9.0.100-alpha.1.23552.4            |
| ASP.NET Core Version    | 9.0.0-alpha.1.23552.3+c90397541f23 |
| .NET Runtime Version    | 9.0.0-alpha.1.23552.2+b54e9d3d33ab |


| load                    |                     |
| ----------------------- | ------------------- |
| Max CPU Usage (%)       | 41                  |
| Max Cores usage (%)     | 1,151               |
| Max Working Set (MB)    | 48                  |
| Max Private Memory (MB) | 363                 |
| Build Time (ms)         | 3,423               |
| Start Time (ms)         | 0                   |
| Published Size (KB)     | 73,211              |
| Symbols Size (KB)       | 0                   |
| .NET Core SDK Version   | 7.0.403             |
| ASP.NET Core Version    | 7.0.13+d634f2bd1ad6 |
| .NET Runtime Version    | 7.0.13+3f73a2f186e0 |
| First Request (ms)      | 95                  |
| Requests/sec            | 497,363             |
| Requests                | 7,510,167           |
| Mean latency (ms)       | 1.07                |
| Max latency (ms)        | 17.65               |
| Bad responses           | 0                   |
| Socket errors           | 0                   |
| Read throughput (MB/s)  | 652.67              |
| Latency 50th (ms)       | 0.96                |
| Latency 75th (ms)       | 1.17                |
| Latency 90th (ms)       | 1.49                |
| Latency 99th (ms)       | 3.92                |

Single query, before the perf PR (5c10602)

crank --config https://raw.githubusercontent.com/aspnet/Benchmarks/main/scenarios/platform.benchmarks.yml --scenario single_query --profile aspnet-citrine-lin --application.framework net9.0 --application.options.outputFiles C:\projects\npgsql\src\Npgsql\bin\Release\net8.0\Npgsql.dll --json c:\temp\benchmarks\FortunesPlatform\single_query-5c106023.json

| db                   |         |
| -------------------- | ------- |
| Max CPU Usage (%)    | 37      |
| Max Cores usage (%)  | 1,043   |
| Max Working Set (MB) | 56      |
| Build Time (ms)      | 1,261   |
| Start Time (ms)      | 1,167   |
| Published Size (KB)  | 368,923 |


| application             |                                    |
| ----------------------- | ---------------------------------- |
| Max CPU Usage (%)       | 97                                 |
| Max Cores usage (%)     | 2,705                              |
| Max Working Set (MB)    | 596                                |
| Max Private Memory (MB) | 1,344                              |
| Build Time (ms)         | 3,470                              |
| Start Time (ms)         | 1,989                              |
| Published Size (KB)     | 101,587                            |
| Symbols Size (KB)       | 54                                 |
| .NET Core SDK Version   | 9.0.100-alpha.1.23552.4            |
| ASP.NET Core Version    | 9.0.0-alpha.1.23552.3+c90397541f23 |
| .NET Runtime Version    | 9.0.0-alpha.1.23552.2+b54e9d3d33ab |


| load                    |                     |
| ----------------------- | ------------------- |
| Max CPU Usage (%)       | 40                  |
| Max Cores usage (%)     | 1,110               |
| Max Working Set (MB)    | 49                  |
| Max Private Memory (MB) | 363                 |
| Build Time (ms)         | 3,324               |
| Start Time (ms)         | 0                   |
| Published Size (KB)     | 73,211              |
| Symbols Size (KB)       | 0                   |
| .NET Core SDK Version   | 7.0.403             |
| ASP.NET Core Version    | 7.0.13+d634f2bd1ad6 |
| .NET Runtime Version    | 7.0.13+3f73a2f186e0 |
| First Request (ms)      | 97                  |
| Requests/sec            | 499,078             |
| Requests                | 7,535,737           |
| Mean latency (ms)       | 1.06                |
| Max latency (ms)        | 17.97               |
| Bad responses           | 0                   |
| Socket errors           | 0                   |
| Read throughput (MB/s)  | 72.24               |
| Latency 50th (ms)       | 0.99                |
| Latency 75th (ms)       | 1.11                |
| Latency 90th (ms)       | 1.26                |
| Latency 99th (ms)       | 3.51                |

Single query, after the perf PR (4b5b897)

crank --config https://raw.githubusercontent.com/aspnet/Benchmarks/main/scenarios/platform.benchmarks.yml --scenario single_query --profile aspnet-citrine-lin --application.framework net9.0 --application.options.outputFiles C:\projects\npgsql\src\Npgsql\bin\Release\net8.0\Npgsql.dll --json c:\temp\benchmarks\FortunesPlatform\single_query-4b5b897a.json

| db                   |         |
| -------------------- | ------- |
| Max CPU Usage (%)    | 38      |
| Max Cores usage (%)  | 1,077   |
| Max Working Set (MB) | 55      |
| Build Time (ms)      | 1,293   |
| Start Time (ms)      | 1,083   |
| Published Size (KB)  | 368,923 |


| application             |                                    |
| ----------------------- | ---------------------------------- |
| Max CPU Usage (%)       | 97                                 |
| Max Cores usage (%)     | 2,705                              |
| Max Working Set (MB)    | 571                                |
| Max Private Memory (MB) | 1,322                              |
| Build Time (ms)         | 3,303                              |
| Start Time (ms)         | 2,044                              |
| Published Size (KB)     | 101,587                            |
| Symbols Size (KB)       | 54                                 |
| .NET Core SDK Version   | 9.0.100-alpha.1.23552.4            |
| ASP.NET Core Version    | 9.0.0-alpha.1.23552.3+c90397541f23 |
| .NET Runtime Version    | 9.0.0-alpha.1.23552.2+b54e9d3d33ab |


| load                    |                     |
| ----------------------- | ------------------- |
| Max CPU Usage (%)       | 41                  |
| Max Cores usage (%)     | 1,136               |
| Max Working Set (MB)    | 48                  |
| Max Private Memory (MB) | 363                 |
| Build Time (ms)         | 3,411               |
| Start Time (ms)         | 0                   |
| Published Size (KB)     | 73,211              |
| Symbols Size (KB)       | 0                   |
| .NET Core SDK Version   | 7.0.403             |
| ASP.NET Core Version    | 7.0.13+d634f2bd1ad6 |
| .NET Runtime Version    | 7.0.13+3f73a2f186e0 |
| First Request (ms)      | 107                 |
| Requests/sec            | 508,344             |
| Requests                | 7,675,807           |
| Mean latency (ms)       | 1.04                |
| Max latency (ms)        | 16.02               |
| Bad responses           | 0                   |
| Socket errors           | 0                   |
| Read throughput (MB/s)  | 73.58               |
| Latency 50th (ms)       | 0.97                |
| Latency 75th (ms)       | 1.09                |
| Latency 90th (ms)       | 1.24                |
| Latency 99th (ms)       | 3.65                |

fortunes-5c106023.json
single_query-5c106023.json
fortunes-4b5b897a.json
single_query-4b5b897a.json

NinoFloris force-pushed the handler-perf branch from 684d797 to cf1bd06 Compare September 25, 2023 23:18

NinoFloris marked this pull request as ready for review September 25, 2023 23:20

NinoFloris requested review from Brar, roji and vonzshik as code owners September 25, 2023 23:20

NinoFloris force-pushed the handler-perf branch from cf1bd06 to 3a829d8 Compare September 25, 2023 23:29

NinoFloris mentioned this pull request Oct 30, 2023

SingleQuery regression aspnet/Benchmarks#1923

Closed

NinoFloris force-pushed the handler-perf branch from 3a829d8 to 45dd271 Compare October 31, 2023 14:17

NinoFloris added 9 commits October 31, 2023 18:20

Lazily allocate parameters

4e7c9d3

# Conflicts: # src/Npgsql/NpgsqlCommand.cs

Take original CommandText if possible

c6c356c

Improve Ensure and ReadMessage allocation profile

be5f542

Some safe simplifications on TryFastRead and data row reads

9925cdb

Fix connector not being broken

1e614a8

Move data row method into process message

fb000cb

Improves code flow for the hot path

Allow TryRead in even more cases

f868fa0

Improve some more code

363910e

# Conflicts: # src/Npgsql/Internal/NpgsqlConnector.FrontendMessages.cs

Properly handle errors in TryRead

1514ebd

NinoFloris force-pushed the handler-perf branch 3 times, most recently from 718d14a to ae76ddb Compare October 31, 2023 18:00

vonzshik reviewed Nov 1, 2023

View reviewed changes

NinoFloris added 9 commits November 1, 2023 14:49

Improve parse speed for resultset reading

c0c3154

Improve forward seeking in non-sequential mode

8bb8ee1

Small speedups

e4920d5

Fix resuming non-sequential reads

1b05b7c

Improve NextResult perf

8d75faa

Store prepared statement name as bytes

86368da

# Conflicts: # src/Npgsql/Internal/NpgsqlConnector.FrontendMessages.cs

Connection open speedups

ef80569

Modernized (and slightly faster) command complete message

77688ac

Remove unused read methods

149b215

# Conflicts: # src/Npgsql/Internal/NpgsqlReadBuffer.cs

NinoFloris added 15 commits November 1, 2023 14:49

Fixes

28ea806

Move some more code around

571e0ee

# Conflicts: # src/Npgsql/Internal/PgReader.cs

Have separate sync and async methods for Commit

9cb01fc

# Conflicts: # src/Npgsql/NpgsqlDataReader.cs

Remove sign extension

4afd699

Remove unused write buffer methods

a3d05ce

Inline multiplexing WriteCommand

b708278

Make metrics and counter collection pay for play

4446a20

Replace UtcNow with cpu clock time

9a91c98

Move TypeToConvert to be a field on PgConverterInfo

71693e4

Don't lookup FieldDescription if we have a matching cached column inf…

058b4e7

…o entry

Reset only the absolute minimum amount of fields

5232c85

Re-use property values where possible

174e892

Write directly into oid/rows backing fields

04a3547

Add fast-path to RentArray

be3127a

Move to timestamp ticks

2c6d9f0

NinoFloris force-pushed the handler-perf branch from 8ec9157 to 2c6d9f0 Compare November 1, 2023 13:50

Rename and add comment

e64441d

vonzshik reviewed Nov 2, 2023

View reviewed changes

Address feedback

ecbcb9f

NinoFloris enabled auto-merge (squash) November 2, 2023 16:43

NinoFloris disabled auto-merge November 2, 2023 16:45

NinoFloris merged commit 4b5b897 into npgsql:main Nov 2, 2023

NinoFloris deleted the handler-perf branch November 2, 2023 16:51

NinoFloris added this to the 8.0.0 milestone Nov 7, 2023

Brar assigned NinoFloris Nov 21, 2023

NinoFloris mentioned this pull request Mar 13, 2024

Further investigate and improve perf for CommandBehavior.SequentialAccess #3453

Closed

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Perf improvements#5245

Perf improvements#5245
NinoFloris merged 35 commits intonpgsql:mainfrom
NinoFloris:handler-perf

NinoFloris commented Aug 31, 2023

Uh oh!

vonzshik left a comment

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

vonzshik left a comment

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

roji commented Nov 2, 2023 •

edited

Loading

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

3 participants

Conversation

NinoFloris commented Aug 31, 2023

Uh oh!

vonzshik left a comment

Choose a reason for hiding this comment

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

vonzshik left a comment

Choose a reason for hiding this comment

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

roji commented Nov 2, 2023 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Fortunes

Single query

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

3 participants

roji commented Nov 2, 2023 •

edited

Loading