20 million row partition strategy
-
Hi, Our db is growing large and I will be moving it to a new server with better IO subsystem. I am also looking into horizontal partition. Here is the scenario.
Daily imports of log data from a dozen windows media servers. Tens to hundreds of thousands of rows daily. The data is static and is never updated once it is in SQL.
On the new server I have requested 4 or 5 SCSI drives. (5 if partitioning employed.)
hd1: os sql bin
hd2: datafiles
hd3: indexes (most reporting is via covering indexes)
hd4: trans logs
hd5: possible a horizontal partion of datafiles
eg. rows older then...
I had assumed that querying across partitions would be automatic, however I was reading here http://www.sqlteam.com/Item.ASP?ItemID=684
that I would need to query against a "partitioned view" for this to be automatic.
If queried against a view wouldn't I lose the benefits of all my indexes? It seems that the benefit of partitioning would not outweight the benefit of the indexes. Opinions/advice?
This is the largest db (by rows not objects) that I have had to administer. I keep thinking that there most be some way that I can take advantage of the fact that the data is static. I can't make it read only because of the daily inserts but is there some other way I can take advantage of its static nature to improve performance?
Once this upgrade is complete with or without partitioning I expect a large performance boost. Looking ahead I think a data warehouse will be in my future. Perhaps I should skip partioning and skip directly to the warehouse?
Thanks for any advice or observations.
Dave
Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors) -
As the SQL Team guys point out, horizontal partitioning requires a *lot* of administrative overhead. I would make sure you thoroughly investigate the overhead before you implement this strategy.
I don't know that your indexing will suffer, as long as your indexes are well designed to begin with, that is. It does seem to me that the UNION ALL on 20 million rows in the view might cause a performance hit when querying, but that's just a guess - and thoroughly un-tested.
It looks like your divisions of the database among several hard drives is right on target. Binary executables and O/S on one hard drive, non-clustered indexes on another, t-logs on yet another. If you decide not to go the horizontal partitioning route, you might consider spreading your datafiles around in multiple filegroups on the two datafile hard drives. This should give you a performance increase over a single datafile on one hard drive.
BTW, what type of RAID do you have planned for this? Or are you going to implement RAID?
-
Thanks for the advice, I think I will avoid the partitioning. I really don't need the overhead and the benefits are iffy compares to the boost I'll get from teh new drive system.
No RAID. I thought 5 individual disks would provide better performance then linking them in a raid, and I don't think I'd get the budget for more disks. I did wonder about controllers though. For example will more controllers automatically translate into improved performance? Or perhaps I should simple put the translog on its own controller? Any advice on that? I looked around the net and didn't find much.
Concerning recovery there are two types of data on the system. Log data and some data that is pulled down from SalesForce.com.
I made the decision that the log data in SQL did not need to be backed up since it already resided in the original disk files and could simply be re-harvested. (This would be automatic because of the way I wrote the windows service program that does the harvesting from each server, they simply insert which ever files are missing on SQL, so I could trunc the table and it would simply re-insert them on their next daily run. (Granted it would take a looong time but as long as it was less then 48 hours we could live with it.)
As part of this upgrade I am thinking about a standby server. The standby server would also function as a dev/testing server for the data warehouse.
I havn't yet decided how I would replicate the data to the standby server but it shouldn't be to difficult since it is a very linear system. Each day new rows are added and none are ever modified or deleted. So after each days harvest I should simply be able to copy the rows over to the standby. I havn't decided which mechanism I will use for that one way transfer but I don't forsee any difficulties. (Damn! I shouldn't tempt the fates like that
Dave
Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors) -
If you place constraints on each of your partitioned tables, only the table that matches the constraint will be queried. This is from what I remember from a Seminar at SQL Pass 2 years ago:
Let's say you partition your table on Customer Type (1,000,000 rows) and you end up with 10 tables (100K rows per table). Place a constraint on customer type field for each table for only the valid customer type. When you run the view looking for a customer type of 5, the query optimizer will ignore the 9 other tables and only scan the 100K rows in the table that contains customer type of 5. Remember though, the field you partition on needs to be a part of your where clause.
The only real pain is putting the data into the partitions, but can be automated pretty easily. Implementation can be a pain, but maintenance is minor.
-
Almost all the queries include a date range so if it coould be a calculated constraint like
tbl1: date dateadd(m,-12,getdate())
Then that what would be great. But am I able to define a covering index say across the entire table (all partitions) like [date,f1,f2,f3]. Then when I pass it a query with the date in the where clause, will it limit itself to the one table and then do an INDEX SEEK within that table?
*...only scan the 100K rows...*
Did you mean scan? or perhaps it would index seek within that table?
Hmm I guess I could find out for sure by running a simply test
Thanks allot everybody for this great feedback.
Dave
Dave
Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors) -
Just how large is 'large' ? 20 Gb, 50 Gb, 100 Gb ? This can be a factor in choosing your disk solution. Also, what type of insertion volume are you looking at on a daily basis ? Again, this may contribute to whethere or not you decide to 'horizontally partition' the table. As for not using RAID, well that's your call if you are not interested in some performance increase or level of fault tolerance on your 'live' server. One other thing is that you may have neglected to think about your full database backups ...
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
Weird I just wrote about 5 paragraphs but you are all saved from reading it all because I hit post and it all dissappeared. So this time just the facts.
~50gb All in one filegroup with 16gb currentlt remaining in filegroup and about 10gb remaining on disk.
Insertion volume in and around ~100k+ rows a day.
Money will constrain me to about 4 or 5 disks and I thought (perhaps incorrectly) that I would get better performance with seperate disks then an array. Also, these are off the rack hosted servers so the cost might escalate if I started requesting specific controllers and configurations. Ordering off the rack the cost is only a couple hundred a month per server. $200+ depending on number of disks.
Again because these are hosted servers so the backup/recovery plan was simply to backup to a cheap stanby server. This would also give me a sandbox to play with reporting services. It's not offsite backup but the original source log file for the data that matters are already being backedup and archived off site.
Fault tolerance is not as much of an issue business wise. With a low failure rate I can have up to 36 hours to recover. Would customers be happy? No, but they would not be any where near lynching us. (low failure rate being once a year or less).
I am interested in hearing the performance differences between RAID and seperate disks. That would give me something to compare to the increased cost.
thx all
Dave
Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors) -
If you remove money from the picture: System executables for SQL and master/model/msdb on internal server disk. Since you are using a 'service provder' I'd assume that they'd be using a SAN for 'renting' you disk (if not I'd run and find another provider now !). Horizontal partition by day/week/month. daily inserts on RAID 0 (zero) SAN disk for performance. week/month partitions on RAID 5 SAN for read performance. I would not differentiate between data and index on the RAID 0. But on the RAID 5 I would. tranasction logs and backups on RAID 1.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
When the optimizer runs the query, it will first look at the constraints and should rule out using the tables that do not fulfill the contraint. From that it will use the index necessary to return the records, provided the index exists.
I used the word "scan" very loosely. I meant to say that by using contraints in this manner you will reduce your initial "universe" to the table that fits the constraint.
-
Thanks for that MidwestDBA. I think that encourages me enough to at least do an experiment using the whole dataset. I'll configuring it with partitioning first and run some queries and check the execution plan. That should tell me if it is working int he most effective way. If not then I'll re-migrate without the paritioning. I'll just have to tell me client that they may have to pay for an extra day for a good chance at better performance
Thanks everyone for the feedback and advice. Very useful as always. HipHip for SQLServerCentral.com
Dave
Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors) -
Whatever you do, do NOT put 5 disks in the system without RAID. Your chances of failure increase substantially. In the setup that you suggest, if one disk fails you will lose your entire system. Plus, assuming each disk has a capacity of 34Gigs you will run out of space very quickly on your transaction log disk, yet you will have lots of space on your OS partition. Couple that with the slow throughput you will have for one disk compared to a 5 way RAID-5 setup and there is no point in pursuing your suggestion.
I work at a direct mail company and we have a logging server that all of our mailing lists. It went online this past February and now has 80 million records. Our reports include Zip code aggregation and time a date queries and queries still have sub 3 second response times. A properly designed database with a modecum of hardware will have no problems handling the load you describe. I doubt you will have any desire to horizontally partition a database that is so small.
-
Follow up:
I have the new server up and running and it has made a big difference in performance.
The server is a hosted dedicated server and I had very little control over the hardware provisioned. I managed to at least get my 4 SCSI-320 drives. I was unsuccessful at getting them to give me more information about the controller but I suspect it might be a single channel model. If we need another performance boost I will lean on them to upgrade the controller as a first step and take it from there.
No RAID and no table partitioning.
I scoured my procedures and found a few small improvements along the way.
The upshot is that the client is very happy with the new responsiveness of their reports
Thanks again everyone for the advice.
Dave
Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors) -
“hd1: os sql bin
hd2: datafiles
hd3: indexes (most reporting is via covering indexes)
hd4: trans logs
hd5: possible a horizontal partion of datafiles” – Chris H.
For a read only DB this doesn’t look right to me.
#1 – What do you need a transaction log for?
This logs changes to the DB so that if there is a corruption of data you can roll back to the point in time before the corruption. The only changes to the DB are your bulk loads so the only thing in the logs will be your bulk loads and I don’t think you need to be able to roll those back. You would stave a hard drive and a ton of performance if you turn the trans log off by setting your Recovery Model to Simple and putting what small log you will have on the OS drive. If you insist on having a log I would use the Bulk_Logged model so that your bulk inserts don’t fill up the log.
#2 – You don’t care about data reduncy and you want speed
So, you need Raid 0. Two drive running raid 0 could be twice as fast as a single drive. So, this is What I would to with 5 drives:
HD1: OS and log
HD2 and HD3: Raid 0 with data
HD4 and HD5: Raid 0 with indexes
I would consider this for it might be faster:
HD1: OS and Log
HD2-5: Raid 0 with data and Indexes
-
If speed is the highest priority then Raid 5 outperforms Raid 0. You will take an infinitessimal performance hit on writes.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
It's not read only. Just read mostly. Each day a a hundred thousand+ rows are added. Putting the trans logs on a drive by themselves allows the file to be serial (?correct description?)
The idea is that when the rows are being inserted they can be written to the tlog drive while reports are being read from the data/index drives. I think that is the benefit.
The upshot is that the site already performs many times faster and the users experience no more delay then if it was a static HTTML page.
Cheers
Dave
Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors)
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply