As much as I like Linux (and, really, I do: I ran Linux 0.99.something on my desktop in college, and wrote my class papers using vim and LaTeX), there are certain things about it that drive me crazy, and the way it reports memory usage is definitely on the list. It should be possible for a reasonably intelligent human being (in which category I place myself) to answer simple questions about system memory usage, such as “How much memory is my database using?” or “How much memory is my web server using?” relatively simply.
Read more »

Time flies when you’re benchmarking. I noticed today that it’s been over a month since my last blog post, so it’s past time for an update. One of the great things about the PostgreSQL community is that it is full of smart people. One of them is my colleague Pavan Deolasee, who came up with a great idea for reducing contention on one of PostgreSQL’s most heavily-trafficked locks: ProcArrayLock. Heikki Linnakangas (another really smart guy, who is also a colleague of mine) did some more work on the patch, and then I cleaned it up further and committed it.
Read more »

I don’t normally follow Linux kernel development, but I was pleased to hear (via Andres Freund) that the Linux kernel developers have committed a series of patches by Andi Kleen to reduce locking around the lseek() system call.  As I blogged about back in August, PostgreSQL calls lseek quite frequently (to determine the file length, not to actually move the file pointer), and due to the performance enhancements in 9.2devel, it’s now much easier to hit the contention problems that can be caused by frequently acquiring and releasing the inode mutex.  But it looks like this should be fixed in Linux 3.2, which is now at rc1, and therefore on track to be released well before PostgreSQL 9.2.

Meanwhile, we’re gearing up for CommitFest #3.  Interesting stuff in this CommitFest includes Álvaro Herrera’s work on reducing foreign key lock strength and a PostgreSQL foreign data wrapper (pgsql_fdw) by Hanada Shigeru.  Reviewers are needed, for those and many other patches!

Every PostgreSQL release adds new features, but sometimes the key to a release has less to do with what you add than with what you take away. PostgreSQL 8.4, for example, removed the settings max_fsm_pages and max_fsm_relations, and replaced them with a per-relation free space map that no longer requires manual sizing. Those parameters are now gone, and more importantly, something that you previously needed to understand and manage was replaced with something that just works. People who are still running PostgreSQL 8.3, or older versions, want to understand exactly how the free space map works; people who are running PostgreSQL 8.4, or newer, don’t care. It’s enough to know that it does work.

Now, about eight months ago, I wrote a blog entry on troubleshooting stuck vacuums. I would not say that this is an everyday problem, but in ten years of working with PostgreSQL, I’ve seen it a few times, and it’s very unpleasant. It’s easy to miss the fact that you have a problem at all, because in most cases, nothing immediately breaks. Instead, system performance just slowly degrades, gradually enough that you may not realize what the problem is until things have gotten pretty bad and you need to CLUSTER or VACUUM FULL to recover.
Read more

Heikki Linnakangas was doing some benchmarking last week and discovered something surprising: in some circumstances, unlogged tables were actually slower than permanent tables. Upon examination, he discovered that the problem was caused by CLOG contention, due to hint bits not being set soon enough. This leads to a few questions:

1. What is CLOG?
2. What are hint bits?
3. How does setting hint bits prevent CLOG contention?
4. Why weren’t hint bits being set sooner?

Let’s take those in order.
Read more

Since I wrote my previous blog entry on index-only scans, quite a bit of additional work has been done. Tom Lane cleaned up the code and improved the costing model, but possibly the most interesting thing he did was to allow index-only scans to be used for queries that don’t involve an indexable condition at all. The classic example is SELECT COUNT(*) FROM table. In previous versions of PostgreSQL, there’s just one way to implement this: sequential scan the table and count ‘em up. In PostgreSQL 9.2, that method will still, of course, be available, but now there will be another choice: pick any index you like and do a full index scan, checking whether each tuple is all-visible either using the visibility map or via a heap fetch. So, how well does it work
Read more

As I mentioned in a previous blog post, I spend some of my time working in and with EnterpriseDB’s support department. And what that means is that every customer I talk to has a problem, typically a fairly serious problem, and they want me to help them fix it. Of course, to fix it, you first have to be able to identify the problem, and sometimes that’s not so simple. Database crashes can be among the more difficult cases to debug.
Read more

Last week, someone pinged me on instant messenger to ask about the following message, which their PostgreSQL instance had just produced:

This message is a complaining about a deadlock. But unless you’ve seen and debugged these a few times before, it might not be entirely obvious to you what’s actually going on here. What, exactly, did the offending processes do that caused the problem?

Read more

Tom Lane committed a patch for index-only scans by myself and Ibrar Ahmed, which also incorporated some previous work by Heikki Linnakangas, after hacking on it some more himself. Woohoo!

There is, of course, more work to be done here – performance fine-tuning, cost estimation, extensions to the core functionality – but the core of the feature is now in.  If you get a chance, please test it out and let us know how it works for you.

For those that may not have been following along at home, what we’re essentially doing here is allowing any index to act as a “covering index”.  If all of the columns the query needs are available from the index tuple, we’ll skip fetching the corresponding heap (table) page if every tuple on that page is visible to all running transactions.

Although I know we’re not even really done with this feature yet, I can’t help wondering what’s next.  Index-only scans have so often be cited as “the big performance feature that PostgreSQL is missing” that it’s become something of a clich.  Now that we have them, what will take their place as the next big thing?

I’ve seen a lot of articles lately about the great new features (and removed limitations) in PostgreSQL 9.1.  Unless you’re a regular reader of pgsql-hackers, you could almost forget about the fact that PostgreSQL 9.2 development is in full swing.  In fact, there’s a CommitFest going on right now and we could use a few more reviewers.

Many of the features that were submitted to this CommitFest are small improvements – minor fine-tuning of existing features, like generating better column names for subquery expressions, or fixing things so that LIKE can more reliably make use of indexes when non-English characters are involved.  But some of the big features that will hopefully become part of PostgreSQL 9.2 are also beginning to materialize.

Read more