| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2003-03-04 : 07:30:20
|
| Subhash writes "We are running an eCommerce webiste on SQL2000 and our peak traffic is growing significantly and hence performance slows down during the peak loads...I am looking for a technique which will improve the performance and at the same time have redudancies build in the system. Would clustering solve my both problems or would it just build redudancies (backup)... Any other suggestions to improve performance and fail over.Thanks" |
|
|
szakszi
Starting Member
9 Posts |
Posted - 2003-03-04 : 09:55:11
|
Hello!It's not a simple question...I guess the best technique to improve the performance is the redesign of your tables' structure and indexes, or create indexes.The clustering solves the redundancy, but it is not a low-price solution.There's no an unequivocal answer, yes or no. I suggest, first you need to examine your hardware: CPU and MEM utilisation, and IO/sec perf counters, etc. Second, check your DB's structure, and make backup frequently. And check your backup frequently, too.  BR,Szakszi |
 |
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2003-03-10 : 23:43:53
|
| Clustering does nothing for your performance.-Chad |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-03-11 : 07:06:28
|
| ...but Viagra does something for it. |
|
|
|
Peter Dutch
Posting Yak Master
127 Posts |
Posted - 2003-03-11 : 10:28:27
|
| >> Clustering does nothing for your performance??? HUH ???So a solution with multiple (clustered) machines is not faster than a single machine ???? |
|
|
|
Onamuji
Aged Yak Warrior
504 Posts |
Posted - 2003-03-11 : 11:00:18
|
| depends on which type of clustering you are doing ... failover clustering is very common with databases as if the primary goes down the secondary picks it back up ...failover clustering is NOT a good idea for web servers ... (not that I can convince our NT team here of this, they are still persistant on the matter) ... what you want for web servers is load balancing ...now load balancing is not normally done with database servers ... but it can be done ... the most common method is through replicationessentially each web farm comprimises of the web server and a subscriber to the main database ... basically for all WRITE modifications are sent to the main database while all READS are from the subscriber ...you could even have maybe 2-4 web servers per database box ...you need to have REAL time replication ... www.livejournal.com does this but with MySQL ... I've played with it with my development servers at home ... i never put it under any real load though ...but I think that is what you are looking for ... |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2003-03-11 : 11:26:39
|
quote:
>> Clustering does nothing for your performance??? HUH ???So a solution with multiple (clustered) machines is not faster than a single machine ????
Nope. Because you aren't using multiple machines. One Machine is just sitting, waiting for the other to fail. But wait you say, what if I use Active/Active. Well that isn't much better, because you can't use more than 50% of the resources of either machine, if you do, what happens at failover? You can't have 2 SQL servers taking more than 50% of the resources.Clustering is a High Availability solution...Period. People may try to spin it in other ways, or sell it to you under false pretenses, but that is it's only intended use.Now if you are talking about federated databases (DPVs), these can help performance in certain situations. But thats not clustering.-Chad |
|
|
|
Peter Dutch
Posting Yak Master
127 Posts |
Posted - 2003-03-11 : 12:32:38
|
| You got me really confused on this subject. What I was referring to is a loadbalancing solution, when I think about it. That might not be a clustering solution, but certainly that should improve performance? Or isn't a loadbalancing solution possible with SQL Server? |
|
|
|
Onamuji
Aged Yak Warrior
504 Posts |
Posted - 2003-03-11 : 12:48:08
|
| it is possible .. but you still need to have a central DB for all write operations ... so that any changes are reflected on the read-only nodes ... then each web farm server needs to use a specific read-only db node ... |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2003-03-11 : 14:52:41
|
| Peter, That is not clustering. Clustering, is a high availability solution. Load Balancing is Load Balancing, and is typically used on the web servers rather than the DB server(s).-Chad |
|
|
|
bm1000
Starting Member
37 Posts |
Posted - 2003-03-11 : 15:49:02
|
| By using distributed partitioned views it is possible to design a database application that spans servers (scaling out). In effect a table in each server contains a subset of all of the data (by defining keyranges). A view that unions all of the tables is then used by the application programs. I am unaware of anyone actually using distributed partitioned views in production. It is much easier to purchase a larger server (scaling up). Regarding your original issue of slow performance: I would turn on perfmon and determine what component of the server is slow and address that. If it is SQL Server, turn on perfmon and identify the slow running statements. |
|
|
|
Onamuji
Aged Yak Warrior
504 Posts |
Posted - 2003-03-11 : 15:57:25
|
| definitely scaling up is much cheaper than out in this case ... make sure you are utilizing all the processorsif you are maxed out on ram or have less than 2GB upgrade to at least 2GB and futher depending on the load ... also note that you will need SQL Server Enterprise Edition for 3GB or more of ram .... next check your disk system ... a nice fast well designed disk system can make a huge difference ... try adding more physical disks and moving tables around ... there are all sorts of articles on this on the web |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2003-03-11 : 16:07:21
|
| Yes, Disk subsystem can make for vast performance improvements.I agree that you need to run perfmon to determine where your bottleneck is. Otherwise all of our suggestions are really just shots in the dark.-Chad |
|
|
|
Peter Dutch
Posting Yak Master
127 Posts |
Posted - 2003-03-12 : 01:52:31
|
| Thanks everyone for your explanations, you never stop learning I guess.Peter |
|
|
|
Onamuji
Aged Yak Warrior
504 Posts |
Posted - 2003-03-12 : 06:36:49
|
| one of the more important things is query performance ... make sure your database is as well normalized as you can get it and make sure proper use of indexes is coming into play ... this can either make or break an application ...I took a system that was using cursors and horendous sql statements and made it into a sleek application that is now over 70% faster ... so sometimes ... its not all about hardware ... sometimes programmers just write poor code |
|
|
|
|
Clustering