Check for obsolete packages after Ubuntu/Debian upgrade

I recently had a vulnerability scanner flag a server that had an OpenSSL library package which had a vulnerability. That was odd, because the server had recently been updated.

The server had been upgraded from Ubuntu 20 to 22, and it turns out the Ubuntu upgrade logic may sometimes leave behind packages where there's no successor package in the new Ubuntu generation. So package libssl1.1 was left behind in a version which had a vulnerability. No software was using the old libssl library, but the finding in the vulnerability scan had to be addressed anyhow.

So next time I upgrade a Debian or Ubuntu server from one generation to the next, I'll make sure to run the following, in addition to "apt --purge autoremove" etc:

apt list ?obsolete

The command will output packages which are not covered by any active APT source. Its output must be taken with a grain of salt, because it will flag deb packages which have been installed  from other sources than APT channels. So on the servers I deal with, it outputs some packages which are OK to have around.

 

Introducing: topnfiles

Surprisingly often, I find myself needing to find the n most/least recently changed files. For example:

• In a large and deep directory structure of configuration files, where has the most recent change activity happened?
• In a nested directory of (many) uploaded files, where are the oldest files?

There doesn't seem to be a good, existing command line tool for this. Some (six) unix tools can be chained together, let's call it the chained find+sort method. But on a file system with lots of files, the chained find+sort method as not efficient as it could be with regards to CPU and memory consumption.

So I've had fun with Rust and have written a little tool called topnfiles. Benchmarks are good. It has very limited memory use. Specifically, its memory use is proportional only to the amount of output (unlike the find+sort method where it is proportional to the number of considered files). Also, I've added some functionality which is hard to get with the chained find+sort method, such as being able to have JSON output.

Example, finding the most recently changed files in /usr on a Debian 13 server:

$ topnfiles --exclude "/__pycache__/" /usr
/usr/lib/modules/6.12.38+deb13-amd64/modules.builtin.bin
/usr/lib/modules/6.12.38+deb13-amd64/modules.builtin.alias.bin
/usr/lib/modules/6.12.38+deb13-amd64/modules.devname
/usr/lib/x86_64-linux-gnu/graphviz/config6a
/usr/bin/topnfiles
/usr/share/doc/topnfiles/LICENSE.txt
/usr/share/man/man8/topnfiles.8.gz
/usr/share/doc/topnfiles/CHANGELOG.txt
/usr/share/doc/topnfiles/README.md
/usr/share/doc/topnfiles/copyright 

Yak Shaving: So There I Am, Shaving a Yak…

An important term to know of in the software development industry is yak shaving. The term is properly defined in Wiktionary, but Wiktionary's etymology section for the term is odd, and it doesn't really convey why hairy cattle is being referred to.

On the web, there are many different anecdotes illustrating yak shaving. In my opinion, the following is the best one. It was posted by the late Bill Gaiennie in 2009 on a website which now only exists in the WayBackMachine. I think it deserves to exist on a live website, so here it is:

I simply wanted to snap some pictures of my dogs running around the park last weekend. As I was about to round up the dogs to head to the park, I went to grab my camera, but then realized that I had left it at work.

So I jumped in the car to run by my office to grab the camera, but realized that I didn’t have a key to get into the building. I knew that a co-worker always had a key, so I started to drive to his house, but then realized that the last time I borrowed something from him, an exotic suit, I never returned it because I had accidentally ripped a pretty big hole in the jacket. I knew that my co-worker wouldn’t trust me with the office key if I didn’t return the suit, sans hole, but I had not been able to get it repaired because it was made out of yak hair, something I wasn’t even sure I could get.

I contacted a tailor who would be able to repair the suit, but wouldn’t be able to do it unless I was able to provide a supply of yak hair so that she could weave it into a fabric, and then from there use the yak fabric to repair the yak suit jacket….

So there I was at the zoo, shaving a yak, all so I could take a few pictures of my dogs at the park.

Disable Fedora's Postgres packages when using PGDG packages

In a poll in the Copenhagen Postgres User Group, I asked folks which software source they use when installing Postgres. For some strange reason, the poll is gone from LinkedIn, but I'm rather sure the majority uses packages from postgresql.org, i.e. the "PGDG" packages.

A word of caution for those who use PGDG package on cutting-edge distributions like Fedora: Your distribution may sometimes get ahead of PGDG, and then your installation will install your distribution's Postgres when updating software. This may wreak havoc, because your distribution's Postgres will have data in a different directory than PGDG's. Consequently:

On RPM-based systems, you should add a line like this in in /etc/yum.repos.d/fedora.repo / /etc/yum.repos.d/fedora-updates.repo:

exclude=postgresql* postgis*

That way there's no ambiguity about which flavor of Postgres packages your system tracks.

 

Overcoming stubborn jstack

Situation: Need to peek into what the different threads of a Java process are doing. But when running jstack, something the following happens after a little while:

target process 1985458 doesn't respond within 10500ms or HotSpot VM not loaded

You could try adding the "-F" flag to jstack, but often it will not work, because of jstack's timeout.

One way to handle this is to create a core dump of the Java process and then running "jhsdb jstack ..." on the resulting core file. Since jhsdb does not have a timeout, this makes a difference.

To generate the core file on Linux, you first need to install gdb on the server, if it's not already around. Then, assuming the Java process ID is 54321:

gdb -p 54321

In the gdb shell, run:

generate-core-file

You should now have a file "core.54321". You may have ro run gdb as root for things to work.

Things which may cause trouble for core file generation:

• When running gdb, your current working directory is on a file system which is too small, or you don't have write permissions.
• Your login session has limits on the size of core files. For this, run "ulimit -c unlimited".

My experience is that core file generation will not cause the process to fail (if you answer yes to leave the process running when exiting gdb), but it will freeze, while the core file is being created.

Now, having produced a core file, analyze it without a timeout (it can take a long time, depending on the size of the Java process). One long command line:

jhsdb jstack --exe /usr/lib/jvm/java-17-openjdk-amd64/bin/java --core core.54321 > output.txt

Of course, you may need to adjust the path to the Java which was used by process 54321.

By the way, you may re-use the core file to get a memory dump (one long line):

jhsdb jmap --exe /usr/lib/jvm/java-17-openjdk-amd64/bin/java --core /tmp/core.54321 > 54321-jmap.txt

Fixing LDAP auth for Postgres with AD

I've spent hours trying to find out why I couldn't get Postgres LDAP auth to work against a Samba active directory server in one setup (it worked well against a Samba active directory in another...).

I kept getting this in Postgres' logs:

2025-01-04 19:03:32.037 CET [58282] LOG:  could not search LDAP with scope 2 for filter "(sAMAccountName=troels)" on server "dcsrv.test": Operations error
2025-01-04 19:03:32.037 CET [58282] DETAIL:  LDAP diagnostics: 00002020: Operation unavailable without authentication

Adding this line in /etc/ldap/ldap.conf made things work:

REFERRALS off

In Red Hat derived Linux distributions, the path to ldap.conf is /etc/openldap/ldap.conf.

For why this make a difference, search for "referrals" in the Python FAQ.

