Francisco Tapia
fhtapia at gmail.com
Tue Dec 22 07:56:12 CST 2009
Your milage may vary. Like I said my expiriance is very different from what Colby had as results. Part of my issues were that my test involves USB drives an it could just be the bus problems. But I have not seen performance like Colby mentioned I even triedthat speed boost in vista whichwas supposed to be fast by moving in readonly files to a 4gb drive and make it available to the os quicker than the sata drive. On the machine with the ssd drive as the primary drive I am unsure if this drive was intel or some other type. On anandtech.com they did an extensive review on flash ssd and why they slow down over time. Sent from my mobile On Dec 22, 2009, at 1:31 AM, Mark Breen <marklbreen at gmail.com> 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 >