Ratheesh K J wrote:
> 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.
What do you mean, "aim of load balancing be hit?" Yes, the slave server
will have to replay the statements from the master server.
> 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?
What load are you talking about? The load on the master will be
essentially unchanged by 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?
Yes, this is true.
> 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.
Again I need you to more clearly define what you mean by "the load."