From: Greg Smith <greg@2ndQuadrant.com>
To: pgsql-performance@postgresql.org
Subject: Re: Degrading PostgreSQL 8.4 write performance
Date: Fri, 17 Jun 2011 13:54:10 -0400
Message-ID: <4DFB94C2.20307@2ndQuadrant.com> (raw)
In-Reply-To: <20110617124857.1A82B432167@Bounce1.estpak.ee>
References: <20110617124857.1A82B432167@Bounce1.estpak.ee>
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@2ndQuadrant.com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: greg@2ndQuadrant.com
Subject: Re: Degrading PostgreSQL 8.4 write performance
In-Reply-To: <4DFB94C2.20307@2ndQuadrant.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox