Thread: Using Small Size SSDs to improve performance?
The prices of large capacity Solid State Disks (SLCs of course) are still too high to most of us.<br /><br />But it´s alreadypossible to find SSDs of small size (8 to 32 GB) today with affordable prices and good performance (0,1ms access timeand at least 150MB/s r/w transfer rate).<br /><br />So, would it possible to use these Small Size SSDs (S5Ds) as a bufferto improve postgreSQL's write performance?<br /><br />For now, I detected two opportunities:<br /><br />1) usage ofa S5D to temporarily store the WAL log files until a deamon process copy them to the regular HD. <br /><br />2) usage ofa S5D to store instructions to a make a checkpoint. Instead of write the "dirty" pages directly to database files, postgreSQLcould dump to SSD the dirty pages and the instructions of how update the data files. Later, a deamon process wouldupdate the files following these instructions and erase the instruction files after that. I guess that MVCC inducesthe spread of writes along the file area, requiring lots of seeks to find the correct disk block. So SSDs will producea good performance in burst situation.<br /><br />I guess these ideas could improve the write performance <font size="2"><spanclass="Apple-style-span" style="border-collapse: separate; color: rgb(0, 0, 0); font-family: arial,helvetica,sans-serif;font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height:normal; orphans: 2; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px;"><spanclass="Apple-style-span" style="line-height: 25px;">significantly</span></span></font> (3x to 50x) in databasessystems that perform writes with SYNC and have many write bursts or handle large (20MB+) BLOBs (many WAL segmentsand pages to write on checkpoint). <br /><br />Of course, postgreSQL should be patched to handle, not only with thenew behaviours, but to handle a possible SSD full. <br /><br />One solution to (1) could be a fast/main volume scheme.In case of the fast volume full condition, postgreSQL should use the main volume.<br /><br />The (2) solution is moredelicate because introduce a new type of file to hold data. But, if the gain worths, it should be examinated ...<br /><br/>Well, that´s it.<br /><br /><br clear="all" /><br />-- <br />Nilson<br />
On Wed, Aug 4, 2010 at 5:09 PM, Nilson <nilson.brazil@gmail.com> wrote: > The prices of large capacity Solid State Disks (SLCs of course) are still > too high to most of us. > > But it´s already possible to find SSDs of small size (8 to 32 GB) today with > affordable prices and good performance (0,1ms access time and at least > 150MB/s r/w transfer rate). > > So, would it possible to use these Small Size SSDs (S5Ds) as a buffer to > improve postgreSQL's write performance? > > For now, I detected two opportunities: > > 1) usage of a S5D to temporarily store the WAL log files until a deamon > process copy them to the regular HD. > > 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. I guess that MVCC induces the > spread of writes along the file area, requiring lots of seeks to find the > correct disk block. So SSDs will produce a good performance in burst > situation. > > I guess these ideas could improve the write performance significantly (3x to > 50x) in databases systems that perform writes with SYNC and have many write > bursts or handle large (20MB+) BLOBs (many WAL segments and pages to write > on checkpoint). > > Of course, postgreSQL should be patched to handle, not only with the new > behaviours, but to handle a possible SSD full. > > One solution to (1) could be a fast/main volume scheme. In case of the fast > volume full condition, postgreSQL should use the main volume. > > The (2) solution is more delicate because introduce a new type of file to > hold data. But, if the gain worths, it should be examinated ... > > Well, that´s it. This is offtopic for -hackers...better -general or -performance. In fact, there is a long highly informative topic on SSDs going on right now which you might be interested in reading. The real short answer to your question is that $/gb is not the only metric, and the current situation with flash SSD is much more complex than it appears on the surface (for example, they basically suck without write buffering). merlin
Nilson wrote: > 1) usage of a S5D to temporarily store the WAL log files until a > deamon process copy them to the regular HD. The WAL is rarely as much of a bottleneck as people think it is. Because it's all sequential writes, so long as you put it onto a dedicated disk there's minimal advantage to be had using an SSD for it. Lots of small sequential writes is really not the place where SSD shines compared with regular disk. > 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. > I guess these ideas could improve the write performance significantly > (3x to 50x) in databases systems that perform writes with SYNC and > have many write bursts or handle large (20MB+) BLOBs (many WAL > segments and pages to write on checkpoint). That's optimistic. Right now heavy write systems get a battery-backed cache in the RAID card that typically absorbs 256MB to 512MB worth of activity. You really need to reference SSD acceleration against that as your reference. If you do that, the SSD gains stop looking so big. Checkpoint writes right now go: shared_buffers -> OS cache -> RAID BBWC -> disk And those two layers in the middle are already providing a significant speedup on burst workloads. Ultimately, all the burst stuff has to make it onto regular disks eventually though, if you can't fit the whole thing on SSD, and then you're back to solving the non-SSD problem again. That's the problem with these things that keeps them from being magic bullets; if you have a database large enough that you can't fit the working set in RAM nowadays, you probably can't fit whole thing on SSD either. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support greg@2ndQuadrant.com www.2ndQuadrant.us
> And those two layers in the middle are already providing a significant
> speedup on burst workloads. Ultimately, all the burst stuff has to make
> it onto regular disks eventually though, if you can't fit the whole
> thing on SSD, and then you're back to solving the non-SSD problem
> again. That's the problem with these things that keeps them from being
> magic bullets; if you have a database large enough that you can't fit
> the working set in RAM nowadays, you probably can't fit whole thing on
> SSD either.
The only times we've seen real gains from using SSD's in production was
when we lavished money on a lot of them for data warehousing. There the
speedup in both throughput and random seeks really boosted performance.In the other use cases I've tested, the only
realadvantage to SSDs was
keeping your form factor down.
I haven't been able to test things like putting a "hot" table on a
specific SSD.
-- -- Josh Berkus PostgreSQL Experts Inc.
http://www.pgexperts.com
Josh Berkus wrote: > I haven't been able to test things like putting a "hot" table on a > specific SSD. > Putting a hot table or even better an index on them, where that relation fits on SSD but the whole database doesn't, can work well. But it doesn't give the speedup levels people expect because "hot" stuff tends to already be in memory, too. I've deflated multiple "SSD will fix our problems!" meetings with output from pg_buffercache, showing everything they were planning to put on there was already in the hottest part of database RAM: shared_buffers. Indexes of heavily written tables are the thing I've seen the most actual improvement on like this. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support greg@2ndQuadrant.com www.2ndQuadrant.us
On Wed, Aug 4, 2010 at 7:43 PM, Greg Smith <greg@2ndquadrant.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