Request for enhancement: my.cnf knob to force slave to process se

Hi,

We've been running mysql for a while in a fairly large set of replicated
databases. We recently found a behavior in mysql replication that is
slightly inconvenient for our needs.

Here's our config:

- a 3.23 version of mysql
- all applications connect to master which has a slave which has
alternating_backups

In our environment, the master databases are sufficiently large and loaded
that we cannot perform slave resynchronization from the website live master.
The transfer traffic exceeds the headroom of the live master and our website
overloads.

Because of this we have an operations policy where we run like this:

Initial state
- application->hostA->hostB (e.g. a routine replication config)
- daily lvm snapshot backups from hostB to an alternating pair of remote
backup hosts
- a careful binlog storage and purge policy that keeps sufficient binlogs
around that match the images on backup hosts

When the master fails we have a manual fail over process to make hostB the
master

- we halt or power off or gracefull shutdown hostA [depending on the fault]
- we verify hostB was in binlog sync at the time of the fault [more or
less]
- we move an application IP (used by all apps) from hostA to hostB
- the application recovers and runs normally [with hostB as master]
- backups from hostB are paused while recovery commences

How-To-Repeat:
The old master hostA machine is then scheduled for repair or replacement

- we analyze and or rebuild the host as appropriate
- then it is time to make hostA into a slave of hostB
- we copy the most recent backup from the backup cluser to hostA [this was
an image from hostB originally]
- we then start mysql on hostA
- we then configure hostA to point to hostB and hostB's logfile position at
the point of the recovery backup
- we then start the slave on hostA

The problem we have is that hostA winds up skipping all its original updates
that are in hostB's binlog -- basically all the application updates from the
time of the backup to the time of the failover.

Now we understand this skip is necessary for bi-directional slave updates
hostA->hostB and hostB->hostA at the same time.

What we are proposing is a my.cnf knob that says a slave server will process
all binlog events, regardless of the original source server ID. [e.g. the
first test in slave.cc:exec_event() that detects that the log entry was from
our serverid]. We understand this will create infinite loops on circular
slave configurations, and this knob should obviously carry that caveat.

In the mean time, we are evalating a change to our safe_mysqld that
generates unique server ids on each invocation of mysql. Right now, server
id is hard coded in our my.cnf configuration. But running each
instantiation of mysqld with a unique server id would probably be fine for
us. (in fact a more sophisticated solution to the 'skip self' test in
question may be to include both the global id and a unique per invocation id
to help the servers know when to get back in the loop in more complicated
ring configurations of the future...).

Let me know your opinion on a change like this.

Thanks in advance.

Greg McNutt
CTO AuctionWatch.com
gmcnutt@auctionwatch.com



------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13845@lists.mysql.com
To unsubscribe, e-mail

Re: Request for enhancement: my.cnf knob to force slave to process se rver"s own binlog entries

Hi,

> How-To-Repeat:
> The old master hostA machine is then scheduled for repair or replacement
>
> - we analyze and or rebuild the host as appropriate
> - then it is time to make hostA into a slave of hostB
> - we copy the most recent backup from the backup cluser to hostA [this was
> an image from hostB originally]
> - we then start mysql on hostA
> - we then configure hostA to point to hostB and hostB's logfile position
> at the point of the recovery backup
> - we then start the slave on hostA
>
> The problem we have is that hostA winds up skipping all its original
> updates that are in hostB's binlog -- basically all the application updates
> from the time of the backup to the time of the failover.
>
> Now we understand this skip is necessary for bi-directional slave updates
> hostA->hostB and hostB->hostA at the same time.

Indeed.

> What we are proposing is a my.cnf knob that says a slave server will
> process all binlog events, regardless of the original source server ID.
> [e.g. the first test in slave.cc:exec_event() that detects that the log
> entry was from our serverid]. We understand this will create infinite
> loops on circular slave configurations, and this knob should obviously
> carry that caveat.

It's a good but dangerous idea as you explained. I will put this in our TODO.
Note that this new feature will be neither in 3.23 nor in 4.0 as this two
versions are frozen (only bugs are fixed in these).

