I have master/slave replication working but I was wondering why I am
getting so many very small relay logs on the slave. It seems to be
ignoring max_relay_log_size and max_binlog_size.

I first noticed this when my replication broke for about a week and I
needed to resync the slave. Instead of a few 1 gig log files like were
created on the master, the slave had over 14000 relay logs ranging in
size from 200-2000 bytes.

From the slave:

+------------------------+----------------------+
| Variable_name | Value |
+------------------------+----------------------+
| max_binlog_size | 1073741824 |
| max_relay_log_size | 0 |
| version | 5.0.41-log |
+------------------------+----------------------+

AFAIK there are no flush logs executing on the slave. I do get one on
the master every night as part of the backup script.

TIA

Chuck wrote:
> I have master/slave replication working but I was wondering why I am
> getting so many very small relay logs on the slave. It seems to be
> ignoring max_relay_log_size and max_binlog_size.
>
> I first noticed this when my replication broke for about a week and I
> needed to resync the slave. Instead of a few 1 gig log files like were
> created on the master, the slave had over 14000 relay logs ranging in
> size from 200-2000 bytes.
>
> From the slave:
>
> +------------------------+----------------------+
> | Variable_name | Value |
> +------------------------+----------------------+
> | max_binlog_size | 1073741824 |
> | max_relay_log_size | 0 |
> | version | 5.0.41-log |
> +------------------------+----------------------+
>
> AFAIK there are no flush logs executing on the slave. I do get one on
> the master every night as part of the backup script.
>
> TIA

No takers?

"Chuck" <skilover_nospam@bluebottle.com> wrote in message
news:ZoRIj.6160$A87.2057@trnddc06...
> Chuck wrote:
>> I have master/slave replication working but I was wondering why I
>> am getting so many very small relay logs on the slave. It seems to
>> be ignoring max_relay_log_size and max_binlog_size.
>>
>> I first noticed this when my replication broke for about a week and
>> I needed to resync the slave. Instead of a few 1 gig log files like
>> were created on the master, the slave had over 14000 relay logs
>> ranging in size from 200-2000 bytes.
>>
>> From the slave:
>>
>> +------------------------+----------------------+
>> | Variable_name | Value |
>> +------------------------+----------------------+
>> | max_binlog_size | 1073741824 |
>> | max_relay_log_size | 0 |
>> | version | 5.0.41-log |
>> +------------------------+----------------------+
>>
>> AFAIK there are no flush logs executing on the slave. I do get one
>> on the master every night as part of the backup script.
>>
>> TIA
>
> No takers?

Is the slave log not starting a new file everytime it is started /
restarted?
Something in the docs somewhere if I remember well.

Try a search for "binlog" "reset slave" on the mysql site, or
variations

Richard.

Richard wrote:
> "Chuck" <skilover_nospam@bluebottle.com> wrote in message
> news:ZoRIj.6160$A87.2057@trnddc06...
>> Chuck wrote:
>>> I have master/slave replication working but I was wondering why I
>>> am getting so many very small relay logs on the slave. It seems to
>>> be ignoring max_relay_log_size and max_binlog_size.
>>>
>>> I first noticed this when my replication broke for about a week and
>>> I needed to resync the slave. Instead of a few 1 gig log files like
>>> were created on the master, the slave had over 14000 relay logs
>>> ranging in size from 200-2000 bytes.
>>>
>>> From the slave:
>>>
>>> +------------------------+----------------------+
>>> | Variable_name | Value |
>>> +------------------------+----------------------+
>>> | max_binlog_size | 1073741824 |
>>> | max_relay_log_size | 0 |
>>> | version | 5.0.41-log |
>>> +------------------------+----------------------+
>>>
>>> AFAIK there are no flush logs executing on the slave. I do get one
>>> on the master every night as part of the backup script.
>>>
>>> TIA
>> No takers?
>
> Is the slave log not starting a new file everytime it is started /
> restarted?
> Something in the docs somewhere if I remember well.
>
> Try a search for "binlog" "reset slave" on the mysql site, or
> variations
>
> Richard.
>
>

Nothing is restarting the slave threads, except for the one time I did
it when I noticed that replication was broken and I had to fix it.

The docs clearly lay out three things that cause a new relay log to be
started ...

.. Each time the I/O thread starts.
.. When the logs are flushed;
.. When the size of the current relay log file becomes too large. The
meaning of “too large” is determined as follows: If the value of
max_relay_log_size is greater than 0, that is the maximum relay log file
size. If the value of max_relay_log_size is 0, max_binlog_size
determines the maximum relay log file size.

I'm not encountering any of these thoughs. It's very strange that I'm
getting thousands of very small relay logs for each binlog on the master.

