February 13, 2011 at 8:31 am
Hi,
As an Organization initiative, We have installed Fusion-IO in our production server
Server configuration:
OS and Hardware : Windows 2008, 64 bit, 4 CPU, 32 GB RAM
SQL : Sql server 2008 R2 (64 bit)
Fusion-IO was installed by a vendor and almost all dont have much idea about its configuration and guidelines to make use of Fusion-IO in effecient manner.
Observation post Fusion-IO installation:
We found new drive (say g: with 250+ gb) and network team moved all databases (all mdf, ndf, ldf including tempdb) into new fusion-io drive.
Issue:
post fusion-io installation, We have not observed even minor performance improvement in BULK data read and write from fusion-io when compared with normal hard drive.
Suggestion required from you:
1. we would like to know, how much performance improvement we can expect in %(ex: 5 times or 3 times) on read or write operation? any idea why we dont find performance difference?
2. we have placed all mdf, ndf, ldf and tempdb in fusion-io drive. is there any guideline to place data files in and out of fusion-io?
3. how we can test performance difference between fusion-io and hard disk.?
4. for test purpose, I have created same table (with 1 millionrecords) in both normal hard drive and fusion-io.
but i could not see any difference to fetch 1 million records from both hard disk and fusion-io.
im sure that there is gap either in fusion-io configuration or in the way we placed data files in fusion io
thanks in advance
February 13, 2011 at 4:49 pm
That is very interesting that you did not see any increase in performance. I am quite interested in your setup and will be more than happy to assist you with your efforts.
Tom Scribner
Fusion-io
February 13, 2011 at 8:00 pm
Thanks for your reply.
in Brent's Fusion-io review post,
(http://www.brentozar.com/archive/2010/03/fusion-io-iodrive-review-fusionio/)
He has mentioned some hardware level details to get maximum performance out of Fusion-io
like,
1. In order to get your money’s worth out of an SSD, you have to connect it with something faster than SATA.
2. Fusion-IO ioDrives get around this limitation because they’re not SATA drives; they plug directly into your server’s much faster PCI Express bus. These cards can push several times more data per second than SATA drives can.
I have no idea about performance of hardware parts like SATA or PCI express.
can u pls tell me,
1. what are the questions in need to ask network team to understand the configuration and to narrow down the analysis of fusion-io performance issue.
can we make use of stress test tools like sqlio to assess configuration and disk vs. fusion-io performance.
is there any other OS level command or tools from fusion-io to collect configuration/performance details of fusion-io
February 14, 2011 at 8:25 am
You have 32GB of memory. If you have small data, EVERYTHING will be sitting in the buffer pool in RAM and drive speed will be virtually irrelevant except for tlog and tempdb activity. How much data do you have?
As for your 1M record test, again that table will sit in memory. The way to test IO speed is to run dbcc dropcleanbuffers prior to EACH access (BEWARE doing this on busy production system!!). Also, do NOT select that 1M rows back to the client - the presentation of the data will overwhelm IO performance from even the slowest disk. Do something like this:
select count(someunindexedfield) from bigtable
that will a) force a table scan and b) only bring 1 row/column back to SSMS.
Oh, and are you CPU bound by any chance? If you are (and it is EASY to do on a 4CPU box with poor schemas/indexing/code) then again your IO performance will be of little value. You could also be network bound for the BULK operations I suppose.
I would do waitstats analysis on your BULK operations to see what things are REALLY waiting for.
Yes, you can use SQLIO to test fusionIO speeds.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 14, 2011 at 10:02 am
Great answer Kevin.
got few inputs to investigate further.
I have asked server management team to provide specification details of both disk and fusion-io and asked them to perform stress test on both hard disk and fusion-io.
let me share finding once we come to conclusion.
thanks a lot for your valueable input and time.
February 14, 2011 at 12:30 pm
i looked at Fusion IO and similar products and may see if we can buy them this year. from what i read you get best results putting your tempdb or indexes on flash storage
February 14, 2011 at 10:05 pm
Before you implemented the fusionIO drive, did you verify that the server was actually IO bound so that you could expect to see an increase in performance?
If most of the activity on the server was reads, and most of the data was cached in memory, I would expect to see very little improvement in performance.
February 15, 2011 at 7:35 am
Michael Valentine Jones (2/14/2011)
Before you implemented the fusionIO drive, did you verify that the server was actually IO bound so that you could expect to see an increase in performance?If most of the activity on the server was reads, and most of the data was cached in memory, I would expect to see very little improvement in performance.
That sounds remarkably similar to what I said! 😀
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 15, 2011 at 8:11 am
Kevin,
as Server is running in 32 GB RAM, I forgot the possibility of keeping/reading all pages from memory irrespective of hard disk and fusion-io.
As you suggested, I 've done a write and read test today with 1.5 GB table by cleaning buffer in each stage.
I can observe 5 times improvement in Fusion-IO read when compared with read from hard-disk.
one more intresting point is, write speed was not par with reading from Fusion-io.
I have asked specification details of both hard disk and fusion-io from network team and we have planned stress test on both hard disk and fusion-io in this weekend.
One more curious question.
as a best practice, We seperate mdf, ndf, ldf and tempdb in seperate drives. as of now all in fusion-io. hope this wont be a problem for Fusion-io.
thanks all for your valuable time and inputs.
February 15, 2011 at 8:25 am
Ramkumar (LivingForSQLServer) (2/15/2011)
Kevin,as Server is running in 32 GB RAM, I forgot the possibility of keeping/reading all pages from memory irrespective of hard disk and fusion-io.
As you suggested, I 've done a write and read test today with 1.5 GB table by cleaning buffer in each stage.
I can observe 5 times improvement in Fusion-IO read when compared with read from hard-disk.
one more intresting point is, write speed was not par with reading from Fusion-io.
I have asked specification details of both hard disk and fusion-io from network team and we have planned stress test on both hard disk and fusion-io in this weekend.
One more curious question.
as a best practice, We seperate mdf, ndf, ldf and tempdb in seperate drives. as of now all in fusion-io. hope this wont be a problem for Fusion-io.
thanks all for your valuable time and inputs.
As I suspected.
Writes will never be on par with reads on ANY IO system that I know of (caching excepted here obviously).
ANY IO system (even FusionIO) can become overwhelmed. Use fileIO stall analysis to monitor your read/write stalls with everything on FusionIO. If something is slow, consider moving it off to another disk system. Note that disk systems are best at sequential IO, so tlogs are actually not a bad thing to put there assuming the spindles are ONLY serving up tlog files AND there is enough IO throughput to keep tlog IO stalls (ESPECIALLY WRITES) to a minimum.
Glad I could help. IO configuration is a VERY complex topic and there are MANY ways you can do things 'suboptimally'. I encourage you to hire a performance tuning professional for a system review. I have personally never seen any client to even half of the things optimally!! :w00t:
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 15, 2011 at 10:03 pm
TheSQLGuru (2/15/2011)
Michael Valentine Jones (2/14/2011)
Before you implemented the fusionIO drive, did you verify that the server was actually IO bound so that you could expect to see an increase in performance?If most of the activity on the server was reads, and most of the data was cached in memory, I would expect to see very little improvement in performance.
That sounds remarkably similar to what I said! 😀
Different point of view. I was more concerned that someone would buy an expensive piece of hardware like that without knowing if it was going to be possible for it to improve performance.
I just hate it when poeple spend a lot of money on something without knowing what they are trying to gain, just throwing money at something in hopes that it will help.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply