|
Home > Archive > MS SQL Server > January 2006 > Slave (replicated) database to improve SELECT performance?
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Slave (replicated) database to improve SELECT performance?
|
|
| Jeff Turner 2006-01-24, 8:23 pm |
| Hello,
First let me waive the standard disclaimer and say I only partly know
what I'm talking about. :)
Anyway, I've got a website that runs SQL 2005 (standard) on the
backend. Right now I'm doing about 4-5 INSERT/UPDATEs (one or the
other) per second at a constant clip... Everything is just fine.
As this grows, I'll get more and more INSERT/UPDATEs per second, which
potentially could grow upwards of 40-50 per second (God willing).
Now I want to allow users to query against this data as well. So I'm
wondering if it will be better to replicate the data to a read-only
database to do my SELECTs from (so my I/Us don't kill the queries)?
The SELECT data doesn't need to be instant, it could be anywhere from 5
to 30 minutes behind with no problems (if that even matters).
I read somewhere this is what the Flickr guys did with MySQL... They
scaled out to slave databases that handled the SELECTs.
Does anyone have any expert advice here that might help?
Thanks!!
| |
| David Browne 2006-01-24, 8:23 pm |
|
"Jeff Turner" <zigjst@gmail.com> wrote in message
news:1138142019.495954.94650@z14g2000cwz.googlegroups.com...
> Hello,
>
> First let me waive the standard disclaimer and say I only partly know
> what I'm talking about. :)
>
> Anyway, I've got a website that runs SQL 2005 (standard) on the
> backend. Right now I'm doing about 4-5 INSERT/UPDATEs (one or the
> other) per second at a constant clip... Everything is just fine.
>
> As this grows, I'll get more and more INSERT/UPDATEs per second, which
> potentially could grow upwards of 40-50 per second (God willing).
>
> Now I want to allow users to query against this data as well. So I'm
> wondering if it will be better to replicate the data to a read-only
> database to do my SELECTs from (so my I/Us don't kill the queries)?
>
> The SELECT data doesn't need to be instant, it could be anywhere from 5
> to 30 minutes behind with no problems (if that even matters).
>
> I read somewhere this is what the Flickr guys did with MySQL... They
> scaled out to slave databases that handled the SELECTs.
>
> Does anyone have any expert advice here that might help?
>
This is an option. However I think you won't need any such trick at the
transaction volumes you are talking about.
With a reasonable database server and and a reasonably well-written
application supporting a few hundred inserts per second along with
simultaneous query access shouldn't be a problem. After all that's what a
database server is for!
You might look into turning on READ CONCURRENT SNAPSHOT ISOLATION which will
help keep SELECT queries and INSERT statements from blocking each other.
David
|
|
|
|
|