From: Vitalii Tymchyshyn <tivv00@gmail.com>
To: Richard Shaw <richard@aggress.net>
Cc: Alan Hodgson <ahodgson@simkin.ca>
Cc: pgsql-performance@postgresql.org
Subject: Re: Rather large LA
Date: Wed, 07 Sep 2011 09:54:25 +0300
Message-ID: <4E671521.3090406@gmail.com> (raw)
In-Reply-To: <9BFA2771-CA9B-4EEF-B671-78FF67F749C6@aggress.net>
References: <F757E48F-2B4F-496D-951B-B04ED98F8FD6@aggress.net>
<4E64D0FE.6020704@squeakycode.net>
<97BCA6CA-A1DB-43C7-B710-C2836B656FD6@aggress.net>
<201109051305.07010@hal.medialogik.com>
<9BFA2771-CA9B-4EEF-B671-78FF67F749C6@aggress.net>
Hello.
As it turned out to be iowait, I'd recommend to try to load at least
some hot relations into FS cache with dd on startup. With a lot of RAM
on FreeBSD I even sometimes use this for long queries that require a lot
of index scans.
This converts random IO into sequential IO that is much much faster.
You can try it even while your DB starting - if it works you will see
IOwait drop and user time raise.
What I do on FreeBSD (as I don't have enough RAM to load all the DB into
RAM) is:
1) ktrace on backend process[es]. Linux seems to have similar tool
2) Find files that take a lot of long reads
3) dd this files to /dev/null
In this way you can find hot files. As soon as you have them (or if you
can afford to load everything), you can put dd into startup scripts. Or
I can imagine an automatic script that will do such things for some time
after startup.
Best regards, Vitalii Tymchyshyn
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: tivv00@gmail.com, richard@aggress.net, ahodgson@simkin.ca
Subject: Re: Rather large LA
In-Reply-To: <4E671521.3090406@gmail.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