Re: fwd: relay_log_space_limit does not seem to work

Hi!

> >Description:
> I have been looking for a way of controlling the size of the relay logs.
> I found nothing in the manuall regarding this but suddenly the changelog for
> 4.0.13 mention relay_log_space_limit.
>
> However, setting this variable does not seem to work.
> Information about relay_log_space_limit should also be mention in the manual,
> now it only appears on the changelog

Well, for replication options and variables I advise you read here:
http://www.mysql.com/doc/en/Replication_Options.html
where you will find the description of this option.

> >How-To-Repeat:
> in /etc/my.cnf on slave I have:
> [mysqld]
> set-variable = relay_log_space_limit=300M
>
> But my relay logs are much bigger:
> $ ls -all /var/lib/mysql/*relay*
> -rw-rw---- 1 mysql mysql 712699765 Jun 12 14:55 /var/lib/mysql/foobar-relay-bin.001

You are right, I need to add one line to this option's present
description in the manual.
I need to add that this option interacts with max_binlog_size (note: in
some future version (maybe 4.0.14 if I get the necessary approvals) I
will introduce a new variable max_relay_log_size, then you'll have to
replace max_binlog_size by max_relay_log_size in what I am writing now;
please check the changelog when 4.0.14 is released).
As the manual says, when the total relay logs size reaches
relay-log-space-limit, the I/O slave thread waits for the SQL slave
thread to purge some unused relay logs. But it will work if there are
any relay logs to purge. In your case you only have the current relay
log, so the SQL slave thread cannot purge anything, so the I/O slave
thread notices that it has to ignore the limit, or replication will be
stuck (which happened in 4.0.12).
So you need to set max_binlog_size to, say, not more than one third or
half of relay_log_space_limit (the default value of max_binlog_size is
1G, so it's too much for you). I need to add this advice to our manual.
Then you'll have several relay logs, purged when the SQL thread finishes
reading them, and the I/O thread will honour relay_log_space_limit,
unless you do a transaction bigger than 300MB.

Regards,
Guilhem
____________________________________________________________ ____________
--
For technical support contracts, visit https://order.mysql.com/?ref=mgbi
Are you MySQL certified? visit http://www.mysql.com/certification/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / 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

Re: fwd: relay_log_space_limit does not seem to work

Hi Vidar,

I cc'ed bugs@lists because I thought the explanations below could be
useful to others having similar concerns.

On Fri, 2003-06-13 at 11:41, Vidar wrote:
> > As the manual says, when the total relay logs size reaches
> > relay-log-space-limit, (...)
> Arg, you right. When I looked in the manual, I searched for
> relay_log_space_limit (with underscore instead for dash) since this is how it
> is written in the changelog. My fault, sorry about that

that's ok. I also get caught by this '-_' thing; in MySQL we try to be
easy-using (MySQl accepts max-relay-log-space and max_relay_log_space)
but the drawback is that we developers sometimes use our personal
convention when writing the documentation. We have colleagues who
re-read our doc changes, but sometimes these details are not caught.
Certainly we would need a policy for this ().

> Sorry for this long email, but I hope you will take the time to read it
> :)

yes, I did :)

> > So you need to set max_binlog_size to, say, not more than one third or
> > half of relay_log_space_limit (the default value of max_binlog_size is
> > 1G, so it's too much for you).
> My max_binlog is also 300M

well, then max_binlog_size if set to more than 1/3 or 1/2 of
relay_log_space_limit, which is bad (I thought of maybe adding a warning
about this in mysqld).

> > I need to add this advice to our manual.
> > Then you'll have several relay logs, purged when the SQL thread finishes
> > reading them, and the I/O thread will honour relay_log_space_limit,
> > unless you do a transaction bigger than 300MB.
> I do not have transactions of that size in my environment, so this should not
> be a problem

ok. Easier for the discussion.

> However, as allready mention, max_binlog_size is 300M
> I now have two binlogs on the server
> -rw-rw---- 1 mysql mysql 322127500 Jun 11 22:54 foobar1-bin.004
> -rw-rw---- 1 mysql mysql 162218080 Jun 13 10:48 foobar1-bin.005

ok, both =<300M.
Aren't we confusing things here? These are apparently master's binlogs
(they don't have "relay" in their names), and we wanted to talk about
relay logs ??
To get the behaviour you want, you must set max_binlog_size on the
*slave*.
>From the example you gave in your first email:
-rw-rw---- 1 mysql mysql 712699765 Jun 12 14:55 /var/lib/mysql/foobar-relay-bin.001
(700M) I tend to think you did NOT set max_binlog_size to 300M on the
slave, which is the cause of the misunderstanding maybe.

> I am running "purge master logs to ...." regullary, so I normally never have
> more than two binlogs on the master.

ok.

> So even though I have both max_binlog_size and relay_log_space_limit set to
> the same, my relaylog is now over twice the size(800M) of my max_binlog_size.
> Are you saying that if I had set relay_log_space_limit to 600M, this would not
> have happend ?

Here is how it works : imagine on the slave you set
max_binlog_size=relay_log_space_limit=300M.
Then a first relay log (relay1) is created and filled by the IO thread.
Reaches 300M. The IO thread rotates it, creates relay2.
The IO thread notices the total exceeds 300M, so stops filling relay2.
The SQL thread, which is always late compared to the IO thread, reads
relay1, has now finished reading it so it can be deleted (provided no
statement is split between relay1 and relay2, example:
relay1:
....
SET RANDSEED1=103897;
SET RANDSEED2=984;
---------
relay2
INSERT INTO T VALUES(RAND())
....
)
so deletes relay1 and tells the IO thread to wake up, etc.
If a statement had been split, then when the SQL thread reaches the end
of relay1 it cannot delete it (because if you do STOP SLAVE at that
precise moment, the SQL thread will need the last lines of relay1 (the
RANDSEED ones) at restart, because otherwise it cannot execute well the
first line of relay2). So the SQL thread keeps relay1 and starts reading
relay2. Reaches the end of relay2 (remember the IO thread is blocked).
Understands this is a deadlock situation. Wakes up the IO thread and
tells it "ok, forget about relay_log_space_limit for now please". So
relay2 is filled again, and the total reaches 600M. When the SQL thread
reaches the end of relay2, it will delete relay1, and probably relay2
(unless a statement is split between relay2 and relay3). Etc etc.
This example was to show that with
max_binlog_size=relay_log_space_limit=300M you may get 600MB of relay
logs. This is why the more sensible setting is
max_binlog_size*2 =< relay_log_space_limit.

> for now, the relay_logs will be rotated at 1G no matter what ?

Yes, because apparently you did not set max_binlog_size on the slave.

> In order to controll the maxsize of each relaylog file, we'l need a
> max_relay_log_size variable, which you hope to introduce in 4.0.14, right ?

max_relay_log_size is intended for people who want their slave's binlogs
(if running with --log-slave-updates) to not have the same size as their
slave's relay logs. For the moment, max_binlog_size applies to binlogs
and relay logs.

> I am pretty sure I found one bug though:
> The relay-log* variables (like relay-log-space-limit ) is not shown when you
> do a "show variables"

We surely have a problem with all variables related to replication:
- some can be dynamically set, others only at startup
- most of them are not printed in SHOW VARIABLES but in SHOW SLAVE
STATUS.

This is TODO. Note however that SHOW VARIABLES is not a solution when
MySQL supports multimaster, because one variable (replicate-do-db for
example) will have different values for different simultanate masters.

Regards,
Guilhem

--
For technical support contracts, visit https://order.mysql.com/?ref=mgbi
Are you MySQL certified? visit http://www.mysql.com/certification/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / 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