a litter test between mysql5.0.77 and percona5.5

MySQL & MariaDB Other MySQL® Questions
1

i use mysql5.0.77 ,due to some reason ,i have to upgrade my DB,after benchmarking,i choose percona5.5,but i do some test on
same machine before and after upgrade ,it seems percona5.5 slow than mysql5.0.77 in inserting data and changing table schema, why? i suppose percona5.5 will faster than old version mysql

mysql> show create table test_me\G
*************************** 1. row ***************************
Table: test_me
Create Table: CREATE TABLE test_me (
id int(11) DEFAULT NULL,
name varchar(20) DEFAULT NULL,
addr varchar(30) DEFAULT NULL,
gender enum(‘F’,‘M’,‘UNKNOWN’) DEFAULT NULL,
comment varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1

on mysql5.0.77

mysql> call big_table(20000000);
Query OK, 1 row affected (11 min 50.71 sec)

mysql> alter table test_me modify column gender enum(‘F’,‘M’,‘UNKNOWN’);
Query OK, 20000000 rows affected (18.44 sec)
Records: 20000000 Duplicates: 0 Warnings: 0

mysql> alter table test_me engine=innodb;
Query OK, 20000000 rows affected (1 min 55.18 sec)
Records: 20000000 Duplicates: 0 Warnings: 0

mysql> alter table test_me modify column gender enum(‘F’,‘M’);
Query OK, 20000000 rows affected (2 min 35.46 sec)
Records: 20000000 Duplicates: 0 Warnings: 0

on percona5.5

mysql> call big_table(20000000);
Query OK, 1 row affected (16 min 36.40 sec)

mysql> alter table test_me modify column gender enum(‘F’,‘M’,‘UNKNOWN’);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> alter table test_me engine=innodb;
Query OK, 20000000 rows affected (2 min 58.02 sec)
Records: 20000000 Duplicates: 0 Warnings: 0

mysql> alter table test_me modify column gender enum(‘F’,‘M’);
Query OK, 20000000 rows affected (3 min 55.66 sec)
Records: 20000000 Duplicates: 0 Warnings: 0

2

What is your configuration? Do you run both tests immediately after starting mysqld or do you first let it read all data? If you run it immediately after starting, is mysqlcheck running (debian/ubuntu have this in their init-script iirc)?

3

i run this test on same host, first i run it on mysql5.0.77,and i remove mysql5.0.77,in stall rpm package for percona server ,and use same my.cnf and start percona server and run the same test

here is my.cnf

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
port=3306
old_passwords=1

server-id=27
innodb-file-per-table

#default-character-set=gbk
character-set-server=gbk

max_allowed_packet=64M

skip-name-resolve
replicate-do-table=XXXX.XXXX
relay_log=mysqld-yiyao-relay-bin
relay_log_index=mysqld-yiyao-relay-bin.index

pid-file=/var/lib/mysql/mysqld.pid
log-error=/var/lib/mysql/err.log

#log-bin=mysql-bin
#binlog-format = mixed

default-storage-engine = myisam

performance_schema
performance_schema_events_waits_history_size=20
performance_schema_events_waits_history_long_size=5000

4

Justlooks: even with the same config default values for other, non specified variables may differ in next MySQL versions.

Also I recommend you to read this:
http://www.mysqlperformanceblog.com/2011/10/10/mysql-version s-shootout/