Not the answer you need?
Register and ask your own question!

a litter test between mysql5.0.77 and percona5.5

justlooksjustlooks ContributorCurrent User Role Beginner
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

Comments

  • gmousegmouse Mod Squad Inactive User Role Beginner
    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)?
  • justlooksjustlooks Contributor Current User Role Beginner
    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
  • przemekprzemek Percona Support Engineer Percona Staff Role
    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/
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.