Testing PostgreSQL 8.5-alpha3 with peg

PostgreSQL 8.5-alpha3 was announced last week. The biggest single feature introduced in it is Hot Standby, which allows you to run queries against a server that's being used as a Warm Standby replica. Since you can make any number of such replicas from a single master database, this introduces a whole new way to scale up PostgreSQL server farms in situations where you can live with queries that won't necessarily have the very latest data in them, due to replication lag. For example, it's a great way to run large batch reports like daily business summaries against the standby, rather than beating the master server with that load. Just wait a little bit after the end of the day for the transactions to copy over, then kick the query off against the hot standby system.

Actually getting two servers up and running so you can test this feature can be a drag though, since the alpha releases aren't necessarily going to be packaged up for you to install easily. In order to test a new version of PostgreSQL built from source, there are a fair number of steps involved: checkout the source, build, create a database cluster, start the server, and then you can finally run a client. Each one of these has its own bits you likely need to customize, from needing a directory tree to keep all these pieces (source, binaries, database) organized to source configuration time options.

After a few years of building increasingly complicated scripts to handle pieces of this job, recently I assembled them Voltron-style into one giant script that takes care of everything: peg, short for "PostgreSQL environment generator". The basic idea is that you give peg a directory to store everything in, point it toward the source you want to use, and it takes care of all the dirty work.

The best way to show how peg can help speed up development and testing is to see how easy it makes testing the latest PostgreSQL 8.5 alpha3:

$ git clone git://github.com/gregs1104/peg.git
$ sudo ln -s /home/gsmith/peg/peg /usr/local/bin/peg
$ mkdir -p pgwork/repo/tgz
$ pushd pgwork/repo/tgz/
$ wget http://wwwmaster.postgresql.org/redir/198/h/source/8.5alpha3/postgresql-8.5alpha3.tar.gz
$ popd 
$ peg init alpha3
Using discovered PGWORK=/home/gsmith/pgwork
Using discovered PGVCS=tgz
Using tgz repo source /home/gsmith/pgwork/repo/tgz/postgresql-8.5alpha3.tar.gz
Extracting repo to /home/gsmith/pgwork/src/alpha3
tar: Read 6656 bytes from -
Extracted tgz root directory is named postgresql-8.5alpha3
No checkout step needed for PGVCS=tgz
$ . peg build
$ psql
psql (8.5alpha3)
Type "help" for help.

gsmith=# \q

That's 9 lines of commands to go from blank development system to working psql client, and half of that was installing peg. The above downloads and installs peg (in /usr/local/bin, you may want to use a private binary location instead), grabs the alpha3 source (from the US, not necessarily best for you), and build a server using the standard options for testing--including debugging and assertion checks. If you want to do performance tests instead, you can do that with peg by setting the PGDEBUG variable to something else. That's covered in the documentation.

Warning: if you try to do this on a RedHat/CentOS system, you will probably discover it won't work. PostgreSQL 8.5 requires a newer version of the Flex tool in order to build from an early source code build now than is available in RHEL5 and its derivatives. I've got some notes on Upgrading Flex from source RPM to compile PostgreSQL from CVS you can read. There were official RPM packages of alpha2 released that bypass this problem, you may be able to get an alpha3 set from there in the near future too.

My goal was to make learning how to use peg pay for itself in time savings the first time you use it for testing a PostgreSQL development snapshot. The script still has some rough edges, but I've been using it regularly for over a month now with minimal functional issues. The documentation is almost complete, even showing examples of how to use peg to create two PostgreSQL installs on the same host--which allows testing hot standby even if you've got only one system.

peg is hosted on github, I've heavily noted the things that need improvement with TODO tags in the source and documentation, and patches are welcome. Hope it helps you out, and that you'll be joining the 8.5-alpha3 testers.

PostgreSQL at the LISA conference in Baltimore

This week the Usenix LISA Conference is running in downtown Baltimore. There will be a PostgreSQL booth in the exhibition area from noon-7pm on Wednesday and from 10am-2pm on Thursday. Robert Treat is the lead elephant for this show, and is too busy with booth setup to have time to write fluff pieces like this one. I'm co-hosting, and we have some other volunteers you might see too. We're basically the least attractive booth babes ever, but if you're looking to talk about open-source databases and in the neighborhood, we're more informative than the local dolphin alternatives.

This week is also my first working for 2ndQuadrant, as the latest addition to their global PostgreSQL and replication consulting staff. If you're in the US, were interested in 2ndQuadrant's services or array of training classes, but figured that would be too hard to coordinate with their UK or Italian offices, that's something I can help out with now.

Watching a hard drive die

One thing I get asked all the time is how to distinguish between a hard drive that is physically going bad and one that is just not working right from a software perspective. This week I had a drive fail mysteriously and saved the session where I figured out what went wrong to show what I do. It's easy enough to find people suggesting "monitor 'x'" for your drive, where 'x' varies a bit depending on who you ask. Writing scripts to do that sort of thing is easier if you've seen how a bad one acts, which (unless you're as lucky as me) you can't just see on demand easily. This is part one to a short series I'm going to run here about hard drive selection, which will ultimately lead to the popular "SATA or SAS for my database server?" question. To really appreciate the answer to that question, you need to start at the bottom first, with how errors play out on your computer.

Our horror story begins on a dark and stormy night (seriously!). I'm trying to retire my previous home server, an old Windows box, and migrate the remainder of its large data files (music, video, the usual) to the new Ubuntu server I live on most of the time now. I fire up the 380GB copy on the Windows system a few hours before going to bed, using cygwin's "cp -arv" so I won't get "are you sure?" confirmations stopping things. I expected it will be finished in the morning. I check up on it later, and the copy isn't even running anymore. Curious what happened, I ran "du -skh /cygdrive/u/destination" to figure out how much it did copy before dying. In the middle of that, the drive starts making odd noises, and the whole system reboots without warning. This reminds me why I'm trying to get everything out of Windows 2000.

At this point, what I want to do is look at the SMART data for the drive. The first hurdle is that I can't see that when the disk is connected via USB. A typical USB (and Firewire) enclosure bridge chipset doesn't pass through requests for SMART data to the underlying drive. So when things go wrong, you're operating blind. Luckily, this enclosure also has an eSATA connector, so I can connect it directly to the Linux PC to see what's going on. That connection method won't have the usual external drive limitations. If that weren't available, I'd have to pull the drive out of its enclosure and connect directly to a Linux system (or another OS with the tools I use) to figure out what's going on.

(If you don't normally run Linux, you can install smartmontools on Windows and other operating systems. Just don't expect any sort of GUI interface. Another option is to book a Linux live CD; I like Ubuntu's for general purpose Linux work, but often instead use the SystemRescueCd for diagnosing and sometimes repairing PC systems that are acting funny.)

Plugged the drive into my desktop Linux system, "tail /var/log/messages" to figure out what device it gets assigned (/dev/sdg), and now I'm ready to start. First I grab the drive's error log to see if the glitch was at the drive hardware level or not:

$ sudo smartctl -l error /dev/sdg
smartctl version 5.38 [x86_64-unknown-linux-gnu] Copyright (C) 2002-8 Bruce Allen
...
Error 2 occurred at disk power-on lifetime: 153 hours (6 days + 9 hours)
  When the command that caused the error occurred, the device was active or idle.

  After command completion occurred, registers were:
  ER ST SC SN CL CH DH
  -- -- -- -- -- -- --
  84 51 00 00 00 00 a0

  Commands leading to the command that caused the error were:
  CR FR SC SN CL CH DH DC   Powered_Up_Time  Command/Feature_Name
  -- -- -- -- -- -- -- --  ----------------  --------------------
  ec 00 00 00 00 00 a0 08      00:00:17.300  IDENTIFY DEVICE

Error 1 occurred at disk power-on lifetime: 154 hours (6 days + 10 hours)
  When the command that caused the error occurred, the device was active or idle.

  After command completion occurred, registers were:
  ER ST SC SN CL CH DH
  -- -- -- -- -- -- --
  40 51 01 01 00 00 a0  Error: UNC 1 sectors at LBA = 0x00000001 = 1

  Commands leading to the command that caused the error were:
  CR FR SC SN CL CH DH DC   Powered_Up_Time  Command/Feature_Name
  -- -- -- -- -- -- -- --  ----------------  --------------------
  25 03 01 01 00 00 a0 ff      11:17:17.000  READ DMA EXT
  25 03 01 01 00 00 a0 ff      11:17:17.000  READ DMA EXT
  25 03 30 5e 00 d4 48 04      11:17:05.800  READ DMA EXT
  25 03 40 4f 00 d4 40 00      11:16:56.600  READ DMA EXT
  35 03 08 4f 00 9c 40 00      11:16:56.600  WRITE DMA EXT

Getting DMA read/write errors can be caused by driver or motherboard issues, but failing to idenitify the device isn't good. Is the drive still healthy? By a rough test, sure:

$ sudo smartctl -H /dev/sdg
SMART overall-health self-assessment test result: PASSED

This is kind of deceptive though, as we'll see here. The next thing we want to know is how old the drive is right now and how many reallocated sectors are there. Those are the usual first warning sign that a drive is losing small amounts of data. We can grab just about everything from the drive like this (bits clipped from the output in all these examples to focus on the relevant parts):

$ sudo smartctl -a /dev/sdg
...
SMART Attributes Data Structure revision number: 16
Vendor Specific SMART Attributes with Thresholds:
ID# ATTRIBUTE_NAME          FLAG     VALUE WORST THRESH TYPE      UPDATED  WHEN_FAILED RAW_VALUE
  1 Raw_Read_Error_Rate     0x000b   092   092   016    Pre-fail  Always       -       2621443
  2 Throughput_Performance  0x0005   100   100   050    Pre-fail  Offline      -       0
  3 Spin_Up_Time            0x0007   111   111   024    Pre-fail  Always       -       600 (Average 660)
  4 Start_Stop_Count        0x0012   100   100   000    Old_age   Always       -       74
  5 Reallocated_Sector_Ct   0x0033   100   100   005    Pre-fail  Always       -       7
  7 Seek_Error_Rate         0x000b   100   100   067    Pre-fail  Always       -       0
  8 Seek_Time_Performance   0x0005   100   100   020    Pre-fail  Offline      -       0
  9 Power_On_Hours          0x0012   100   100   000    Old_age   Always       -       154
 10 Spin_Retry_Count        0x0013   100   100   060    Pre-fail  Always       -       0
 12 Power_Cycle_Count       0x0032   100   100   000    Old_age   Always       -       69
192 Power-Off_Retract_Count 0x0032   100   100   050    Old_age   Always       -       77
193 Load_Cycle_Count        0x0012   100   100   050    Old_age   Always       -       77
194 Temperature_Celsius     0x0002   114   114   000    Old_age   Always       -       48 (Lifetime Min/Max 18/56)
196 Reallocated_Event_Count 0x0032   100   100   000    Old_age   Always       -       12
197 Current_Pending_Sector  0x0022   100   100   000    Old_age   Always       -       4
198 Offline_Uncorrectable   0x0008   100   100   000    Old_age   Offline      -       0
199 UDMA_CRC_Error_Count    0x000a   200   253   000    Old_age   Always       -       4
...

Make sure to scroll this to the right, the last column is the most important. UDMA_CRC_Error_Count matches the errors we're still seeing individually. But the real smoking gun here, and in many other cases you'll see if you watch enough drive die, is Reallocated_Sector_Ct (7) and its brother Reallocated_Event_Count (12). Ignore all the value/worst/thresh nonsense; that data is normalized by this weird method that doesn't make any sense to me. The "raw_value" is what you want. On a healthy drive, there will be zero reallocated sectors. Generally, once you see even a single one, the drive is on its way out. This is always attribute #5, I like to monitor #194 (temperature) too because that's a good way to detect when a system fan has died. The drive overheating can be a secondary monitor for that very bad condition. You can even detect server room cooling failures that way, it's fun watching a stack of servers all kick out temperature warnings at the same time after the circuit the AC is on blows.

The other thing to note here is Power_On_hours. Here the raw value (154 hours) confirms that the recent errors in the logs did just happen. This is a backup drive I only power on to copy files to and from it, and it's dissapointing that it's died with so little life. Why that happened and how to prevent it is another topic.

Next thing to do is to run a short self-test, wait a little bit, and check the results. This drive is loud enough that I can hear when the test is running, and it doesn't take long:

$ sudo smartctl -t short /dev/sdg
$ sudo smartctl -l selftest /dev/sdg
...
Num  Test_Description    Status                  Remaining  LifeTime(hours)  LBA_of_first_error
# 1  Short offline       Completed: read failure       60%       153         717666530

Here's the thing to realize: the drive reports "healthy" to the simple test. But isn't; there are reallocated sectors, and even the simplest of self-checks will find them. Enterprise RAID controllers can be configured to do a variety of "scrubbing" activities when the drives aren't being used heavily, and this is why they do that: early errors can get caught by the drive long before you'll notice them any other way. Nowadays drives will reallocate marginal sectors without even reporting an error to you, so unless you look at this data yourself you'll never know when the drive has started to go bad.

At this point I ran a second short test, then an extended one; here's the log afterwards:

$ sudo smartctl -t short /dev/sdg
$ sudo smartctl -t long /dev/sdg
$ sudo smartctl -l selftest /dev/sdg

Num  Test_Description    Status                  Remaining  LifeTime(hours)  LBA_of_first_error
# 1  Extended offline    Completed: read failure       90%       157         2454905
# 2  Short offline       Completed: read failure       40%       156         711374978
# 3  Short offline       Completed: read failure       60%       153         717666530

The extended test found an error even earlier on the disk. It seems pretty clear this drive is failing quickly. At this point, there's little hope for it beyond saving any data you can (which I did before even launching into this investigation) and moving toward running the manufacturer's diagnostic software. What I'd expect here is that the drive *should* get marked for RMA if it's already in this condition. It's possible it will "fix" it instead. That's a later topic here.

In short, there are a few conclusions you can reach yourself here, and since this is a quite typical failure I can assure you these work:

• Try not to connect external drives to a Windows server if you can avoid it, as they're pretty error prone and Windows isn't great at recovering from this sort of error. This fact is one reason I get so many of these requests to help distinguish true hardware errors from Windows problems.


• Drives that are setup such that they can't check themselves via SMART are much more likely to quietly fail. Had this one been connected directly rather than via USB, I could have picked up this problem when the reallocated sector count was lower and decreased my risk of data loss.


• If you're taking an offline drive out of storage to use it again, a short SMART check and look at the logs afterwards is a good preventative measure. Some drives even support a "conveyance" self-test aimed for checking quality after shipping, this one didn't so I went right from the short to long tests.


• When monitoring regularly with smartmontools, you must monitor reallocated sectors yourself, the health check will not include them in its calculations. They are an excellent early warning system for errors that will eventually lead to data loss.


• Regularly running the short SMART check doesn't introduce that high of a disk load, and it is extremely good at finding errors early too. I highly recommend putting a periodic SMART self-test on your server outside of peak hours if you can, if you're not using some other form of data consistency scrubbing


• Running a baseline SMART self-test when you first put a drive into service helps provide a baseline showing good performance before things go wrong. I didn't do that in this case and I wish I had that data for comparison. It's helpful for general drive burn-in too.
  

That's the basics of what an error looks like when you catch it before the drive stops responding altogether. I've found this is quite often the case. Anecdotally, 75% of the drive failures I've seen in the last seven years (3/4 failures since when I started doing this) show up like this before the drive stops responding altogether. Some of the larger drive studies floating around recently suggest it's not quite that accurate as an early warning for most, it's certainly much better than not checking for errors at all.

The fact that this drive died somewhat mysteriously, in a way that it even still passed its SMART health check, has some interesting implications for its suitability in a RAID configuration. That's where I'm heading with this eventually.

Whatever OS you're running, you should try to get smartmontools (or a slicker application that does the same thing) running and setup to e-mail you when it hits an error. That regime has saved my data on multiple occasions.

Upgrading Flex from source RPM to compile PostgreSQL from CVS

This week I'm working on reviewing a patch that's part of the current PostgreSQL CommitFest, the periodic points where all outstanding patches are looked at and committed if ready. The patch I'm looking at requires some performance testing, and all my servers I'd do that on are running CentOS 5, the popular RedHat Enterprise clone. There's a fun surprise waiting for anyone else who tries this: as of last week, you can't build PostgreSQL from the development CVS or GIT repositories anymore on that platform without updating the Flex package from the default one.

The problem started when I ran "make" to build everything and saw this:

make[3]: Entering directory `/home/gsmith/pgproject/pgsql.tpgbench/src/backend/bootstrap'
***
ERROR: `flex' is missing on your system. It is needed to create the
file `bootscanner.c'. You can either get flex from a GNU mirror site
or download an official distribution of PostgreSQL, which contains
pre-packaged flex output.
***

I knew I had flex on my system so this was kind of confusing. Usually there's more detail about dependency failures in the config.log file, and sure enough it had the details:

configure:6793: WARNING:
*** The installed version of Flex, /usr/bin/lex, is too old to use with PostgreSQL.
*** Flex version 2.5.31 or later is required, but this is /usr/bin/lex version 2.5.4.
configure:6807: result: no
configure:6815: WARNING:
*** Without Flex you will not be able to build PostgreSQL from CVS nor
*** change any of the scanner definition files.  You can obtain Flex from
*** a GNU mirror site.  (If you are using the official distribution of
*** PostgreSQL then you do not need to worry about this because the Flex
*** output is pre-generated.)

A quick check shows this change was made to allow some more powerful scanning capabilities in the PostgreSQL language parser, followed by inserting that warning.

Now, while it's possible to just build quickly from source and install directly over top of the existing flex in this case, I don't like to do that on production systems (or even development ones). It's not a good idea to mix the RPM packages on the system with stuff installed that way. It's better if you can create a new flex RPM package based on newer source code and upgrade to that one, then everything stays managed consistently with RPM.

You can locate SRPMs with a newer version of flex, the one released with Fedora 9, from the following locations:

• kernel.org


• stanford.edu


• usc.edu

Those were the first I found with a new enough version number that they should work. You could easily try to substitute the flex SRPM that comes with Fedora 10 or Fedora 11 instead, haven't tested that here myself yet to say how that goes; probably fine.

I used the kernel.org link, downloaded and installed like this:

$ wget ftp://mirrors.kernel.org/fedora/releases/9/Fedora/source/SRPMS/flex-2.5.35-1.fc9.src.rpm
$ sudo rpm -i flex-2.5.35-1.fc9.src.rpm 

This dumps the source into /usr/src/redhat:

$ cd /usr/src/redhat/
$ ls SOURCES SPECS
SOURCES:
flex-2.5.35.tar.bz2

SPECS:
flex.spec

You can then build like this:

$ cd /usr/src/redhat/SPECS/
$ sudo rpmbuild -bb flex.spec 

That produces the new RPM we want:

$ ls -l /usr/src/redhat/RPMS/x86_64/
-rw-r--r-- 1 root root 322619 Jul 22 17:14 flex-2.5.35-1.x86_64.rpm
-rw-r--r-- 1 root root 283675 Jul 22 17:14 flex-debuginfo-2.5.35-1.x86_64.rpm

Now install it:

$ cd /usr/src/redhat/RPMS/x86_64/
$ ls
flex-2.5.35-1.x86_64.rpm  flex-debuginfo-2.5.35-1.x86_64.rpm
$ rpm -qa flex
flex-2.5.4a-41.fc6
$ sudo rpm -Uvh flex-2.5.35-1.x86_64.rpm 
Preparing...                ########################################### [100%]
   1:flex                   ########################################### [100%]
$ rpm -qa flex
flex-2.5.35-1

And then cleanup:

$ cd /usr/src/redhat/SPECS/
$ sudo rpmbuild --clean --rmsource flex.spec
Executing(--clean): /bin/sh -e /var/tmp/rpm-tmp.99970
+ umask 022
+ cd /usr/src/redhat/BUILD
+ rm -rf flex-2.5.35
+ exit 0

After that, I had to run "configure" again to pick up the change, ran "make again", and now my build against CVS checkout of the future PostgreSQL 8.5 in progress compiles without any problems.

Note that normally, when you substitute a package like this you need to be careful you keep up with security patches to it because you're not going to get them automatically anymore. Since flex is a pretty low-level tool used only for developing software, I'm not too concerned about the security implication of my running a custom version here.

Bottom-up PostgreSQL benchmarking and PGCon2009

Last week I got a lot of positive feedback from my PGCon presentation in Ottawa about how to benchmark systems at a low-level when the intended application is to run a database. There were three main topics I was trying to cover in that:

1. Why you should always run your own hardware benchmarks on every piece of hardware you can
   
2. Examples of the simplest benchmarks I've found to be accurate
3. How do organize your tests and your vendor interactions to support performance measurement as a purchasing requirement

There was one slide missing from the set I presented. I've uploaded a version of the slides that fixes that (along with a typo in the sysbench seeks slide) to my home page. For those who missed it, a couple of people have put their notes from the talk as part of PGCon coverage on the PG wiki, and video of many talks from the conference is already available from FOSSLC.

Also available on my web page now is a presentation I did last month at PG East 2009. Titled "Using and Abusing pgbench", that talk also has 3 things it tries to convey:

1. How does pgbench and its internal scripting language work? (Most people aren't even aware there is such a scripting language available)
   
2. What should you do in order to get good results from the built-in pgbench tests?
3. How can you use pgbench as a test harness for writing your own tests?

The hardware benchmarking presentation ends where the pgbench ones starts, with a bit of overlap. That's intentional--I always consider pgbench tests to be something you should do only after confirming all of your hardware does the right thing, top to bottom. A perfect example just came out recently: even someone who's done as much benchmarking work as Joshua Drake can end up measuring the wrong thing, because he skipped the step I suggest for confirming expected commit rate before moving onto higher-level pgbench tests. Since not many people saw the pgbench talk at PG East I'm hoping to repeat that one in the near future to a larger audience.

As part of putting that presentation together, I did more work on a toolchain I've been using for a couple of years now (since I was working on 8.3 development) I've named pgbench-tools. The current 0.4 release posted to my home page is the first to benefit from having some users, which has gotten me an enormous amount of feedback toward making the program bug-free and more usable. Thanks in particular to Robert Treat and Jignesh Shah for their contributions. I think it's finally mature enough that it might be useful for others who want to automate running large numbers of pgbench tests too.

Documentation is still minimal, but I have written some (and what's there is accurate, both of which put me ahead of a lot of open-source projects I guess). There is an into README in the tar file and the presentation tries to give some examples of usage too. When I get more time I'm putting the source code into the PostgreSQL git repository (the repo is already there, I just haven't pushed to it yet), where it will be easier for other people to work with and on. There's a growing need in the PG community for regression testing of performance results, and at the yearly PGCon Developer Meeting I volunteered to see if an improved version of this pgbench-tools package might be useful in that role. I hope the ideas in my presentations and the suggested practice demonstrated by these tools turns out to be helpful to others.

The approach taken in pgbench-tools, that you should parse results from pgbench, save them to a database, and then graph the lot of them using SQL to summarize as needed, is only partially mine. I stole the first rev of the graphing code and several other ideas from the work Mark Wong and others did on the dbt2 program (here's an intro to using dbt2). Now that I've got something useful for my purposes and am free from conferences for a while, I'm hoping to spend some time investigating how to integrate the unique things I'm doing with some of the tools he's already written. The biggest thing the dbt tests have that I haven't provided for pgbench yet is a framework for measuring I/O and similar statistics during the test run. Given that the PostgreSQL development process already has a heavy requirement on Perl, I really should fall into line and adopt that myself too--despite my strong personal preference for Python in this role.

Automating initial postgresql.conf tuning

About two years ago I returned to full-time work on PostgreSQL performance tuning for a contract job, after having done some earlier work in that area for a personal project. One of the things that struck me as odd was that no progress had been made in the intervening years toward providing an automatic tool to help with that goal. Since many of the existing guides only covered older versions of PostgreSQL (the 8.1 release changed what you can adjust usefully quite a bit), even the few template suggestions for various machine sizes floating around the web were largely obsolete--not to mention that the modern hardware was a lot more capable. When I tried writing an automatic tool myself, I started to realize just how much of a harder problem it was than it seemed.

One stumbling block is that you need a fair amount of information about the parameters in the postgresql.conf file, and some of that information wasn't all available any way except to read the associated source code. There's enough information embedded only there that it was impractical to expect a potential tool writing author to extract and maintain their own copy of the settings database. For example, I had to submit a patch myself earlier this year to make it possible to easily discover what value a parameter you don't set will get. Now you can look at the pg_settings view and that shows up as "boot_val".

A second problem is that coming up with a robust model for what to set everything to was harder than it seems at first. Earlier this year I worked with some other community members on codifying some of that into tuning guidelines, and there was a somewhat arcane tuning spreadsheet from Josh Berkus floating around. After showing I was close to an initial solution to this long outstanding TODO item, Josh came to my aid with a simplified model that was easy to implement as code.

There have been more than one attempt to write this kind of tool that fizzled after some initial work. The other classic trap people have fallen into here is worrying about the user interface before getting all the boring parts done. It takes a fair amount of code to read a postgresql.conf file usefully, learn the rules for what can you do with it (again, some of which are only documented in the database source code), and publish an updated file. As an example there, while it's possible to just maintain a list of which parameters are specified using "memory units" and what unit they default to (some are multiples of a single kB, others in 8kB blocks), what you really should do is look at the "unit" column that comes out of pg_settings instead to figure that out. And to do that, you need to save a copy of pg_settings into a file (since at the point this tool is being run the server isn't up yet) and load it into memory such that the related code can rely on it. Then it's trivial for the tool to distinguish between a value like "max_connections" (which is a simple integer that accepts no additional units) and "work_mem", which is specified in 1kB units but you can use 'MB' and 'GB' suffixes for. Oh, and don't forget the upper limit for that particular setting differs depending on whether you're on a 32 or 64 bit platform. What fun.

One of the guidelines I try to live by is "don't complain about anything you're not willing to help fix yourself", and I've done my share of complaining on this topic. This past holiday weekend, in between rounds of turkey overdose I reached a new milestone here: my post to the pg hackers mailing list yesterday includes a Python implementation that solves all the traditional problems besetting tuning tools authors in the context of the upcoming PostgreSQL 8.4. You feed it an existing postgresql.conf file and tell it what kind of database application you expect to build (data warehouse, web app, etc.). It tries to figure out how much RAM you have, then produces an updated postgresql.conf file for you with updates to 10 critical parameters.

Since the default configuration file for PostgreSQL is aimed at working with the conservative parameters many operating systems ship with, that makes those defaults only appropriate for a system having approximately 128MB of total RAM. If you have more than that and haven't considering performance tuning improvements for your PostgreSQL database, this tool, the model expressed in its source code, or a future version of it (once 8.4 is done I plan a version backward compatible with versions 8.1-8.3) may eventually be useful to you.

And if you're one of the frustrated potential tool authors who was stopped by one of the problems I mentioned here, consider this code release as an initial resource you can use. Even if it's not your preferred language, Python is pretty easy to read. I think it's easier to start by using this program as a spec to reference and emulate than it is to dig into the source code and resources I did to write it.

Linux disk failures: Areca is not so SMART

One of the most frequently asked questions on the PostgreSQL Performance list is "what disk controller works best with PostgreSQL?" It's a tough question; every product has different things it's good and bad at, and it can be hard to through all that to figure out what makes sense for your application. You need to focus equally on performance and reliability, as disk drives are very prone to failure.

I have a mini-server at home I use mainly for performance testing. The main thing that makes it a mini-server rather than an oversized desktop is that I've got an Areca ARC-1210 as the disk controller, which when I bought it cost more than the whole rest of the computer I installed it in. Areca is a popular recommendation because for a while there, their products were considerably faster per dollar than the competition in the SATA RAID space (mainly 3Ware and LSI Logic), and reports of how the controllers handled disk failure suggested reasonable behavior there.

I knew the performance was good, but with the relatively small number of drives I run at home I hadn't actually run into a SATA disk failure to comment on that part of the controller's firmware yet. Luckily Murphy stalks my life, and during a weekend I really didn't have time to fool around with hardware I got my chance. Snipped from /var/log/messages:

sd 0:0:1:0: [sdb] Result: hostbyte=DID_OK driverbyte=DRIVER_SENSE,SUGGEST_OK
sd 0:0:1:0: [sdb] Sense Key : Medium Error [current]
Info fld=0x0
kernel: sd 0:0:1:0: [sdb] Add. Sense: Unrecovered read error
end_request: I/O error, dev sdb, sector 33470855
sd 0:0:1:0: [sdb] Result: hostbyte=DID_OK driverbyte=DRIVER_SENSE,SUGGEST_OK
kernel: sd 0:0:1:0: [sdb] Sense Key : Medium Error [current]
kernel: Info fld=0x0
kernel: sd 0:0:1:0: [sdb] Add. Sense: Unrecovered read error
end_request: I/O error, dev sdb, sector 33470855

Here the end_request lines show the sector on the drive that went bad--two of them here, there were plenty more. This disk was part of a 3-disk software RAID0 set, which meant the file these sectors were in the middle of was trashed. Obviously I wasn't optimizing for reliability using RAID0 so the file wasn't important. What was important is that I finally had a chance to see exactly how the Areca controller handled this situation.

Normally what you do when a drive looks like it's going bad is to run smartctl. Actually, you should be running smartctl proactively all the time to try and catch these errors as soon as possible, and getting e-mailed the results. Again, this is a development and test system so I hadn't set all that up. And the results when I tried to run a scan on this disk were disappointing:

# smartctl --all /dev/sdb
smartctl version 5.36 [x86_64-redhat-linux-gnu] Copyright (C) 2002-6 Bruce Allen
Home page is http://smartmontools.sourceforge.net/

Device: WDC      WD1600AAJS-00WAA Version: R001
Serial number: WD-WCAS20994885
Device type: disk
Transport protocol: Fibre channel (FCP-2)
Local Time is: Sun Aug 24 02:23:58 2008 EDT
Device supports SMART and is Enabled
Temperature Warning Disabled or Not Supported
SMART Health Status: OK

Current Drive Temperature:     30 C
Drive Trip Temperature:        25 C
Manufactured in week 30 of year 2002
Current start stop count:      256 times
Recommended maximum start stop count:  4278190080 times
Elements in grown defect list: 0

Error counter log:
       Errors Corrected by           Total   Correction     Gigabytes    Total
           ECC          rereads/    errors   algorithm      processed    uncorrected
       fast | delayed   rewrites  corrected  invocations   [10^9 bytes]  errors
read:          0        0         0         0          0          0.000           0
write:         0        0         0         0          0          0.000           0

Non-medium error count:        0
Device does not support Self Test logging

The data returned here is clearly bogus--every time I looked at it after encountering errors, the counters were still all 0. Generally the best way to figure out what's going on is to run a self-test:

# smartctl -t long /dev/sdb
smartctl version 5.36 [x86_64-redhat-linux-gnu] Copyright (C) 2002-6 Bruce Allen
Home page is http://smartmontools.sourceforge.net/

Long (extended) offline self test failed [unsupported scsi opcode]

Oops. Much like how the other counters aren't supported, the Areca controller doesn't pass through SMART test requests. I vaguely recalled that from when I was doing the shopping for this card. Areca supplies a couple of utilities on their FTP site to handle chores like this. The first one, the HTTP server, lets you access all the card configuration you might normally do as part of the BIOS setup from a web browser. You run that utility and then access it via ports 81 and 82 on localhost. That worked OK but didn't tell me too much.

The other thing they provide, the part I was intending to rely on here, is the command line interface program. They provide two versions for both 32-bit and 64-bit Linux. The problem with this utility is that unlike the disk driver, the CLI is not open source.

The Areca driver made its way into the mainstream Linux kernel as of 2.6.19, and has been improved significantly by contributors outside of Areca since. The version that ships with kernel 2.6.22 is the one I recommend, that rev is solid. Areca's CLI they provide in binary form, tested against a couple of supported distributions, and that's all you get.

My server system was recently upgraded to run Ubuntu 8.04.1 in addition to the CentOS 5.2 it's had on it for a while now. The CLI tool didn't work at all on Ubuntu; it didn't find the device and I have no idea why. If you're really committed to having an true open-source stack for your controller card, perhaps because you value being able to understand and possibly resolve issues yourself when they happen, cross Areca off your list. Their essential CLI utility can easily break on you and leave you stuck.

Luckily I still had the CentOS partition setup, and as that's a derivative of the supported RHEL their CLI is aimed at it worked fine there. Here's a little session I had with it, looking for what went wrong here:

Copyright (c) 2004 Areca, Inc. All Rights Reserved.
Areca CLI, Version: 1.80A, Arclib: 260, Date: Oct 12 2007( Linux )

Controllers List
----------------------------------------
Controller#01(PCI): ARC-1210
Current Controller: Controller#01
----------------------------------------

CMD     Description
==========================================================
main    Show Command Categories.
set     General Settings.
rsf     RaidSet Functions.
vsf     VolumeSet Functions.
disk    Physical Drive Functions.
sys     System Functions.
net     Ethernet Functions.
event   Event Functions.
hw      Hardware Monitor Functions.
mail    Mail Notification Functions.
snmp    SNMP Functions.
exit    Exit CLI.
==========================================================
Command Format: cmd [Sub-Command] [Parameters].
Note: Use cmd -h or -help to get details.

CLI> event info
Date-Time               Device              Event Type     
===============================================================================
2008-08-24  06:14:51    H/W MONITOR         Raid Powered On
2008-08-24  06:01:35    H/W MONITOR         Raid Powered On
2008-08-24  05:48:13    Proxy Or Inband     HTTP Log In
2008-08-24  05:44:24    IDE Channel #02     Reading Error
2008-08-24  05:44:16    IDE Channel #02     Reading Error
2008-08-24  05:44:08    IDE Channel #02     Reading Error
2008-08-24  05:43:54    IDE Channel #02     Reading Error
2008-08-24  05:43:46    IDE Channel #02     Reading Error
2008-08-24  05:37:12    Proxy Or Inband     HTTP Log In
2008-08-24  05:20:18    H/W MONITOR         Raid Powered On

That's pretty clear: adjusting for time zone the disk attached to channel #02 (which is in fact /dev/sdb) had a read error at the same time the I/O errors showed up in the kernel logs. So the controller did catch that. Clearly any production system with an Areca controller should be monitoring the output from "cli
event info" for these messages.

If I were running this in a hardware RAID environment, I suspect the Areca controller would have kicked the disk out after that. Sometimes, though, the SMART errors are more subtle. I like to schedule an extended disk test for any production drive at least once a week, trying to find issues before they actually wipe data out. Here's the options there, and what the SMART info for a good disk in the system looks like:

CLI> disk -h
smart       Parameter: 
        Fn: Display Disk S.M.A.R.T Data.
        Ex: Display Disk#1 S.M.A.R.T Data.
        Command: disk smart drv=1 [Enter]

dctest      Parameter: .
        Fn: Perform A S.M.A.R.T Off-Line Data Collection.
        Ex: Perform A Test On Disk#1~4.
        Command: disk dctest drv=1~4 [Enter]

sttest      Parameter:  [mode=].
        Fn: Perform A S.M.A.R.T Off-Line Short or Extended Self-Test.
        Ex: Perform A Short Self-Test On Disk#1~4.
        Command: disk sttest drv=1~4 mode=short [Enter]
        Note: Default Mode=Short.

CLI> disk smart drv=1
S.M.A.R.T Information For Drive[#01]
# Attribute Items                           Flag   Value  Thres  State
===============================================================================
1 Raw Read Error Rate                       0x0f     200     51  OK      
3 Spin Up Time                              0x03     152     21  OK      
4 Start/Stop Count                          0x32     100      0  OK      
5 Reallocated Sector Count                  0x33     200    140  OK      
7 Seek Error Rate                           0x0e     100     51  OK      
9 Power-on Hours Count                      0x32      99      0  OK      
10 Spin Retry Count                          0x12     100     51  OK      
11 Calibration Retry Count                   0x12     100     51  OK      
12 Device Power Cycle Count                  0x32     100      0  OK      
192 Power-off Retract Count                   0x32     200      0  OK      
193 Load Cycle Count                          0x32     200      0  OK      
194 Temperature                               0x22     102      0  OK      
196 Reallocation Event Count                  0x32     200      0  OK      
197 Current Pending Sector Count              0x12     200      0  OK      
198 Off-line Scan Uncorrectable Sector Count  0x10     200      0  OK      
199 Ultra DMA CRC Error Count                 0x3e     200      0  OK      
===============================================================================

Here's what the bad one reported:

CLI> disk smart drv=2
S.M.A.R.T Information For Drive[#02]
# Attribute Items                           Flag   Value  Thres  State
===============================================================================
1 Raw Read Error Rate                       0x0f     200     51  OK      
3 Spin Up Time                              0x03     153     21  OK      
4 Start/Stop Count                          0x32     100      0  OK      
5 Reallocated Sector Count                  0x33     200    140  OK      
7 Seek Error Rate                           0x0e     100     51  OK      
9 Power-on Hours Count                      0x32      99      0  OK      
10 Spin Retry Count                          0x12     100     51  OK      
11 Calibration Retry Count                   0x12     100     51  OK      
12 Device Power Cycle Count                  0x32     100      0  OK      
192 Power-off Retract Count                   0x32     200      0  OK      
193 Load Cycle Count                          0x32     200      0  OK      
194 Temperature                               0x22     104      0  OK      
196 Reallocation Event Count                  0x32     200      0  OK      
197 Current Pending Sector Count              0x12     193      0  OK      
198 Off-line Scan Uncorrectable Sector Count  0x10     193      0  OK      
199 Ultra DMA CRC Error Count                 0x3e     200      0  OK      
===============================================================================

Ah ha--so the old "Uncorrectable Sector Count" quality factor
is going down. That's usually a sign of early failure. But how to
trigger a more comprehensive test?

CLI> disk sttest drv=2 mode=extended
ErrMsg: Drive 2 Is Not Available

CLI> disk dctest drv=2
ErrMsg: Drive 2 Is Not Available

Oops. A bit of research reinforced my fear here: if you've got a device that's part of a RAID group or, like in this case, assigned as a JBOD disk, the Areca controller won't let you run a SMART test on it. This is really unreasonable behavior for a disk controller aimed at enterprise use. I only ran into these bad sectors because I used them and then tried to read the result back. The extended test may have caught the read problem before I did that, saving me from the data loss. That's certainly how it's worked for the last two disk failures I ran into at home.

The next question, then, is how to run a full scan over this disk if I can't do it via smart? First I tried rebuilding the RAID0 /dev/md0 filesystem this disk is part of using the bad block checker you can invoke as part of mkfs:

mkfs -j -c -c /dev/md0

This returned clean, no I/O errors at the kernel level. It took many hours to complete though. What about the Areca logs?

Date-Time               Device              Event Type     
===============================================================================
2008-08-24  12:26:37    IDE Channel #02     Reading Error
2008-08-24  12:26:21    IDE Channel #02     Reading Error
2008-08-24  12:26:13    IDE Channel #02     Reading Error
2008-08-24  12:26:05    IDE Channel #02     Reading Error
2008-08-24  12:25:57    IDE Channel #02     Reading Error
2008-08-24  12:25:43    IDE Channel #02     Reading Error
2008-08-24  12:25:35    IDE Channel #02     Reading Error
2008-08-24  12:25:27    IDE Channel #02     Reading Error
2008-08-24  12:25:06    IDE Channel #02     Reading Error
2008-08-24  12:24:59    IDE Channel #02     Reading Error
...

That goes on for a while. Seeing a bunch of errors in the controller that didn't show up in the kernel is kind of disconcerting. I'm left distrusting the way mkfs does this test; perhaps the writes it does to disk are staying cached by the controller, causing the read to return with that cached data? Not sure what's going on here, but I sure don't like it.

Time to test this out at the filesystem level by forcing a read of everything instead, which is easier to do anyway because it doesn't write:

e2fsck -c /dev/md0

Ah, here come the kernel level errors in /var/log/messages. The end_request lines are the important ones here, here's the first of those (so many showed up they didn't even all appear in the logs):

end_request: I/O error, dev sdb, sector 34426095
end_request: I/O error, dev sdb, sector 34426127
end_request: I/O error, dev sdb, sector 34426151
end_request: I/O error, dev sdb, sector 34428623
end_request: I/O error, dev sdb, sector 34428623
end_request: I/O error, dev sdb, sector 34444831
end_request: I/O error, dev sdb, sector 34453535
end_request: I/O error, dev sdb, sector 34453631

It ends saying that it made modifications to the filesystem, presumably to mark the blocks that were bad. But how do you find out where those are? You can get that info with the superblock header option of dumpe2fs:

# dumpe2fs -f /dev/md0
Bad blocks: 12909760, 12909778, 12909779, 12909785, 12909790, 12909791, 12909796, 12909797, 12909802, 12909803, 12909809, 12909814, 12909815, 12909820, 12909821, 12910712, 12910714, 12916792, 12920048, 12920092

What have we learned:

• The Areca controller has serious problems reporting SMART errors when you're using it in JBOD mode. It seems it's only really designed for handling all that internally
• The CLI they provide is a closed source mess that you must test for compatibility
• You must monitor the Areca event log using their CLI if you're using their controller in production
• The controller doesn't support any user-requested SMART scans
• On a caching controller, using "e2fsck -c" may give more reliable results than the full "mkfs -c"

Since the disk here is obviously trashed, and I can't do any of the SMART operations to try and make it better when attached to the Areca controller, at this point I moved it to another system and replaced it with the spare I had for this array. I didn't lose any data here, but I did find myself a lot less comfortable recommending Areca controllers.

I just pointed out last month that current 3ware controllers have caught up with Areca's performance. Of course, as far as I can tell, 3ware's tw_cli utility is just as closed source as Areca's. But since you can run smart tests directly with the Linux smartctl on 3ware controllers, all you really need the CLI for is looking inside the hardware RAID to see what the disk status there is.

As for what I went through trying to get the failing drive back to operating order again, that's a whole 'nother story.

"The essential postgresql.conf" at the BWPUG

One of the common complaints about how you setup and tune a PostgreSQL server is that the postgresql.conf file is way too big, and there's no good guidance to where you should start. I've been talking and corresponding with many PostgreSQL administrators over the last few months, and getting writing help from Christopher Browne and then Robert Treat, to work on that problem. I'm proud to announce a document to address that need that is now both current and pretty complete, while not being overwhelming: Tuning Your PostgreSQL Server.

Robert and I finished this off just in time to present the results at this week's Baltimore/Washington PostgreSQL User Group meeting, providing edutainment going through the whole list of 18 most important parameters (plus logging). Thanks to Robert, Chris, the regulars on the PostgreSQL mailing lists, and everyone who gave us feedback at the meeting (now applied to the Wiki document) for helping to bring all this information together into one relatively easy to digest bite.

A Linux write cache mystery

One happy coincidence for me last month is that I discovered a friend of mine had built a Linux-based server running PostgreSQL and put it into a colo, something I had been pricing out myself. He created me an account and now I've got a place to host some personal MediaWiki projects I'd been planning. One known issue my friend mentioned is that he'd been running into regular problems where the whole server just froze up for a few seconds. Because of the pattern of when it happened, he suspected it was related to heavy writes, and furthermore suspected the software RAID-1 implementation. Since that seemed unlikely to me, I told him to hold off on breaking the RAID until I could take a look at things.

The server is a quad-core system with 8GB of RAM and a pair of SATA disks in software RAID-1. The OS is CentOS 5.2, based on the RHEL5 release, and it's using kernel 2.6.18 (the regular one, not the Xen one).

I started by doing the same read/write testing I always do on a system whose I/O I don't necessarily trust or understand: that procedure is outlined at Testing your disks for PostgreSQL. Since the server has 8GB of RAM I used 2,000,000 blocks. I opened a couple of sessions to the server, executed that in one window, top in a second, and "vmstat 1" in a third. Watching a single second vmstat is one of the most useful things you can do for figuring out where bottlenecks are at on a system.

In this case, what quickly became apparent is that the system was alternating between healthy periods that looked like this:

procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu------
r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
2  5   5196  46428  28160 6188640    0    0     0 539720 1261  673  0 28 25 47  0
0  6   5196  47268  28212 6047744    0    0     0 988400 1235  615  0 21 25 54  0
0  6   5196  46980  28272 6047216    0    0     0 64032 1260  658  0  6 13 81  0
1  5   5196  45884  28336 6046788    0    0     0 61568 1281  677  0  7  0 93  0
1  5   5196  47276  28400 6043408    0    0     0 65632 1267  665  0  7  0 93  0
0  6   5196  46272  28460 6044080    0    0     0 65568 1272  641  0  6  0 94  0
0  6   5196  48188  28524 6042420    0    0     0 65536 1271  626  0  6  0 94  0
0  6   5196  46228  28592 6044836    0    0     0 66928 1298  668  0  7  0 94  0
0  5   5196  46648  28652 6044812    0    0     0 61504 1264  648  0  6  8 86  0

The bo (block out) number is the number to watch on this write test. That's in KB/s, so the entries in the later section here are all approximately 65MB/s. But at the beginning, it's writing in the Linux disk cache at a really high speed, as much as 988MB/s at the beginning. Note that these numbers are total I/O, which includes both of the disks in the RAID-1 pair. That means the actual per-disk write rate is closer to 32MB/s, a bit on the low side, but that's presumably because the disks are already backlogged with writes from the initial burst.

That's a healthy period. Here's what the unhealthy ones looked like:

procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu------
r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
0  4   3780 935592  32048 5205528    0    0     0     0 1270  371  0  4 25 71  0
0  4   3780 945140  32048 5205528    0    0     0     0 1293  383  0  3 25 72  0
0  4   3780 954316  32048 5205528    0    0     0     0 1271  370  0  3 25 72  0
0  4   3780 963616  32048 5205528    0    0     0     0 1293  385  0  3 25 72  0
1  4   3780 973288  32048 5205528    0    0     0     0 1293  372  0  3 25 72  0
0  4   3780 982464  32048 5205528    0    0     0     0 1280  395  0  3 25 72  0
0  4   3780 992384  32048 5205528    0    0     0     0 1283  373  0  4 25 71  0
0  4   3780 1002180  32048 5205528    0    0     0     0 1320  380  0  3 25 72  0
0  4   3780 1011480  32048 5205528    0    0     0     0 1280  371  0  3 25 72  0
0  4   3780 1021028  32048 5205528    0    0     0     0 1315  389  0  4 25 71  0
0  4   3780 1030204  32048 5205528    0    0     0     0 1280  372  0  3 25 72  0
0  4   3780 1039132  32048 5205528    0    0     0     0 1279  383  0  3 25 72  0
0  4   3780 1049052  32048 5205528    0    0     0     0 1273  369  0  3 25 72  0
0  4   3780 1058600  32048 5205528    0    0     0     0 1285  388  0  3 25 72  0
0  4   3780 1067900  32048 5205528    0    0     0     0 1259  373  0  3 25 72  0
0  4   3780 1077696  32048 5205528    0    0     0     0 1293  388  0  4 25 71  0
0  4   3780 1087740  32048 5205528    0    0     0     0 1267  371  0  3 25 72  0
0  4   3780 1096296  32048 5205528    0    0     0     0 1262  385  0  3 25 72  0
0  4   3780 1106216  32048 5205528    0    0     0     0 1274  367  0  3 25 72  0
1  3   3780 898384  32268 5428364    0    0     0     8 1344  489  1 19 25 55  0
2  4   3780 313476  32816 5991000    0    0     0     0 1248  374  0 28 25 47  0
1  5   5196  47324  27828 6253400    0 1928     0  1928 1289  470  0 19 17 64  0

That's over 20 seconds straight where zero blocks were written. That certainly seems to match the reported problem behavior of a long unresponsive period, and sure enough some of the sessions I had open were less responsive while this was going on. The question, then, is why it's happening? The disks seem to be working well enough; here's the summary at the end of the dd (the version of dd included in RHEL5 now provides this for you):

16384000000 bytes (16 GB) copied, 209.117 seconds, 78.3 MB/s

78MB/s to each disk in the pair is completely reasonable.

I wrote a long paper on how Linux handles heavy writes called The Linux Page Cache and pdflush because I never found a source that really covered what happens in this situation. What I recommend there is watching /proc/meminfo to see what's going on. Here's a little shell bit you can execute to do that:

while [ 1 ]; do cat /proc/meminfo; sleep 1; done

With some practice you can note what numbers are constantly moving, catch when the bad behavior occurs, then hit control-C to break and peruse the last few entries in your terminal app scrollback buffer. Here's what I found right around the same time as the lull periods:

MemTotal:      8174540 kB
MemFree:         62076 kB
Buffers:         21724 kB
Cached:        6158912 kB
SwapCached:          0 kB
Active:        1126936 kB
Inactive:      6101688 kB
HighTotal:           0 kB
HighFree:            0 kB
LowTotal:      8174540 kB
LowFree:         62076 kB
SwapTotal:    16771840 kB
SwapFree:     16766644 kB
Dirty:            6640 kB
Writeback:     3230792 kB ***
AnonPages:     1047724 kB
Mapped:          65736 kB
Slab:           823308 kB
PageTables:      23036 kB
NFS_Unstable:        0 kB
Bounce:              0 kB
CommitLimit:  20859108 kB
Committed_AS:  1893448 kB
VmallocTotal: 34359738367 kB
VmallocUsed:    271572 kB
VmallocChunk: 34359465955 kB
HugePages_Total:     0
HugePages_Free:      0
HugePages_Rsvd:      0
Hugepagesize:     2048 kB

Note the line I starred there for Writeback. At the point where the system was stalling, a full 3.2GB of data was queued to write. That's 40% of RAM. Going back to my Linux page cache paper, you'll find that number listed: 40% is the point where Linux switches to the high dirty_ratio behavior, where all processes are blocked for writes. On a fast server with this much RAM, you can fill gigabytes of RAM in seconds, but writing that out to disk is still going to take a long time. If we work out the math, 3.2GB to write to two disks capable of 78MB/s each works out to...20.5 seconds. Well look at that, almost exactly the length of our long slow period, where process writes were stalled waiting for the kernel to clean up. I love it when the math comes together.

So, what to do? Well, this problem (way too many writes buffered on systems with large amounts of RAM) was so obvious that in the 2.6.22 Linux kernel, the defaults for the caching here were all lowered substantially. This is from the release notes to 2.6.22:

Change default dirty-writeback limits. This means the kernel will write "dirty" caches differently...dirty_background_ratio defaulted to 10, now defaults to 5. dirty_ratio defaulted to 40, now it's 10

A check of this server showed it was using the 2.6.18 defaults as expected:

[gsmith@server ~]$ cat /proc/sys/vm/dirty_ratio
40
[gsmith@server ~]$ cat /proc/sys/vm/dirty_background_ratio
10

So what I suggested to my friend the server admin was to change these to the new values that are now standard in later kernels. It's easy to put these lines into /etc/rc.d/rc.local to make this change permanent after trying it out:

echo 10 > /proc/sys/vm/dirty_ratio
echo 5 > /proc/sys/vm/dirty_background_ratio

After doing that, I re-ran the dd test and things were much better. There were a few seconds where there was a small blip in throughput. During the 4 minute long test I found one 4-second long period writes dropped to the 2MB/s level. But for the most part, the giant bursts followed by lulls were gone, replaced by a fairly steady 130MB/s of writing the whole time. The final dd numbers looked like this after the retuning:

16384000000 bytes (16 GB) copied, 261.112 seconds, 62.7 MB/s

So this did drop average and peak throughput a bit. That's usually how things worst: best throughput to disks usually involves writing in larger bursts, which is efficient but very disruptive. But that's a small price to pay for making the many second long pauses go away.

This particular problem shows up in all kinds of places where heavy writing is being done. Tuning these parameters is also one of the suggestions I make for people running PostgreSQL 8.2 or earlier in particular who want to tune checkpoint behavior better. In that context, there have even been reports of people turning this particular write cache off altogether, which again would lower average throughput, but in that case it was worth it for how much it decreased worst-case behavior.

Time will tell if there's anything else going on that was contributing to the pauses originally reported that is still happening on this server, but this looks like a pretty clear smoking gun that's now been holstered.

pgbench suffering with Linux 2.6.23-2.6.26

About a month ago I got Linux installed on some new quad-core hardware at home, with the intention being that box would primarily be used for my PostgreSQL work which includes a lot of benchmarking. The hardware on the system is new enough that I needed a very recent Linux kernel version for it to work well. It was possible to install my standard distributions, CentOS 5 and Ubuntu 7.04, if I set the motherboard to "ATA/IDE Mode=legacy". But that limited me to old ATA modes without DMA. That topped out at 3MB/s of hard drive speed as measured by hdparm -t, an issue well documented in the libATA FAQ. In order to get reasonable speed (close to 70MB/s) I had to use a much newer kernel that allowed me to switch to AHCI mode. The earliest kernel that worked well was 2.6.22-19.

I should have stopped there.

For kicks, I decided to try the latest kernel at the time, 2.6.24.3, to see if it was any faster. The big change in recent kernels I was curious about was the introduction of the controversial Completely Fair Scheduler (CFS). Because that was merged into the kernel quite a bit faster than I find comfortable, I was rather suspicious of its performance, and I launched into a full set of tests using pgbench.

These went very badly, with an extreme drop in performance compared with earlier versions. By the time I quantified that and reported to PostgreSQL land, I was told Nick Piggin had already reported the problem and a fix was due for 2.6.25, which literally came out in the middle of the night the day was I coming up with a repeatable test case for 2.6.24.

2.6.25 gave the worst pgbench results yet. I did confirm that it's not in the server though; if I run the pgbench client on a remote system, the results scale fine to the level I expect.

It took me some time to organize all my test results and automate my test cases well enough that I thought they could be replicated. This week I submitted a kernel regression report about this problem. Mike Galbraith was easily able to reproduce it, and I've already gotten and tested one patch to improve things.

Still a ways back from 2.6.22 though, and with 2.6.26 being on -rc3 I have my doubts this will be fully wrapped up before that goes live. So here's my suggestions for those of you using PostgreSQL on recent Linux versions who care about performance:

• Kernel 2.6.18 has been a fairly solid kernel for me under RHEL5 and the matching CentOS.
  
• Kernel 2.6.19 was the first to merge the libata drivers and I avoid it because of that.
  
• Kernel 2.6.20 in the form of Ubuntu 7.04 Feisty Fawn seems to have stabilized after the libata merge. I can't comment on the kernel.org distribution.
  
• Kernel 2.6.22 seems solid on everything I throw at it, this is the latest Linux kernel I recommend using for PostgreSQL without serious caveats. The first 2.6.22 came out in July of last year, so it's almost a year old now, and it's currently at 2.6.22-19. Ubuntu's 7.10 Gutsy Gibbon includes 2.6.22.
  
• Kernels 2.6.23 and 2.6.24, the first to use CFS, have known serious server-side PostgreSQL performance issues. The main distributions I'm aware of that are likely impacted here are Fedora Core 8 and Ubuntu 8.04. I expect Fedora to be running bleeding edge kernels with regressions (you could argue the existing of Fedora has pushed kernel testing toward the users rather than being done as much by the developers). I'm really disappointed Ubuntu adopted such a kernel given the rawness of CFS. Already ranted about this a bunch on my last blog post. I think this is just a timing problem for them. Had they adopted 2.6.22 instead, they'd be facing 5 years of LTS with a kernel using a model abandoned by the mainstream development and therefore have nowhere to turn when issues popped up. The right answer would be not to do a LTS right now, but I digress.
  
• Kernel 2.6.25 seems to have resolved the worst of the server-side issues. This is what Fedora Core 9 is running. But results from pgbench should be considered suspect, particularly under high client loads, and without that working I can't really prove to myself that 2.6.25 is fine in all the situations I like to test.
  
• Kernel 2.6.26 may get some fixes in to improve pgbench, depends on how things go. The patch I've already gotten closed much of the performance gap with only a few lines of code changed. You can follow my thread on lkml to see how things are going.
  

The really important lesson here I drill into people whenever I talk about performance and benchmarking issues is that you've got to measure baseline expectations on any system you put together, continue to check periodically to make sure things haven't eroded, and use that as guidance for any new system. Here I compared new hardware about to go into service against my older, trusted "production" unit to confirm it was faster, discovering both the disk and the pgbench issues. It's really handy to know how to quantify how fast your reference benchmark is on a system from a CPU/memory/disk perspective when you get to where you're looking to upgrade it. Not only will that help guide what should be changed, but it will let you know if the upgrade really worked or not when you're done.

Conference East 08 and Solaris Notes

I had fun and picked up a lot of information at the East Coast PostgreSQL conference two weeks ago. I was waiting for a few things to come online before writing about it, rather than post before I had links to everything.

I was surprised at how good the reaction to my own presentation on the internals of the PostgreSQL buffer cache was. Much of that information you can only pick up from staring at the source code to the database, and apparently that's not so popular. There's theoretical aspects to the whole thing, but I did close with some practical suggestions for tuning with queries that use the pg_buffercache module. Slides and sample query source code now posted at my PostgreSQL Performance Pitstop. I'm hoping to present that again at the upcoming OSCON PostgreSQL Day in Portland. And this time I'll be prepared for the question Jeff Davis nailed me on about how the 8.3 buffer re-use changes actually work.

The first really informative talk I saw was Greg Sabino Mullane's discussion of replication solutions. I just used some of the insight gained there to write an updated intro to the Replication, Clustering, and Connection Pooling page on the PostgreSQL Wiki. That page still needs a lot more attention.

Solaris Adventures

One of the things I've been doing recently is deploying more systems on Solaris 10 instead of my traditional Linux environment. There were a few talks discussing Solaris-specific issues and I picked up some tricks at all of them.

Theo from OmniTI talked about their experiences as well as tools they've made available, like their PostgreSQL-Solaris Tools and
Project DTrace. Some things I picked up there:

• ZFS has a pretty substantial overhead compared to the traditional UFS filesystem. It takes some tuning to pull that down for PostgreSQL use: dropping the default large block/stripe size values, reducing the ZFS buffer cache to being less aggressive, and adjusting the direct I/O parameters as appropriate.
• They've found the shared lock that pg_dump takes on large tables makes it really inappropriate for use on larger databases. When they do instead is take a ZFS snapshot, then run the dump against that snapshot. Then there's no database locks on the snapshot to be worried about stomping on.
  
• PostgreSQL major version upgrades are hard to pull off because the recommended procedure is a dump/reload; that doesn't work on TB scale databases very well. Slony is often recommended here, but using it requires a lot of hardware: you basically need a whole additional server just to handle the version upgrade in addition to the primary/backup combination the main business is running on. Here again you can play some tricks with ZFS snapshots to try things out on the production boxes while leaving the ability to roll-back if there's a problem. This greatly enhances the potential for a tool like pg_migrator to be useful for verions upgrades to a large databases, instead of a full dump/reload.
  

Jignesh from Sun did two talks about Solaris deployments. The benchmarking one was interesting for me personally as a performance tuning geek, but the focus on commercial benchmarks (TPC is not cheap) and practical suggestions aimed at gigantic user loads isn't exactly mainstream. But his Best Practices with PostgreSQL on Solaris talk is must reading for people using this combination. The comments about UFS and ZFS tuning cleared up many mysteries I'd wondered about differences in performance on those two compared to the Linux ext3 setups I'm used to tuning. Some details from the talk to expand on the slides a bit:

• Disabling the freebehind algorithm turns off the somewhat unexpected Solaris behavior that avoids caching larger files like those you'll find in a database application. It's worth your time to look into all the parameters he mentions for filesystem tuning, they're pretty obscure but the defaults are not tuned well for database use--by PostgreSQL at least, which relies much more heavily on the OS filesystem cache than, say, Oracle.
  
• One non-obvious reason that direct I/O really doesn't work well on the main database filesystem is that PostgreSQL really expects that access to the pg_clog files will be readily cached by the OS. When they're not that causes some ugly performance issues.
• It wasn't really anticipated by the original design, but apparently the postgresql.conf commit_delay parameter has a highly useful application in very high throughput situations because it allows database writes to be much larger on average. That makes them better match the underlying storage/filesystem block sizes while also decreasing the number of I/O operations aka iops (which can become a bottleneck if you're doing lots of small writes).
  
• There are alternative memory allocation and scheduler methods available (ibumem and the FX Scheduler) that seem more appropriate for PostgreSQL use than the defaults.

Finally, another talk that mentioned Solaris tools like ZFS snapshots was Robert Treat's "Big, Bad, Broken PostgreSQL". I would recommend waiting to see if the video for this talk becomes available; it was so funny in a black comedy vein to see how his disaster unfolded that I'd hate to see you ruin the surprises by just reading the slides first.