[wp-hackers] MySQL Replication Latency Issues in wp-admin pages

[Dion Hulse (dd32)]

I've never used HyperDB myself, however, If you're running an older version,
You'll certainly want to update to the latest release of it.

I just looked through the source code, and it looks as if, If a write is
made to the database, it records that table name, and any further reads to
-that table- get directed to the master instead of a slave.
So if you make a write to wp_options, future reads for wp_options goto the
master, whilst reads for wp_posts go to the slaves.

Thats based on this section from the config file:
* HyperDB accommodates slave replication lag somewhat by keeping track of
the
* tables that it has written since instantiation and sending subsequent read
* queries to the same server that received the write query. Thus a master
set
 * up this way will still receive read queries, but only subsequent to
writes.

and the part of the main db class mentioned as
// Determine whether the query must be sent to the master (a writable
server)





On 17 January 2011 16:53, dave morris <dvmorris at gmail.com> wrote:

> I just noticed that the latest version of HyperDB contains a new
> function called send_reads_to_master(), which should solve my problem
> technically.
>
> Dion, It seems that I could just add a conditional check to db-
> config.php that would set send_reads_to_master(), or $srtm, to true,
> is is_admin() is true.
>
> Would that work?
>
> Dave
>
> On Jan 17, 1:55 pm, "Dion Hulse (dd32)" <wordpr... at dd32.id.au> wrote:
> > Another solution (Which I'm not sure how to achieve) would be to switch
> All
> > reads/writes to the master upon is_admin() & SQL is Update/insert, That
> way
> > you retain as many reads to the slaves as possible, but once the wp-admin
> > decides to make a write, all future reads come from the only source that
> is
> > guarantee'd to be uptodate.
> >
> > On 17 January 2011 12:37, dave morris <dvmor... at gmail.com> wrote:
> >
> >
> >
> >
> >
> >
> >
> >
> >
> > > I have an environment running WP 3.0.1 with a Master db and two
> > > slaves. I am using HyperDB to force all writes to go to the Master,
> > > and all reads to read from the two slaves.
> >
> > > I am experiencing a variety of issues in wp-admin pages where data is
> > > being written to the master, and WordPress attempts to read from a
> > > slave, and the data has not reached the slave yet. An example of this
> > > is when I hook 'dbx_post_advanced' to preset some category and custom
> > > taxonomy terms on new posts. I have verified that when I configure
> > > HyperDB to read and write from the Master alone, the
> > > 'dbx_post_advanced' hook works fine.
> >
> > > I'm looking into the following options:
> >
> > > * Dedicate only one web server to all wp-admin (aka back office)
> > > traffic
> > > ** Configure that server to read and write to/from the Master db only
> > > ** Configure the load balancer to route all /wp-admin url's to that
> > > web server
> >
> > > * Configure HyperDB to read/write against the Master only for wp-admin
> > > pages
> >
> > > * Setup MySQL Semisynchronous Replication
> > > **http://dev.mysql.com/doc/refman/5.5/en/replication-semisync.html
> > > ** This solution will most likely not work, however, because
> > > semisynchronous replication will only wait until ONE slave has
> > > completed its writes, not both slaves in my case
> >
> > > Let me know if you have any advice for this issue.
> >
> > > Thanks,
> > > Dave
> > > _______________________________________________
> > > wp-hackers mailing list
> > > wp-hack... at lists.automattic.com
> > >http://lists.automattic.com/mailman/listinfo/wp-hackers
> >
> > _______________________________________________
> > wp-hackers mailing list
> > wp-hack... at lists.automattic.comhttp://
> lists.automattic.com/mailman/listinfo/wp-hackers
> _______________________________________________
> wp-hackers mailing list
> wp-hackers at lists.automattic.com
> http://lists.automattic.com/mailman/listinfo/wp-hackers
>