"Chuck" <skilover_nospam@bluebottle.com> wrote in message
news:L7TIj.6169$A87.1381@trnddc06...
> Richard wrote:
>> "Chuck" <skilover_nospam@bluebottle.com> wrote in message
>> news:ZoRIj.6160$A87.2057@trnddc06...
>>> Chuck wrote:
>>>> I have master/slave replication working but I was wondering why I
>>>> am getting so many very small relay logs on the slave. It seems
>>>> to be ignoring max_relay_log_size and max_binlog_size.
>>>>
>>>> I first noticed this when my replication broke for about a week
>>>> and I needed to resync the slave. Instead of a few 1 gig log
>>>> files like were created on the master, the slave had over 14000
>>>> relay logs ranging in size from 200-2000 bytes.
>>>>
>>>> From the slave:
>>>>
>>>> +------------------------+----------------------+
>>>> | Variable_name | Value |
>>>> +------------------------+----------------------+
>>>> | max_binlog_size | 1073741824 |
>>>> | max_relay_log_size | 0 |
>>>> | version | 5.0.41-log |
>>>> +------------------------+----------------------+
>>>>
>>>> AFAIK there are no flush logs executing on the slave. I do get
>>>> one on the master every night as part of the backup script.
>>>>
>>>> TIA
>>> No takers?
>>
>> Is the slave log not starting a new file everytime it is started /
>> restarted?
>> Something in the docs somewhere if I remember well.
>>
>> Try a search for "binlog" "reset slave" on the mysql site, or
>> variations
>>
>> Richard.
>
> Nothing is restarting the slave threads, except for the one time I
> did it when I noticed that replication was broken and I had to fix
> it.
>
> The docs clearly lay out three things that cause a new relay log to
> be started ...
>
> . Each time the I/O thread starts.
> . When the logs are flushed;
> . When the size of the current relay log file becomes too large. The
> meaning of “too large” is determined as follows: If the value of
> max_relay_log_size is greater than 0, that is the maximum relay log
> file size. If the value of max_relay_log_size is 0, max_binlog_size
> determines the maximum relay log file size.
>
> I'm not encountering any of these thoughs

I dont know answers.... sorry, but
Are you sure? And how do you know?
Is the binlog the same thing as the relay log?

I have a replication setup since last month, and still find it a sort
of dark corner. Lots of repeated and copied info everywhere on the
web, but hardly any details...

Richard.

Richard wrote:
> I dont know answers.... sorry, but
> Are you sure? And how do you know?
> Is the binlog the same thing as the relay log?

Not exactly but it's similar. In fact the binary format is identical and
you can read the contens of relay logs with mysqlbinlog just like you
can with binlogs.

There are two threads on the slave that work with relay logs. The i/o
thread reads the binlog on the master and writes relay logs to the
slave. The SQL thread applies the SQL from the relay log to the slave
database and deletes it once it's applied.

> I have a replication setup since last month, and still find it a sort
> of dark corner. Lots of repeated and copied info everywhere on the
> web, but hardly any details...
>

I find the same to be true.

My best guess is that in order for the slave to be in sync with the
master, it's got to apply changes either every N seconds, or every N
commits. For the sql slave to apply a relay log, it needs to be handed
one by the i/o slave, so the i/o slave is probably breaking up the logs
based on time or commits. Again, just a WAG (wild a** guess).

Chuck <skilover_nospam@bluebottle.com> wrote:
> Richard wrote:
>
>> I have a replication setup since last month, and still find it a sort
>> of dark corner. Lots of repeated and copied info everywhere on the
>> web, but hardly any details...
>
> I find the same to be true.

Huh? It's all explained nicely in the manual.

http://dev.mysql.com/doc/refman/5.0/en/replication.html

what are you missing?

> My best guess is that in order for the slave to be in sync with the
> master, it's got to apply changes either every N seconds, or every N
> commits. For the sql slave to apply a relay log, it needs to be handed
> one by the i/o slave, so the i/o slave is probably breaking up the logs
> based on time or commits.

No. That's not how it works. The binary log (and relay log) is
structured in log events. In most cases a log event contains a
single SQL statement. The slave threads keep track of where they
are by offets into the file (position) where a log event starts.
Both slave threads can handle any number of log events in a
binlog/relay log.

There is one point missing in the list of events that make the
slave start a new relay log. That is when the master starts a new
binary log.

So if you see excessive relay log rotations, its most probably
someone excessively doing FLUSH LOGS on either the slave or
the master.


XL
--
Axel Schwenke, Support Engineer, MySQL AB

Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
MySQL User Forums: http://forums.mysql.com/

