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
