Stuart McLachlan
stuart at lexacorp.com.pg
Tue Dec 22 05:49:49 CST 2009
Your mileage may vary :-) -- Stuart On 22 Dec 2009 at 9:31, Mark Breen wrote: > 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 > > > > > _______________________________________________ > dba-SQLServer mailing list > dba-SQLServer at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > http://www.databaseadvisors.com >