SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Performance tunig Fusion-IO


Performance tunig Fusion-IO

Author
Message
Ramkumar (LivingForSQLServer)
Ramkumar (LivingForSQLServer)
Mr or Mrs. 500
Mr or Mrs. 500 (509 reputation)Mr or Mrs. 500 (509 reputation)Mr or Mrs. 500 (509 reputation)Mr or Mrs. 500 (509 reputation)Mr or Mrs. 500 (509 reputation)Mr or Mrs. 500 (509 reputation)Mr or Mrs. 500 (509 reputation)Mr or Mrs. 500 (509 reputation)

Group: General Forum Members
Points: 509 Visits: 620
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
tscribner 12044
tscribner 12044
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 9
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
tscribner@fusionio.com
Fusion-io
Ramkumar (LivingForSQLServer)
Ramkumar (LivingForSQLServer)
Mr or Mrs. 500
Mr or Mrs. 500 (509 reputation)Mr or Mrs. 500 (509 reputation)Mr or Mrs. 500 (509 reputation)Mr or Mrs. 500 (509 reputation)Mr or Mrs. 500 (509 reputation)Mr or Mrs. 500 (509 reputation)Mr or Mrs. 500 (509 reputation)Mr or Mrs. 500 (509 reputation)

Group: General Forum Members
Points: 509 Visits: 620
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
TheSQLGuru
TheSQLGuru
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32199 Visits: 8674
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
Ramkumar (LivingForSQLServer)
Ramkumar (LivingForSQLServer)
Mr or Mrs. 500
Mr or Mrs. 500 (509 reputation)Mr or Mrs. 500 (509 reputation)Mr or Mrs. 500 (509 reputation)Mr or Mrs. 500 (509 reputation)Mr or Mrs. 500 (509 reputation)Mr or Mrs. 500 (509 reputation)Mr or Mrs. 500 (509 reputation)Mr or Mrs. 500 (509 reputation)

Group: General Forum Members
Points: 509 Visits: 620
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.
alen teplitsky
alen teplitsky
SSCertifiable
SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)

Group: General Forum Members
Points: 7096 Visits: 4674
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
Michael Valentine Jones
Michael Valentine Jones
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14459 Visits: 11848
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.
TheSQLGuru
TheSQLGuru
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32199 Visits: 8674
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! :-D

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Ramkumar (LivingForSQLServer)
Ramkumar (LivingForSQLServer)
Mr or Mrs. 500
Mr or Mrs. 500 (509 reputation)Mr or Mrs. 500 (509 reputation)Mr or Mrs. 500 (509 reputation)Mr or Mrs. 500 (509 reputation)Mr or Mrs. 500 (509 reputation)Mr or Mrs. 500 (509 reputation)Mr or Mrs. 500 (509 reputation)Mr or Mrs. 500 (509 reputation)

Group: General Forum Members
Points: 509 Visits: 620
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.
TheSQLGuru
TheSQLGuru
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32199 Visits: 8674
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search