| Author |
Topic |
|
Onamuji
Aged Yak Warrior
504 Posts |
 Posted - 2002-02-05 : 12:25:43
|
| I was wondering what type of structures everyone else uses for creating databases. My methodology has been the default database settings, due mainly to the incompitence of the people that setup the servers they raided all the drives into one huge drive, not that this is always a bad thing. After reading several articles on FILEGROUPs and placing indexes on one set and heavy hit tables on another and then normal mainly read tables on another you could increase performance quite substantially(spelling?). The reason I am looking into this and wondering what everyone else thinks is because we are trying to design a main SQL server intranet based server(s) and I'm pushing the fact that they shouldn't raid the discs all into one drive on each machine. I'm wondering if this is just common practice (to use filegroups a lot) and when do using too many file groups become a hassle, any concerns and issues you've run into when using file groups would also be very welcomed. Thanks for any input anyone can give...- Onamuji |
|
|
butlermi_11
Starting Member
10 Posts |
Posted - 2002-02-05 : 12:43:19
|
| Designing your filesystem to suit your database is based on the usage of your system. 1. What type of database? OLAP, OLTP 2. Size of database? 3. Transactions per minute(TPM)? 4. User load? The BASIC DESIGN I try to follow is Mirror the os. Raid five the database. Mirror for the Transaction Log. This is just a baseline for our databases, each database is different depending on the usage and activity. Get into filegroups when you want to seperate highly active tables or indexes on to a seperate disk.Ask the questions about response time, look at the process flow of the application to see heavily used tables and indexes etc. This will help you understand what type of design you need.Michael |
 |
