We are implementing new database applications with large users and the developer thinks we need to look into doing some kind of load balanced SQL database.our server is slow due to vast amount of data. This is a new area to me and I was wondering if someone can point me at some good resources on doing this? ,is it hypertable a alternate solution for this?
migrated from stackoverflow.com May 29 at 7:56
This question came from our site for professional and enthusiast programmers.
I've had other developers tell me that we need to look at load balancing database servers. And more often than not when I looked at their code, they were using horrendous queries that were not optimised, that were returning data (which was then thrown away), that were querying tables that had no indexes, that were preforming multiple queries when one could have done the job, and so on.
Nine times out of ten, I'd say that the developer needs to learn more about how the database or their ORM works and structure their code better to accommodate that.
For example, we've got queries generated by Entity Framework that were horrendous and were bringing back way more information than we needed. We created views that the application could query. The views were optimised to what the application actually needed and one they were in place we could put indexes in the relevant places. The original EF generated queries were essentially grabbing the whole row of everything that was touched (so it could generate the full entity in code) even although we only needed a column or two from each table. After that exercise queries were running much faster and more efficiently.
If you are using ORMs then eager load what you need. I've seen cases where an EF query got the header row of something (say an Order) then looped around all the subitems (say OrderLines) and on each and every subitem it generated a new query and roundtrip to the database. Each roundtrip was taking a few milliseconds. If you eager load you have one trip to the database and can speed up the application that way. I once had an argument with a vendor over their database access patterns. They swore blue in the face that their code was the most efficient it could be. Yet, I could see that their CMS solution was generating 2000 queries just to render our home page as they got a list of things to display and got each one individually.
We also have multiple databases on one server. We are looking at creating a second SQL Server and moving some of the databases onto that to distribute the workload. Obviously, if you only have one large database that's not really going to help, but if you do have multiple databases on one server it is something to think about - and it is probably cheaper than load balancing operations.
So, these are just a few things that can be thought about before going down the route of load balancing a SQL Server.
I my be wrong here but I would immediately question a developer who says "we need to look into some load balancing solution". Keeping active/active databases in sync in real time is not trivial and/or cheap, e.g. how do you manage identity columns for a start etc. Also where is the bottleneck going to occur ?
That being said there are a couple of (relatively) straight forward approaches you could take depending on your use cases.
If you have large amounts of reads with relatively smaller amounts of write activity then one approach is to use a single database for writing to and export this data to several "read-only" databases
Solutions like 3DNS & BigIP are excellent for creating active/active clusters at an OS level (but they are pricey) but once more you need a thorough understanding of how you will handle synchronization
Odds are that you don't need to load balance the database across multiple servers. In 99% of cases when I'm told that people want to load balance across multiple servers all they need to do is fix the poorly performing queries and in some cases buy more RAM. I've got clients with tables that are going to grow past 2B rows per table (there's two tables in this system I'm thinking of) all in one databases running nicely on that system. Response time is typically under 1/3 of a second. There are thousands of queries a minute running against that table. That specific table has been very carefully tuned as have the queries which are running against it.
Now this system currently has 256 Gigs of RAM on the server. We are actually adding more RAM to the system to extend the life of the server as well. Why do this instead of just trying to scale the database across multiple servers? Adding RAM is cheap, scaling the database across multiple server is very expensive in both costs of SQL Server licensing as well as developer time.
Start by bringing in someone who can do some good solid performing tuning and see where that gets you. Odds are it'll fix most if not all of the performance problems.