Axel Schwenke wrote:
> Chuck <skilover_nospam@bluebottle.com> wrote:
>> Richard wrote:
>>
>>> I have a replication setup since last month, and still find it a sort
>>> of dark corner. Lots of repeated and copied info everywhere on the
>>> web, but hardly any details...
>> I find the same to be true.
>
> Huh? It's all explained nicely in the manual.

Really?

>
> http://dev.mysql.com/doc/refman/5.0/en/replication.html
>
> what are you missing?

Nothing in that link or anywhere else in the manual explains why when my
binlogs are set to 1g and I get 1 or 2 a day, that I'm getting thousands
of relay logs < 2k.

>
>> My best guess is that in order for the slave to be in sync with the
>> master, it's got to apply changes either every N seconds, or every N
>> commits. For the sql slave to apply a relay log, it needs to be handed
>> one by the i/o slave, so the i/o slave is probably breaking up the logs
>> based on time or commits.
>
> No. That's not how it works. The binary log (and relay log) is
> structured in log events. In most cases a log event contains a
> single SQL statement. The slave threads keep track of where they
> are by offets into the file (position) where a log event starts.
> Both slave threads can handle any number of log events in a
> binlog/relay log.
>
> There is one point missing in the list of events that make the
> slave start a new relay log. That is when the master starts a new
> binary log.
>

Actually that is documented in the manual on the 2nd bullet point of
this link:

http://dev.mysql.com/doc/refman/5.0/en/slave-logs.html

However as I said in the OP, I get one log flush on the master per day -
when the daily backup runs. I could be misunderstanding that page if
it's talking about log flushes on the slave, but NOTHING is doing log
flushes on the slave. If something on the master (or slave) were doing
excessive log flushes, I'd be getting lots of binlogs too. But I'm not
seeing that.

Chuck <skilover_nospam@bluebottle.com> wrote:
> Axel Schwenke wrote:
>> Chuck <skilover_nospam@bluebottle.com> wrote:
>>> Richard wrote:
>>>
>>>> I have a replication setup since last month, and still find it a sort
>>>> of dark corner. Lots of repeated and copied info everywhere on the
>>>> web, but hardly any details...
>>> I find the same to be true.
>>
>> Huh? It's all explained nicely in the manual.
>
> Really?

Really!

>> http://dev.mysql.com/doc/refman/5.0/en/replication.html
>>
>> what are you missing?
>
> Nothing in that link or anywhere else in the manual explains why when my
> binlogs are set to 1g and I get 1 or 2 a day, that I'm getting thousands
> of relay logs < 2k.

Because it's not the normal behavior.

>> There is one point missing in the list of events that make the
>> slave start a new relay log. That is when the master starts a new
>> binary log.
>
> Actually that is documented in the manual on the 2nd bullet point of
> this link:
>
> http://dev.mysql.com/doc/refman/5.0/en/slave-logs.html

That one is about FLUSH LOGS on the slave.

What I meant: when the binary log on the master is rotated (for
whatever reason) - then the slave will rotate the relay log too.

> NOTHING is doing log flushes on the slave.

This is hard to believe. Can you turn on the general log on the
slave? This will show you what is going on.

Note there are two ways to get the logs rotated:

1. the SQL statement FLUSH LOGS
- will be shown as Command=Query, Argument=FLUSH LOGS

2. a Refresh command packet as sent by 'mysqladmin flush-logs'
- will be shown as Command=Refresh, Argument=<empty>


XL
--
Axel Schwenke, Support Engineer, MySQL AB

Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
MySQL User Forums: http://forums.mysql.com/

"Axel Schwenke" <axel.schwenke@gmx.de> wrote in message
news:2rdhc5-27g.ln1@xl.homelinux.org...
> Chuck <skilover_nospam@bluebottle.com> wrote:
>> Richard wrote:
>>
>>> I have a replication setup since last month, and still find it a
>>> sort
>>> of dark corner. Lots of repeated and copied info everywhere on the
>>> web, but hardly any details...
>>
>> I find the same to be true.
>
> Huh? It's all explained nicely in the manual.
>
> http://dev.mysql.com/doc/refman/5.0/en/replication.html
>
> what are you missing?

Hi,

mySQL replication seems (or is?) dependent on so many variables and
conditions, that it is really hard to get started.

The manual is good as a description of the process, but unreadable
because of all the remakrs and examples.

The manual is no good when you want to know what the message "could
not initialise master info structure" (or something like it, from
memory) means and how to deal with it.
Its also not good when replication is not working, and you wonder if
you maybe missed something, or if your config is wrong.

I was looking for real-world cases, a sort of how to, and
what-not-to-do. User experiences.
I have not found that on the net.
Many pages with : "I did it like this, and it just works".
Nothing on: I did this, got this, and still it doesnt work. Now what?

Richard.