|
|
royv
Constraint Violating Yak Guru
455 Posts |
Posted - 2002-02-05 : 12:44:41
|
| You are correct, FILEGROUPS are best used across multiple disk drives. I just keep in mind the general rule that the more you have the more to manage and a bigger headache. BOL should actually give a limit if there is one.*************************Just trying to get things done |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2002-02-05 : 13:59:51
|
| The only thing I would add is that RAID 0+1 actually gives you better performance than RAID 5 for your data. RAID 5 is not bad though.-Chad |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-02-05 : 18:03:40
|
| Also, if you have highly transactional databases, keeping the log files on separate physical disks is a great way to get high performance. You don't need to use striping because the log writes are sequential in nature. However, mirroring (RAID 1) the log device would give you the needed redundacy and very high performance. |
|
|
|
coolerbob
Aged Yak Warrior
841 Posts |
Posted - 2005-03-21 : 08:57:09
|
| We have 4 disks on one raid 5. A 7 gig file sits in it in one filegroup. We have two tables that count for most of the activity/size of the db. Are you guys saying I could have a significant performance improvement by messing with filegroups? |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2005-03-21 : 10:29:13
|
quote:
Originally posted by chadmat
The only thing I would add is that RAID 0+1 actually gives you better performance than RAID 5 for your data. RAID 5 is not bad though.-Chad
I used to think that too. I recently had a friend tell me to look at the IO going on in my system. He said, since there are almost always more reads than writes in a SQL database, that RAID 5 is almost always better than RAID 1/0. Since even an INSERT requires a read, there will always be more reads. Since RAID 5 does better with reads than RAID 1/0, RAID 5 should be where you put your data.If anyone can refute my statemeent and back it up, I'm all ears. It made sense to me though.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2005-03-21 : 12:13:20
|
RAID 5 offers better read performance than RAID 1, but RAID 10 will offer bettter performance than RAID 5, provided the data is stripped across an equal number of drives. The disadvantage of RAID 10 is that it uses many more disks.You may want to look at these two articles:http://www.sql-server-performance.com/q&a38.asphttp://www.sql-server-performance.com/sql_server_setup.aspquote:
Originally posted by MichaelP
quote:
Originally posted by chadmat
The only thing I would add is that RAID 0+1 actually gives you better performance than RAID 5 for your data. RAID 5 is not bad though.-Chad
I used to think that too. I recently had a friend tell me to look at the IO going on in my system. He said, since there are almost always more reads than writes in a SQL database, that RAID 5 is almost always better than RAID 1/0. Since even an INSERT requires a read, there will always be more reads. Since RAID 5 does better with reads than RAID 1/0, RAID 5 should be where you put your data.If anyone can refute my statemeent and back it up, I'm all ears. It made sense to me though.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda>
CODO ERGO SUM |
|
|
|
coolerbob
Aged Yak Warrior
841 Posts |
Posted - 2005-03-21 : 12:45:53
|
| Which Michael is right? One says RAID 5, the other RAID 10! |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2005-03-21 : 13:31:32
|
| Given an equal number of disks, RAID 5 should perform reads better than RAID 1/0. For writes, the RAID 1/0 will smoke the RAID 5.Example 6 disksRAID 5, you basically have 5 of the 6 disks doing the readRAID 1/0 You have three drives doing the read, and the other three are for mirroring.So, which is faster for reads in this case, five drives or three?Now, given an unlimited number of disks, RAID 1/0 will generally outperform RAID 5 because you tend to put more disks in the RAID 1/0 set. Depending on yoru vendor, somewhere around 5-8 disks in a RAID 5 set is optimal. Any more than that you start getting diminished performance. I'm not sure what the optimal is for RAID 1/0, but I bet it's somewhere around 14 disks.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2005-03-21 : 14:04:58
|
| I don't disagree with what MichaelP, if you have an equal number of disks. I was just trying to make the point that RAID 10 is not inherently slower than RAID 5, but RAID 10 will need more disks because of the way it stores data.If you are going with RAID 10, for example, you might have your data STRIPPED across 10 disks to match the storage of 6 RAID 5 disks. RAID 10 is a high performance solution for when overall throughput is the most important criteria, and disk cost is secondary.CODO ERGO SUM |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2005-03-21 : 14:50:49
|
| ^^ what he said.If you've got enough space and money RAID 1/0 is faster.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2005-03-21 : 15:38:36
|
| Onamuji,You are definately on the right track. Forgetting the disks for a minute.For larger DB's (anything over a 1GB is my base mark), I create a new primary group for basic data and a group for non-clustered indexes. So you have 3 groups, Metadata, Data, Indexes.Inside the groups, the file management is up to you. A little known fact is that a filgroup can be marked READONLY. Unfortunately, you cannot mark the "system" filegroup readonly... would have been a great way to lock down schema.....DavidMA front-end is something that tries to violate a back-end. |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2005-03-21 : 15:40:52
|
| What would be in the metadata group? Master, tempdb??Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2005-03-21 : 15:55:25
|
| Michael,The metadata group is the system group. All procs, views, triggers and table schema are located there. They will ALWAYS be created on the filegroup called PRIMARY. And not the group marked "Primary".. Confused? I was the first time I tango'd with it....DavidMA front-end is something that tries to violate a back-end. |
|
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2005-03-21 : 15:57:44
|
| I read an article a while back that suggested putting the TempDB on its own RAID 0 drive. The reasoning was that since TempDB is used for intermediate results (implicit and explicit) that it should be on its own fast drive. Since the data there was ephemeral, there was no need for the mirroring.HTH=================================================================In order to improve the mind, we ought less to learn than to contemplate.-Rene Descartes, philosopher and mathematician (1596-1650) |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2005-03-21 : 16:01:32
|
| I've heard the same sort of thing Busta, but what happens when the RAID 0 dies? SQL server will not automatically put it somewhere else will it??Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2005-03-21 : 18:09:47
|
| >>>I've heard the same sort of thing Busta, but what happens when the RAID 0 dies? SQL server will not automatically put it somewhere else will it??<<<I wouldn't think so. To me the thought process was if the RAID 0 goes down, the data lost is transient. Replace the broken hardware and the TempDB rebuilds itself, as it does every time, when you resetart the SQL Service.HTH=================================================================In order to improve the mind, we ought less to learn than to contemplate.-Rene Descartes, philosopher and mathematician (1596-1650) |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-03-21 : 20:42:40
|
| Except if tempdb goes down for even a moment, your SQL Server stops. Dead. This is one of the reasons they removed the "tempdb in RAM" option. And if the drive(s) that tempdb resides on are not available, you cannot restart SQL Server. You'd either have to fix the drives or restart in console mode to move tempdb to another drive. Technically, you could almost function without the master database before you could lose tempdb. |
|
|
|
coolerbob
Aged Yak Warrior
841 Posts |
Posted - 2005-03-22 : 03:43:12
|
| Good point well made again Robvolk.OK so let's talk more about what Byrmol said.How much of a performance gain is there in having 3 filegroups (One for metadata(Sp's, Views,...), Data(All my tables) & indexes(non-clustered)) on a RAID 5 array? |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2005-03-22 : 12:33:10
|
| Well, if you put them all on the SAME RAID 5 array, I doubt youll notice any performance gain / loss.If you separate the Data and indexes into separate RAID 5 or RAID 1/0 arrays, then you should see a good gain depending on the number and type of disks.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
Next Page
|
Best Practices: FILEGROUPs ?