NuGet package restore fails due to timeout - poor performance

[Mik4sa]

Description

As part of our build pipelines we create multiple NuGet packages which results in about 100 new packages / package versions a day. Currently we have a total of 52.488 packages with a total size of 4.4 GiB. Since some time the NuGet restore in our pipelines fail with on of the following messages (there might be even more):

GET https://*****/api/packages/*****/nuget/package/*****/index.json
Retrying 'FindPackagesByIdAsync' for source 'https://*****/api/packages/*****/nuget/package/*****/index.json'.
The HTTP request to 'GET https://*****/api/packages/*****/nuget/package/*****/index.json' has timed out after 100000ms.

OR

error NU1301: Fehler beim Abrufen von Informationen zu "*****" aus der Remotequelle "https://*****/api/packages/*****/nuget/package/*****/index.json"

With means something like:

error NU1301: Error while retrieving information about "*****" from remote source "https://*****/api/packages/*****/nuget/package/*****/index.json"

Note: For the single package above we have currently a total of 3609 versions.

---

So NuGet tries to gether some information but times out after 10s. I can reproduce it when opening the url from above in the browser. It takes ages to finish. So the performance seems really bad when having many packages.

The SQL Server Management Studio shows this query as a resource intensive query:

SELECT [id], [ref_type], [ref_id], [name], [value] FROM [package_property] WHERE (ref_type = @p1 AND ref_id = @p2)

As a workaroud we created the following index for these columns which made the performance a bit better. The pipeline was able to run again. But the performance is still quite slow. It could be better/faster

CREATE NONCLUSTERED INDEX [NonClusteredIndex-20230525-113932] ON [dbo].[package_property]
(
	[ref_type] ASC,
	[ref_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

The studio printed some more resource intensive queries. I'm not sure whether they are relevant, I didn't digged any deeper:

SELECT TOP 1 [id], [version_id], [blob_id], [name], [lower_name], [composite_key], [is_lead], [created_unix] FROM [package_file] WHERE [version_id]=@p1 AND [lower_name]=@p2
SELECT TOP 1 [id], [owner_id], [repo_id], [type], [name], [lower_name], [semver_compatible], [is_internal] FROM [package] WHERE [id]=@p1
SELECT TOP 1 * FROM [package_version] INNER JOIN [package] ON package.id = package_version.package_id WHERE package_version.is_internal=@p1 AND package.owner_id=@p2 AND package.type=@p3 AND package.lower_name=@p4 AND package_version.lower_version=@p5 ORDER BY [package_version].[created_unix] DESC
SELECT count(*) FROM [package_version] LEFT JOIN [package_version] [pv2] ON (package_version.package_id = pv2.package_id AND (package_version.created_unix < pv2.created_unix OR (package_version.created_unix = pv2.created_unix AND package_version.id < pv2.id))) AND pv2.is_internal=@p1 INNER JOIN [package] ON package.id = package_version.package_id WHERE package_version.is_internal=@p2 AND package.owner_id=@p3 AND (pv2.id IS NULL)
SELECT * FROM [package_version] INNER JOIN [package] ON package.id = package_version.package_id WHERE package_version.is_internal=@p1 AND package.owner_id=@p2 AND package.type=@p3 AND package.lower_name=@p4 ORDER BY [package_version].[created_unix] DESC

The problem with the workaround/index is that it gets deleted once gitea is restarted.

Gitea Version

1.20.0 built with GNU Make 4.1, go1.20.6 : bindata, sqlite, sqlite_unlock_notify

Can you reproduce the bug on the Gitea demo site?

No

Log Gist

No response

Screenshots

No response

Git Version

2.39.1, Wire Protocol Version 2 Enabled

Operating System

Windows Server 2016 - Version 1607 (Build 14393.6085)

How are you running Gitea?

• Using gitea-1.20.0-windows-4.0-amd64.exe from your download page
• Registered as a Windows service with the following command line: D:\gitea\gitea.exe web --config D:\gitea\custom\conf\app.ini

Database

MSSQL

[KN4CK3R]

Should be fixed with #26075.

[Mik4sa]

Great! This sounds fantastic :)
Will have a look at it once merged and published

[KN4CK3R]

Out of curiosity, why do you have so many versions? Do you build and store the package on every commit?

[Mik4sa]

Yes. For now this is actually the case. Also we don't use the Cleanup Rules feature yet.