Thread: Tuning read ahead

Hi all,

I've just gotten into my new database server yesterday and I've started doing database setup and tuning.

I'm on a Rackspace Linux server with two raid arrays. Both are ext3. One is a two disk RAID1 I plan on using for WAL
andOS, the other is a four disk RAID10 I will use for the data.  

I read in Postgres 9.0 High Performance that one of the most important parameters I should tune is the device
read-ahead. 

My question: Is that advice just for the database drive, or should I increase read ahead on the OS/WAL disk as well? I
assumeboth. I should ask the same for noatime advice while I'm at it. Is it important to disable atime on the WAL as
wellas the data? 

Thanks,

Ramsey

On 05/15/2013 08:04 PM, Ramsey Gurley wrote:

> My question: Is that advice just for the database drive, or should I
> increase read ahead on the OS/WAL disk as well?

Definitely the database drive, but it doesn't hurt to do both. It
doesn't mention it in the book, but if you have a Debian or Ubuntu
system, you can set it up to retain these settings through reboots very
easily. The udev system can be set with rules that can target whole
ranges of devices. Here's one we use:

* In a file named /etc/udev/rules.d/20-pg.rules

ACTION=="add|change", KERNEL=="sd[a-z]",ATTR{queue/read_ahead_kb}="4096"

Our systems are also NVRAM based, so we also throw in a NOOP access
scheduler:

ACTION=="add|change", KERNEL=="sd[a-z]", ATTR{queue/scheduler}="noop"

There's really no reason to do it any other way if you have udev
installed. You *could* put blockdev calls in /etc/rc.local I suppose,
but udev applies rules at device detection, which can be beneficial.

> I assume both. I should ask the same for noatime advice while I'm at
> it.

You can probably get away with relatime, which is the default for most
modern systems these days.

--
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | 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 May 16, 2013, at 6:01 AM, Shaun Thomas wrote:

> On 05/15/2013 08:04 PM, Ramsey Gurley wrote:
>
>> My question: Is that advice just for the database drive, or should I
>> increase read ahead on the OS/WAL disk as well?
>
> Definitely the database drive, but it doesn't hurt to do both. It doesn't mention it in the book, but if you have a
Debianor Ubuntu system, you can set it up to retain these settings through reboots very easily. The udev system can be
setwith rules that can target whole ranges of devices. Here's one we use: 
>
> * In a file named /etc/udev/rules.d/20-pg.rules
>
> ACTION=="add|change", KERNEL=="sd[a-z]",ATTR{queue/read_ahead_kb}="4096"
>
> Our systems are also NVRAM based, so we also throw in a NOOP access scheduler:
>
> ACTION=="add|change", KERNEL=="sd[a-z]", ATTR{queue/scheduler}="noop"
>
> There's really no reason to do it any other way if you have udev installed. You *could* put blockdev calls in
/etc/rc.localI suppose, but udev applies rules at device detection, which can be beneficial. 


Interesting point. I had not considered whether the setting would be maintained through reboots. I'll have to google
forthe appropriate settings on Red Hat. 


>> I assume both. I should ask the same for noatime advice while I'm at
>> it.
>
> You can probably get away with relatime, which is the default for most modern systems these days.



I will probably go with noatime on the data drive then. I see where that would require lots of reads and should not be
writingto the drive. In my mind, WAL should be read much less frequently. Maybe I am wrong about that :-) 

Thank you,

Ramsey