Announcement

Announcement Module
Collapse
No announcement yet.

Performance MySQL 5.1.30 to Maria 5.5.23

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

  • Performance MySQL 5.1.30 to Maria 5.5.23

    Hello,

    My RDBMS environnent currently use MySQL 5.1.30 but I intend to upgrade MySQL to MariaDB or Percona server (5.5 branch) server.

    I started with MariaDB 5.5.23 (will try Percona just after).

    MySQL and MariaDB are on the same server, with different data dir but in the same partition. (mysqldump to import/export)
    MariaDB use the same configuration file.

    [client]
    port = 3307
    socket = /tmp/maria.sock
    [mysqld]
    port = 3307
    socket = /tmp/maria.sock
    max_allowed_packet = 67108864
    table_cache = 1024
    myisam_sort_buffer_size = 64M
    thread_concurrency = 16
    log-bin=mysql-bin
    server-id = 1
    max_connections = 4096
    slow_query_log = 1
    long_query_time = 2
    binlog_format=mixed
    expire_logs_days = 14
    wait_timeout=1800
    interactive_timeout=1800
    join_buffer_size = 262144
    key_buffer_size = 4096M

    query_cache_limit = 8M
    query_cache_size = 64M
    query_prealloc_size = 16384
    read_buffer_size = 4194304
    read_rnd_buffer_size = 16777216
    sort_buffer_size = 4194304
    thread_cache_size = 64
    tmp_table_size = 67108864
    transaction_alloc_block_size = 16384
    transaction_prealloc_size = 8192
    innodb_file_per_table
    innodb_flush_method=O_DIRECT
    innodb_log_file_size = 1G
    innodb_buffer_pool_size = 10G
    default-storage-engine = innodb
    group_concat_max_len = 1M
    [mysqldump]
    quick
    max_allowed_packet = 67108864
    [mysql]
    no-auto-rehash
    [isamchk]
    key_buffer = 256M
    sort_buffer_size = 256M
    read_buffer = 2M
    write_buffer = 2M
    [myisamchk]
    key_buffer = 256M
    sort_buffer_size = 256M
    read_buffer = 2M
    write_buffer = 2M
    [mysqlhotcopy]
    interactive-timeout


    I selected few queries from my application to try performance.
    Unfortunately I was disappointed with the performance of Mariadb almost 1,5 times slower...

    Databases contains ~200 Innodb tables and many triggers & stored procedures but no(or not many) transactions.

    I use simple php script and / or profiling option to get these results.

    Someone should have an idea? Maybe 5.1 branch is faster for my application than 5.5

    PS:SQL_NO_CACHE is used to avoid cache and when I test mariadb, mysql is shutdown vice versa

    EDIT:
    For some query, EXPLAIN between MySQL and Maria is different for the same query (on column Extra)
    Example
    MySQL
    +----------------------------------------------+
    | Extra
    +----------------------------------------------+
    | Using where; Using temporary; Using filesort
    | Using where
    +----------------------------------------------+
    2 rows in set (0.01 sec)
    Mariadb
    +----------------------------------------------------------- ----------+
    | Extra
    +----------------------------------------------------------- ----------+
    | Using index condition; Using where; Using temporary; Using filesort
    | Using where
    +----------------------------------------------------------- ----------+
    2 rows in set (0.01 sec)
Working...
X