Mark Breen
marklbreen at gmail.com
Tue Dec 22 03:31:25 CST 2009
Hello Francisco, ymmv ? You may ... ... ? You might ... ...? You must ... ...? Yonder Machines May Virtualise? Thanks Mark 2009/12/21 Francisco Tapia <fhtapia at gmail.com> > 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 > >