Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Performance tunig Fusion-IO Expand / Collapse
Author
Message
Posted Sunday, February 13, 2011 8:31 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 4:29 AM
Points: 153, Visits: 585
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
Post #1063148
Posted Sunday, February 13, 2011 4:49 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, September 29, 2013 9:30 AM
Points: 1, 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
Post #1063220
Posted Sunday, February 13, 2011 8:00 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 4:29 AM
Points: 153, Visits: 585
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
Post #1063246
Posted Monday, February 14, 2011 8:25 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 1:45 PM
Points: 4,128, Visits: 5,837
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 at GMail
Post #1063566
Posted Monday, February 14, 2011 10:02 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 4:29 AM
Points: 153, Visits: 585
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.
Post #1063645
Posted Monday, February 14, 2011 12:30 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, April 03, 2014 12:46 PM
Points: 1,413, Visits: 4,531
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

https://plus.google.com/100125998302068852885/posts?hl=en
http://twitter.com/alent1234
x-box live gamertag: i am null
[url=http://live.xbox.com/en-US/MyXbox/Profile[/url]
Post #1063793
Posted Monday, February 14, 2011 10:05 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 1:11 PM
Points: 3,081, Visits: 11,230
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.

Post #1063997
Posted Tuesday, February 15, 2011 7:35 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 1:45 PM
Points: 4,128, Visits: 5,837
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 at GMail
Post #1064286
Posted Tuesday, February 15, 2011 8:11 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 4:29 AM
Points: 153, Visits: 585
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.
Post #1064319
Posted Tuesday, February 15, 2011 8:25 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 1:45 PM
Points: 4,128, Visits: 5,837
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!!



Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #1064328
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse