GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

Percona 5.5.23 running slower than MySQL 5.1.44?

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

  • Percona 5.5.23 running slower than MySQL 5.1.44?

    Hello all!

    I hope you chaps we'll be able to help me on this most bizarre issue i have.

    We've been trialling percona server 5.5.23 to replace our mysql 5.1.44 enterpise servers.

    We set up 2 identical VM's on an ESXi host running debian lenny amd-64.

    After dropping the binaries in and tweaking the cnf accordingly percona started running fine (see cnf here

    [mysqld]
    #federated
    socket=/var/run/mysqld/mysqld1/mysqld.sock
    general-log-file=/var/log/mysql/mysqld.log
    log-error=/var/log/mysql/mysqld.err
    log_warnings=2
    innodb_buffer_pool_size=12G
    innodb_log_file_size=750M
    innodb_log_buffer_size=8M
    innodb_flush_log_at_trx_commit=2
    innodb_flush_method=O_DIRECT
    #innodb_file_per_table
    transaction-isolation=READ-COMMITTED
    max_connections=1000
    #log-bin=mysql.bin
    #binlog-format=MIXED
    server-id=1
    #ignore_builtin_innodb
    #plugin-load=innodb=ha_innodb_plugin.so
    default-storage-engine=InnoDB
    thread_cache_size=24
    tmp_table_size=128M
    max_heap_table_size=128M
    #slow_query_log_file=/storage/logs/mysql/slow-query/sql1.log
    #slow_query_log=1
    long_query_time=3
    #log-slow-admin-statements
    #log-queries-not-using-indexes
    expire_logs_days=7
    net_read_timeout=3000
    net_write_timeout=3000
    max_allowed_packet=1G
    group_concat_max_len=32M
    #binlog_cache_size=1M
    table_open_cache=2048
    table_definition_cache=1024
    join_buffer_size=1M
    innodb_lock_wait_timeout=2400
    skip-name-resolve
    query_cache_size=48M
    bind-address=0.0.0.0
    wait_timeout=86400
    skip-external-locking
    key_buffer=16M
    thread_stack=192K
    query_cache_limit=1M
    connect_timeout=120

    I ran a few mysqlslap tests just to get a taste for how much faster the percona server would be and as expected it came out faster than the 5.1.44 server:

    percona 5.5.23: mysqlslap --user=root -p***** --auto-generate-sql --concurrency=300 --engine=innodb Benchmark Running for engine innodb Average number of seconds to run all queries: 1.069 seconds Minimum number of seconds to run all queries: 1.069 seconds Maximum number of seconds to run all queries: 1.069 seconds Number of clients running queries: 300 Average number of queries per client: 0

    MySQL 5.1.44: mysqlslap --user=root -p***** --auto-generate-sql --concurrency=300 --engine=innodb Benchmark Running for engine innodb Average number of seconds to run all queries: 1.552 seconds Minimum number of seconds to run all queries: 1.552 seconds Maximum number of seconds to run all queries: 1.552 seconds Number of clients running queries: 300 Average number of queries per client: 0

    Then i yanked some of the more larger SELECTS from our production db to see how well it handled a more bespoke query, and oddly the percona server was consistently slower than the mysql server. Putting this down to the possibility that the query itself was just badly written i ran the following commands on each server:

    percona server:

    mysql> SELECT BENCHMARK(10000000000,1+1);
    +----------------------------+|
    BENCHMARK(10000000000,1+1)
    |+----------------------------+|
    0 |+----------------------------+1
    row in set (3 min 32.06 sec)

    mysql server:

    mysql> SELECT BENCHMARK(10000000000,1+1);
    +----------------------------+
    | BENCHMARK(10000000000,1+1) |
    +----------------------------+
    | 0 |
    +----------------------------+
    1 row in set (2 min 32.35 sec)

    Thinking i had a botched install i re ran the install script set the server back up again it came out the same?

    Now i'm almost certain i have an iffy setting in my cnf somewhere which is causing this and i'm not overly confident about throwing a full test suite at it until i'm happy thats correct.

    Could someone maybe point me in the right direction re my cnf or are my intial tests just a bit naff?

  • #2
    Slithers wrote on Tue, 12 June 2012 17:50
    We set up 2 identical VM's on an ESXi host running debian lenny amd-64.
    Are you sure that the CPU's in the ESXi hardware are exactly the same, or could the hardware have been upgraded at a later point by adding more cpu's, which might different from the original ones?

    Slithers wrote on Tue, 12 June 2012 17:50
    Then i yanked some of the more larger SELECTS from our production db to see how well it handled a more bespoke query, and oddly the percona server was consistently slower than the mysql server. Putting this down to the possibility that the query itself was just badly written i ran the following commands on each server:
    Did you get the same execution plan for the queries on both servers?

    Slithers wrote on Tue, 12 June 2012 17:50
    mysql> SELECT BENCHMARK(10000000000,1+1);
    +----------------------------+|
    BENCHMARK(10000000000,1+1)
    |+----------------------------+|
    0 |+----------------------------+1
    row in set (3 min 32.06 sec)

    mysql server:

    mysql> SELECT BENCHMARK(10000000000,1+1);
    +----------------------------+
    | BENCHMARK(10000000000,1+1) |
    +----------------------------+
    | 0 |
    +----------------------------+
    1 row in set (2 min 32.35 sec)

    Now i'm almost certain i have an iffy setting in my cnf somewhere which is causing this and i'm not overly confident about throwing a full test suite at it until i'm happy thats correct.

    Could someone maybe point me in the right direction re my cnf or are my intial tests just a bit naff?
    Running a Benchmark like the one above is not really useful, since it basically just tests CPU performance in one thread (more or less just GHz since you are performing an addition of two int values). That is why I asked if you are sure that all CPU's in the ESXi hardware are exactly the same.

    Most speed improvements in the more recent MySQL versions can only be seen when running a lot of queries in parallel on a multicore machine. Where the parallelism is highly improved but the speed of an individual query is basically the same.

    So looking at the speed of an individual query run alone on a machine will usually not show anything interesting when comparing an older and newer version of MySQL.
    But if you put the machine under heavy load and then compare the total throughput you usually see big differences.

    Comment

    Working...
    X