Announcement

Announcement Module
Collapse
No announcement yet.

Percona 5.5 performance regression FreeBSD

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

  • Percona 5.5 performance regression FreeBSD

    Percona seems to be slower on FreeBSD as we tested on a clean install server:

    Hardware: Supermicro PDSMU Xeon 3220 quad core 2.4Ghz, 4 GB ECC ram, 160 GB Seagate Barracuda 3.0 Gb/s SATA drive

    OS: FreeBSD 8.4-RELEASE-p1 AMD64

    - all software were installed using FreeBSD ports and default compile configurations
    - test were conducted using sysbench 0.4.12
    - MySQL data dir was cleared (removed all files) prior to each install of MySQL and Percona
    - each test ran twice per number of threads

    Configuration file generated from Percona Configuration Wizard:

    my.cnf:
    Code:
    [mysql]
    
    # CLIENT #
    port = 3306
    socket = /tmp/mysql.sock
    
    [mysqld]
    
    # GENERAL #
    user = mysql
    default_storage_engine = InnoDB
    socket = /tmp/mysql.sock
    #pid_file = /var/db/mysql/mysql.pid
    
    # MyISAM #
    key_buffer_size = 32M
    myisam_recover = FORCE,BACKUP
    
    # SAFETY #
    max_allowed_packet = 16M
    max_connect_errors = 1000000
    skip_name_resolve
    sql_mode = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_S UBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ONLY_FULL _GROUP_BY
    sysdate_is_now = 1
    innodb = FORCE
    innodb_strict_mode = 1
    
    # DATA STORAGE #
    datadir = /var/db/mysql/
    
    # BINARY LOGGING #
    #log_bin = /var/db/mysql/mysql-bin
    #expire_logs_days = 14
    #sync_binlog = 1
    
    # CACHES AND LIMITS #
    tmp_table_size = 32M
    max_heap_table_size = 32M
    query_cache_type = 0
    query_cache_size = 0
    max_connections = 500
    thread_cache_size = 50
    open_files_limit = 65535
    table_definition_cache = 1024
    table_open_cache = 128
    
    # INNODB #
    innodb_flush_method = O_DIRECT
    innodb_log_files_in_group = 2
    innodb_log_file_size = 128M
    innodb_flush_log_at_trx_commit = 1
    innodb_file_per_table = 1
    innodb_buffer_pool_size = 2G
    
    # LOGGING #
    log_error = /var/db/mysql/mysql-error.log
    log_queries_not_using_indexes = 1
    slow_query_log = 1
    slow_query_log_file = /var/db/mysql/mysql-slow.log

    TESTING:

    test command:
    #sysbench --test=oltp --num-threads={i} --mysql-user=root --max-requests=0 --max-time=120 --mysql-password=mypasswd {prepare | run}

    Results:

    percona 5.5

    4 thread:
    transactions: 132707 (1105.86 per sec.)
    deadlocks: 0 (0.00 per sec.)

    8 thread:
    transactions: 155294 (1294.04 per sec.)
    deadlocks: 15 (0.12 per sec.)

    12 thread:
    transactions: 158591 (1321.55 per sec.)
    deadlocks: 24 (0.20 per sec.)

    16 thread:
    transactions: 155354 (1294.54 per sec.)
    deadlocks: 26 (0.22 per sec.)


    ++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++++++++++++++++++++++

    mysql-community 5.5

    4 thread:
    transactions: 134547 (1121.20 per sec.)
    deadlocks: 0 (0.00 per sec.)

    8 thread:
    transactions: 158412 (1320.07 per sec.)
    deadlocks: 15 (0.12 per sec.)

    12 thread:
    transactions: 161310 (1344.19 per sec.)
    deadlocks: 19 (0.16 per sec.)

    16 thread:
    transactions: 158446 (1320.34 per sec.)
    deadlocks: 25 (0.21 per sec.)

  • #2
    This could be related to many things -the additions of some Percona-only features or the lack of optimization for the that Operating System. In order to profile the issue, it would be really nice to have the output of oprofile/perf and/or mutexes hit by the servers to understand what is the bottleneck on each case. There are several things that are changed in PS, from the flushing algorithms, to the drop table process, and the partitioning of several InnoDB internal structures. Also, please make sure that you restart both servers between tests and execute them several times, as we have seen some strange cases of performance suddenly dropping without known cause (in any flavor of MySQL), even more than that 1% that you are suffering.

    Also, 120 seconds per test may be not enough to show some of the improvements in Percona, as some of them, for example, are aimed to have a better stability on the flushing algorithm on the long term. Try 1 hour of activity on your peak throughput.
    Additionally, please note that if you are only interested in raw performance, there are some parameters that probably should be tuned for both servers (and of course, there are no Percona-only features activated by default).

    Comment


    • #3
      I'm sure as with any OS there are tuning parameters that can be done to improve performance. The point of this benchmark is just a apple to apple comparison of a generic / out-of-the-box configuration of both software. I know that a long time ago Percona did a FreeBSD benchmark that showed unfavorable results. It would be nice if they revisited FreeBSD since there is a stable port of Percona for it and I am sure a lot of FreeBSD admins would like to implement Percona in their production servers.

      Comment


      • #4
        just additional testing with longer duration:

        - same process and configuration as stated above (with additional server restart after each install of Percona and MySQL
        - increased table size from default 10000 to 1000000 rows
        - tested 8 threads only
        - database is warmed up using 120 secs of OLTP test via sysbench prior to the longer test

        test:
        #sysbench --test=oltp --oltp-table-size=1000000 --num-threads=8 --mysql-user=root --max-requests=0 --max-time=21600 --mysql-password=mypasswd {prepare|run}

        Percona 5.5 server | Percona 5.5 client

        OLTP test statistics:
        queries performed:
        read: 352106286
        write: 125752232
        other: 50300893
        total: 528159411
        transactions: 25150444 (1164.37 per sec.)
        deadlocks: 5 (0.00 per sec.)
        read/write requests: 477858518 (22123.07 per sec.)
        other operations: 50300893 (2328.74 per sec.)

        Test execution summary:
        total time: 21600.0069s
        total number of events: 25150444
        total time taken by event execution: 172521.0576
        per-request statistics:
        min: 2.91ms
        avg: 6.86ms
        max: 423.15ms
        approx. 95 percentile: 9.47ms

        Threads fairness:
        events (avg/stddev): 3143805.5000/2675.13
        execution time (avg/stddev): 21565.1322/0.20


        Innodb mutexes:
        Code:
        ----------
        SEMAPHORES
        ----------
        OS WAIT ARRAY INFO: reservation count 2878508, signal count 11155593
        Mutex spin waits 38129568, rounds 157730134, OS waits 1895815
        RW-shared spins 3926004, rounds 30666043, OS waits 524392
        RW-excl spins 4186506, rounds 37184802, OS waits 229033
        Spin rounds per wait: 4.14 mutex, 7.81 RW-shared, 8.88 RW-excl

        ++++++++++++++++++++++++++++++++++++++++++++++++++ +++++++++++++

        MySQL 5.5 server | MySQL 5.5 client (community version)

        OLTP test statistics:
        queries performed:
        read: 362446336
        write: 129445102
        other: 51778041
        total: 543669479
        transactions: 25889017 (1198.57 per sec.)
        deadlocks: 7 (0.00 per sec.)
        read/write requests: 491891438 (22772.75 per sec.)
        other operations: 51778041 (2397.13 per sec.)

        Test execution summary:
        total time: 21600.0052s
        total number of events: 25889017
        total time taken by event execution: 172516.9098
        per-request statistics:
        min: 2.63ms
        avg: 6.66ms
        max: 2659.06ms
        approx. 95 percentile: 9.15ms

        Threads fairness:
        events (avg/stddev): 3236127.1250/3392.91
        execution time (avg/stddev): 21564.6137/0.31

        InnoDB mutexes:
        Code:
        ----------
        SEMAPHORES
        ----------
        OS WAIT ARRAY INFO: reservation count 3296925, signal count 19525633
        Mutex spin waits 72244656, rounds 200727598, OS waits 1964663
        RW-shared spins 4415105, rounds 40987989, OS waits 727326
        RW-excl spins 3422351, rounds 106873545, OS waits 335181
        Spin rounds per wait: 2.78 mutex, 9.28 RW-shared, 31.23 RW-excl

        Comment

        Working...
        X