Re: Production databases on SSDs?
Mark Fowler wrote: Are you using any special kind of SSD tailored storage engine on these, or just placing a stock InnoDB / MyISAM on the device? Mark. The SSD replicas are stock MyISAM only, though the masters are a mixture with InnoDB. The initial speedup from disc array to SSD was dramatic enough that we just used our standard build. This suggests scope for extra gains at some later point. With regards, Dave Webb -- Tech Guy - LOVEFiLM.com
Re: Production databases on SSDs?
On Tue, Nov 10, 2009 at 4:51 PM, Dave Webb wrote: > We've been using a fusion-io 160GB drive in production for about a year now. > It is hosting partial replicas of a couple of our databases (mysql) for an > operation that needs two largeish data sets. > And for the past three months or so, we've had a "reporting" replica running > on a pair of fusion-io 320GB SSDs. Are you using any special kind of SSD tailored storage engine on these, or just placing a stock InnoDB / MyISAM on the device? Mark.
Re: Production databases on SSDs?
On 10 Nov 2009, at 20:36, Dirk Koopman wrote: Ovid wrote: Does anyone here have any experience putting a production database on a solid-state drive? Our database is heavily used and it sounds to me like we could get a massive performance boost for minimal cost and no architectural changes. Are there any downsides I should be aware of? Write cycles. They are very limited. Even on best kwalitee flash chips you will only get approx 100,000 write cycles. Most flash controllers "get over" this by doing "wear levelling" (which means that writes are spread about the available blocks and not concentrated on the same few blocks [think inodes]). This will get you another couple of orders of magnitude. So maybe 10,000,000 write cycles. Then there is the UDI file system. Which does some other optimisations, but is probably no (or not much) better than the wear leveling hardware. Heavy usage? Don't think flash will help you, or at least - not for long. I'm kinda concerned that this is all trying to sidestep understanding of database usage. It slapping 32G of RAM on top of a hard drive really not good enough? Do you really not have control over the database queries? -- Dave HodgkinsonMSN: daveh...@hotmail.com Site: http://www.davehodgkinson.com UK: +44 7768 490620 Blog: http://www.davehodgkinson.com/blog Photos: http://www.flickr.com/photos/davehodg
Re: Production databases on SSDs?
Ovid wrote: Does anyone here have any experience putting a production database on a solid-state drive? Our database is heavily used and it sounds to me like we could get a massive performance boost for minimal cost and no architectural changes. Are there any downsides I should be aware of? Write cycles. They are very limited. Even on best kwalitee flash chips you will only get approx 100,000 write cycles. Most flash controllers "get over" this by doing "wear levelling" (which means that writes are spread about the available blocks and not concentrated on the same few blocks [think inodes]). This will get you another couple of orders of magnitude. So maybe 10,000,000 write cycles. Then there is the UDI file system. Which does some other optimisations, but is probably no (or not much) better than the wear leveling hardware. Heavy usage? Don't think flash will help you, or at least - not for long. Dirk
Re: Production databases on SSDs?
On 10 Nov 2009, at 19:42, James Laver wrote: [...] Or play other fun tricks, like only partitioning 25% of the disk and leaving the rest to waste, so it's using the fastest 25% of the disk. On a WD VelociRaptor[1], it could be extra awesome. Part-stroking one of those would give you such a high price per GB that you'd be better off with a SSD. You can pick up the 80GB Intel SSD for under £200 now.
Re: Production databases on SSDs?
On Tue, Nov 10, 2009 at 5:46 PM, Richard Huxton wrote: > > * - it's not the transaction logs themselves that are the problem so > much as having constant small writes to them causing the disk heads to > seek back and fore. This is why you tend to put them on their own disks. Or play other fun tricks, like only partitioning 25% of the disk and leaving the rest to waste, so it's using the fastest 25% of the disk. On a WD VelociRaptor[1], it could be extra awesome. --James [1] Yes, the name sucks. Apparently they wanted to fit 'raptor' in so people would know it's an evolution of the raptor.
Re: Production databases on SSDs?
Ovid wrote: I only meant "minimal cost" in relation to setting up a bunch of master/slave mysql servers, configuring them, getting replication going, etc. OpenMosix or one of its successors?
Re: Production databases on SSDs?
Ovid wrote: > - Original Message > >> From: Chris Jack > >> Be aware that there is a major difference between the reliability >> and cost of the pen drives you get on the high street and >> production quality solid state drives. Your minimal cost comment >> worries me. > > I only meant "minimal cost" in relation to setting up a bunch of > master/slave mysql servers, configuring them, getting replication > going, etc. Have a search through the PostgreSQL mailing-list archives for various discussions. Like Elizabeth mentioned, transaction logs* can be a good choice, as are mostly-read indexes of course. Recent MySQL supports tablespaces, but I don't know if you can just put an index on a table-space or you have to move your whole table + all its indexes onto it. If your disks are spending most of their time seeking rather than reading data it's a win. I don't think there's any useful data about reliability yet, just because most models aren't that old. The performance degradation issues that got widely reported on the Intel drives suggests there is still a lot to learn about these things. * - it's not the transaction logs themselves that are the problem so much as having constant small writes to them causing the disk heads to seek back and fore. This is why you tend to put them on their own disks. -- Richard Huxton Archonet Ltd
Production databases on SSDs?
Hi, Does anyone here have any experience putting a production database on a solid-state drive? Our database is heavily used and it sounds to me like we could get a massive performance boost for minimal cost and no architectural changes. Are there any downsides I should be aware of? We've been using a fusion-io 160GB drive in production for about a year now. It is hosting partial replicas of a couple of our databases (mysql) for an operation that needs two largeish data sets. We got about about a 10x increase in sequential read speeds (doing table scans etc.) and up to about 40x for random IO. Operations that build temporary tables really benefit from the drive. This has been reliable, and the box has an uptime of 347 days today. And for the past three months or so, we've had a "reporting" replica running on a pair of fusion-io 320GB SSDs. This is used for queries that join a couple of our data sets, and is far, far faster than the HDD databases serving similar roles. We have no plans to master any data on SSDs anytime soon, but we hope to replace a fair chunk of kit with them. Because of the disc IO gains, one box with SSDs can replace a handful of HDD replicas. The SSDs we use plug into the PCIe slots on the motherboard, so are not throttled by going through standard disc controllers. Plus they have wear-levelling and 20% redundancy and a 3-year warranty. We're keeping an eye on the number of degraded cells though, as this is still new technology. It's all been upside for us so far (apart from the cost :) ). The query speedup is like going back in time 4 years to when we had hardly any customers! With regards, Dave Webb -- Tech Guy - LOVEFiLM.com
Re: Production databases on SSDs?
Ovid wrote: Does anyone here have any experience putting a production database on a solid-state drive? Our database is heavily used and it sounds to me like we could get a massive performance boost for minimal cost and no architectural changes. Are there any downsides I should be aware of? This might prove useful to you http://www.anandtech.com/storage/showdoc.aspx?i=3631 Relevance: I like beer. Beer is good. Mike Woods Full of squishy cynicism
Re: Production databases on SSDs?
- Original Message > From: Chris Jack > Be aware that there is a major difference between the reliability and cost of > the pen drives you get on the high street and production quality solid state > drives. Your minimal cost comment worries me. I only meant "minimal cost" in relation to setting up a bunch of master/slave mysql servers, configuring them, getting replication going, etc. Cheers, Ovid -- Buy the book - http://www.oreilly.com/catalog/perlhks/ Tech blog- http://use.perl.org/~Ovid/journal/ Twitter - http://twitter.com/OvidPerl Official Perl 6 Wiki - http://www.perlfoundation.org/perl6
Re: Production databases on SSDs?
On Tue, Nov 10, 2009 at 4:04 PM, Chris Jack wrote: > Be aware that there is a major difference between the reliability and cost of > the pen drives you get on the high street and production quality solid state > drives. Your minimal cost comment worries me. Well by comparison to the cost of a shedload of new hardware, it is quite cheap. --James
RE: Production databases on SSDs?
Ovid publiustemp-londo...@yahoo.com wrote: > > Does anyone here have any experience putting a production database on a > solid-state drive? Our database is heavily used and it sounds to me like we > could get a massive performance boost for minimal cost and no architectural > changes. Are there any downsides I should be aware of? Be aware that there is a major difference between the reliability and cost of the pen drives you get on the high street and production quality solid state drives. Your minimal cost comment worries me. Chris _ Use Hotmail to send and receive mail from your different email accounts http://clk.atdmt.com/UKM/go/186394592/direct/01/
Re: Production databases on SSDs?
On Nov 10, 2009, at 3:46 PM, Ovid wrote: > Does anyone here have any experience putting a production database on a > solid-state drive? Our database is heavily used and it sounds to me like we > could get a massive performance boost for minimal cost and no architectural > changes. Are there any downsides I should be aware of? If you're using MySQL and replication, I've heard stories about performance gains putting the binlog on an SSD, rather than the db itself. Advantages: smaller SSD necessary, and no unrecoverable problems should the SSD decide to quit in the end. Liz
Re: Production databases on SSDs?
On 10/11/2009 14:46, Ovid wrote: Does anyone here have any experience putting a production database on a solid-state drive? Our database is heavily used and it sounds to me like we could get a massive performance boost for minimal cost and no architectural changes. Are there any downsides I should be aware of? No experience, but I've been been considering it for a while and until very recently, I'd have said the write performance was too awful to consider. However you may wish to examine.. http://perspectives.mvdirona.com/2009/10/14/ReplacingALLDiskWithSSD.aspx for more points to consider. - mark
Production databases on SSDs?
Does anyone here have any experience putting a production database on a solid-state drive? Our database is heavily used and it sounds to me like we could get a massive performance boost for minimal cost and no architectural changes. Are there any downsides I should be aware of? Relevance: I like beer. Cheers, Ovid -- Buy the book - http://www..oreilly.com/catalog/perlhks/ Tech blog- http://use.perl.org/~Ovid/journal/ Twitter - http://twitter.com/OvidPerl Official Perl 6 Wiki - http://www.perlfoundation.org/perl6