jwcolby
jwcolby at colbyconsulting.com
Mon Dec 21 11:40:46 CST 2009
Francisco, Flash drives are notorious for slow writes. One thing to know is that flash drives have historically had an issue with the controller inside of them. There was a company that was "the" supplier and they weren't doing a very good job. This article is old but a good place to start understanding the issues: http://benchmarkreviews.com/index.php?option=com_content&task=view&id=299&Itemid=60&limit=1&limitstart=12 In particular note the pages for random access speed http://benchmarkreviews.com/index.php?option=com_content&task=view&id=299&Itemid=60&limit=1&limitstart=5 and I/Os per second http://benchmarkreviews.com/index.php?option=com_content&task=view&id=299&Itemid=60&limit=1&limitstart=8 I purchased an OCZ Vertex 30 gig drive shortly after reading this stuff and have been quite pleased with the performance. OTOH I have NOT used it as the home for huge database files. That is my intent however. This "new" generation of drives has *mostly* solved the various problems encountered. I can tell you that I partitioned my 30 gig drive and gave each partition to a VM (four VMs total) running on one of my servers. This was used as storage of about 1 gig of foxpro database files for the Accuzip address validation software. These files are read-only. Placing these files on a regular hard disk allowed three VMs to process around 1 million records per hour. Placing these files on a hardware "RAM DISK" (Gigabyte i-Ram) upped that to about 2 million records / hour. Placing these files on a my Vertex 30 gig Flash drive allowed all three VMs to process simultaneously and the speed climbed from around 2 million records per hour to between 5 and 6 million records per hour. Essentially I more than doubled my processing speed for each of three virtual machines using a single flash drive. I credit this to the increased IOPS and streaming read speeds. This technology ROCKS for *some* purposes. We shall have to wait and see if it rocks for large SQL Server read only files, but I suspect that it will. John W. Colby www.ColbyConsulting.com Francisco Tapia wrote: > I have not tested SQL Server files, but on my 32 gb flash drive, I noticed > that bigger files are slower than smaller files to both read and write to. > On a few machines where the hdd have been replaced with flash drives, the > OS is smoking fast where it boots from cold to full windows in just a few > seconds, however even these machines start to degrade in performance > whenever we place large VM files on them and whenever there has been enough > reads and writes on the drive. The access time is FAST, but for large > transfers of data all flash drives I have worked with up to this point have > been very slow. I have not worked with any of the intel ssd's which from > what I have read are supposed to be extreamly fast, and I have not worked > with raid flash drives as some manufactures have architected, so ymmv. I > was simply stating from my personal experience what I've come across. Most > files sizes I have had problems with are all files over 15gb in size. > > -Francisco > http://sqlthis.blogspot.com | Tsql and More... > > > On Thu, Dec 17, 2009 at 7:14 AM, jwcolby <jwcolby at colbyconsulting.com>wrote: > >> Francisco, >> >> > Flash drives do not do well with very large files. >> >> Why do you say this? I have never heard anything like this ever espoused. >> I have seen file >> TRANSFER numbers that display this, but transferring files from one disk to >> another is fundamentally >> different from reading pieces of a large file for internal processing by a >> program (database). >> >> From a technology perspective flash drives are a page at a time block read >> system interfaced to a >> very large cache ram interfaced to an SATA disk controller. >> >> Because of this, flash drives have two huge advantages over rotating media >> - (random) access time >> and IOPS. Getting at any block of data occurs at the speed of the >> electronics, but is typically >> around 100 nanoseconds. Exactly because of this phenomena, the number of >> IOs that can be processed >> in any given second skyrockets. Basically IOPs are dispatched instead of >> being queued waiting for >> the media to rotate and heads to move. Examine the numbers for flash vs >> rotating and the REAL IOPS >> go from the low hundreds to the mid thousands. That is a LOT more data >> accessed per second. >> >> In fact flash drives rapidly become "disk controller bound" precisely >> because they can shovel data >> faster than the SATA interface can move it. Even so, rotating media only >> uses about 10% of the >> capacity of the SATA interface, so if you turn around and saturate the >> interface with a flash drive >> you are getting 10 times the data moved. That is impressive and something >> worth shooting for. >> >> From what I have read, databases absolutely SCREAM when placed on flash >> drives, in fact some of the >> big databases are moving there exactly because of the performance gains. >> Databases need exactly >> this kind of "read a block from over here" technology that flash drives >> just naturally excel at. >> From what I am reading, placing your databases on flash is getting close >> to "memory resident". >> >> In my case, my databases are not transactional, they just sit there >> handling read requests. As a >> result even the known issues of wear should not be an issue. Set the db >> files up on a standard disk >> and then copy them to a flash drive, sit back and watch it fly. >> >> As for the details, what I would LIKE to do is make a raid 0 array using my >> intelligent raid >> controllers. If I could get 4 or 5 (or more) "spindles" of flash raid >> zero... >> >> Now back to reality. I haven't done this yet because of cost, I am money >> bound. ;) When I do, I >> will do some real life testing and publish results here. >> >> On that note, I do have a question, whether it is possible to specify what >> database files a specific >> index goes into. It happens that this table from hell uses specific >> columns much more frequently >> than others, and some columns (so far) not at all, or only once or twice. >> If I could create >> multiple data files, and then move the indexes for the first 200 fields >> into one file and the rest >> into another, then I could move that heavily used index file onto a flash >> and leave the rest on >> rotating media. >> >> John W. Colby >> www.ColbyConsulting.com >> >> >> Francisco Tapia wrote: >>> Flash drives do not do well with very large files. Flash drives excell >>> at random access wich for SQL makes them good with both tempdb and >>> transaction log files because they both have random inserts and reads. >>> To improve on what you have you will want to double the number of hdd >>> spindles. The more spindles you have the bettertheperformance. We have >>> an enterprise database riding on 40 spindles, this in turn is able to >>> crank out searches for a 1.5 terabyte db in usually 10 seconds to ??? >>> Depending on the query my developers need. We have discussed as an >>> architecture change that we may soon upgrade to 80 spindles. >> _______________________________________________ >> dba-SQLServer mailing list >> dba-SQLServer at databaseadvisors.com >> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver >> http://www.databaseadvisors.com >> >> > _______________________________________________ > dba-SQLServer mailing list > dba-SQLServer at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > http://www.databaseadvisors.com > >