(I'm getting the impression it would be nice if "off" was the default for REFERRALS.)

Antivirus software considered harmful

The IT profession needs to confront policies which demand antivirus be installed as a general measure

IT professionals still have in fresh memory the failure of CrowStrike's Falcon endpoint protection product which caused an US$10 billion global IT breakdown. The incident has been extensively covered in news outlets, on YouTube channels, and in blog posts – and rightly so.

However, no-one seems to question the idea of having such software installed on servers in the first place. That has long been a mystery to me.

Lack of independent justification for AV

After seven years, the following well-written ITPro article is still relevant: Does antivirus software do more harm than good? In the article, several sources describe how antivirus (AV) software itself has security holes, and that AV may interfere with other security measures. The problem with AV software is that it often runs in close proximity to the guts of the underlying system, so vulnerabilities in AV software (which are not uncommon) can have extraordinarily nasty security implications, as well as stability implications, see CrowdStrike.

In other sectors like research and medicine, it's considered natural to ask "Is there any independent evidence supporting the net gain from spending time and money on X?". Even though the IT world spends huge amounts of resources on AV, I've yet to see studies which provide such evidence. Sure, there are reports demonstrating that a given product detects malware XYZ (though not necessarily malware ZYX). But where's a study which documents how that detection makes a system more secure? Bear in mind that a file with malware might not get a chance to infect, because it relies on specific user interaction which is unlikely to happen, or it may rely on operating system vulnerabilities which have been patched long ago. On the other hand, when the AV product sometimes introduces gaping security holes, then the overall gain may well be negative.

Down-to-earth evidence of AV's questionable role

The above may appear overly academic, so here are some down-to-earth observations:

• Billions of Internet-connected devices run without AV software, and it generally works very well. Phone operating systems get software through well-understood, curated channels, and they have built-in robustness such as sandboxing and ASLR. So even though the smartphone ecosystem mainly consists of happy-go-lucky non-technical end-users, we have seen very little (if any) akin to an ILOVEYOU pandemic, and we see very little ransomware on phones.
  
  
• I did not have to clean up after the CrowStrike incident. But in my professional life, I've lately had to deal with detrimental effects from another AV product: That AV product had suddenly decided to "eat" a DLL file from a perfectly valid piece of software which had been installed weeks before. As a consequence, a rather important service stopped working. Paradoxically, the impacted service was helping us conduct safe IT practices. It took time and money to get things back running, and it has happened several times. I'm sure many of you have also seen AV software constitute a source of arbitrary and unpredictable outage.

AV at odds with pillars of secure IT practice

Let's step back and review decade-old basic system administration principles, and how they are often broken by AV:

• Install as little software as possible in order to limit attack surfaces – especially minimize software which runs with high privileges. AV breaks this principle, because it's perfectly possible to run a computer without an AV add-on (which millions of Macs and Linux servers, and probably also some Windows servers, do).
  
  
• Keep your software up-to-date in order to close the holes which malware will try to exploit. I've seen several cases where AV would conflict with software updates from Microsoft, making this principle harder to follow.
  
  
• Run software with the lowest possible privileges: All non-trivial software has bugs, so processes should be constrained as much as possible. I claim that many AV products run with excessively high privileges and/or in kernel space, breaking the principle. See this article about DLL search order hijacking for extra suspense.
  
  
• Make software hard to infect, primarily by disallowing software to write to its own binaries, but also by using techniques like address space layout randomization (ASLR). The article from 2017 quotes a software developer who spent lots of time fighting tricks performed by AV which could conflict with the product's built-in robustness.
  
  
• Prefer to have software be delivered via well known channels, such as yum repositories, app stores, etc. Many AV products introduce proprietary update services which see little scrutiny and may even decide to replace itself with another product, see Kaspersky's recent surprise act. On top of that, CrowdStrike's update system is hardly the only one which made it impossible to roll out in non-production, before applying updates to production.
  
  
• Stay away from software which shows signs of sloppy software engineering. Kudos to AV vendors for publishing CVE reports. But less so for what seems to be sloppiness: CrowdStrike has huge financial ballast, yet it failed to conduct basic testing steps, and it was not the first time in recent history. Other AV products also have questionable track records, but I've yet to hear such aspects being part of IT departments' decision making.
  
  
• Absolutely remove abandoned or end-of-life software. Yet, AV software is often rather hard to remove, causing friction when trying to uphold the principle. I've seen many servers running AV software which was no longer being updated, after a vendor change long ago (in one case, because removal required down-time where the server had to be booted into safe mode, before the abandoned product could be removed).
  
  
• Spend time wisely, because there is never time for it all. Time spent administering/maintaining AV software and battling AV-derived problems may result in less time spent on security enhancing activities like log analysis, systems/code cleanup, restore tests, AD hardening, etc.
  I've been in a meeting where a server break-in was discussed. The server had known vulnerabilities which would take time to address. Someone proposed installing AV on the server, buying time. If the decision for AV had been chosen, I'm rather sure it would have stolen much needed attention on getting the root cause handled, because it would have added a false sense of security.

On top of that, AV software is often seen incurring significant system overhead, adding latency and affecting end-user productivity. System overhead also requires extra CPU and RAM, which goes against green IT ambitions.

Culture and profit

Given all the technical arguments against AV, we may instead be dealing with a cultural issue: What is considered a given in one community is foreign to another. You will probably agree that in some parts of the IT landscape, it would be surprising to run across AV. For example:

• Internet routers don't run AV, even though they are directly exposed to all sorts of traffic.
• I claim most VMware administrators would strongly object, if someone requested AV be run directly on hypervisors.
• Printers don't run AV (and are often not patched, but that's a separate, sad story).
• Millions of compute nodes in high-performance compute environments deliver gazillions of compute hours, typically without having AV interfere.

The list goes on. And fortunately, many Linux and Mac hosts are still allowed to run without AV, although many overly-general IT policies are starting to force AV into those environments.

AV got started in the old days when Microsoft had not yet introduced proper kernel/userland and administrator/user separation into Windows, and where organizations had no good way to curb users from installing software from arbitrary sources. Fast-forward to 2024 where Windows systems can be very robust, but AV thinking prevails -- why? Inertia is not foreign to the IT business, of course: "Nobody Gets Fired For Buying IBMAV". Let me propose an additional reason: Some software vendors and certain security consultants have found AV to be a very profitable business, and they would naturally hate to see profits go down.

Time to start questioning

AV uselessness may appear to be a controversial proposition, and many will disagree with me. On the other hand, I also know of many system administrators and programmers who agree.

IT professionals, can we at least agree that it's our responsibility to start questioning AV as a general requirement? The compliance crowd will not have the guts to do it. So-called IT security consultants often profit from antivirus products, so they don't want to rock the boat.

It's on IT professionals' plate to address this.

PS 1

There may be situations where AV makes good sense to have. I argue that AV can make sense to have

• on mail gateways, as part of general anti-junkmail efforts
• on file shares which are shared by many users, depending on the environment
• on hosts which – for some reason – can neither be regularly patched, nor brought onto a segregated network (but don't expect it to be very effective)

PS 2

While I generally object to the notion of buying security as an add-on product, I acknowledge that some products can provide real security. A product like Nessus' vulnerability scanner, for example, is a good addition to an IT organization's arsenal, especially because it can run in an unintrusive manner which neither installs anything on a server, nor tries to intercept all network traffic.

Find recently updated files

The following task comes up once in a while, but I keep forgetting how to do it. So I decided to finally write it down.

Find the ten youngest directory entries in the current directory and all subdirectories:

find . -print0 | xargs -0 stat --format '%Y :%y %n' | sort -nr | cut -d: -f2- | head

Same task, but solely with regards to files (not directories):

find . -type f -print0 | xargs -0 stat --format '%Y :%y %n' | sort -nr | cut -d: -f2- | head

Note: In case no files/directories are found, the following error message may be returned: "stat: missing operand". 

Note also, that this may not work on non-Linux operating systems: For example, I've seen it fail on an old FreeBSD server where the stat command behaves very different from what I'm used to.

There is a simpler (and probably a bit faster) way, but it doesn't deal well with file names that have newlines:

find . -type f -printf '%T+ %p\n' | sort -r | head

(Un)safe English locales

I recently spent a frustrating amount of time troubleshooting a Java application which had stopped working properly after an upgrade: Certain features of the product were broken due to the application running on a Linux server which had been configured with the "en_DK" locale.

"en_DK" had been chosen expecting to have system with messages in English, but with currency symbols etc. suitable for Denmark. This makes sense, because it's not uncommon for IT systems to provide more precise (error) messages in English, compared to a small language like Danish.

Unfortunately, there is not universal agreement about locales. Even within Linux distributions, there is not 100% consistency.

The GNU C Library (Glibc) seems to have the longst list of recognized locales, including 19 English ones. Java's list of supported locales is somewhat shorter and includes only 11 English locales. Interestingly, Java has an English locale for Malta (en_MT) which glibc does not have.

I haven't been able to find MacOS's list of locales, but forum posts suggest it has only 6 English locales: en_AU, en_CA, en_GB, en_IE, en_NZ, en_US.

Ignoring Mac for a moment, these are unsafe English locales, i.e. not supported by both glibc and Java:

Locale	Country
en_AG	Antigua and Barbuda
en_BW	Botswana
en_DK	Denmark
en_HK	Hong Kong
en_IL	Israel
en_MT	Malta
en_NG	Nigeria
en_SC	Seychelles
en_ZM	Zambia
en_ZW	Zimbabwe

On the other hand, the following locales should be safe:

Locale	Country	Safe even om Mac
en_AU	Australia	🍎
en_CA	Canada	🍎
en_GB	Great Britain	🍎
en_IE	Ireland	🍎
en_IN	India
en_NZ	New Zealand	🍎
en_PH	Philippines
en_SG	Singapore
en_US	USA	🍎
en_ZA	South Africa

Looking beyond unix/POSIX-like systems: Windows recognizes more than 100 English locale identifiers. Windows' list does invalidate the above safe-list.

For Danes, I suggest using one of the following locales:

• da_DK (and sometimes accept poor error messages)
• en_IE, as the Irish are sane enough to use a 24-hour date format, etc
• C, which is the fall-back "POSIX system default" locale

Unbootable RHEL 9 when using BP-028

When installing Red Hat Enterprise Linux 9, you may choose to apply a security profile, such as ANSSI-BP-028 High.

I've recently seen two VMware-virtualized RHEL 9.2 servers not being able to boot properly when installed with the ANSSI-BP-028 High profile. Instead, they booted into emergency mode.

The way to fix it:

Add file /etc/modules-load.d/for_uefi.conf containing a single line:

vfat 

Then run the following two commands:

dracut -f /boot/initramfs-$(uname -r).img $(uname -r)
reboot

parallel_ntp_scan

NTP-based DDoS attacks are fashionable, currently.

I've coded a little application which quickly scans a network for NTP servers. For those found, it rates them according to their susceptibility to being implicated in an amplification DDoS attack.

What a dying SFP looks like

Fibre channel (FC) storage is handy, and generally very reliable, in my experience. I certainly do not miss the days of messing around with disks in a server-room. And I like the fact that RAIDs may be cut up into slices (LUNs) which may be shared by many servers, resulting in very efficient use of the disks (if so wanted).

One part about FC that I dislike (in addition to the price tags): SFPs. Why on earth are transceivers not an integral part of a Fibre Channel switch? Having the transceivers be separate units means more electrical contact points, and a potential support mess (it's not hard to imagine a situation where the support contract of an SFP has run out, while the switch itself is still covered).

Anyway: Today, I experienced an defunct SFP, for the first time. The following observations may give a hint of how to discover that an SFP is starting to malfunction. The setup is an IBM DS4800 storage system where port 2 on controller B is connected to port 0 on an IBM TotalStorage SAN32B FC switch (which is an IBM-branded Brocade 5100 switch).

Friday morning at 07:49, in syslog: A few messages like this from the FC switch:

raslogd: 2011/09/30-07:49:07, [SNMP-1008], 2113, WWN 10:... | FID 128, INFO, IBM_2005_B32_B,  The last device change happened at : Fri Sep 30 07:49:01 2011

At the same time the storage system started complaining about "Drive not on preferred path due to ADT/RDAC failover", meaning that at least one server had started using a non-optimal path, most likely due to I/O timeouts on the preferred path. And a first spike in the bad_os count occurred for the FC switch port:

bad_os is a counter which exists in Brocade switches, and possibly others. Brocade describes it as the number of invalid ordered sets received.

At 10:55, in syslog:

raslogd: 2011/09/30-10:55:02, [FW-1424], 2118, WWN 10:... | FID 128, WARNING, IBM_2005_B32_B, Switch status changed from HEALTHY to MARGINAL

At the same time, there was a slightly larger spike in the bad_os graph.
Coinciding: The storage system sent a mail warning about "Data rate negotiation failed" for the port.

At 17:00: The count for bit-traffic flat-lined (graph not shown). I.e.: All traffic had ceased.

At no point did the graphs for C3 discards, encoding errors or CRC errors show any spikes.

The next morning, the involved optical cable was switched; that didn't help. Inserting another SFP helped, leading to the conclusion that the old SFP had started to malfunction.

Morale: Make sure to not just keep spare cables around. A spare SFP should also be kept in stock.
And monitor your systems: A centralized and regularly inspected syslog is invaluable. Generating graphs for key counters is also mandatory for mature systems operation; one way to collect and display key counts for Brocade switches is to use Munin and a Munin plugin which I wrote.

PS: Brocade documentation states that SFP problems might result in the combination of rises in CRC errors and encoding/disparity errors. This did not happen in this situation.

Separation or co-location of database and application

In a classical three-tier architecture (database, application-server, client), a choice will always have to be made: Should the database and the application-server reside on separate servers, or co-located on a shared server?

Often, I see recommendations for separation, with vague claims about performance improvements. But I assert that the main argument for separation is bureaucratic (license-related), and that separation may well hurt performance. Sure, if you separate the application and the database on separate servers, you gain an easy scale-out effect, but you also end up with a less efficient communication path.

If a database and an application is running on the same operating system instance, you may use very efficient communication channels. With DB2, for example, you may use shared-memory based inter-process communication (IPC) when the application and the database are co-located. If they are on separate servers, TCP must be used. TCP is a nice protocol, offering reliable delivery, congestion control, etc, but it also entails several protocol layers, each contributing overhead.

But let's try to quantify the difference, focusing as closely on the query round-trips as possible.

I wrote a little Java program, LatencyTester, which I used to measure differences between a number of database<>application setups. Java was chosen because it's a compiled, statically typed language; that way, the test-program should have as little internal execution overhead as possible. This can be important: I have sometimes written database benchmark programs in Python (which is a much nicer programming experience), but as Python can be rather inefficient, I ended up benchmarking Python, instead of the database.

The program connects to a DB2 database in one of two ways:

• If you specify a servername and a username, it will communicate using "DRDA" over TCP
• If you leave out servername and username it will use a local, shared memory based channel.

After connecting to the database, the program issues 10000 queries which shouldn't result in I/Os, because no data in the database system is referenced. The timer starts after connection setup, just before the first query; it stops immediately after the last query.

The application issues statements like VALUES(...) where ... is a value set by the application. Note the lack of a SELECT and a FROM in the statement. When invoking the program, you must choose between short or long statements. If you choose short, statements like this will be issued:

VALUES(? + 1)

where ? is a randomly chosen host variable.
If you choose long,  statements like

VALUES('lksjflkvjw...pvoiwepwvk')

will be issued, where lksjflkvjw...pvoiwepwvk is a 2000-character pseudo-randomly composed string.
In other words: The program may run in a mode where very short or very long units are sent back and forth between the application and the database. The short queries are effectively measuring latency, while the long queries may be viewed as measuring throughput.

I used the program to benchmark four different setups:

• Application and database on the same server, using a local connection
• Application and database on the same server, using a TCP connection
• Application on a virtual server hosted by the same server as the database, using TCP
• Application and database on different physical servers, but on the same local area network (LAN), using TCP

The results are displayed in the following graph:

Click on figure to enlarge; opens in new window

Clearly, the the highest number of queries per second is seen when the database and the application are co-located. This is especially true for the short queries: Here, more than four times as many queries may be executed per second when co-locating on the same server, compared to separating over a LAN. When using TCP on the same server, short-query round-trips run at around half the speed.

The results need to be put in perspective, though: While there are clear differences, the absolute numbers may not matter much in the Real World. The average query-time for short local queries were 0.1ms, compared to 0.8ms for short queries over the LAN. Let's assume that we are dealing with a web application where each page-view results in ten short queries. In this case, the round-trip overhead for a location connection is 10x0.1ms=1ms, whereas round-trip overhead for the LAN-connected setup is 10x0.8ms=8ms. Other factors (like query disk-I/O, and browser-to-server roundtrips) will most likely dominate, and the user will hardly notice a difference.

Even though queries over a LAN will normally not be noticeably slower, LANs may sometimes exhibit congestion. And all else being equal, the more servers and the more equipment being involved, the more things can go wrong.

Having established that application/database server-separation will not improve query performance (neither latency, nor throughput), what other factors are involved in the decision between co-location and separation?

• Software licensing terms may make it cheaper to put the database on its own, dedicated hardware: The less CPUs beneath on the database system, the less licensing costs. The same goes for the application server: If it is priced per CPU, it may be very expensive to pay for CPUs which are primarily used for other parts of the solution.
• Organizational aspects may dictate that the the DBA and the application server administrator each have their "own boxes". Or conversely, the organization may put an effort into operating as few servers as possible to keep administration work down.
• The optimal operating system for the database may not be the optimal operating system for the application server.
• The database server may need to run on hardware with special, high-performance storage system attachments. - While the application server (which probably doesn't perform much disk I/O) may be better off running in a virtual server, taking advantage of the flexible administration advantages of virtualization.
• Buying one very powerful piece of server hardware is sometimes more expensive than buying two servers which add up to the same horsepower. But it may also be the other way around, especially if cooling, electricity, service agreements, and rack space is taken into account.
• Handling authentication and group memberships may be easier when there is only one server. E.g., DB2 and PostgreSQL allows the operating system to handle authentication if an application connects locally, meaning that no authentication configuration needs to be set up in the application. (Don't you just hate it when passwords sneak into the version control system?)
• A mis-behaving (e.g. memory leaking) application may disturb the database if the two are running on the same system. Operating systems generally provide mechanisms for resource-constraining processes, but that can often be tricky to setup.

Summarized:

Pro separation	Con separation
Misbehaving application will not be able to disturb the database as much.	Slightly higher latency.
May provide for more tailored installations (the database gets its perfect environment, and so does the application).	Less predictable connections on shared networks.
If the application server is split into two servers in combination with a load balancing system, each application server may be patched individually without affecting the database server, and with little of no visible down-time for the users.	More hardware/software/systems to maintain, monitor, backup, and document.
May save large amounts of money if the database and/or the application is CPU-licensed.	Potentially more base software licensing fees (operating systems, supporting software).
Potentially cheaper to buy two modest servers than to buy one top-of-the-market server.	Potentially more expensive to buy two servers if cooling and electricity is taken into account.
Allows for advanced scale-out/application-clustering scenarios.	Prevents easy packaging of a complete database+application product, such as a turn-key solution in a single VMWare or Amazon EC2 image.

Am I overlooking something?
Is the situation markedly different with other DBMSes?

PostgreSQL innovation: Exclusion constraints

It seems like the next generation of PostgreSQL will have a new, rather innovative feature: Exclusion constraints. The feature is explained in a video from a presentation at a recent San Francisco PostgreSQL Users' Group; the presentation couples the new feature with the time period data type.

In a perfect World where databases implement the full ISO SQL standard (including non-core features), exclusion constraints could be nicely expressed as SQL assertions, but the perfect World hasn't happened yet. And I think that I know the reason for this: SQL assertions seem like a strong cocktail of NP-hard problems - they are probably very hard to implement in an efficient way.

In our less than perfect World, it's nice that PostgreSQL will soon offer a way to specify exclusion constraints other than the UNIQUE constraint.

The presenter, Jeff Davis, has an interesting blog, by the way. An on the subject of video, Vimeo has a little collection of PostgreSQL video clips that looks interesting.

SQL comparison update: PostgreSQL 8.4

I finally got around to adjusting my SQL comparison page, so that the improvements in PostgreSQL 8.4 are taken into account. PostgreSQL is now standards-compliant in the sections about limiting result sets; actually, PostgreSQL is the first DBMS that I know of which supports SQL:2008's OFFSET + FETCH FIRST construct for pagination. PostgreSQL's new support for window functions is also very nice.

My page doesn't cover common table expressions (CTEs) yet, but it's certainly nice to see more and more DBMSes (including PostgreSQL, since version 8.4) supporting them. Even non-recursive CTEs are important, because they can really clean up SQL queries and make them more readable.

SQL comparison updates: Oracle 11R2, diagnostic logs

I finally found some time to update my page which compares SQL implementations. I performed a general update regarding Oracle, now that Oracle 11R2 has been released. And I added a new (incomplete) section which describes where the different DBMSes store diagnostic logs.

I turned out that very little has changed in Oracle since generation 10. The only remarkable new SQL feature in Oracle 11 is support for CTEs and proper CTE-based recursive SQL (introduced in version 11, release 2) -- but as I don't cover this topic on my page, updating the Oracle items was mostly a question of updating documentation links. Oracle still doesn't support the standard by having a CHARACTER_LENGTH and a SUBSTRING function, for example. This is simple, low-hanging fruit, Oracle(!) Sigh. It seems like Oracle's market position has made them (arrogantly) ignore the SQL standard.

VLDB2009: Non-cloud storage

Not all of VLDB2009 was related to cloud storage. Luckily, local and SAN storage is still being explored. Here are some notes from selected presentations.

Mustafa Canim: An Object Placement Advisor for DB2 Using Solid State Storage: What data should be put on disks, and what data on solid state disk storage? Solid state drives (SSDs) shine at random read I/O, but in most situations, there's limited funds, so only part of a database will be eligible for SSD placement. It turns out that if a naïve/simplified strategy like let's place all indexes on SSD is used, only a small overall performance gain is measurable, and it's hardly a justification of the expensive SSD storage. But if placement of database objects (tables/indexes) is based on measurements from a representative workload, then data which is often seeked to can be placed on the SSDs. Canim has created a prototype application which uses DB2 profiling information to give advice on the most optimal use for xGB of SSD storage, and he demonstrated very convincing price/performance gains from his tool. The principle should be easy to apply to other DBMSes.

Devesh Agrawal: Lazy-Adaptive Tree: An Optimized Index Structure for Flash Devices: Since SSDs do not shine at random writes, an SSD-optimized index structure would be highly welcome. The Lazy-Adaptive (LA) Tree index is a (heavily) modified B+ tree which buffers writes in a special way, yielding significant performance improvements.

Nedyalko Borisov and Shivnath Babu had a poster and a demonstration about a prototype application they have created: DIADS. DIADS integrates information from DB2 query access plans with knowledge about the storage infrastructure (all the way from files on the disk, through the LVM and SAN layers, to individual disks) to help diagnosing performance bottlenecks. This would certainly be useful for DBAs, and it could probably bridge the worlds of DBAs and storage people. They are considering making it an open source project. By the way: I believe that I've heard Hitachi claim to be selling a tool with a similar objective: Hitachi Tuning Manager.

Finally, a little rule of thumb from Patrick O'Neil's The Star Schema Benchmark and Augmented Fact Table Indexing (part of the TPC Workshop): Throughput of magnetic disks has grown much more than seek latency, so at the moment, 1MB of scanning can justify 1 seek.

VLDB2009: Sloppy by choice

One of the recurring themes at the VLDB2009 conference was how to create massively scalable database systems, by decreasing expressiveness, transaction coverage, data integrity guarantees—or any combination of these. The theoretical justification for this is partly explained by the CAP Theorem. Much has already been written about database key-value/object stores, cloud databases, etc. But there were still a few surprises.

Ramakrishnan's keynote
Yahoo!'s Raghu Ramakrishnan (whose textbook many readers of this article will know) gave a keynote on the subject. It was actually new to me that Yahoo! is also entering the cloud business; it's getting crowded in the sky, for sure. As we know, the business idea is this: A large operation like Amazon already has a massive, distributed IT infrastructure; so letting other companies in isn't that much of an extra burden. And the more users of the hardware, the easier it is to build a cost-efficient setup which can still handle spikes in performance demands (with many users it's very unlikely that their systems run at peak performance at the same time). Nice idea, but it may take a long while before users convert to using software which runs in the cloud, and it remains to be seen how many cloud vendors which can survive that long.

Anyway, Raghu Ramakrishnan presented a much-needed comparison of cloud database solutions (pages 55-60 in this presentation). The consistency model of Yahoo!'s cloud database system, PNUTS, does not provide ACID, but nor is it 'sloppy' to the degree of BASE. Another nice aspect of Yahoo!'s cloud systems is that much of it is based on open source software. Yay Yahoo!

When to be sloppy
At the conference, some claimed that cloud storage can also be used for important data, but no one gave a plausible example. In cloudy times, we should not forget that not all data are about tweets, status updates, weblog postings, etc. There are actually data which is actually important. That's why I liked the Consistency Rationing in the Cloud: Pay only when it matters presentation: It provided at framework for categorizing data in degrees of acceptable sloppiness, based on the cost associated with potential inconsistencies, versus the savings gained from the lower transaction overhead.

Panel on cloud storage
On Wednesday, there was a panel discussion on How Best to Build Web-Scale Data Managers, moderated by Philip Bernstein. Random notes from the discussion:

• A Surprising, and somewhat strange viewpoint from Bernstein: We should not ditch ACID (not surprising coming from Mr Transaction himself), but we should give hierarchical DBMSes a new chance. According to Bernstein: The reason why it will not fail this time is that we have become so good at handling materialized views, and they allow us to make sure that fast queries are not restricted to restricting/scanning one one dimension. Bernstein failed to alleviate my fear of the return of another major drawback of hierarchical databases: navigational queries.
• While there's much not-so-important data out there, the phenomenon of moderate amounts of important data hasn't gone away (not every business is a twitter.com). So although the non-ACID, non-relational database systems may have a lot of attention, it doesn't matter for the makers of "traditional" DBMSes, because RDBMS business is doing great.
• Sub-question: Why do web start-ups seem to make use of key-value stores, and not use Oracle's DBMS (for example) when they need to scale to beyond a single data server? There wasn't much opposition to the view that—in addition to being administration labor intensive—the cost of an Oracle cluster is way out of budget in many businesses. So: If database researchers want to help prevent relational+transactional research from becoming increasingly irrelevant, it's time to help the open source database projects. While I agree that researchers can make a difference in the open source world, but I's skeptical to the perception of RDBMSes being abandoned; whatever numbers I've seen actually indicate the opposite. And while Facebook—for example—has a key-value store, their non-clickstream-data is actually in sharded MySQL databases, as far as I've heard; sharded MySQLs will never win relational database beauty contests, but at least it's tabular data, accessible with SQL, and with node-local transactions.
• Interesting point: SAP, an application with undisputed business significance, is well known for using nothing but the most basic RDBMS features. With that in mind, one should be cautious to denounce cloud databases for lack of expressiveness.

Finally, a couple of pictures from the nearby Tête d'Or Park:

VLDB2009: TPC Workshop

Monday, I attended the Transaction Processing Council (TPC) Workshop about the latest developments in database benchmarks. The workshop was kicked off with a keynote from a true database hero, Michael Stonebraker. Mr. Stonebraker has not only published significant research papers—he is also initiated a number of projects and startups: Postgres (the precursor to the great PostgreSQL DBMS), Vertica (one of the pioneers within the "column-oriented"/"column-store" database realm), StreamBase, and others. Stonebraker held it that benchmarks have been instrumental in increasing competition among vendors, but there are aspects to be aware of: As the benchmarks allow the vendors to tune the DBMS (and sometimes create setups not resembling most setups, like using five figure disk counts), this doesn't improve the out-of-box experience—an experience which all too often needs to be significantly tweaked. Stonebraker also criticized the TPC from being too vendor focused, instead of having focus on users and (simple) applications. And he urged the TPC to speed up the development of new benchmark types (I'm thinking: geospatial, recovery, ...), partly by cutting down on organizational politics.

Personally, I'm astonished that some (most?) of the big DBMS vendors prohibit their users from publishing performance findings. This curbs discussion among practitioners, and it decreases reproducibility and detail of research papers ("product A performed like this, product B performed like that"). I doubt that this actually holds in a court of law, but it would certainly take guts (and a big wallet) to challenge it. I'm also annoyed that the vendors don't really support the TPC much: The TPC-E benchmark (OLTP-benchmark, sort-of modernized version of TPC-C) is two years old, yet only one vendor (Microsoft) has published any results yet.

Nevertheless, references to TPC benchmarks were prevalent at the conference, being referred to in several papers.

I'm planning to try running TPC-H on our most important database systems, to see if it is feasible to use it for regular performance measurements—in order to become aware of performance pro-/re-gressions. By the way: It would be of great if IBM (and others) published a table of reference DB2 TPC findings for a variety of normal hardware configurations. That way, a DBA like me could get an indication of whether I've set up our systems properly.

Other speakers had various ideas for new benchmarks, including benchmarks which measure performance per kWh, and benchmarks which expose how well databases handle error situations.

A researcher from VMWare pledged for benchmarks of databases running in virtual environments. He presented some numbers of a TPC-C-like workload running on ESX guests, showing that a DBMS (MSSQL, I believe) can be set up to run at 85%-92% of the native speed. Certainly acceptable. And much in like with what I'm experiencing. I hope that figures like this can gradually kill the myth that DBMSes shouldn't run in virtual hosts—a myth which results in a situation where many organizations don't realize the full potentials of virtualization (increased overall hardware utilization/lower need for manpower/less late-night service windows, as workloads can be switched to other hosts when a server needs hardware service).

I forgot to take a picture from the workshop, so here's a picture of me being sceptical about traditional DBMS vendors—next to the Saôme river.

At VLDB2009

I'm attending the 35th VLDB conference in Lyon: VLDB2009. The conference portrays itself as the premier international forum for database researchers, vendors, practitioners, application developers, and users. Of the 700 people (from 44 countries), I'm one of the few practitioners at the conference; and though there's a risk that the conference will be too research oriented, I've signed up, especially hoping to get the latest updates and thoughts on

• probabalistic databases
• column-oriented databases
• performance quantification
• cloud databases

During the next couple of days, I'll share my experiences here.

I—and others—have tweeted a bit from the conference, as well.