PostgreSQL make install times

I have decided that make install is too slow for me. Compare: A run of make install takes about 10 seconds (details below), but a run of make all with the tree mostly up to date and using ccache for the rest usually takes about 1 or 2 seconds. You can end up wasting a lot of time if you need to do many of these build and install cycles during development. In particular, make check includes a run of make install, so all this time is added to the time it takes for tests to complete.

So let's optimize this. The times below are all medians from 5 consecutive runs, writing over an existing installation, so they all had to do the same amount of work.

This is the baseline:

• make install — 10.493 s

The first change is to use a faster shell. This system is using bash as /bin/sh. Many Linux distributions now use dash instead, but for some reason I haven't changed this system during the upgrade.

• make install SHELL=/bin/dash — 6.344 s

I guess I'll be switching this system soon as well then!

The next thing is to avoid installing the translation files. This exploded the number of files that need to be installed. Instead of, say, one program file, you end up installing one program file and a dozen or so translation files.

• make install SHELL=/bin/bash enable_nls=no — 6.890 s
• make install SHELL=/bin/dash enable_nls=no — 4.482 s

(In practice you would use configure --disable-nls, which is the default. The above is just a way to do this without reconfiguring.) Now I have in the past preferred to build with NLS support to be able to catch errors in that area, but considering this improvement and the availability of the make maintainer-check target, I might end up building without it more often.

Another tip I remembered from the past was to use the make -s option to avoid screen output. Depending on the operating system and whether you are logged in locally or remotely, this can be a big win. On my system, this got lost in the noise a bit, but it appeared to make a small difference over many runs.

• make install SHELL=/bin/bash -s — 10.511 s
• make install SHELL=/bin/dash -s — 6.146 s

Do add this to your arsenal anyway if you want to get maximum performance.

Next, let's replace the install-sh script that does the actual file copying. For obscure reasons, PostgreSQL always uses that shell script, instead of the /usr/bin/install program that an Autoconf-based build system would normally use. But you can override the make variables and sustitute the program you want:

• make install SHELL=/bin/bash INSTALL=install — 5.418 s
• make install SHELL=/bin/dash INSTALL=install — 3.995 s

Interestingly, the choice of shell still makes a noticeable difference, even though it's no longer used to execute install-sh.

Finally, you can also use parallel make for the installation step:

• make install SHELL=/bin/bash -j2 — 6.538 s
• make install SHELL=/bin/dash -j2 — 4.158 s

You can gather from these numbers that the installation process appears to be mostly CPU-bound. This system has 4 cores, so let's add some more parallelization:

• make install SHELL=/bin/dash -j3 — 3.330 s
• make install SHELL=/bin/dash -j4 — 2.944 s
• make install SHELL=/bin/dash -j5 — 2.930 s
• make install SHELL=/bin/dash -j6 — 2.952 s

That's probably enough.

Now let's put everything together:

• make install SHELL=/bin/dash enable_nls=no INSTALL=install -s -j4 — 1.708 s

Or even:

• make install SHELL=/bin/dash enable_nls=no INSTALL=install -s -j3 — 1.654 s

That's a very nice improvement from 10.493 s!

The problem is, it is not all that easy to pass these options to the make install calls made in make check runs. If you can and want to change your system shell, and you configure without NLS support, then you will probably already be more than half way there. Then again, I suspect most readers already have that setup anyway. For the other options, to take down the installation time to almost instantaneous, you have to do ad hoc surgery in various places. I'm looking into improving that.

git whoami

My favorite feature in bzr (Bazaar) is the bzr whoami command, which prints what your current identity (name and email) is, as far as the repository is concerned. You can tell I haven't used bzr much if that's as far as I have gotten. But seriously, with so many Git repositories around, several project identities, directory-specific shell configuration, and so on, it's easy to get confused, and it's annoying to have to check and repair commits for correct user name and email all the time. So here is git whoami. This has already saved me so many headaches.

plpydbapi: DB-API for PL/Python

One thing that's weird about PL/Python is that its database access API is completely different from the standard Python DB-API. It is similar to PL/Perl and PL/Tcl, and the C "SPI" API, from which they are all derived, but that's little help for a Python programmer. (The reasons for this are lost in history. Probably laziness.) Moreover, the two APIs use the same function names for different purposes.

So I set out to develop a DB-API compatible layer on top of PL/Python: plpydbapi

Example:

CREATE FUNCTION test() RETURNS void
LANGUAGE plpythonu
AS $$
import plpydbapi

dbconn = plpydbapi.connect()
cursor = dbconn.cursor()
cursor.execute("SELECT ... FROM ...")
for row in cursor.fetchall():
    plpy.notice("got row %s" % row)
