November 16, 2010 at 5:20 am
Hi all
Maybe you could shed some light and add your opinions here.
(Background)
We have a production database instance, hosting the databases for our booking engine (tourism). OLTP 24/7 availability
Now altogether I have about 13 databases working together to meet the engine requirements, including ASPState and my system db's, of which msdb is used a lot for email.
Out of these 13 databases, 3 are extremely IO intensive and large (avg 160gb).
I get a lot of contention on Tempdb so I will need to spread that too over some drives. I also have contention on inserts to large tables with clustered indexes so I'm going to partition those and spread those across drives too.
We are going to be upgrading our SAN as we are running out of drive space and the buzz word lately seems to be "Virtual raid" Well that's what most of the salesmen are telling us.
From what I have read this seems ok for most databases, depending on your IO intensity, but I am having to share 13 database data files across one storage array, and I have some very specific requirements mentioned above so in my books shared storage with virtual raid doesn't sound like the right solution.:unsure:
So if you had to store the data for all 13 databases on a SAN, some read intensive, others read/write intensive, spread your tempdb to reduce contention, andpartition large tables to relieve insert contention on the clustered index, what strategy would you go for?
Thanks
Matt
November 16, 2010 at 6:32 am
Matthew Lang (11/16/2010)
Maybe you could shed some light and add your opinions here.(Background)
We have a production database instance, hosting the databases for our booking engine (tourism). OLTP 24/7 availability
Now altogether I have about 13 databases working together to meet the engine requirements, including ASPState and my system db's, of which msdb is used a lot for email.
Out of these 13 databases, 3 are extremely IO intensive and large (avg 160gb).
I get a lot of contention on Tempdb so I will need to spread that too over some drives. I also have contention on inserts to large tables with clustered indexes so I'm going to partition those and spread those across drives too.
We are going to be upgrading our SAN as we are running out of drive space and the buzz word lately seems to be "Virtual raid" Well that's what most of the salesmen are telling us.
From what I have read this seems ok for most databases, depending on your IO intensity, but I am having to share 13 database data files across one storage array, and I have some very specific requirements mentioned above so in my books shared storage with virtual raid doesn't sound like the right solution.:unsure:
So if you had to store the data for all 13 databases on a SAN, some read intensive, others read/write intensive, spread your tempdb to reduce contention, andpartition large tables to relieve insert contention on the clustered index, what strategy would you go for?
I'll start by addressing the TempDB contention issue, I'll create as many datafiles as processors are in the host, all of the same size.
In regards to insert contention on clustered-index table more information is needed.
What kind of columns make up the clustered-index?
Is this a self-inflicted wound? - like inserting rows out of order... or is it a real contention issue because of volume?
What's the proposed partitioning strategy?
Hope this helps to jump start the discussion.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.November 16, 2010 at 6:42 am
Please describe your current hardware configuration. ie TempDB is located on ??, RAID ??
Etc.
What kind of SAN are you currently using?
Is everything on it and how is it configured?
Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
November 16, 2010 at 7:37 am
Hi
It's a clustered index on an Int column, identity (1,1)
I think it's volume.
I am considering hash partitioning, ie breaking up the table into 4 parts to begin with, horizontally partitioned by means of a hash, so I have less page contention for clustered index inserts.
More specifically I do have an idea on how the data is used in my databases, I would like to know if you think shared storage is better than dedicated storage for SQL data files.
Everyone seems to think that if one throws a big collection of disks at this and then uses virtual raid it will meet the IO requirements, I am not too sure of this myself though.
Thanks
Matt
November 16, 2010 at 7:50 am
Everyone seems to think that if one throws a big collection of disks at this and then uses virtual raid it will meet the IO requirements, I am not too sure of this myself though.
Again, it goes back to hardware configuratin, and what the SAN is. I had a major battle with a company awhile ago over the exact same statement. Basically, it comes down to what the SAN is, how is it set up, how is the database configured (simple .mdf and .ldf files, or .ndf files as well)
I have taken some VLDB's (600GB +) and segmented them. Placing these segments on seperate logical drives with the proper RAID configuration for each pool/lun and had some miraculous results. This is because it lets SQL Server utilize multiple threads to connect to the database. But this is not a cure all.
I would strongly urge though that Tempdb not be placed on the SAN, but on a RAID 1 or RAID 10 drive arrray simply do to the amount of use that it can see in a high volume OLTP system. I would also do the same with the transaction logs if possible for the same reason. Otherwise, if the SAN can be configured for RAID 10 pools/LUNs then create seperate logical drives for this. In a lot of the newer SAN's they have the ability to move files around on the array's to tune the performance by moving the physical location of the files for higher use closer to the center of the platter and thse files used les further out. If this is the case, I would suggest that after you place the files on the SAN that you wait 24 hrs and then check performance.
A lot of work/planning needs to be done in order to get the best use out of your SAN, and I appologize for being so generic in the response but without specifics, that is all I can do.
Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
November 16, 2010 at 11:24 pm
Thanks Steve that's great, that's the kind of answer I'm looking for.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply