Thread: postgres files in use not staying in linux file cache

On 06/05/2014 04:32 PM, Brio wrote:

> So here's where I'm stuck. How can reading a file not leave it in the
> Linux cache? I'd expect it to enter the inactive list (which is about
> 80GB), so I'd expect another 80GB would need to be read before it would
> be its turn to be evicted.... which should take a long time if my
> maximum read speed is 100MB/s.

So here's the thing. The Linux page reclamation code is *extremely
broken* in everything before 3.11. Take a look at this, then realize
that this is *only one patch* from several that target the memory
manager weightings:

http://linux-kernel.2935.n7.nabble.com/patch-v2-0-3-mm-improve-page-aging-fairness-between-zones-nodes-td696105.html

This is especially true of the 3.2 kernel you're using. It's extremely
aggressive about ageing pages out of memory when there's high memory
pressure from frequent disk reads. Chances of promotion into the active
set is dismal, so you end up with a constant churn between inactive and
disk. Worse, if you kick it hard enough by having too many PostgreSQL
backends using memory, it'll actively purge the active set while still
failing to promote the inactive set.

The dev that linked me to this patch said he tested it against 3.10,
meaning it probably went into 3.11 or 3.12. So I personally wouldn't
trust anything before 3.13. :p

Since you're using Ubuntu 12.04, I strongly suggest upgrading your core
to 12.04.4 and apply the linux-generic-lts-saucy pseudo-package to at
least get onto the 3.11 instead. The 3.2 kernel is pants-on-head
retarded; we've had a lot more luck with 3.8 and above.

Cheers!

--
Shaun Thomas
OptionsHouse, LLC | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604
312-676-8870
sthomas@optionshouse.com

______________________________________________

See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email

From: Shaun Thomas <sthomas@optionshouse.com>
Date: Tuesday, 10 June 2014 22:07

So here's the thing. The Linux page reclamation code is *extremely

broken* in everything before 3.11. Take a look at this, then realize

that this is *only one patch* from several that target the memory

manager weightings:

<snipped>

On 06/13/2014 02:19 AM, Tim Kane wrote:

> I ask because I’ve just read your statement above about 3.2 being
> pants-on-head, and having had more luck with 3.8 and above – despite
>  most installations being on much older (2.6.19) kernels (as per the
> thread).

Well, the issue is that the 3.2 kernel was a huge departure from the 2.6
tree that most people are still using. Thanks to RHEL, CentOS and their
ilk, the 2.6 tree has had a much longer lifetime than it probably should
have. As a result, the newer kernels haven't had sufficient real-world
server testing.

With 3.2 being the first of those, it was a bit wonky, to be honest. The
new CPU scheduler didn't have enough knobs, and the knobs that *were*
there, were set more appropriately for desktop use. The memory manager
was a train wreck and has been patched numerous times with rather
sweeping changes since. For a while, there was even a bug at how system
load was calculated that caused it to be off by an order of magnitude or
more based on process switching activity.

The overall situation has improved significantly, as has the development
momentum. It's extremely difficult to say which kernel versions have
more risk than others, since no kernel seems to be around for more than
a month or two before the next one comes out. My opinion has been to get
on the latest stable kernel for the distribution, and ignore everything
else.

For Ubuntu 12.04.4 LTS, that's 3.11.

Our systems are definitely much happier since the upgrade, but the
plural of anecdote is not data. :)

--
Shaun Thomas
OptionsHouse, LLC | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604
312-676-8870
sthomas@optionshouse.com

______________________________________________

See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email

On Thu, Jun 5, 2014 at 2:32 PM, Brio <brianoraas@gmail.com> wrote:
> Hi, I'm trying to investigate a performance problem.
>
> We have a large database (over 1TB) running on a server with 160GB of RAM
> and 32 cores (Xeon E5-2650). The database files are on a NetApp mount.
>
...
>
> I have noticed a few times that an index scan may be taking a long time, and
> the query's backend process is reading from disk at about 2 MB/s, spending
> 99% of its time waiting for I/O (using iotop). This makes sense, if scanning
> an index that is not in cache.

Does the index scan dirty most of the index blocks it touches?  (When
an index scan follows an index entry to a heap page and finds that the
tuple is no longer needed, when it gets back to the index it might
kill that entry, so that the next index scan doesn't need to do the
futile heap look up.  This dirties the index block, even for a "read
only" scan.  However, It would be unusual for a typical index scan to
do this for most of the blocks it touches.  It could happen if the
index scan is to support a giant rarely run reporting query, for
example, or if your vacuuming schedule is not tuned correctly.)

The reason I ask that is that I have previously seen the dirty blocks
of NetApp-served files get dropped from the linux page cache as soon
as they are written back to the NetApp.

I had written a little Perl script to cut postgresql out of the loop
entirely to demonstrate this effect, but I no longer have access to
it.

Cheers,

Jeff