Hi all,

on a replication architecture, with the same server, the same Mysql version
(4.1.21) and the same configuration, the same database.

I have a difference between two ibdata file size

innodb_data_file_path=ibdata1:2000M;ibdata2:2000M; ibdata3:2000M;ibdata4:2000M;ibdata5:2000M;ibdata6: 2000M;ibdata7:500M:autoextend

on the master :
-rw-rw---- 1 mysql mysql 2.0G Oct 30 11:40 ibdata1
-rw-rw---- 1 mysql mysql 2.0G Oct 30 11:39 ibdata2
-rw-rw---- 1 mysql mysql 2.0G Oct 30 11:39 ibdata3
-rw-rw---- 1 mysql mysql 2.0G Oct 30 11:39 ibdata4
-rw-rw---- 1 mysql mysql 2.0G Oct 30 11:39 ibdata5
-rw-rw---- 1 mysql mysql 2.0G Oct 30 11:36 ibdata6
-rw-rw---- 1 mysql mysql 22G Oct 30 11:40 ibdata7

on the slave

-rw-rw---- 1 mysql mysql 2.0G Oct 30 11:40 ibdata1
-rw-rw---- 1 mysql mysql 2.0G Oct 30 11:40 ibdata2
-rw-rw---- 1 mysql mysql 2.0G Oct 30 11:40 ibdata3
-rw-rw---- 1 mysql mysql 2.0G Oct 30 11:40 ibdata4
-rw-rw---- 1 mysql mysql 2.0G Oct 30 11:40 ibdata5
-rw-rw---- 1 mysql mysql 2.0G Oct 30 11:40 ibdata6
-rw-rw---- 1 mysql mysql 15G Oct 30 11:40 ibdata7

The difference is over 7Go !!!

Is there anybody who has got any explanation about this ???

Thanks all

One possible explanation (possibly not the only one): if you do a
massive update on the master, that transaction would need to create
many blocks of versioned data. If you roll that transaction back,
those blocks will be freed to be reused, but the datafiles won't
shrink.

Since that transaction wasn't commited, it won't be written to the
binary log, so it won't be executed and rolled back on the slave
(that's only true when all tables involved on a transaction are
transaction-safe tables).

--
Augusto Bott



On 10/30/07, Thomas Raso <thomas.raso@gmail.com> wrote:
> Hi all,
>
> on a replication architecture, with the same server, the same Mysql version
> (4.1.21) and the same configuration, the same database.
>
> I have a difference between two ibdata file size
>
> innodb_data_file_path=ibdata1:2000M;ibdata2:2000M; ibdata3:2000M;ibdata4:2000M;ibdata5:2000M;ibdata6: 2000M;ibdata7:500M:autoextend
>
> on the master :
> -rw-rw---- 1 mysql mysql 2.0G Oct 30 11:40 ibdata1
> -rw-rw---- 1 mysql mysql 2.0G Oct 30 11:39 ibdata2
> -rw-rw---- 1 mysql mysql 2.0G Oct 30 11:39 ibdata3
> -rw-rw---- 1 mysql mysql 2.0G Oct 30 11:39 ibdata4
> -rw-rw---- 1 mysql mysql 2.0G Oct 30 11:39 ibdata5
> -rw-rw---- 1 mysql mysql 2.0G Oct 30 11:36 ibdata6
> -rw-rw---- 1 mysql mysql 22G Oct 30 11:40 ibdata7
>
> on the slave
>
> -rw-rw---- 1 mysql mysql 2.0G Oct 30 11:40 ibdata1
> -rw-rw---- 1 mysql mysql 2.0G Oct 30 11:40 ibdata2
> -rw-rw---- 1 mysql mysql 2.0G Oct 30 11:40 ibdata3
> -rw-rw---- 1 mysql mysql 2.0G Oct 30 11:40 ibdata4
> -rw-rw---- 1 mysql mysql 2.0G Oct 30 11:40 ibdata5
> -rw-rw---- 1 mysql mysql 2.0G Oct 30 11:40 ibdata6
> -rw-rw---- 1 mysql mysql 15G Oct 30 11:40 ibdata7
>
> The difference is over 7Go !!!
>
> Is there anybody who has got any explanation about this ???
>
> Thanks all
>

Thanks a lot for this explanation,

yeah we are doing many massive update and insert in our databases.

--

Thomas Raso

2007/10/30, Augusto Bott <augusto@bott.com.br>:
>
> One possible explanation (possibly not the only one): if you do a
> massive update on the master, that transaction would need to create
> many blocks of versioned data. If you roll that transaction back,
> those blocks will be freed to be reused, but the datafiles won't
> shrink.
>
> Since that transaction wasn't commited, it won't be written to the
> binary log, so it won't be executed and rolled back on the slave
> (that's only true when all tables involved on a transaction are
> transaction-safe tables).
>
> --
> Augusto Bott
>
>
>
> On 10/30/07, Thomas Raso <thomas.raso@gmail.com> wrote:
> > Hi all,
> >
> > on a replication architecture, with the same server, the same Mysql
> version
> > (4.1.21) and the same configuration, the same database.
> >
> > I have a difference between two ibdata file size
> >
> >
> innodb_data_file_path=ibdata1:2000M;ibdata2:2000M; ibdata3:2000M;ibdata4:2000M;ibdata5:2000M;ibdata6: 2000M;ibdata7:500M:autoextend
> >
> > on the master :
> > -rw-rw---- 1 mysql mysql 2.0G Oct 30 11:40 ibdata1
> > -rw-rw---- 1 mysql mysql 2.0G Oct 30 11:39 ibdata2
> > -rw-rw---- 1 mysql mysql 2.0G Oct 30 11:39 ibdata3
> > -rw-rw---- 1 mysql mysql 2.0G Oct 30 11:39 ibdata4
> > -rw-rw---- 1 mysql mysql 2.0G Oct 30 11:39 ibdata5
> > -rw-rw---- 1 mysql mysql 2.0G Oct 30 11:36 ibdata6
> > -rw-rw---- 1 mysql mysql 22G Oct 30 11:40 ibdata7
> >
> > on the slave
> >
> > -rw-rw---- 1 mysql mysql 2.0G Oct 30 11:40 ibdata1
> > -rw-rw---- 1 mysql mysql 2.0G Oct 30 11:40 ibdata2
> > -rw-rw---- 1 mysql mysql 2.0G Oct 30 11:40 ibdata3
> > -rw-rw---- 1 mysql mysql 2.0G Oct 30 11:40 ibdata4
> > -rw-rw---- 1 mysql mysql 2.0G Oct 30 11:40 ibdata5
> > -rw-rw---- 1 mysql mysql 2.0G Oct 30 11:40 ibdata6
> > -rw-rw---- 1 mysql mysql 15G Oct 30 11:40 ibdata7
> >
> > The difference is over 7Go !!!
> >
> > Is there anybody who has got any explanation about this ???
> >
> > Thanks all
> >
>