Bug in max_binlog_size ?

------=_NextPart_000_000C_01C30FCE.59EE1280
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Hi,

I'm running Win2000 Server (fully patched), MySQL 4.0.12.

In my.ini I have set=20
set-variable =3D max_binlog_size=3D3M

The binary files output today is ...
10.350 KB
9.054 KB
4.129 KB
3.074 KB
3.089 KB
4.423 KB
2.814 KB (last)

In my mind they all ought to be apx. 3.000 KB.

Best regards
Peter=20
Copnehgane
denmark

------=_NextPart_000_000C_01C30FCE.59EE1280--

Re: Bug in max_binlog_size ?

> Hi,
>
> I'm running Win2000 Server (fully patched), MySQL 4.0.12.
>
> In my.ini I have set
> set-variable = max_binlog_size=3M
>
> The binary files output today is ...
> 10.350 KB
> 9.054 KB
> 4.129 KB
> 3.074 KB
> 3.089 KB
> 4.423 KB
> 2.814 KB (last)
>
> In my mind they all ought to be apx. 3.000 KB.

Hi,

The only normal reason for such sizes which do not respect max_binlog_size
is that you are doing big transactions. A transaction is never cut (if the BEGIN
is written to a binary log, this binary log will never be rotated before the
COMMIT is written to it). (Rotation= close the binlog and start a new one).

Example: if your binlog is 2.5MB. It is < 3MB, so we don't rotate it.
Then you commit a transaction which contained a lot of queries, so many
that this writes 2MB to the binlog. Then your binlog is 4.5MB. Oh, that's
more than 3MB, so we rotate the binlog. And you have a 4.5MB binlog.

If big transactions are not your case, this is probably a bug and I'm
interested in getting a zipped (gzipped, bzipped) version of your
4.423 KB binlog above. In that case please put this compressed file here
ftp://support.mysql.com/pub/mysql/secret/.

--
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



--
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: Bug in max_binlog_size ?

Hi,

I have checked my update-sequence one-by-one, and found the reason - so I
call of this issue about max_binlog_size - it seams MySQL shift as it
should, except for big transactions (see later this email).

The issue raises because of import of ASCII files ...
LOAD DATA INFILE 'X:/ftpfolder/TDdaglig/fh999999/999999.txt' INTO TABLE
impprice FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\r\n'

This is one transaction, and will create one bin-file when having ex. 25.000
records imported.

On the other hand, it should be possible to exclude SQL-instructions like
you can exclude databases and tabels.

Instructions like LOAD DATA INFILE is importing data for further processing
in the database,
and by that the imported data are just "unnescessary overhead" in the
bin-log files - this is my case, and I could imagine majority of importet
ASCII-file data having the same case.

BUT - this issue came up (when I recall back) because the slave refused to
accept bigger bin-log files than 3MB, because they where bigger than defined
at master-site (max_binlog_size=3MB - I dont have original error-text from
slave-site) - so bacicly this is creating a dead-lock situation, where you
have to accept very, very big bin-log files at master-site, for slave-site
to accept the bin-log-size - but you can always risk a bigger transaction
exceed any max_binlog_size set where the slave-site wil reject accepting the
bin-log-files - and you cannot keep the bin-log-files lower because there is
only one max_binlog_size-level.
From my experience on 3.23.49 several, smaller bin-log-files are preferable
when ex. replicating over ADSL, or
modem from master to slave - and by that, complicating the replication
because you must replicate a very big bin-file "error-free" before updating
of slave can take place.

Hope You can follow me.

So probably the slave-site needs to be less rejective, specially if the
slave-site can see there are more bin-log files then just this to-big-one.


Best regards
Peter

----- Original Message -----
From: "Guilhem Bichot"
To:
Cc:
Sent: Thursday, May 01, 2003 10:31 PM
Subject: Re: Bug in max_binlog_size ?


> > Hi,
> >
> > I'm running Win2000 Server (fully patched), MySQL 4.0.12.
> >
> > In my.ini I have set
> > set-variable = max_binlog_size=3M
> >
> > The binary files output today is ...
> > 10.350 KB
> > 9.054 KB
> > 4.129 KB
> > 3.074 KB
> > 3.089 KB
> > 4.423 KB
> > 2.814 KB (last)
> >
> > In my mind they all ought to be apx. 3.000 KB.
>
> Hi,
>
> The only normal reason for such sizes which do not respect max_binlog_size
> is that you are doing big transactions. A transaction is never cut (if the
BEGIN
> is written to a binary log, this binary log will never be rotated before
the
> COMMIT is written to it). (Rotation= close the binlog and start a new
one).
>
> Example: if your binlog is 2.5MB. It is < 3MB, so we don't rotate it.
> Then you commit a transaction which contained a lot of queries, so many
> that this writes 2MB to the binlog. Then your binlog is 4.5MB. Oh, that's
> more than 3MB, so we rotate the binlog. And you have a 4.5MB binlog.
>
> If big transactions are not your case, this is probably a bug and I'm
> interested in getting a zipped (gzipped, bzipped) version of your
> 4.423 KB binlog above. In that case please put this compressed file here
> ftp://support.mysql.com/pub/mysql/secret/.
>
> --
> 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
>
>
>


--
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: Bug in max_binlog_size ?

PeterWR wrote:
> The issue raises because of import of ASCII files ...
> LOAD DATA INFILE 'X:/ftpfolder/TDdaglig/fh999999/999999.txt' INTO TABLE
> impprice FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\r\n'
>
> This is one transaction, and will create one bin-file when having ex. 25.000
> records imported.
>
> On the other hand, it should be possible to exclude SQL-instructions like
> you can exclude databases and tabels.
>
> Instructions like LOAD DATA INFILE is importing data for further processing
> in the database,
> and by that the imported data are just "unnescessary overhead" in the
> bin-log files - this is my case, and I could imagine majority of importet
> ASCII-file data having the same case.

You say you don't need the LOAD be in the binlog. Easy:
SET SQL_LOG_BIN=0;
LOAD DATA INFILE... ;
SET SQL_LOG_BIN=1;
and you're done.
But some users may need to write this LOAD to the binlog. We have customers
who use LOAD DATA INFILE every second on the master (to load the data from
an Apache log), for them LOAD DATA is not an initial import at all, it's just
a normal query like INSERT (except that it's much faster than INSERT). So
in that case LOAD must go into the binlog, to be replicated on the slave.

> BUT - this issue came up (when I recall back) because the slave refused to
> accept bigger bin-log files than 3MB, because they where bigger than defined
> at master-site (max_binlog_size=3MB - I dont have original error-text from
> slave-site)

Strange... If you can reproduce it I'm interested in a testcase.
I think your problem could be with max_allowed_packet rather than max_binlog_size.
You must have a max_allowed_packet on the slave greater or equal to the
max_allowed_packet on the master, otherwise the master will send packets to
the slave which are too big for the slave.

- so bacicly this is creating a dead-lock situation, where you
> have to accept very, very big bin-log files at master-site, for slave-site
> to accept the bin-log-size - but you can always risk a bigger transaction
> exceed any max_binlog_size set where the slave-site wil reject accepting the
> bin-log-files - and you cannot keep the bin-log-files lower because there is
> only one max_binlog_size-level.
>>From my experience on 3.23.49 several, smaller bin-log-files are preferable
> when ex. replicating over ADSL, or
> modem from master to slave - and by that, complicating the replication
> because you must replicate a very big bin-file "error-free" before updating
> of slave can take place.
>
> Hope You can follow me.

uh... Not really.
If you have a testcase (a repeatable sequence of options and commands which
make the slave reject the master's output), it's welcome.

--
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: Bug in max_binlog_size ?

PeterWR wrote:
>>From my experience on 3.23.49 several, smaller bin-log-files are preferable
> when ex. replicating over ADSL, or
> modem from master to slave

The binlog size should have no influence on the speed of the transfer.
A binlog is not sent as a whole to the slave. It is sent event by event.
Having 10 small binlogs is probably slower than having 1 big binlog
because the master loses time closing/opening the 10 binlogs.



--
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