Mysql: (re)create a slave online faster using LVM snapshot and Netcat

Summary: 

Flush tables & lock , create snapshot for datas AND logfiles

Prerequisites:

-create or replace /etc/my.cnf on slave machine
-make sure innodb_log_file_size is same on both sides

Flush, lock and create snapshot:

> FLUSH TABLES WITH READ LOCK;

> show master status;

+----------------------------+-----------+--------------+------------------+
| File                       | Position  | Binlog_Do_DB | Binlog_Ignore_DB |
+----------------------------+-----------+--------------+------------------+
| mysql-toto50-bin.003912    | 653187465 |              |                  |
+----------------------------+-----------+--------------+------------------+
(write this down)

> system sudo  /usr/sbin/lvcreate  -L 100G  -n snap1 -s /dev/mysql_vg2/mysql_vg2-mysql_lv 
(/dev/mysql_vg2/mysql_vg2-mysql_lv is the volume where I keep mysql data AND log )

> UNLOCK TABLES;
> quit;



Copy the snapshot via NETCAT

Netcat is much faster than FTP, SCP, SFTP, Samba, NFS...
Exception: If partial copy : use rsync
It has to be on a trusted network, but so shall be the link between slave and master!

# mount /dev/mysql_vg2/snap1 /var/mnt


SLAVE:
# mkdir /var/lib/mysql/new_data
# cd /var/lib/mysql/new_data  && nc -l 64738 | tar -xvf -

(When I have enough room, I usually keep the old slave directory just in case)
(alternative: rsync -arv ... )

MASTER:
# cd /var/mnt && nice tar -cf - data  | nice nc toto51 64738

Release the snapshot

I like to check the percentage used before:
# /usr/sbin/lvs
  LV                 VG         Attr   LSize   Origin             Snap%  Move
...
  mysql_vg2-mysql_lv mysql_vg2  owi-ao   1.00T                                 
  snap1              mysql_vg2  swi-a- 150.00G mysql_vg2-mysql_lv   1.22  


# umount /var/mnt
# /usr/sbin/lvremove /dev/mysql_vg2/snap1

Start the mysql slave instance:

#make sure the slave wont start yet
set: SKIP SLAVE START in my.cf


#move files in proper place (now I delete the old version)

#check permissions (chown, chmod, SElinux chattr... )

#start slave instance

service mysql start
or /etc/init.d/mysql start

#Define the master

> CHANGE MASTER TO MASTER_HOST='toto50',MASTER_USER='replicator', MASTER_PASSWORD='****', MASTER_LOG_FILE='mysql-toto50-bin.003912', MASTER_LOG_POS=653187465;


#start slave process

start slave

show slave status\G;

#do a test insert on master to check it is replicated...

#remove SKIP SLAVE START in case there is a reboot

Remarks:

With this method, the slave instance starts with a crash recovery
I sometimes notice this in the log:

140110  9:03:30 InnoDB: highest supported file format is Barracuda.
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
140110  9:03:31  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
InnoDB: Last MySQL binlog file position 0 653173962, file name /var/log/mysql/mysql-toto50-bin.003912
InnoDB: We removed now the InnoDB internal data dictionary entry
InnoDB: of table 140110  9:03:52 [Warning] Invalid (old?) table or database name '#sql1308_3a493_267e'
"tmp".<result 2 when explaining filename '#sql1308_3a493_267e'>.
InnoDB: We removed now the InnoDB internal data dictionary entry
InnoDB: of table 140110  9:03:52 [Warning] Invalid (old?) table or database name '#sql1308_3dbbf_1fb'
"tmp".<result 2 when explaining filename '#sql1308_3dbbf_1fb'>
....

These #sql... are temp tables so it's no big deal



Extra trick:

When I do it too quickly on demo systems, I sometimes forgot to write down the result of 'SHOW MASTER STATUS'
Never mind, on the mounted snapshot volume I can simply check which one was the latest binlog:

$ ls -ltr | tail
..
-rw-rw---- 1 mysql      mysql 1161283517 Oct 31 06:08 mysql-toto50-bin.003288
-rw-rw---- 1 mysql      mysql  261972409 Oct 31 06:59 mysql-toto50-bin.003289    <--HERE
-rw-r-x--- 1 mysql      mysql  268435456 Oct 31 06:59 ib_logfile1
-rw-r-x--- 1 mysql      mysql  268435456 Oct 31 06:59 ib_logfile0

And inside this binlog I can look up for the latest entry:

$ mysqlbinlog mysql-toto50-bin.003289 | tail
eGtTVBcyAAAAbgAAAJ5hnQ8AAKc4BwAAAAEABP/wCmxvY2tlZEZsb3cRaGVhcnRiZWF0LXdhdGNo
ZXIdAGNscHJpd3M1MC5wcml2LmRlc2phcmRpbnMuZGV2EzIwMTQtMTAtMzEgMDY6NTk6MDQ=
'/*!*/;
# at 261972382
#141031  6:59:04 server id 50  end_log_pos 261972409    Xid = 2164008837
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

Oracle: partitioning a huge table _online_

My customer has an application in production 24/24 with tables getting huge (billions of rows... )  we'd like to partition it to compress and move older data.

Not a problem -as long as we have disk space. With Oracle 10g/11g we can partition it online, without any interruption.

The package to use is DBMS_REDEFINITION, and the basic idea is:
-Create a new table how you like it (partitionned... )
-This table becomes a materialized view and is synchronized with the existing table (need double space!)
-Add indexes etc..
-When ready: Lock shortly the table while it does a last resync and switch the segments in the data dictionary

The segments formerly belonging to the MV now belongs to the new table, and it was completely transparent.

( I think in Oracle 12c, this is getting easier with a single command. -will check)



-- In this example we have a table: BIGAPP1.BIGTABLE


-- create the new table as we want it  (this will be transormed to a Materialized View)
drop table BIGAPP1.BIGTABLE_TMPPARTNING ;

create table BIGAPP1.BIGTABLE_TMPPARTNING
PARTITION BY RANGE (TIME_STAMP_UTC)
( 
  PARTITION P2010 VALUES LESS THAN (TO_DATE('2011-01-01','YYYY-MM-DD' )) TABLESPACE TS_DATA110,
  PARTITION P2011 VALUES LESS THAN (TO_DATE('2012-01-01','YYYY-MM-DD' )) TABLESPACE TS_DATA110,
  PARTITION P2012 VALUES LESS THAN (TO_DATE('2013-01-01','YYYY-MM-DD' )) TABLESPACE TS_DATA110,
  PARTITION P2013 VALUES LESS THAN (TO_DATE('2014-01-01','YYYY-MM-DD' )) TABLESPACE TS_DATA110,
  PARTITION P2014 VALUES LESS THAN (TO_DATE('2015-01-01','YYYY-MM-DD' )) TABLESPACE TS_DATA110
) as select * from BIGAPP1.BIGTABLE where 0=1 ;


-- call dbms_redefintion
-- there are two methods, by PK or by ROWID which we had to use here
-- ref: Admin guide / chapter 15 Managing Tables / Redefining Tables Online

set serveroutput on;
exec dbms_output.put_line('check');

BEGIN
DBMS_REDEFINITION.CAN_REDEF_TABLE(
   uname        => 'BIGAPP1',
   tname        => 'BIGTABLE',
   options_flag => DBMS_REDEFINITION.CONS_USE_ROWID);
END;
/

-- The following will actually copy the data to the materialized view, and add a temporary index (because of USE_ROWIS)
-- on the (slow) test system it took 25 minutes for 11M rows
BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE('BIGAPP1', 'BIGTABLE','BIGTABLE_TMPPARTNING',
       null, dbms_redefinition.cons_use_rowid);
END;
/

--  now our  table has become a MV, but has only been synced once (an INSERT at this point is not copied )
-- now we may create the indexes