dbconn.close()
$$;

Granted, it's more verbose than the native PL/Python syntax, so you might not want to use it after all. But it can be helpful if database calls are nested in some other modules, or you just don't want to learn another database access API.

This started out more as an experiment, but it turns out that with the many improvements in PL/Python in PostgreSQL 9.1, it's possible to do this. (Subtransaction control and exception handling were the big issues.) The one gaping hole is that there is apparently no way to get metadata out of a query result. Something to address in PostgreSQL 9.2, perhaps.

Thanks go to the DB-API compliance test suite, which was extremely helpful in making this happen. (Nonetheless, the test suite is quite incomplete in some regards, so treat the result with care anyway.)

Another thing that I found neat about this project is that I managed to get the unit tests based on Python's unittest module to run in the PL/Python environment inside the PostgreSQL server. That's the power of unittest2.

Switching desktop environments

So, like many people, I have received GNOME 3 in the latest upgrade on Debian testing, and switched away to Xfce very soon after. What's amazing (to me) about this whole experience, however, is how easy it is to switch desktop environments in Debian. Two years ago, I fled KDE in very similar circumstances. I have since used Openbox, LXDE, GNOME, Xfce, as well as a few more obscure window managers in short-lived experiments. All of this was easy to do and worked flawlessly. Most of my settings, my preferred programs, and startup applications were preserved, and if I didn't like it, I could switch back without a problem. (Ironically, the upgrades from KDE 3 to KDE 4 and from GNOME 2 to GNOME 3 lost far more settings than changes to other environments.) And of course you can mix and match applications from different environments, and these days this doesn't result in a visual crime. I don't know whom to thank for this, probably a combination of upstream developers, freedesktop.org guys, and Debian maintainers. Keep it up.

ccache and clang, part 2

There's more funny business when using ccache in combination with clang. Last time I suggested that you use the invocation

./configure CC='ccache clang -Qunused-arguments -fcolor-diagnostics'

to get rid of the "argument unused during compilation" warnings.

But you still might get loads of warnings that you wouldn't normally get without ccache, such as this example (from the PostgreSQL source code):

extension.c:382:35: warning: equality comparison with extraneous parentheses [-Wparentheses]
 if (( (((control->directory)[0]) == '/') ))
        ~~~~~~~~~~~~~~~~~~~~~~~~~~^~~~~~
extension.c:382:35: note: use '=' to turn this equality comparison into an assignment

(This is the opposite of the warning that tells you to put two pairs of parentheses around an assignment used as a truth value.) Or:

path.c:727:7: warning: explicitly assigning a variable of type 'char *' to itself [-Wself-assign]
 path = (path);
 ~~~~ ^  ~~~~

The problem is, these come from macro expansions, so wouldn't normally see them, because (I guess) the compiler driver is smart enough not to warn about such things when they come from macro expansions.

The way ccache works is approximately

1. preprocess the input file
2. look for it in the cache
3. if not found, compile the preprocessed file

What would be better in this situation is

1. preprocess the input file
2. look for it in the cache
3. if not found, compile the original file

And indeed you can turn on that second behavior by setting the obscure environment variable CCACHE_CPP2 (as in, run cpp twice):

export CCACHE_CPP2=yes

Then all these extra warnings disappear.

(The ccache man page is worth a read. There are a few interesting settings to play with.)

I'm currently playing around with a shell script ccache-clang that looks like this:

CCACHE_CPP2=yes exec ccache clang -Qunused-arguments `test -t 2 && echo -fcolor-diagnostics` "$@"

Beta work

PostgreSQL 9.1.0 was wrapped the yesterday (or the day before yesterday, depending on from where you see it). It is now in a special place, where it can rest and let the flavor soak in, before it's released into the wild next week.

Time to reflect.

One thing that is sometimes criticized about the PostgreSQL release process is that there is too much time between the start of beta (or the end of the last commit fest) and the final release, and that it's not clear what ordinary contributors can do during that time to speed things up. This is now worked around to some degree by starting the next development cycle during the beta period of the previous one. (Evaluating the success of that approach is another matter.) But because this matter had also bothered me, I have taken notes this time, first of all so I don't forget anything next time, but perhaps also to provide some inspiration for others.

So here is what I'd been up to during the 9.1 beta period:

Documentation work

It is well understood that the documentation needs a general review during beta time. As features are patched in, chunks of documentation are added here and there, and at the end of it all you just need to stare at it for a bit and see whether the order and flow still make sense. I didn't do much of that this time, but I know others did. Other things to do are:

