Re: requested shared memory size overflows size_t

On Jun 16, 2010, at 1:53 PM, Alvaro Herrera wrote:

> Excerpts from Tom Lane's message of lun jun 14 23:57:11 -0400 2010:
>> Scott Carey <scott(at)richrelevance(dot)com> writes:
>>> Great points. There is one other option that is decent for the WAL:
>>> If splitting out a volume is not acceptable for the OS and WAL -- absolutely split those two out into their own partitions. It is most important to make sure that WAL and data are not on the same filesystem, especially if ext3 is involved.
>>
>> Uh, no, WAL really needs to be on its own *spindle*. The whole point
>> here is to have one disk head sitting on the WAL and not doing anything
>> else except writing to that file.
>
> However, there's another point here -- probably what Scott is on about:
> on Linux (at least ext3), an fsync of any file does not limit to
> flushing that file's blocks -- it flushes *ALL* blocks on *ALL* files in
> the filesystem. This is particularly problematic if you have pgsql_tmp
> in the same filesystem and do lots of disk-based sorts.
>
> So if you have it in the same spindle but on a different filesystem, at
> least you'll avoid that extra fsync work, even if you have to live with
> the extra seeking.

yes, especially with a battery backed up caching raid controller the whole "own spindle" thing doesn't really matter, the WAL log writes fairly slowly and linearly and any controller with a damn will batch those up efficiently.

By FAR, the most important thing is to have WAL on its own file system. If using EXT3 in a way that is safe for your data (data = ordered or better), even with just one SATA disk, performance will improve a LOT if data and xlog are separated into different file systems. Yes, an extra spindle is better.

However with a decent RAID card or caching storage, 8 spindles for it all in one raid 10, with a partition for xlog and one for data, is often better performing than a mirrored pair for OS/xlog and 6 for data so long as the file systems are separated. With a dedicated xlog and caching reliable storage, you can even mount it direct to avoid polluting OS page cache.

>
> --
> Álvaro Herrera <alvherre(at)commandprompt(dot)com>
> The PostgreSQL Company - Command Prompt, Inc.
> PostgreSQL Replication, Consulting, Custom Development, 24x7 support