-- local index
CREATE INDEX BIGAPP1.IX_BIGTABLE_AUDOBJID ON BIGAPP1.BIGTABLE_TMPPARTNING(SURV_OBJ_ID) LOCAL
( PARTITION P2010  TABLESPACE TS_DATA110, 
  PARTITION P2011  TABLESPACE TS_DATA110, 
  PARTITION P2012  TABLESPACE TS_DATA110, 
  PARTITION P2013  TABLESPACE TS_DATA110, 
  PARTITION P2014  TABLESPACE TS_DATA110 );
-- 3 minutes

...

-- a global index because it is unique and does not contain the partition key
-- we partition it with another range

CREATE UNIQUE INDEX BIGAPP1.IX_BIGTABLE_OBJID ON BIGAPP1.BIGTABLE_TMPPARTNING(OB_ID) GLOBAL PARTITION BY RANGE (OB_ID)
( 
  PARTITION P10M VALUES LESS THAN (10000000) TABLESPACE TS_DATA110,
  PARTITION P20M VALUES LESS THAN (20000000) TABLESPACE TS_DATA110,
  PARTITION P30M VALUES LESS THAN (30000000) TABLESPACE TS_DATA110
  PARTITION PMAX VALUES LESS THAN (MAXVALUE) TABLESPACE TS_DATA110
) ;


--process grants, triggers, constraints and privileges
-- here we do not set the 3r param to CONS_ORIG_PARAMS, because we re-creates the index manually

DECLARE
num_errors PLS_INTEGER;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('BIGAPP1', 'BIGTABLE','BIGTABLE_TMPPARTNING', 
   0, TRUE, TRUE, TRUE, TRUE, num_errors);
END;
-- 1 minute

--We can check for errors here, typically: already existing constraints
select object_name, base_table_name, ddl_txt from    DBA_REDEFINITION_ERRORS;

-- Optional re-synchro : I prefer to do that since it reduces the last operation, which is the only one doing some locking

BEGIN
DBMS_REDEFINITION.SYNC_INTERIM_TABLE('BIGAPP1', 'BIGTABLE','BIGTABLE_TMPPARTNING');
END;
/

-- And right after this, call the final step
BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE('BIGAPP1', 'BIGTABLE','BIGTABLE_TMPPARTNING');
END;
/

-- 20 sec

--this final step did a lock, a final sync and switched the segments references in the data dictionary
-- BIGTABLE is now partitioned and  BIGTABLE_TMPPARTNING is actually pointing to the old table (including its data: we have a copy)

-- We may keep a little the older version just to verify everything is OK

-- Verify we do have data in different partitions now:

select count(*) from BIGAPP1.BIGTABLE partition (P2010);
select count(*) from BIGAPP1.BIGTABLE partition (P2011);
select count(*) from BIGAPP1.BIGTABLE partition (P2012);
select count(*) from BIGAPP1.BIGTABLE partition (P2013);


-- OK DONE!  




References:
Admin Guide - redefinition example
This example demonstrates redefining a single partition. It moves the oldest partition of a range-partitioned sales table to a tablespace named TBS_LOW_FREQ

Generate stuff with SQL 'connect by'

'connect by' was added in Oracle to build hierarchical queries, but is really usefull to generate STUFF.
(I think I first saw this usage from Johnathan Lewis... )

SQL> select rownum from dual connect by level <= 4;
         1
         2
         3
         4

Create a huge table:

With the help of RPAD:

SQL> select rpad('A',50,'B') from dual;
ABBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB

We can create quickly a big table with data:

SQL> create table testbig as
             select rownum as n,  
                    rpad('A',50,'B') as txt 
             from dual connect by level <= 100000;


Somewhat more varied with modulo:

create table testbig2 as
  select rownum as n,  
         mod(rownum,100) as m, 
         rpad('A',50,'B') as txt 
  from dual connect by level <= 10000;



Generate days:
SQL> select TO_DATE('2012-01-01','YYYY-MM-DD')+rownum from dual connect  by level <=3;
2012-01-02
2012-01-03
2012-01-04




