Francisco Tapia
fhtapia at gmail.com
Mon Dec 21 10:36:33 CST 2009
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 > >