1

I work on a few high traffic websites that all share the same database and that are all heavily database driven. Our SQL server is max-ed out and, although we have already implemented many changes that have helped but the server is still working too hard.

We employ some caching in our website but the type of queries we use negate using SQL dependency caching.

We tried SQL replication to try and kind of load balance but that didn't prove very successful because the replication process is quite demanding on the servers too and it needed to be done frequently as it is important that data is up to date.

We do use a Varnish web caching server (Linux based) to take a bit of the load off both the web and database server but as a lot of the sites are customised based on the user we can only do so much.

Anyway, the reason for this question... Varnish gave me an idea for a possible application that might help in this situation.

Just like Varnish sits between a web browser and the web server and caches response from the web server, I was wondering about the possibility of creating something that sits between the web server and the database server.

Imagine that all SQL queries go through this SQL caching server. If it's a first time query then it will get recorded, and the result requested from the SQL server and stored locally on the cache server. If it's a repeat request within a set time then the result gets retrieved from the local copy without the query being sent to the SQL server. The caching server could also take advantage of SQL dependency caching notifications.

This seems like a good idea in theory. There's still the same amount of data moving back and forward from the web server, but the SQL server is relieved of the work of processing the repeat queries.

I wonder about how difficult it would be to build a service that sort of emulates requests and responses from SQL server, whether SQL server's own caching is doing enough of this already that this wouldn't be a benefit, or even if someone has done this before and I haven't found it? I would welcome any feedback or any references to any relevant projects.

Improve this question
8
  • 1
    A bit away from what you are looking for, but Apache Solr would come with the ability to cache requests. The kind of query is quite different from SQL of course, but you could either use a interface that 'emulates' this or maybe using something like Solr could get away some of the heavy queries anyway if used properly (would depend on the kind of query you run, Solr can do a bit ore than full text search)
    – thorsten müller
    Commented Dec 13, 2012 at 10:09
  • Your idea is solid enough. We actually already have this type of caching built into our query engine (in a sense we are our own database) and are moving it out of there into a REST server layer around it. This will enable us to move the caching out of the query service entirely, should we so want to.
    – Marjan Venema
    Commented Dec 13, 2012 at 12:29
  • 1
    Did you had a chance to look at AppFabric caching? msdn.microsoft.com/en-us/library/ff383731(v=azure.10).aspx
    – Yusubov
    Commented Dec 13, 2012 at 13:27
  • Would it not be a better idea to spend the resources on upgrading the SQL Server and rely on it to cache queries for itself?
    – deed02392
    Commented Dec 13, 2012 at 13:52
  • How are you currently sending queries to your server, are you using an ORM? It sounds like you could probably benefit from making some stored procedures.
    – Ryathal
    Commented Dec 13, 2012 at 14:19

2 Answers 2

Reset to default
1

Citrix Netscaler provides this functionality. http://blogs.citrix.com/2012/05/10/netscaler-datastream-enhancements-in-v10%E2%80%A6continuing-to-build-on-game-changing-adc-functionality/

I'm not a Citrix Rep, but we use the Netscaler for load-balancing, fail-over and GSLB.

Amazon, I believe uses Netscalers to manage their hosting service, so it's enterprise certified.

Basically, it works just like Squid except for SQL, plus other things like pointing all reads to one SQL svr and writes to another. Cool stuff.

Don't know if there's an open-source equivalent product.

Long-term... might want to look into Hadoop (http://hadoop.apache.org/) to solve the problem in a different way.

Improve this answer
1

There are solutions for what you want: above mentioned AppFabric cache (code name "Velocity") or Memcached.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.