Rather dramatic performance change

CellarTracker Main Site

Rather dramatic performance change

View related threads: (in this forum | in all forums)

Logged in as: Guest

Users viewing this topic: none

Printable Version

All Forums >> [Cellar Talk] >> Release Notes >> Rather dramatic performance change

Page: [1]

Message

<< Older Topic Newer Topic >>

Rather dramatic performance change - 8/4/2008 12:40:39 AM

Eric

Posts: 7762
Joined: 10/10/2003
From: Seattle, WA
Status: offline

Performance has been lagging quite badly lately, as I am sure that may of you have noticed. I have been mulling various ways to improve this including a migration to some gargantuan hardware. However, before doing that I wanted to try out what I hoped would be a neat trick. As of this posting I have JUST turned this on, and so far it seems to be working pretty well. Here is the gist:

The main database server now replicates in real time to a parallel database server. (It happens to be the older database server which I pulled out of rotation a year ago.) The latency between these servers is pretty minimal--the replicant is typically just a few transactions (5-10 seconds) behind the live database server.
I have coded the web server so that logged in users (e.g. registered users who can do read/write operations) continue to access the same database server that we have been using for the past year.
And, to improve performance, 'guest' users all query the read-only replicated server.

In effect this allows me to spread the load across two database servers. There are two potential sets of issues:

In theory replication can be somewhat fragile if the two servers have trouble seeing each other. It can also make my life more difficult if/when I want to deploy database changes. I will be wrapping my head around this over the coming days.
My traffic allocation may well be unbalanced. For example, before I deployed this change the main database server was at a rather heavy 50% CPU utilization. Now it is at 2%. Meanwhile the read-only server has gone from unused to a rather drastic 70% CPU. (It is not as beefy a server as the main server.) So the load is nowhere near balanced. THis makes sense since 75-90% of the traffic these days is from 'guests' coming to read the wine reviews that we all generate.

Anyway, it need to tweak things to better balance this (whether by just targeting known user agents such as web crawlers, using a random number generator or some IP algorithm to assign some guest to the RO server and some to the main server etc.) The fear is that I may well have dramaticlly improved the experience for one set of users while torching it for another.

All that said, the approach here appears to be quite promising. I will likely still be pursuing much more enterprise grade hardware, 64-bit migration, AND looking at some segmentation across two or more databases servers.

_____________________________

Cheers!
-Eric LeVine

http://twitter.com/cellartracker

Post #: 1

RE: Rather dramatic performance change - 8/4/2008 1:13:55 AM

Eric

Posts: 7762
Joined: 10/10/2003
From: Seattle, WA
Status: offline

Well I have been tweaking it a bit, and right now I am sending 70% of the guest traffic to the wimpier read-only server. That is keeping it plenty busy but not drowning it. Meanwhile the main database server is now coasting along easily. Performance seems dramatically better all the way around.

I clearly have more tweaking to do here and likely lots more learning about replication, but I am very pleased with this experiment so far.

_____________________________

Cheers!
-Eric LeVine

http://twitter.com/cellartracker

(in reply to Eric)

Post #: 2

RE: Rather dramatic performance change - 8/4/2008 1:37:44 AM

Eric

Posts: 7762
Joined: 10/10/2003
From: Seattle, WA
Status: offline

I have settled on 75% for the moment. Fascinating the sort of queries that guests run seem to perform quite well even when the server is very busy. In contrast, add/editing data and the more complex queries that a cellar manager would run seem to have a dramatic difference in performance based on how loaded the server is. Anyway, this is pretty fun. And wow the site hasn't felt this snappy in a year. I am curious to see how it does tomorrow when more people are parting away. (Monday AM is one of the busiest times of the week if you can believe it.)

_____________________________

Cheers!
-Eric LeVine

http://twitter.com/cellartracker

(in reply to Eric)

Post #: 3

RE: Rather dramatic performance change - 8/4/2008 8:30:57 AM

Eric

Posts: 7762
Joined: 10/10/2003
From: Seattle, WA
Status: offline

Ahh, what an absolute pleasure to wake up to this!

The main database CPU utilization is at 5% this AM, and the site is snappy, snappy, snappy for logged in users.
And the guest database (handling 75% of the guest load) is at 40-50%, nowhere near as pegged as last night. It is likely that Google and other crawlers were hammering the site last night when I was doing this switchover.

I think this is going to work out really well.

Next up: a beefy SAN, VMWARE, Iron Mountain Enterprise backup instead of my current approach. The goal is a high-availability site that is insulated from more than 2 minutes of downtime.

_____________________________

Cheers!
-Eric LeVine

http://twitter.com/cellartracker

(in reply to Eric)

Post #: 4

RE: Rather dramatic performance change - 8/10/2008 2:26:53 PM

Eric

Posts: 7762
Joined: 10/10/2003
From: Seattle, WA
Status: offline

One week later, and this replication is working beautifully. I am in awe of how seamless it has been actually. I deserve none of the credit here: all that goes to Microsoft. Big time kudos to them!

_____________________________

Cheers!
-Eric LeVine

http://twitter.com/cellartracker

(in reply to Eric)

Post #: 5

RE: Rather dramatic performance change - 8/11/2008 3:37:17 PM

grafstrb

Posts: 2583
Joined: 11/6/2007
From: Los Angeles, via Minnesota
Status: online

Thank you for your continued hard work in the trenches, Eric. Your commitment to CT and its community is unfaltering and commendable.

Thanks!

(in reply to Eric)

Post #: 6

RE: Rather dramatic performance change - 8/11/2008 4:35:58 PM

Eric

Posts: 7762
Joined: 10/10/2003
From: Seattle, WA
Status: offline

Much appreciated.

_____________________________

Cheers!
-Eric LeVine

http://twitter.com/cellartracker

(in reply to grafstrb)

Post #: 7

RE: Rather dramatic performance change - 8/13/2008 9:25:57 AM

Serge Birbrair

Posts: 3308
Joined: 4/23/2006
From: Boca Raton, Florida
Status: offline

If I could understand 1/10th of what you said....

anyway, glad it works faster now, hooray!

_____________________________

Good wine needs no bush.
William Shakespeare

(in reply to Eric)

Post #: 8

RE: Rather dramatic performance change - 9/9/2008 10:49:35 PM

Eric

Posts: 7762
Joined: 10/10/2003
From: Seattle, WA
Status: offline

Well this has been working beautifully far, far better than I could have ever hoped.

_____________________________

Cheers!
-Eric LeVine

http://twitter.com/cellartracker

(in reply to Serge Birbrair)

Post #: 9

RE: Rather dramatic performance change - 10/7/2008 2:37:43 PM

brouigu1

Posts: 1
Joined: 9/30/2007
Status: offline

Totally agree... Performance has been great and is much appreciated! I have turned several of my wine friends on to this as well and its become a topic of conversation over wine... Thanks for making this fun and enjoyable.

Cheers

(in reply to Eric)

Post #: 10

RE: Rather dramatic performance change - 10/10/2008 1:08:11 AM

Paul S

Posts: 1816
Joined: 1/15/2008
From: Singapore
Status: offline

Much better Eric - thanks for all the hard work!

(in reply to brouigu1)

Post #: 11