Announcement

Announcement Module
Collapse
No announcement yet.

MySQL 5.1 -> Percona 5.5 == performance degredation despite my.cnf tuning.

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

  • MySQL 5.1 -> Percona 5.5 == performance degredation despite my.cnf tuning.

    The upgrade was a bit more than the DB software. I went from 1) to 2)
    1) Old : (4 core w/ HT - 8 logical, 16 GB mem, raid1 Velociraptors, MySQL 5.1)
    2) New : (12 core w/ HT - 24 logical, 32 GB mem, raid1 SSDs 840 evo, Percona 5.5)

    Obviously every layer has changed except the data set. This is the first time I've used MySQL|Percona 5.5.

    The problem was.. that a simple query that used to take 1 second, is now taking 4 seconds.
    The query is of the form:
    Code:
    SELECT column FROM TABLE WHERE column1='i' AND column2='j' AND column3='k' ORDER BY column DESC LIMIT 1;
    I'm not sure how many times that query was being run a minute.

    I'm mostly concerned how my query time quadrupled on a more powerful machine. I've been scowering the internet.. read endless forums, have read about 50 pages of O'Reily High Performance MySQL, been watching webinars... I just can't figure out what I'm doing wrong. I'd appreciate any advice.


    Does my my.cnf look adequate:

    Code:
    [mysql]
    
    # CLIENT #
    port                           = 3306
    socket                         = /var/lib/mysql/mysql.sock
    
    [mysqld]
    
    # GENERAL #
    user                           = mysql
    default-storage-engine         = InnoDB
    socket                         = /var/lib/mysql/mysql.sock
    pid-file                       = /var/lib/mysql/mysql.pid
    
    # MyISAM #
    key-buffer-size                = 32M
    myisam-recover                 = FORCE,BACKUP
    
    # SAFETY #
    max-allowed-packet             = 16M
    max-connect-errors             = 1000000
    skip-name-resolve
    sysdate-is-now                 = 1
    
    # DATA STORAGE #
    datadir                        = /var/lib/mysql/
    
    # BINARY LOGGING #
    log-bin                        = /var/lib/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               = 32M
    max-connections                = 500
    thread-cache-size              = 50
    open-files-limit               = 65535
    table-definition-cache         = 4096
    table-open-cache               = 4096
    
    # INNODB #
    innodb-flush-method            = O_DIRECT
    innodb-log-files-in-group      = 2
    innodb-log-file-size           = 4G
    innodb-flush-log-at-trx-commit = 2
    innodb-file-per-table          = 1
    innodb-buffer-pool-size        = 26G
    
    
    # SSD tuning #
    innodb_flush_neighbor_pages = 0
    innodb_adaptive_flushing_method = keep_average
    innodb_log_block_size = 4096
    innodb_log_buffer_size = 8M
    innodb_read_ahead = none
    innodb_io_capacity = 500
    
    # LOGGING #
    log-error                      = /var/lib/mysql/mysql-error.log
    log-queries-not-using-indexes  = 0
    slow-query-log                 = 1
    slow-query-log-file            = /var/lib/mysql/mysql-slow.log

  • #2
    I've been doing some tests with sysbench, and have been changing around the different threads. I have noticed that my new server does very well with 32 or 64 threads while my old server does best with about 8 threads.

    So perhaps threading is my problem with the new 24 core system. I found two variables:
    Code:
    innodb_io_read_threads
    innodb_io_write_threads
    Could these potentially have been my weak link in the configuration? I see that they have defaulted to 4, with which, for my READ ONLY tests, the old server had the advantage. But the data set is entirely in memory, and it's a select statement, so do these variables still apply?



    Code:
     Purpose: Analyze poor performance experienced today.
      Details:
      Test Methods: Sysbench mysql test
      
      
      
      
      Table of Transactions/sec with different thread counts. (Read/Write Test)
       
    # of Threads PredictiveDB Replication Server DB
    4 1446 109
    8 2567 109
    12 3630 108
    24 5848 107
    32 6100 108
    64 6139 109
    Table of Transactions/sec with different thread counts.( Read ONLY Test)
    # of Threads PredictiveDB Replication Server DB
    4 2677 4092
    8 4758 5933
    16 7950 5695
    24 9619 5695
    32 10104 5685
    64 10012 5497
    Tests Below: PredictionDB Server, 4 threads [root@PredictiveDB bowenit]# sysbench --db-driver=mysql --test=oltp --oltp-table-size=1000000 --mysql-db=test --mysql-user=root --mysql-password= --max-time=60 --max-requests=0 --num-threads=4 run sysbench 0.4.12: multi-threaded system evaluation benchmark Running the test with following options: Number of threads: 4 Doing OLTP test. Running mixed OLTP test Using Special distribution (12 iterations, 1 pct of values are returned in 75 pct cases) Using "BEGIN" for starting transactions Using auto_inc on the id column Threads started! Time limit exceeded, exiting... (last message repeated 3 times) Done. [root@PredictiveDB bowenit]# sysbench --db-driver=mysql --test=oltp --oltp-table-size=1000000 --mysql-db=test --mysql-user=root --mysql-password= --max-time=60 --max-requests=0 --num-threads=32 run sysbench 0.4.12: multi-threaded system evaluation benchmark Running the test with following options: Number of threads: 32 Doing OLTP test. Running mixed OLTP test Using Special distribution (12 iterations, 1 pct of values are returned in 75 pct cases) Using "BEGIN" for starting transactions Using auto_inc on the id column Threads started! Time limit exceeded, exiting... (last message repeated 31 times) Done. OLTP test statistics: queries performed: read: 5124938 write: 1830330 other: 732132 total: 7687400 transactions: 366065 (6100.57 per sec.) deadlocks: 2 (0.03 per sec.) read/write requests: 6955268 (115911.34 per sec.) other operations: 732132 (12201.17 per sec.) Test execution summary: total time: 60.0051s total number of events: 366065 total time taken by event execution: 1917.0571 per-request statistics: min: 2.39ms avg: 5.24ms max: 47.66ms approx. 95 percentile: 7.92ms Threads fairness: events (avg/stddev): 11439.5312/46.26 execution time (avg/stddev): 59.9080/0.00 OLTP test statistics: queries performed: read: 1214738 write: 433835 other: 173534 total: 1822107 transactions: 86767 (1446.05 per sec.) deadlocks: 0 (0.00 per sec.) read/write requests: 1648573 (27474.90 per sec.) other operations: 173534 (2892.09 per sec.) Test execution summary: total time: 60.0029s total number of events: 86767 total time taken by event execution: 239.5165 per-request statistics: min: 1.81ms avg: 2.76ms max: 39.46ms approx. 95 percentile: 3.32ms Threads fairness: events (avg/stddev): 21691.7500/21.71 execution time (avg/stddev): 59.8791/0.00 Replicatiion Server, 4 threads [root@mysql01-node02 tmp]# sysbench --db-driver=mysql --test=oltp --oltp-table-size=1000000 --mysql-db=test --mysql-user=root --mysql-password= --max-time=60 --max-requests=0 --num-threads=4 run sysbench 0.4.12: multi-threaded system evaluation benchmark Running the test with following options: Number of threads: 4 Doing OLTP test. Running mixed OLTP test Using Special distribution (12 iterations, 1 pct of values are returned in 75 pct cases) Using "BEGIN" for starting transactions Using auto_inc on the id column Threads started! Time limit exceeded, exiting... (last message repeated 3 times) Done. OLTP test statistics: queries performed: read: 92232 write: 32940 other: 13176 total: 138348 transactions: 6588 (109.74 per sec.) deadlocks: 0 (0.00 per sec.) read/write requests: 125172 (2085.08 per sec.) other operations: 13176 (219.48 per sec.) Test execution summary: total time: 60.0321s total number of events: 6588 total time taken by event execution: 240.0464 per-request statistics: min: 12.05ms avg: 36.44ms max: 177.84ms approx. 95 percentile: 53.99ms Threads fairness: events (avg/stddev): 1647.0000/1.87 execution time (avg/stddev): 60.0116/0.01 PredictionDB Server, 8 threads [root@PredictiveDB bowenit]# sysbench --db-driver=mysql --test=oltp --oltp-table-size=1000000 --mysql-db=test --mysql-user=root --mysql-password= --max-time=60 --max-requests=0 --num-threads=8 run sysbench 0.4.12: multi-threaded system evaluation benchmark Running the test with following options: Number of threads: 8 Doing OLTP test. Running mixed OLTP test Using Special distribution (12 iterations, 1 pct of values are returned in 75 pct cases) Using "BEGIN" for starting transactions Using auto_inc on the id column Threads started! Time limit exceeded, exiting... (last message repeated 7 times) Done. OLTP test statistics: queries performed: read: 2156658 write: 770232 other: 308093 total: 3234983 transactions: 154046 (2567.32 per sec.) deadlocks: 1 (0.02 per sec.) read/write requests: 2926890 (48779.26 per sec.) other operations: 308093 (5134.65 per sec.) Test execution summary: total time: 60.0028s total number of events: 154046 total time taken by event execution: 479.0563 per-request statistics: min: 1.91ms avg: 3.11ms max: 49.57ms approx. 95 percentile: 3.73ms Threads fairness: events (avg/stddev): 19255.7500/55.51 execution time (avg/stddev): 59.8820/0.00 TRUNCATED
    Last edited by jbowen7; 11-01-2013, 01:29 AM.

    Comment

    Working...
    X