Re: Using Small Size SSDs to improve performance?

On Wed, Aug 4, 2010 at 7:43 PM, Greg Smith <greg(at)2ndquadrant(dot)com> wrote:
>> 2) usage of a S5D to store instructions to a make a checkpoint. Instead of
>> write the "dirty" pages directly to database files, postgreSQL could dump to
>> SSD the dirty pages and the instructions of how update the data files.
>> Later, a deamon process would update the files following these instructions
>> and erase the instruction files after that.
>
> This is essentially what happens with the operating system cache:  it
> buffers writes into memory as the checkpoint does them, and then later does
> the actual I/O to write them to disk--hopefully before the sync call that
> pushes it out comes in.  There are plenty of problems with how that's done
> right now.  But I don't feel there's enough benefit to optimize specifically
> for SSD when a more general improvement could be done instead in that area
> instead.

One of the tricky parts here is that we don't actually handle large
values of shared_buffers that well. If I recall your previous posts
correctly, the benefit dries up at a level no higher than about 10GB.
We'd probably need to try to understand what underlies that effect
before thinking too hard about ways to effectively enlarge shared
buffers even more.

Another thought I had was whether there would be any benefit to
attempting to tune the buffer management algorithm to improve cache
locality. In other words, if we don't really need all of
shared_buffers, maybe there would be some benefit in trying to use
only as much of it as we actually need; or maybe provide some
mechanism for individual backends to have a weak preference for
reusing the same pages that they've used before. But maybe this
doesn't actually matter: even a L3 cache isn't big enough to hold a
significant percentage of a large shared_buffers setting, I think.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company