Re: Degrading PostgreSQL 8.4 write performance

On 06/17/2011 08:48 AM, Kabu Taah wrote:
>
> Load testing of postgresql 8.4 for OLTP application suitability showed
> that throughput of the database significantly degraded over time from
> thousands of write transactions per second to almost zero...Postgres
> performance degraded in a couple of minutes after the first run of the
> test, and the problem was reproducible with only 2 parallel client
> threads.
>

When you write with PostgreSQL, things that are modified ("dirtied") in
its cache are written out to the operating system write cache.
Eventually, that data gets written by the OS; sometimes it takes care of
it on its own, in others the periodic database checkpoints (at least
every 5 minutes) does it.

It's possible to get a false idea that thousands of transactions per
second is possible for a few minutes when benchmarking something,
because of how write caches work. The first few thousand transactions
are going to fill up the following caches:

-Space for dirty data in shared_buffers
-Operating system write cache space
-Non-volatile ache on any RAID controller being used
-Non-volatile cache on any drives you have (some SSDs have these)

Once all three of those are full, you are seeing the true write
throughput of the server. And it's not unusual for that to be 1/10 or
less of the rate you saw when all the caches were empty, and writes to
disk weren't actually happening; they were just queued up.

You can watch Linux's cache fill up like this:

watch cat /proc/meminfo

Keep your eye on the "Dirty:" line. It's going to rise for a while, and
I'll bet your server performance dives once that reaches 10% of the
total RAM in the server.

Also, turn on "log_checkpoint" in the server configuration. You'll also
discover there's a drop in performance that begins the minute you see
one of those start. Performance when a checkpoint is happening is true
server performance; sometimes you get a burst that's much higher outside
of that, but you can't count on that.

The RedHat 4 kernel is so old at this point, I'm not even sure exactly
how to tune it for SSD's. You really should be running RedHat 6 if you
want to take advantage of disks that fast.

--
Greg Smith 2ndQuadrant US greg(at)2ndQuadrant(dot)com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books