Thread: By now, why PostgreSQL 9.2 don't support SSDs?
As we know, SSDs are widely used in various kinds of applications. But the SMGR in PostgreSQL still only
--
Just do it!
support magnetic disk. How do we make full use of SSDs to improve the performance of PostgreSQL?
Just do it!
On Sat, Mar 30, 2013 at 10:08:44PM +0800, 赖文豫 wrote: > As we know, SSDs are widely used in various kinds of applications. But the SMGR > in PostgreSQL still only > support magnetic disk. How do we make full use of SSDs to improve the > performance of PostgreSQL? When the storage manager (SMGR) says magnetic disk, it is talking about read/write media with random access capabillity, vs. something like write-only media, which was originally supported in the code. Postgres works just fine with SSDs; the only adjustment you might want to make is to reduce random_page_cost. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
Bruce Momjian <bruce@momjian.us> writes:
> On Sat, Mar 30, 2013 at 10:08:44PM +0800, 赖文豫 wrote:
>> As we know, SSDs are widely used in various kinds of applications. But the SMGR
>> in PostgreSQL still only
>> support magnetic disk. How do we make full use of SSDs to improve the
>> performance of PostgreSQL?
> When the storage manager (SMGR) says magnetic disk, it is talking about
> read/write media with random access capabillity, vs. something like
> write-only media, which was originally supported in the code. Postgres
> works just fine with SSDs; the only adjustment you might want to make
> is to reduce random_page_cost.
To enlarge on that point: the current smgr layer is basically vestigial,
because the sorts of device dependencies the Berkeley guys envisioned
switching between are nowadays always handled at the filesystem and
kernel device driver layers. md.c is really an interface to the Unix
block device APIs; it has nothing whatsoever to do with whether the bits
are stored on spinning rust or something else.
regards, tom lane
On 03/30/2013 12:28 PM, Tom Lane wrote: > Bruce Momjian <bruce@momjian.us> writes: >> On Sat, Mar 30, 2013 at 10:08:44PM +0800, 赖文豫 wrote: >>> As we know, SSDs are widely used in various kinds of applications. But the SMGR >>> in PostgreSQL still only >>> support magnetic disk. How do we make full use of SSDs to improve the >>> performance of PostgreSQL? >> When the storage manager (SMGR) says magnetic disk, it is talking about >> read/write media with random access capabillity, vs. something like >> write-only media, which was originally supported in the code. Postgres >> works just fine with SSDs; the only adjustment you might want to make >> is to reduce random_page_cost. > To enlarge on that point: the current smgr layer is basically vestigial, > because the sorts of device dependencies the Berkeley guys envisioned > switching between are nowadays always handled at the filesystem and > kernel device driver layers. md.c is really an interface to the Unix > block device APIs; it has nothing whatsoever to do with whether the bits > are stored on spinning rust or something else. > > This isn't the first time I've seen this sort of comment. Do we need to add some wording like the above to the top of md.c and the README in that directory? cheers andrew
Andrew Dunstan <andrew@dunslane.net> writes:
> This isn't the first time I've seen this sort of comment. Do we need to
> add some wording like the above to the top of md.c and the README in
> that directory?
Yeah, probably. I'll go write something ...
regards, tom lane
2013/03/30 23:31, Bruce Momjian wrote: > On Sat, Mar 30, 2013 at 10:08:44PM +0800, 赖文豫 wrote: >> As we know, SSDs are widely used in various kinds of applications. But the SMGR >> in PostgreSQL still only >> support magnetic disk. How do we make full use of SSDs to improve the >> performance of PostgreSQL? > > When the storage manager (SMGR) says magnetic disk, it is talking about > read/write media with random access capabillity, vs. something like > write-only media, which was originally supported in the code. Postgres > works just fine with SSDs; the only adjustment you might want to make > is to reduce random_page_cost. BTW, using the larger block size (>64kB) would improve performance when using SSD drive? I found that configure script supports --with-blocksize option to change the block size up to 32kB. (and the configure script does not support >64kb block size so far.) But I heard that larger block size, like 256kB, would take advantage of the SSD performance because of the block management within SSD. So, I'm just curious to know that. Regards, -- Satoshi Nagayasu <snaga@uptime.jp> Uptime Technologies, LLC. http://www.uptime.jp
<p dir="ltr">On Mar 30, 2013 7:13 PM, "Satoshi Nagayasu" <<a href="mailto:snaga@uptime.jp">snaga@uptime.jp</a>> wrote:<br/> > But I heard that larger block size, like 256kB, would take<br /> > advantage of the SSD performance becauseof the block management<br /> > within SSD.<p dir="ltr">This is only true for very bad SSDs. Any SSD that you wouldwant to trust with your data do block remapping internally, eliminating the issue. (See for example Intel DC3700 sustaining34'000 random 4k writes/s)<p dir="ltr">Larger block sizes would just lift the random access workload write amplificationinto Postgresql where the drive can't fix it. For sequential or mostly sequential workloads the OS can takecare of it by merging writes. Additionally, contention for page level locks will increase with page size, cache efficiencygoes down. I would expect cases where larger block size is a significant benefit to be very rare.<p dir="ltr">Regards,<br/> Ants Aasma