> In the mean time, we are evalating a change to our safe_mysqld that
> generates unique server ids on each invocation of mysql. Right now, server
> id is hard coded in our my.cnf configuration. But running each
> instantiation of mysqld with a unique server id would probably be fine for
> us. (in fact a more sophisticated solution to the 'skip self' test in
> question may be to include both the global id and a unique per invocation
> id to help the servers know when to get back in the loop in more
> complicated ring configurations of the future...).

You are right, for MySQL to support multimaster configurations like

M1
| |
| |
+--+ +--+
| |
v v
M2 M3
| |
+--+ +--+
| |
| |
v v
S

in the future, new ids will have to be implemented (if M1 executes a query,
then it propagates to M2 and M3 then S gets it twice, which must be avoided).

> Let me know your opinion on a change like this.

Ids changes will come when multimaster is implemented.

In your case, as you suggested, the easiest and perfect solution is modifying
your safe_mysqld to generate a unique server id at each server startup.
For example, if you have 5 machines, machine 1 could get server ids of the
form n*5+0, machine 2 of the form n*5+1, ... machine 5 of the form n*5+4.
This way 2 different machines will never have the same id, and you just need
to have a small file on each machine which keeps track of the last 'n' for
this machine ('n's needn't be the same for all 5 machines).

Thank you for your suggestions.

--
MySQL 2003 Users Conference -> http://www.mysql.com/events/uc2003/
For technical support contracts, visit https://order.mysql.com/?ref=mgbi
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Guilhem Bichot
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Software Developer
/_/ /_/\_, /___/\___\_\___/ Bordeaux, France
<___/ www.mysql.com

------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13848@lists.mysql.com
To unsubscribe, e-mail

UNION and QUERY CACHE on DEMAND

Hello,

1) "SQL_CACHE" doesn't work for SELECTs in UNION - they are not stored in query cash
(MySQL 4.0.10)
2) so, is there any posibility to make MySQL store whole UNION query in query cache using
"on demand" type???

Thanx

------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13850@lists.mysql.com
To unsubscribe, e-mail

Re: UNION and QUERY CACHE on DEMAND

Dmitry Adams writes:
> Hello,
>
> 1) "SQL_CACHE" doesn't work for SELECTs in UNION - they are not stored in query cash
> (MySQL 4.0.10)
> 2) so, is there any posibility to make MySQL store whole UNION query in query cache using
> "on demand" type???
>
> Thanx
>

1) No it does not. The way that UNION's are implemented does not make
it possible to cache individual SELECT's and it will not be
possible in a foreseeable future

2) UNION as a whole is cached in the query cache, just as any other


--
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
<___/ www.mysql.com

Join MySQL Users Conference and Expo:
http://www.mysql.com/events/uc2003/


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13851@lists.mysql.com
To unsubscribe, e-mail

Re: UNION and QUERY CACHE on DEMAND

Hi!

On Thu, Feb 27, 2003 at 08:22:30PM +0200, Sinisa Milivojevic wrote:
> Dmitry Adams writes:
> > Hello,
> >
> > 1) "SQL_CACHE" doesn't work for SELECTs in UNION - they are not stored in query cash
> > (MySQL 4.0.10)
> > 2) so, is there any posibility to make MySQL store whole UNION query in query cache using
> > "on demand" type???
> >
> > Thanx
> >
>
> 1) No it does not. The way that UNION's are implemented does not make
> it possible to cache individual SELECT's and it will not be
> possible in a foreseeable future
>
> 2) UNION as a whole is cached in the query cache, just as any other

Beside Sinisa's fine answers, I would like to touch on the topic that was
not elaborated upon.
You have mentioned the usage of SQL_CACHE select option within UNION's.
It turned out that this IS actually a bug, that I am addressing right now.

For now this option have influence on query cache to be used only in one of
UNION's SELECT. I will provide patch that fix this problem which will be
included in next release of mysql.

--
For technical support contracts, visit https://order.mysql.com/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Oleksandr Byelkin
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Lugansk, Ukraine
<___/ www.mysql.com

------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13854@lists.mysql.com
To unsubscribe, e-mail

Re: UNION and QUERY CACHE on DEMAND

Hi!

