August 15, 2014 at 2:57 pm
Assuming i have enterprise edition, is partitioning recommended for OLTP? My thought is it is good for OLAP and might not suite for OLTP ( 1000 read/writes per hour):
i) Partition will have only one log file , there might be some disk contention with so many transactions it need to commit.
ii) Easily scalable if i have multiple databases, i can split them across multiple servers.
iii) If db goes corrupt i would impact all the other partitions also.
Does this make sense? Is there anyone using partition for OLTP? Please share.
August 15, 2014 at 3:46 pm
curious_sqldba (8/15/2014)
Assuming i have enterprise edition, is partitioning recommended for OLTP? My thought is it is good for OLAP and might not suite for OLTP ( 1000 read/writes per hour):i) Partition will have only one log file , there might be some disk contention with so many transactions it need to commit.
You should only have one log file per database anyway. The transactions in a database with or without partitioning will affect the log in the same way. If you are concerned about transactions and the effect on the log, you should be looking somewhere else than to worry about partitioning.
ii) Easily scalable if i have multiple databases, i can split them across multiple servers.
Again, splitting databases across multiple servers is irrelevant to partitioning.
iii) If db goes corrupt i would impact all the other partitions also.
That is not typically how corruption works. If a table is partitioned and the corruption is severe enough, you could have corruption in all of the partitions for that table. Corruption could be in a single partition, in multiple partitions or in a table that is not partitioned.
Does this make sense? Is there anyone using partition for OLTP? Please share.
Yes, I have clients who have OLTP databases and tables partitioned within those databases.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 15, 2014 at 3:50 pm
You can't create partitioned tables across different databases. You can get significant performance improvements with partitioned tables on OLTP, even if it is just for managing and archiving data.
August 15, 2014 at 3:52 pm
Lets say you have database partitioned, all the transactions need to commit to same log file. In total there will be 10,000 commits per hour on the same log file, instead if i have individual database those commits will be in parallel since every database has its own log file. Do u still think partitioning is good of typical OLTP?
August 15, 2014 at 3:58 pm
curious_sqldba (8/15/2014)
Lets say you have database partitioned, all the transactions need to commit to same log file. In total there will be 10,000 commits per hour on the same log file, instead if i have individual database those commits will be in parallel since every database has its own log file. Do u still think partitioning is good of typical OLTP?
10,000 commits is really nothing for an oltp database. 10,000,000 is also pretty easily managed on busy oltp databases.
If you want to split a table up so that an application now has to maintain 10 connection strings, and so the application has to have logic help determine which database to hit for the said activity, you could do that. But you are asking to create a headache for yourself.
Your focus on the log file seems ill-placed. And architecting a multi-server multi database system is over-designing and leading to a maintenance and upkeep nightmare.
If you have issues with slow log activity - then fix the problem there rather than trying to architect something of this nature.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 15, 2014 at 4:04 pm
SQLRNNR (8/15/2014)
curious_sqldba (8/15/2014)
Lets say you have database partitioned, all the transactions need to commit to same log file. In total there will be 10,000 commits per hour on the same log file, instead if i have individual database those commits will be in parallel since every database has its own log file. Do u still think partitioning is good of typical OLTP?10,000 commits is really nothing for an oltp database. 10,000,000 is also pretty easily managed on busy oltp databases.
If you want to split a table up so that an application now has to maintain 10 connection strings, and so the application has to have logic help determine which database to hit for the said activity, you could do that. But you are asking to create a headache for yourself.
Your focus on the log file seems ill-placed. And architecting a multi-server multi database system is over-designing and leading to a maintenance and upkeep nightmare.
If you have issues with slow log activity - then fix the problem there rather than trying to architect something of this nature.
Ok. What about scalability, as i add more partitions activity also goes up so i can only scale-up so much on sql server, but if i have multiple databases i can easily scale-out them across multiple servers.
Thanks for your advice.
August 15, 2014 at 4:07 pm
curious_sqldba (8/15/2014)
In total there will be 10,000 commits per hour on the same log file
10 000 an hour? Unless my log file's on a 3.5" floppy disk, I'm pretty sure it can manage. Now, if you'd said 10 000 a second things would be interesting, an I might (read might) be thinking in the direction of Hekaton and RAID 10 SSDs for the logs (because at 10 000 a second, page latching may well be a problem)
Scaling out a SQL Server (databases spread across multiple servers) is one hell of an architectural challenge. I've seen MySpace do it, back when they were the big social network. I haven't seen it anywhere else myself. I suspect the biggest SQL implementation do (like the NASDAQ). Among the huge challenges is restoring all the DBs to a consistent point if it's necessary. Restoring to a point in time is not sufficient when the DBs are distributed because the transactions are separate across the DBs (and you can't do distributed transactions without imposing so much overhead you lose the gain from scaling out)
Are you really sitting in a scenario where you have or are expecting MySpace-level load?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 15, 2014 at 4:12 pm
curious_sqldba (8/15/2014)
i can only scale-up so much on sql server, but if i have multiple databases i can easily scale-out them across multiple servers.
What is your current maximum sustained transactions/sec (not a spike, sustained over 20+min) and what are you expecting that value to increase to over the next 5 years?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 15, 2014 at 4:13 pm
curious_sqldba (8/15/2014)
Ok. What about scalability, as i add more partitions activity also goes up so i can only scale-up so much on sql server, but if i have multiple databases i can easily scale-out them across multiple servers.
Thanks for your advice.
What is your scenario? What do you think you are partitioning on in order to scale?
Are you thinking of a database for an ASP or SAS where the partition will represent different clients?
Activity doesn't typically go up because you add more partitions. Activity goes up because you have more business. As a result of more business you might have more partitions. But the partition does not correlate to activity levels.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 15, 2014 at 4:17 pm
Sounds to me you are talking about sharding data across multiple databases and multiple server.
I supported a telco billing application that had an account catalog which contained info about in which database/server the account's data lived. The application then connected to the appropriate db/server.
This was during the last century.
August 15, 2014 at 4:17 pm
GilaMonster (8/15/2014)
curious_sqldba (8/15/2014)
In total there will be 10,000 commits per hour on the same log file10 000 an hour? Unless my log file's on a 3.5" floppy disk, I'm pretty sure it can manage. Now, if you'd said 10 000 a second things would be interesting, an I might (read might) be thinking in the direction of Hekaton and RAID 10 SSDs for the logs (because at 10 000 a second, page latching may well be a problem)
Scaling out a SQL Server (databases spread across multiple servers) is one hell of an architectural challenge. I've seen MySpace do it, back when they were the big social network. I haven't seen it anywhere else. Among the huge challenges is restoring all the DBs to a consistent point if it's necessary. Restoring to a point in time is not sufficient when the DBs are distributed because the transactions are separate across the DBs (and you can't do distributed transactions without imposing so much overhead you lose the gain from scaling out)
Are you really sitting in a scenario where you have or are expecting MySpace-level load?
Makes perfect sense now. Ok, i have a application generating about 10,000 transactions read/write per hour. we have 50 + clients, should i partition per client ( data has to be separated) or per database? Recommendations?
August 15, 2014 at 4:24 pm
curious_sqldba (8/15/2014)
Ok, i have a application generating about 10,000 transactions read/write per hour. we have 50 + clients, should i partition per client ( data has to be separated) or per database? Recommendations?
Well with that load you're not needing to scale out for performance, so what do the business rules say? If they say that each client's data must be separate, then each client's data must be separate (and that usually means separate database). If the business rules state that you may need to restore Client76's data to 3 hours ago while leaving all the others untouched, that means separate databases for definite.
btw, to give you an idea, I have a client whose main OLTP server does 600 transactions/sec sustained during business hours. That's 36 000/hour and we're no where close to hitting the limits of where SQL can scale. Their problem though is a few badly designed tables. Fix those and I wouldn't be surprised to see that figure triple.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 15, 2014 at 4:35 pm
GilaMonster (8/15/2014)
curious_sqldba (8/15/2014)
Ok, i have a application generating about 10,000 transactions read/write per hour. we have 50 + clients, should i partition per client ( data has to be separated) or per database? Recommendations?Well with that load you're not needing to scale out for performance, so what do the business rules say? If they say that each client's data must be separate, then each client's data must be separate (and that usually means separate database). If the business rules state that you may need to restore Client76's data to 3 hours ago while leaving all the others untouched, that means separate databases for definite.
btw, to give you an idea, I have a client whose main OLTP server does 600 transactions/sec sustained during business hours. That's 36 000/hour and we're no where close to hitting the limits of where SQL can scale. Their problem though is a few badly designed tables. Fix those and I wouldn't be surprised to see that figure triple.
So you are saying point in time recovery for a partitioned db will take longer time as it will have hardened transactions for all the partitions. Let's say we don't need point in time recovery, is partition still better option in my case? I am thinking it will be more headache to have those many partitions
August 15, 2014 at 4:36 pm
Unless you've got some business rule that says that customer data needs to be physically isolated from other customer data for some reason, I think you need to take a step back and look at the workloads that you are expecting.
For the numbers that have been mentioned here so far a single server with a couple of cpu cores and 8 gigs of ram will probably do the trick nicely. And with minimal tuning it'll run that workload for years without issues.
Things like partitioning don't need to be looked at until you've got very large or very specific workloads.
Scaling across multiple servers is only for the busiest of applications. 1M transactions per second, let's spread that across servers. These days a single server can scale VERY large. I've got clients who have servers with 80 cores and 1TB of ram and these aren't the largest servers which are available. I can go buy a box with 256 cores and 2TB of ram and have it sitting on a loading dock in a couple of weeks. If you've got a workload that size then you want to start worrying about that.
August 15, 2014 at 4:39 pm
curious_sqldba (8/15/2014)
GilaMonster (8/15/2014)
curious_sqldba (8/15/2014)
Ok, i have a application generating about 10,000 transactions read/write per hour. we have 50 + clients, should i partition per client ( data has to be separated) or per database? Recommendations?Well with that load you're not needing to scale out for performance, so what do the business rules say? If they say that each client's data must be separate, then each client's data must be separate (and that usually means separate database). If the business rules state that you may need to restore Client76's data to 3 hours ago while leaving all the others untouched, that means separate databases for definite.
btw, to give you an idea, I have a client whose main OLTP server does 600 transactions/sec sustained during business hours. That's 36 000/hour and we're no where close to hitting the limits of where SQL can scale. Their problem though is a few badly designed tables. Fix those and I wouldn't be surprised to see that figure triple.
So you are saying point in time recovery for a partitioned db will take longer time as it will have hardened transactions for all the partitions. Let's say we don't need point in time recovery, is partition still better option in my case? I am thinking it will be more headache to have those many partitions
No the purpose of that is that if a client has specific Recovery objectives, you can't restore a database and take down all of your clients because of a request or a different objective by one client.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply