From: sasha
Date: October 24 2000 3:27am
Subject: Re: Replication
List-Archive: http://lists.mysql.com/internals/47
Message-Id: <39F50185.8E8BCED2@mysql.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit


> There are querys that modify more than table ?

currently,

drop table tbl1, tbl2;

once 4.0 comes out, the first one will be delete from foo,bar where foo.id =
bar.id and foo.val = 'val';

then we may also add a similar multi-table update.

> There are surely no updates on the slave. We started developing on one DB
> (now Master) and
> later added the Slaves and redirected all selects to them.
> In the error-log it says...
> Error reading packet from server: Lost connection to MySQL server during
> query (0)
> ...and this every 60 seconds
> Error reading packet from server: bogus data in log event (0)

That is a bug, in fact, I have a pretty good idea which one, we fixed it in
3.23.26 - if the query is long enough and you have high load, the slave could
read a log event partially because of a mutex bug - the binlog dump thread was
not acquiring the binlog mutex when reading log events - so if a log event was
short enough to be written atomically, this was ok, but otherwise, the slave
would get confused, drop the connection, and then re-open it - by that time the
event in question was already fully in the log, so this was not fatal, just
performance degrading.

If 3.23.26 does not fix this, let us know - it would be nice if you had a test
case we can run, but if not, we can still try to figure something out. 3.23.26
at least has better diagnostic about partial fail log even reads, so it should
be easier to debug ( although we hope we won't have to do it).

> 
> At this point (during development) the DB is running with an average of 160
> querys/second, surely not the problem.
> There are about 2GB binlog/day at the moment. After we set the page online
> it will be twice to thrice this much.
> 
> ONE other thing I forgot, log-rotation.
> We do it actually with an cron-triggered sh-script but it would be more
> comfortable (secure) to do via mysql.

That is a great idea - I will look into intergrating this into MySQL internally.

> 
> /*********************************************************/
> #!/bin/sh
> 
> OLDPWD=`pwd`
> cd /mysql/dbs
> 
> INDEX_FILE=/mysql/dbs/oracle-bin.index
> TMP_FILE=/var/tmp/mysql_rotate.tmp
> umask 117
> 
> for i in `cat $INDEX_FILE`; do
>   LAST_BIN=$i
> done
> 
> echo "Aktuelles File: $LAST_BIN"
> FDATE=`date -r $INDEX_FILE +%d`
> 
> if [ $FDATE -ne `date +%d` ]; then
>   echo "I've to flush(Date)"
>   /mysql/bin/mysqladmin -umysql -pXXXXX flush-logs
> elif [ `ls -s $LAST_BIN | cut -d" " -f1` -gt 524288 ]; then
>   echo "I've to flush(Size)"
>   /mysql/bin/mysqladmin -umysql -pXXXXX flush-logs
> else
>  # Nach dem Flushen sollte man nicht gleich versuchen, das Index-File zu
> bearbeiten
>   echo "I don't have to flush ..."
>   echo " ... try to delete old files"
>   echo
>   echo "---> IndexFile (davor) ---------------------------------------->"
>   cat $INDEX_FILE
>   echo "---> ls -l ---------------------------------------------------->"
>   ls -l /mysql/dbs/oracle*
>   echo "--------------------------------------------------------------->"
>   echo
>   for i in `find . -name "oracle-bin.[0-9][0-9][0-9]"`; do
>     if [ `date +%Y%m%d%H` -gt `date -r $i +%Y%m%d%H` ]; then
> echo "Lösche: $i"
> grep -v "$i" $INDEX_FILE >$TMP_FILE
> chown mysql:mysql $TMP_FILE
> cp $TMP_FILE $INDEX_FILE
> chown mysql:mysql $INDEX_FILE
> rm -f $i
>         rm -f $TMP_FILE
>     else
>       echo "Halte: $i"
>     fi
>   done
>   echo
>   echo "---> IndexFile (danach) --------------------------------------->"
>   cat $INDEX_FILE
>   echo "---> ls -l ---------------------------------------------------->"
>   ls -l /mysql/dbs/oracle*
>   echo "--------------------------------------------------------------->"
>   echo
> fi
> 
> cd $OLDPWD
> /*********************************************************/
> 
> Regards,
> 
> Christian "Maverick" Rabe
> IT-Development
> wallstreet:online AG
> ------------------------------------------
> http://www.wallstreet-online.de
> Tel.:  03 37 01 5 29 16
> Fax.:  03 37 01 5 29 19
> 
> --
> MySQL Development Team
>    __  ___     ___ ____  __
>   /  |/  /_ __/ __/ __ \/ /   Sasha Pachev <sasha@stripped>
>  / /|_/ / // /\ \/ /_/ / /__  MySQL AB, http://www.mysql.com/
> /_/  /_/\_, /___/\___\_\___/  Provo, Utah, USA
>        <___/
> 
> ---------------------------------------------------------------------
> Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before
> posting. To request this thread, e-mail internals-thread45@stripped
> 
> To unsubscribe, send a message to the address shown in the
> List-Unsubscribe header of this message. If you cannot see it,
> e-mail internals-unsubscribe@stripped instead.

-- 
MySQL Development Team
   __  ___     ___ ____  __ 
  /  |/  /_ __/ __/ __ \/ /   Sasha Pachev <sasha@stripped>
 / /|_/ / // /\ \/ /_/ / /__  MySQL AB, http://www.mysql.com/
/_/  /_/\_, /___/\___\_\___/  Provo, Utah, USA
       <___/