On Thu, Feb 27, 2003 at 12:25:15PM -0500, Dmitry Adams wrote:
> Hello,
>
> 1) "SQL_CACHE" doesn't work for SELECTs in UNION - they are not stored in query cash
> (MySQL 4.0.10)
> 2) so, is there any posibility to make MySQL store whole UNION query in query cache using
> "on demand" type???

Thank you for your bugreport. Here is patch to fix this problem:

diff -Nrc a/sql/sql_cache.cc b/sql/sql_cache.cc
*** a/sql/sql_cache.cc Thu Feb 27 22:29:30 2003
--- b/sql/sql_cache.cc Thu Feb 27 22:29:30 2003
***************
*** 2439,2445 ****

if (lex->sql_command == SQLCOM_SELECT &&
(thd->variables.query_cache_type == 1 ||
! (thd->variables.query_cache_type == 2 && (lex->select->options &
OPTION_TO_QUERY_CACHE))) &&
thd->safe_to_cache_query)
{
--- 2439,2445 ----

if (lex->sql_command == SQLCOM_SELECT &&
(thd->variables.query_cache_type == 1 ||
! (thd->variables.query_cache_type == 2 && (lex->select_lex.options &
OPTION_TO_QUERY_CACHE))) &&
thd->safe_to_cache_query)
{
diff -Nrc a/sql/sql_yacc.yy b/sql/sql_yacc.yy
*** a/sql/sql_yacc.yy Thu Feb 27 22:29:30 2003
--- b/sql/sql_yacc.yy Thu Feb 27 22:29:30 2003
***************
*** 1530,1536 ****
Select->options|= OPTION_FOUND_ROWS;
}
| SQL_NO_CACHE_SYM { current_thd->safe_to_cache_query=0; }
! | SQL_CACHE_SYM { Select->options|= OPTION_TO_QUERY_CACHE; }
| ALL {}
;

--- 1530,1539 ----
Select->options|= OPTION_FOUND_ROWS;
}
| SQL_NO_CACHE_SYM { current_thd->safe_to_cache_query=0; }
! | SQL_CACHE_SYM
! {
! Lex->select_lex.options|= OPTION_TO_QUERY_CACHE;
! }
| ALL {}
;



--
For technical support contracts, visit https://order.mysql.com/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Oleksandr Byelkin
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Lugansk, Ukraine
<___/ www.mysql.com

------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13856@lists.mysql.com
To unsubscribe, e-mail

Re: Request for enhancement: my.cnf knob to force slave to process

Hi,

We had been in touch on this one year ago.

On Thu, 2003-02-27 at 15:00, Guilhem Bichot wrote:
> Hi,
>
> > How-To-Repeat:
> > The old master hostA machine is then scheduled for repair or replacement
> >
> > - we analyze and or rebuild the host as appropriate
> > - then it is time to make hostA into a slave of hostB
> > - we copy the most recent backup from the backup cluser to hostA [this was
> > an image from hostB originally]
> > - we then start mysql on hostA
> > - we then configure hostA to point to hostB and hostB's logfile position
> > at the point of the recovery backup
> > - we then start the slave on hostA
> >
> > The problem we have is that hostA winds up skipping all its original
> > updates that are in hostB's binlog -- basically all the application updates
> > from the time of the backup to the time of the failover.
> >
> > Now we understand this skip is necessary for bi-directional slave updates
> > hostA->hostB and hostB->hostA at the same time.
>
> Indeed.
>
> > What we are proposing is a my.cnf knob that says a slave server will
> > process all binlog events, regardless of the original source server ID.
> > [e.g. the first test in slave.cc:exec_event() that detects that the log
> > entry was from our serverid]. We understand this will create infinite
> > loops on circular slave configurations, and this knob should obviously
> > carry that caveat.
>
> It's a good but dangerous idea as you explained. I will put this in our TODO.
> Note that this new feature will be neither in 3.23 nor in 4.0 as this two
> versions are frozen (only bugs are fixed in these).

Good news: I have implemented this feature today, it will be in MySQL
4.0.19 (which should be released in a few days). It is called
the --replicate-same-server-id option, it is exactly the knob you asked
for.

Does MySQL still work fine for you?

--
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Guilhem Bichot
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Software Developer
/_/ /_/\_, /___/\___\_\___/ Bordeaux, France
<___/ www.mysql.com



--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org