• Various bits of SGML markup usually need to be cleaned up, e.g., alignment of verbatim environments, linking to formal objects.
• I always run the whole documentation through a spell checker, which always results in a bunch of corrections.
• The SQL features list in the documentation needs to be updated.
• The SQL key words list in the documentation needs to be updated.
• All generated man pages should be looked through manually for formatting artifacts.
• Non-ASCII characters in the SGML source need to be checked. They should be converted to entities, and ideally they should fit into the Latin 1 set.

Other people also spent significant time on preparing the release notes, which is a huge job. I didn't even get into that, except that the spell checking and automated cleanups apply to the release notes as well, since they are part of the documentation build.

Source code cleanup

We run pgindent during beta, but there are also a number of other things to do to clean up the source code. A lot of these I didn't plan. Mostly, one thing led to another.

• Check for non-ASCII characters in source. These should either be in UTF-8 or replaced by an ASCII equivalent.
• Spell checking: You can't run the whole source through a spell checker, but there are also a few typical misspellings or variant spellings that are easy to clean up over the entire source tree, such as GIST -> GiST or cancelled -> canceled.
• Check the --help and --version output of all programs to see whether it follows conventions.
• In particular, check that the --help output is aligned, not too wide, and that the options are ordered sensibly. (Very often a new option is just added at the end or at some random place in the middle.)
• Diff the --help outputs between the previous and the current version to find out about all new command-line options and check whether they are sensibly named and properly documented.
• And the order in which options are presented in the --help output and on the man pages should not be gratuitously different.
• Similarly, the backslash command help output in psql (\?) should be checked that it looks ok.
• Go through the configuration file samples and check them for sanity, up-to-dateness, and proper formatting.
• Remove literal tabs from string constants. How did they ever get in there?
• Compile the code with a bunch of extra warning options. GCC has a lot of those, try them all out. A lot of them generate a lot of noise, but going through them about once a release does find the occasional trouble spot, or at least provides inspiration for future code cleanup projects. As of 9.1, you can also throw Clang into the mix.
• Also, check building against cutting edge versions of library dependencies. Good candidates are Perl and Python, which introduce small breakage with each new release. If there is a new release candidate of those guys out while we prepare the PostgreSQL release, our users will be building against the final versions soon, so we need to be prepared.

Translation work

Besides the translation work itself and orchestrating the translator contributors, this is also the best chance to read through every single user-visible message that was introduced in the new version. So there are occasional spelling fixes, some messages are poorly worded or incomprehensible to a normal user, or sometimes the code can be restructured so that some error condition doesn't happen at all. A lot of this feeds back into source code cleanup items.

Help the packagers

We have a great bunch of packagers who prepare PostgreSQL for installation on various operating systems. Help them out. Make sure all new features are properly integrated, package descriptions are up-to-date, upgrades work.

And then of course you field submissions from testers and other contributors who will submit countless bug reports, failures on unusual platforms, documentation fixes.

So that can keep you busy for a month or three. The goal ought to be to automated most of this. But much of it can't be. So we really do need to spend the time to do these things, especially polishing the user-facing things. We don't want the first program output or documentation page the user sees of the new release to be ugly or wrong.

Undefined symbol

Here is a small DBA tale from a few months ago. I wanted to upgrade a few servers to the latest minor release of PostgreSQL. It was the upgrade from 8.4.4 to 8.4.5. To minimize downtime, I had the following obvious idea: I'd install all the packages first, and once everything was in place and an opportune time for a service disruption had arrived, I'd just restart the services, and everything would be backup in less than a minute. (If you just let the package manager do its thing, it's always a bit unpredictable when the services are stopped and restarted, which might result several minutes of downtime.)

This operation had dramatic consequences. As soon as I had the new packages installed, I got massive amounts of these errors:

ERROR: could not load library "/usr/lib/postgresql/8.4/lib/plpgsql.so": /usr/lib/postgresql/8.4/lib/plpgsql.so: undefined symbol: PinPortal

Since a lot of things use PL/pgSQL, this meant that the database system was pretty much dead at this point.

What had happened?

Well, version 8.4.5 introduced some new functions in the server that PL/pgSQL made use of. As soon as you install the new plpgsql module, the next session that starts will load the new libary, but unless you restarted the server, the running server process won't have that symbol.

It was easy to fix this by restarting the server, but that's not how things were planned.

Consequence: Unless shown otherwise and a lot of care is taken, you must upgrade the server and all loadable modules to exactly matching versions and at the same time, and you must restart the server at the same time.

It might be useful as a preventive measure to compare symbol tables before upgrading. It could also be tracked more carefully at the source level, by explicitly mentioning the addition of meant-to-be-called-externally functions to the server in the release notes. (I suppose we don't usually remove functions, but who knows.)