A while back the question come up why MacPorts sometimes (and sometimes often) takes ages to return control to the user after the requested operations were all completed, and if anything could be done to reduce the duration.
The why question was easy to answer: it does a bit of registry database management. The registry of installed ports is stored in an sqlite3 database and MacPorts does a regular cleanup (VACUUM) on it to remove stale information.
The 2nd question was harder to answer, in part because the registry database requires an extension to be operated on with external tools and there was an explicit lack of willingness among the MP devs to help out with suggestions or instructions. After all, no one else complained so why would they...
I wouldn't be writing this if I did manage to answer the question and yes, the duration of that final limbo situation can be reduced considerably, possibly in a win-win situation with a somewhat smaller file size of the registry db. How much gain there is to be had will depend on the number of installed ports, the age of the installation and the drive on which the registry is stored. But to give an idea: I've seen the duration of that final VACUUM operation which is the main culprit decrease by a factor of about 3x to over 10x (the latter being on Linux with the registry stored on ZFS with LZ4 compression hosted on an SSD, the former on an otherwise much faster Mac but the registry stored on HFSX on a probably pretty fragmented partition of an SSHDD).
This is all done by tweaking just a single parameter: the db page_size. This is akin to the (virtual) sector size with which harddisks, SSDs and in practice also filesystems work: an sql database can in fact be thought of as a specialised filesystem. There are no other parameters to tweak (that haven't already been set at their optimum settings), but the granularity with which data is written to storage can have a big impact as anyone who has ever done some disk benchmarking knows. MacPorts has always used the default page_size, which evolved over time (with sqlite3 versions) from 1k (1024 bytes) to 2k to the current 4k. Really not very big if you realise that just the listing of a moderately large port like qt5-qtbase (port contents qt5-qtbase) is already about 220k (and the registry stores other information as well).
In a test installation of mine with 28 active ports (of 33 total installed) the total file listing (port contents installed) is 642263 bytes (627Kb) but the registry is 3.7Mb (with the default 4k page_size).
So, how do we achieve this. First, a word of warning: this is not without danger: MacPorts doesn't make backups of the registry so there's a real risk of bodging your install here.
In the procedure below I assume an installation in the standard /opt/local; replace this with your $prefix if it is different.
Step 1 : install port:sqlitebrowser Step 2 : install the required sqlite3 extension. AFAICT, the one provided by port:macports-sqlext is broken but you can get it from my port:MacPorts-devel in MacStrop:
> port -nok -v configure MacPorts-devel [your favourite build options here]
> gmake -C `port work MacPorts-devel`/MacPorts-git src/cregistry -w macports.sqlext
> [sudo] cp -pv `port work MacPorts-devel`/MacPorts-git src/cregistry/macports.sqlext /opt/local/libexec/macports/lib/registry2.0/macports.sqlext
Step 3 : configure sqlitebrowser to load that extension for every database you open, in the Extensions tab of the Preferences (I think that's safe!)
Step 4 : make certain that the registry database in /opt/local/var/macports/registry is clean, i.e. that the registry.db-wal file is empty (0 bytes). There is no specific recipe, but de-and-re-activating some ports, running rev-upgrade, calling port installed (all via sudo) should eventually have the intended effect.
Step 5 : make a backup (e.g. sudo cp -pv /opt/local/var/macports/registry/registry{,-backup-$(date)}.db).
!! IMPORTANT: do NOT do any MacPorts operations from this moment until the end of the procedure!!
Step 6 : make a copy in a different location where you have write access as yourself, NOT with sudo; give that copy a different name like registry-ps=64k.db .
Step 7 : open that copy: sqlitebrowser /path/to/registry-ps=64k.db &
You should see this message on the terminal:
creating collation sequence VERSION to sql_version() for database "/path/to/registry-ps=64k.db"
Step 7b (opt) : If you want, you can now go into the Execute SQL tab, type VACUUM; in the "SQL 1" tab and hit the "play" button above it. The panel at the bottom will print the time this took (should be close to the familiar "limbo duration"). You could do this a few times to get an average reference/before duration.
Step 8 : go into the "Edit Pragmas" tab. This is where the magic will happen. First thing to do is to set the Journal Mode setting to "Off" because otherwise sqlite3 will not allow you to change the page size. Make the change and hit the "Save" button. Now we can change the Page Size: pick 65536 from the drop-down menu and hit "Save". Remember to toggle the Journal Mode back to "WAL" when done (the UI becomes responsive again)!
This will take a while, possibly longer than the usual duration because the db is actually being rewritten completely. If it used a 4k page size it will go from N 4k "pages" of data to N/16 64k pages.
If you want you can now go back and repeat step 7b (opt) to see how much you gained. You could then even repeat Step 8 with as many different page sizes as you like.
Step 9 : quit sqlitebrowser.
You now have a more optimally performing registry-ps=64k.db file in your chosen location. It will not have the same size as the original. The db file of that test install of mine went from 3.7Mb to 5.1Mb (a slight loss) but a larger, representative install (about 1400 active ports of 2500 ports installed total) went from about 307Mb to 288Mb. I saw a comparable decrease on an even larger installation.
Step 10 : copy the file back into /opt/local/var/macports/registry, using sudo. Give the file the same ownership and permissions as the original registry.db file.
Step 11 : Test it. This requires some temporary hacking in MacPorts "base", not very elegant but the best/safest way to ensure that you didn't mess anything up. Step 11a : Get the current state (use sudo if required):
> port installed > installed.txt
> port contents installed > installed-contents.txt
Step 11b : Hack "base". The reference command using vi would be:
> vi +/registry.db `fgrep -l registry.db -R /opt/local/libexec/macports/lib --include="*.tcl"`
but a priori it will be these 3 files: /alt/local/libexec/macports/lib/macports1.0/macports.tcl, /opt/local/libexec/macports/lib/port1.0/porttrace.tcl and /opt/local/libexec/macports/lib/registry2.0/registry_util.tcl.
Replace registry.db with registry-ps=64k.db (one location per file). I like to keep the files open in the editor so I can just undo the changes and save them again.
Step 11c : get the current state according to the optimised registry and compare:
> port installed > installed2.txt
> port contents installed > installed-contents2.txt
You should get the exact same output. You can do more tests like activating other versions/variants of installed ports but remember to revert the changes unless you are certain that you want to accept the optimised registry and keep using it.
Step 12 : if that is indeed the case: ensure that registry-ps=64k.db is clean (the corresponding -wal file is empty) and then copy each of the registry-ps=64k.db* files in /opt/local/var/macports/registry to the corresponding registry.db file, using sudo. Finally, revert the changes in the tcl files made under Step 11b.
You should now be using a registry db in the usual location (i.e. the optimisation will survive updates to MacPorts, or at least as long as a future version doesn't reset the page size).