Announcement

Announcement Module
Collapse
No announcement yet.

a litter test between mysql5.0.77 and percona5.5

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • a litter test between mysql5.0.77 and percona5.5

    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)?

    Comment


    • #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

      Comment


      • #4
        How big in MB is the data in this table?

        And more importantly what are the:
        innodb_buffer_pool_size
        innodb_log_file_size
        innodb_log_buffer_size
        configurations set to for each instance? (use SHOW GLOBAL VARIABLES to get this information).


        And why do you have: default-storage-engine=myisam (which indicates that you want to use MyISAM tables) and no key_buffer_size configured?

        Those variables are the most crucial ones to get performance out of a MySQL instance.

        Comment


        • #5
          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/

          Comment

          Working...
          X