@all,
Currently we run all our complex reporting queries on a different server. We
are not using replication though.
What we are doing is to restore the backup of the live data every night onto
the reporting server. So we are running reports on data that is not real time.
And we are OK with that.
We made this setup just to share the load between the two DB servers.
SO on the reporting DB server there will be only select queries running. And so
all the queries will be served from the Query cache and hence there is a big
performance gain.
Any updates that happen is only during the night when the backup from DB server
1 is restored onto the DB server 2 (Reporting DB server).
So every morning all the queries will be slow for the first time on the DB
server 2 and thereafter will be served by the query cache as they will be
cached and never invalidated until the night.
Now suppose we enable real time replication between DB server 1 and DB server 2
then:
1) Will our aim of load balancing be hit because there will be no overall
performance gain as still INSERTS, UPDATES and DELETES will continue to run on
DB server 2 due to replication ?
i.e the load of the DML statements will still continue to be there.
2) Is this notion correct that Replication will provide a performance boost
considering the context. Isnt the load same as when there was no replication?
3) And the query cache will get invalidated on DB server 2 when there is real
time replication. So isnt it another perormance hit?
These questions are just to get an hint of the performance benfit due to
replication because conceptually I feel that there is still the same amount of
load.
On one hand I know that replication is not for load balancing ( am I right? )
and on the other hand I am doubting why Replication? if the load is same.
Pleasecorrect me if I am wrong
Thanks & regards,
Ratheesh
