SQLite based local cache

[dkarlovi]

Testing the completion feature from #10320, the one thing that stands out is completion for available packages being rather slow, at times even seeming like it's not working (a second or two pass between the interaction and the response).

This is somewhat expected since it's based on current search feature. Testing it, I'm getting typical responses of about 800msec, going up to 1500msec. This, while it works, is not great. This might improve if the search gets "vendor only" mode as discussed in #10325, but we'll still need to use the current name-only search once the vendor is established.

I'm wondering if it would be possible to have some sort of SQLite based, actionable local metadata index for simpler operations, sort of like DNF and APT do too? It would be an optional extension, only if ext-sqlite is found would it be enabled / used.

It would work sort of like:

• pull the basic information (like vendors and packages), this would be used for a while and occasionally (say, 1h TTL) a diff would be fetched from Packagist
• this could be enough info to power most of the general stuff, it could power vendor & name-only searches, maybe other stuff too

Alternative is of course to improve the search speed on Packagist itself, but I'd expect that part isn't exactly trivial. Interestingly, name-only search is 50% slower for me than regular search, I guess the latter one is powered by Algolia?

[GromNaN]

I benched the command composer search --only-name ^composer/ -vvv.

Full output

Running 2.2-dev+f5ffedfe60b5b0043c368b91e656288517aad0d9 (2021-11-30 13:33:38) with PHP 8.0.12 on Darwin / 20.2.0
Loading config file /root/.composer/config.json
Loading config file /root/.composer/auth.json
Reading /root/.composer/composer.json
Loading config file /root/.composer/config.json
Loading config file /root/.composer/auth.json
Loading config file /root/.composer/composer.json (/root/.composer/composer.json)
Loading config file /root/.composer/auth.json
Reading /root/.composer/auth.json
Checked CA file /usr/local/etc/ca-certificates/cert.pem: valid
Executing command (/root/.composer): git branch -a --no-color --no-abbrev -v
Executing command (/root/.composer): git describe --exact-match --tags
Executing command (CWD): git --version
Executing command (/root/.composer): git log --pretty="%H" -n1 HEAD --no-show-signature
Executing command (/root/.composer): hg branch
Executing command (/root/.composer): fossil branch list
Executing command (/root/.composer): fossil tag list
Executing command (/root/.composer): svn info --xml
Reading /root/.composer/vendor/composer/installed.json
Loading plugin PackageVersions\Installer (from composer/package-versions-deprecated)
Loading config file /root/.composer/config.json
Loading config file /root/.composer/auth.json
Executing command (/root): git branch -a --no-color --no-abbrev -v
Executing command (/root): git describe --exact-match --tags
Executing command (/root): git log --pretty="%H" -n1 HEAD --no-show-signature
Executing command (/root): hg branch
Executing command (/root): fossil branch list
Executing command (/root): fossil tag list
Executing command (/root): svn info --xml
Reading /root/.composer/composer.json
Loading config file /root/.composer/config.json
Loading config file /root/.composer/auth.json
Loading config file /root/.composer/composer.json (/root/.composer/composer.json)
Loading config file /root/.composer/auth.json
Reading /root/.composer/auth.json
Reading /root/.composer/vendor/composer/installed.json
Loading plugin PackageVersions\Installer_composer_tmp0 (from composer/package-versions-deprecated, installed globally)
Downloading https://repo.packagist.org/packages.json
[200] https://repo.packagist.org/packages.json
Writing /root/.composer/cache/repo/https---repo.packagist.org/packages.json into cache
Downloading https://packagist.org/packages/list.json
[200] https://packagist.org/packages/list.json
composer/ca-bundle
composer/composer
composer/installers
composer/metadata-minifier
composer/package-versions-deprecated
composer/pcre
composer/satis
composer/semver
composer/spdx-licenses
composer/xdebug-handler`

Blackfire trace: https://blackfire.io/profiles/ba2b0131-4cc5-4d30-af89-98bc2a92490c/graph

1. Listing is always re-downloaded, even with consecutive runs.

• https://repo.packagist.org/packages.json has no cache cache-control: private, max-age=0, no-cache, while file is written (and never read?).
• https://packagist.org/packages/list.json has 5 minutes shared-cache cache-control: public, s-maxage=300 but nothing local.
• It should be possible to add etag or last-modified in order to leverage 304 responses.

2. 324 200 calls to preg_match spend 309 ms. For completion usage, it can be replaced by str_starts_with (or the polyfill)

[dkarlovi]

@GromNaN nice! Would be a nice tweak for this specific search special case indeed, maybe fast enough to not require anything else.

[dkarlovi]

Having said that, the SQLite based approach would likely be a fraction of the time even with this fix since it would have an index prepared.

[Seldaek]

The search stuff was definitely not built with such a high poll rate in mind, so yeah this is not surprising you're hitting some roadblocks, but I think most of it can be fixed fairly easily.

• https://repo.packagist.org/packages.json is read to find out about https://packagist.org/packages/list.json
• https://repo.packagist.org/packages.json is sent with last modified, but not retrieved using the if-modified-since, fixed in 7c8267f
• preg_match is gone now, fixed in 128a264

This should help a bit already. Ideally we'd also cache the full list.json file locally but I'm in a rush now so can do some other time.

I think once we have that, and possibly an optimization to search by vendor first it should be fine without introducing sqlite (which sounds to me way overkill here.. it's an array of names).

Once we have the vendor name btw we can also optimize ComposerRepository::search() to use https://packagist.org/packages/list.json?vendor=x which should return way less data, although if we cache the whole list maybe it's still faster to work from local cache.