Generate months:
SQL> select ADD_MONTHS( TO_DATE('2012-01-01','YYYY-MM-DD'),rownum) from dual connect  by level <=3;
2012-02-01
2012-03-01
2012-04-01


Generate PARTITION clauses...



SQL> set pagesize 0
SQL> select 'PARTITION P'
||TO_CHAR(ADD_MONTHS( TO_DATE('2012-01-01','YYYY-MM-DD'),rownum),'YYYYMM')
||' VALUES LESS THAN (TO_DATE('''||TO_CHAR(ADD_MONTHS( TO_DATE('2011-01-01','YYYY-MM-DD'),rownum),'YYYYMMDD') ||''',''YYYYMMDD'')),'
from dual connect  by level <=3; 
PARTITION P201202 VALUES LESS THAN (TO_DATE('20110201','YYYYMMDD')),
PARTITION P201203 VALUES LESS THAN (TO_DATE('20110301','YYYYMMDD')),
PARTITION P201204 VALUES LESS THAN (TO_DATE('20110401','YYYYMMDD')),





Sadly I know no equivalent in MySQL,
(discussed here: stackoverflow.com/questions/701444 )


Of course in bash you can also generate things like so:

$ for i in {1..5}
> do
> echo PARTITION$i
> done
PARTITION1
PARTITION2
PARTITION3
PARTITION4

But I don't know an easy way to process dates though...

MySQL SHOW SLAVE STATUS Explained

Sometimes I don't have to deal with MySQL slaves for months and again I forgot which entry means what.

Here is a SHOW SLAVE STATUS explained  while catching up (I just restarted the slave after an error)




On the slave:
10:24 [vega]:> show slave status\G
...
      Master_Log_File: vega20-bin.000740  just current log and pos on the master
  Read_Master_Log_Pos: 3396922             == show master status
                                           (sometimes takes time to be refreshed)

       Relay_Log_File: vega21-relay-bin.000074 relay file on the slave we're
                                               currently applying

        Relay_Log_Pos: 274423937          position in bytes in the relay file.

Relay_Master_Log_File: vega20-bin.000727  corresponding log file on the master to
                                          what we're applying 
                                          (727 -> 740  is left to be done)
...
  Exec_Master_Log_Pos: 274423781          position on master log corresponding to
                                          what we're applying
...
Seconds_Behind_Master: 110997             estimated time to complete applying,
                                          here 727->740
Files on slave:
        219 Sep 10 04:08 vega21-relay-bin.000076
     510344 Sep 10 04:08 vega21-relay-bin.000075
 1073741921 Sep 10 04:08 vega21-relay-bin.000074 <- applying this one
     193266 Sep 10 07:18 vega21-relay-bin.000079  (a bit later, are deleted)
...
  196984797 Sep 11 09:03 vega21-relay-bin.000112
    3397078 Sep 11 10:24 vega21-relay-bin.000114 <- last redo info we copied from the master (data from master's log is copied to a slave relay log. Size may not be same as master ex:if we stop/start etc..)

Files on the master:
 1073904414 Sep  7 09:59 vega20-bin.000720
 1073774868 Sep 10 02:21 vega20-bin.000721
...
 1074251834 Sep 10 04:07 vega20-bin.000727<- applying data which is here 
 1073934723 Sep 10 07:17 vega20-bin.000728  (copied on slave in relay log 74)
 1073993761 Sep 10 07:27 vega20-bin.000729
...
 1091352292 Sep 11 09:02 vega20-bin.000739
    3396922 Sep 11 10:23 vega20-bin.000740<- current log written by the master




Status on the master:

10:24 [(none)]:> show master status;
+----------------------------+----------+--------------+------------------+
| File                       | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+----------------------------+----------+--------------+------------------+
| mysql-clappdb50-bin.000740 |  3396922 |              |                  |
+----------------------------+----------+--------------+------------------+



Other notes:

11:00 [vega]:> show processlist;
+--+-----------+----+----+-------+------+--------------------------------+------+
|Id|User       |Host|db  |Command|Time  |State                           |Info  |
+--+-----------+----+----+-------+------+--------------------------------+------+
| 5|system user|    |NULL|Connect|  6979|Waiting for master to send event|NULL  |
|69|system user|    |NULL|Connect|111664|Reading event from the relay log|NULL  |

Waiting for master to send event: currently the Master isn't writing much, so nothing to send to the slave relay log

Reading event from the relay log: means we're reading the relay file. If this is seen often, it means we're spending more time reading than applying... (slow I/O!)

Sometimes we see the action replicated on the slave:

|97|system user |    |STOCKS|Connect|101289| NULL      |TRUNCATE TABLE `TOTA01`|

simulating a slow drive

I want to simulate a slow drive, to better grasp the symptoms of a database suffering from a slow disk.

Until now I used to follow up on suspicions by testing with  IOzone or sysbench/fileio , but:

• This is sometimes difficult to do if the sysadmin is not me, and will not/can not cooperate
• This is imperfect because we would typically do such tests at night when the SAN which I suspect is less used ;-)

I could use a SD card or something, but most of my test setups are on VM, I would prefer a software solution.
I almost started to write my own slow FUSE filesystem, when I finally found a nice trick from this post:
http://askubuntu.com/questions/13590/can-i-simulate-a-slow-hard-drive

Use nbd, the Network Block Device, and then rate limit access to it using say trickle.
sudo apt-get install nbd-client nbd-server trickle

Knowing neither trickle nor nbd, I tested each  before doing the setup:

1) Trickle test

Trickle will limit the network speed of a command:

trickle -u <upload speed> -d <download speed> command

Example:$ wget http://192.168.0.6/videos2007/CIMG0500-small.avi

=> 11M/s

$ trickle -d 20 wget http://192.168.0.6/videos2007/CIMG0500-small.avi

=> 22 K/s




2) NBD test

NBD stands for Network Block Device. I understand it to be a simple way to use a block device from a remote machine (simpler than drbd or iscsi targets/initiators implementations)

Blocks may be files or partitions, logical volumes... Let's try to create a file :

dd if=/dev/zero of=/home/phil/apps/NBD/nbd-drive bs=1024 count=10240

Then run the nbd server on that file:

nbd-server 9500 /home/phil/apps/NBD/nbd-drive 

It seems to launch a daemon 'nbd-server', which do listen on 9500:


$ netstat -plnt
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name
...          
tcp        0      0 0.0.0.0:9500            0.0.0.0:*               LISTEN      2195/nbd-server 



Let's try to connect the client:

sudo nbd-client localhost 9500 /dev/nbd0

Seems to launch a daemon as well:
root      2276     1  0 07:04 ?        00:00:00 nbd-client localhost 9500 /dev/nbd0
root      2277     2  0 07:04 ?        00:00:00 [nbd0]

We can now format this device and mount it:
sudo mkfs /dev/nbd0 
sudo mount /dev/nbd0 /mnt

$ df -h
Filesystem            Size  Used Avail Use% Mounted on
...
/dev/nbd0             9.7M   92K  9.1M   1% /mnt

The test is OK. Let's umount and release:

sudo umount /mnt

sudo nbd-client -d /dev/nbd0

killall nbd-server

3) Trickle + NBD to make a slow drive

I succeeded by running the server through trickle this way:

$ trickle -d 20 -u 20 nbd-server 9500 /home/phil/apps/NBD/nbd-drive bs=1024 count=10240

trickle: Could not reach trickled, working independently: No such file or directory

Then started the client again with:

sudo nbd-client localhost 9500 /dev/nbd0

And indeed this block device was really slow :

$ sudo dd if=/dev/nbd0 of=/dev/null bs=65536 skip=100 count=10

10+0 records in

10+0 records out

655360 bytes (655 kB) copied, 28.5783 s, 22.9 kB/s

Finally I wrote that script to make the drive ready when I need it:

$ cat mountslowdrive.sh 

#!/bin/bash

FILE_OR_PARTITION=/home/phil/apps/NBD/nbd-drive

MOUNTPOINT=/home/phil/apps/NBD/mnt

if [ $# -eq 0 ] ; then

  echo making/mounting slow drive

  trickle -d 20 -u 20 nbd-server 9500 $FILE_OR_PARTITION bs=1024 count=10240

  sudo nbd-client localhost 9500 /dev/nbd0

  sudo mount /dev/nbd0 $MOUNTPOINT

else

  if [ \( $# -eq 1 \) -a \(  $1 == "-u" \) ]; then

    echo unmountint slow drive

    sudo umount $MOUNTPOINT

    sudo nbd-client -d /dev/nbd0

    killall nbd-server

  else

    echo  USAGE: $0 [-u]

    echo         -u : umount the slow drive

  fi

fi

Todo:
Use the -l or /etc/nbd_server.allow option of the server to restrict the clients (Otherwise any client on the network can use the block device... )

proving bad CPU

Acting as DBA today I experience bad CPU performance it seems
The Oracle database is running on a Oracle/Sparc virtualization system.

AWR/Statspack shows:

DB CPU		11,825		87.51
log file sync	21,933	1,292	59	9.56	Commit
db file parallel read	16,277	268	16	1.98	User I/O

But looking at the top "SQL by CPU Time" I see no demanding statement,
and all seem to be using a lot of CPU evenly.

Working on the system everything is slow, for instance 'autotrace' and 'explain plan'

I have a heavy suspicion it's the system's fault. Maybe some other VM on the machine is sucking all CPU
I reported to the syadmin team, but I'd like proofs.

I thought about writing a PL/SQL CPU-intensive job, but it wont help with the system guys. It maybe Oracle's fault for all they know.

So I quickly wrote a BASH prime computation script, and compared results to an old PC at home

$ cat p.sh
#!/bin/bash

if [ -z "$1" ]; then
  echo usage: $0 MAXNUM; exit 1
fi
nmax=$1

primes=(2)
n=3

while [ $n -le $nmax ]; do
  #echo verifier $n
  isprime=1
  for d in ${primes[*]} ; do
        #echo check div par $d
        r=$(( $n % $d ))
        if [ $r -eq 0 ]
        then
          isprime=0
          #echo No: $d div $n
          break
        fi
  done

  if [ $isprime -eq 1 ]; then
    primes=(${primes[*]} $n)   #primes+=($n)    dont work on older bash(solaris 10)
  fi

  #echo primes = ${primes[*]}
  n=$(( $n + 2 ))
done

echo primes = ${primes[*]}




Results are indeed bad:

customer_system$ time ./p.sh 2000
primes = 2 3 5 7 11 ...
real    0m9.376s
user    0m8.496s
sys     0m1.182s

my_old_pc$ time ./p.sh 2000
primes = 2 3 5 7 11 ...
real    0m2.594s
user    0m2.348s
sys     0m0.226s

A VM on a 5 years old PC is 3x faster ! wow!

Then I thought: These are T-series Sparc system. I imagine they might tell me this could be normal, it is designed for parallel-intensive stuff (Still, 5y old PC... )
But to be sure I also tried some parallel work:

$ cat para.sh
#!/bin/bash

if [ -z "$2" ]; then
  echo usage: $0 parallel_degree primemax; exit 1
fi

max=$1
i=0

while [ $i -le $max ]; do
  ./p.sh $2 &

  i=$(( $i + 2 ))
done
wait
echo fin

Results:

my_old_pc$ time ./para.sh 10 500
primes = 2 3 5 7 ...
primes = 2 3 5 7 ...
primes = 2 3 5 7 ...
...
fin
real    0m1.487s
user    0m1.288s
sys     0m0.184s


customer_system$ time ./para.sh 10 500
primes = 2 3 5 7 ...
primes = 2 3 5 7 ...primes = 2 3 5 7 ...
...
fin
real    0m2.427s
user    0m5.027s
sys     0m4.148s



Less bad, but still BAD. There is definitely weak a CPU on this system causing these bad DB performances.

mySQL fillup fake data & autocommit

Playing around with MySQL performance, I had to fill up tables with test data

As with Oracle, how much you write before a commit has huge effects on performance. If you don't pay attention it takes AGES:

delimiter //

create table tbad (n integer, n2 integer, t varchar(20000), primary key(n))//

create procedure fill1 ()
begin
    declare x integer;
    set x=0;
    while x<50000 do
        insert into tbad values(x,x,'bobobobobobobobobo');
        set x=x+1;
    end while;
end;

//

mysql> call fill1 //
Query OK, 1 row affected (31 min 39.88 sec)

OUCH! 31 minute for just 50K rows!

That's because MySQL has autocommit on by default

Solution is to add something like:

begin
    declare x integer;
    SET AUTOCOMMIT=0;
    set x=0;
    while x<50000 do
        insert into tbad values(x,x,'bobobobobobobobobo');
        set x=x+1;
        if mod(x,1000) = 0 THEN
            commit;
        end if;
    end while;

    commit;
end;

And this is of course much faster
Query OK, 0 rows affected (3.49 sec)

I often commit about every 1000, but then how much is good enough? I did a test, by writing a procedure to create a test table and fill it up with varying commit bulk sizes.

delimiter //

# this is a result table
drop table if exists zztresults;
create table zztresults(testno int auto_increment primary key,nbrows int,commit_every int, time int);
//


drop procedure testperf1//

create procedure testperf1 (nbrows integer, commit_every integer)
begin
    declare x integer;
    declare t_start timestamp;
    declare totaltime integer;

    drop table if exists zztperf1;
    create table zztperf1 (n integer, n1 integer, v varchar(2000));

    select now() into t_start;

    SET AUTOCOMMIT=0;
    set x=0;
    while x<nbrows do
        insert into zztperf1 values(x,x,'bobobobobobobobobo');
        set x=x+1; 
        if mod(x,commit_every) = 0 THEN
            commit;
          end if;
    end while;
    commit;

    select (now()-t_start) into totaltime;
insert into zztresults values (null,nbrows,commit_every,totaltime);
select totaltime;
end;
//call testperf1(1024000,1)//
call testperf1(1024000,10)//
call testperf1(1024000,50)//
call testperf1(1024000,100)//
call testperf1(1024000,500)//
call testperf1(1024000,1000)//
call testperf1(1024000,5000)//
call testperf1(1024000,10000)//
call testperf1(1024000,50000)//



mysql> select * from zztresults //
+--------+---------+--------------+--------+
| testno | nbrows  | commit_every | time   |
+--------+---------+--------------+--------+
|      1 | 1024000 |            1 | 870397 |
|      2 | 1024000 |           10 |  14597 |
|      3 | 1024000 |           50 |   1394 |
|      4 | 1024000 |          100 |    703 |
|      5 | 1024000 |          500 |    201 |
|      6 | 1024000 |         1000 |    120 |
|      7 | 1024000 |         5000 |     87 |
|      8 | 1024000 |        10000 |     83 |
|      9 | 1024000 |        50000 |     79 |
+--------+---------+--------------+--------+


So it can take from days, down to a large minute to have a million rows...

Put it in CSV file like this:

SELECT commit_every,time from zztresults
into outfile '/tmp/speed.csv'
fields terminated by ','
lines terminated by '\n'
//

Actually numbers are so exponentially disparate, I used the logarithmic scale on both axis:

-->

commit_every	time(s)		log(commitnb)	log(time)
1	870397		0.00	5.94
10	14597		1.00	4.16
50	1394		1.70	3.14
100	703		2.00	2.85
500	201		2.70	2.30
1000	120		3.00	2.08
5000	87		3.70	1.94
10000	83		4.00	1.92
50000	79		4.70	1.90

So in this case with a default MySQL install, I'd go with 5000 commits at once.

What are the cons of using larger sets of commit? In my opinion: Increasing undo space. 

On Oracle that would mean the UNDO tablespace could grow or at least internal undo segments.
On MySQL/innodb alike, the rollback segment will grow to hold all uncomitted data. In 5.5 and earlier it is by default inside the one and only tablespace (ibdata)