Not the answer you need?
Register and ask your own question!

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

jbowen7jbowen7 EntrantInactive User Role Beginner
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:
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:
[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


Comments

  • jbowen7jbowen7 Entrant Inactive User Role Beginner
    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:
    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?


    Purpose: Analyze poor performance experienced today.
      Details:
      Test Methods: Sysbench mysql test
      
      
      
      
      Table of Transactions/sec with different thread counts. (Read/Write Test)
       [TABLE]
     	 		[TR]
     			[TD="width: 33%"]# of Threads[/TD]
     			[TD="width: 33%"]PredictiveDB[/TD]
     			[TD="width: 33%"]Replication Server DB[/TD]
     		[/TR]
     		[TR]
     			[TD="width: 33%"]4[/TD]
     			[TD="width: 33%"]1446[/TD]
     			[TD="width: 33%"]109[/TD]
     		[/TR]
     		[TR]
     			[TD="width: 33%"]8[/TD]
     			[TD="width: 33%"]2567[/TD]
     			[TD="width: 33%"]109[/TD]
     		[/TR]
     		[TR]
     			[TD="width: 33%"]12[/TD]
     			[TD="width: 33%"]3630[/TD]
     			[TD="width: 33%"]108[/TD]
     		[/TR]
     		[TR]
     			[TD="width: 33%"]24[/TD]
     			[TD="width: 33%"]5848[/TD]
     			[TD="width: 33%"]107[/TD]
     		[/TR]
     		[TR]
     			[TD="width: 33%"]32[/TD]
     			[TD="width: 33%"]6100[/TD]
     			[TD="width: 33%"]108[/TD]
     		[/TR]
     		[TR]
     			[TD="width: 33%"]64[/TD]
     			[TD="width: 33%"]6139[/TD]
     			[TD="width: 33%"]109[/TD]
     		[/TR]
     	 [/TABLE]
      Table of Transactions/sec with different thread counts.( Read ONLY Test)
       [TABLE]
     	 		[TR]
     			[TD="width: 33%"]# of Threads[/TD]
     			[TD="width: 33%"]PredictiveDB[/TD]
     			[TD="width: 33%"]Replication Server DB[/TD]
     		[/TR]
     		[TR]
     			[TD="width: 33%"]4[/TD]
     			[TD="width: 33%"]2677[/TD]
     			[TD="width: 33%"]4092[/TD]
     		[/TR]
     		[TR]
     			[TD="width: 33%"]8[/TD]
     			[TD="width: 33%"]4758[/TD]
     			[TD="width: 33%"]5933[/TD]
     		[/TR]
     		[TR]
     			[TD="width: 33%"]16[/TD]
     			[TD="width: 33%"]7950[/TD]
     			[TD="width: 33%"]5695[/TD]
     		[/TR]
     		[TR]
     			[TD="width: 33%"]24[/TD]
     			[TD="width: 33%"]9619[/TD]
     			[TD="width: 33%"]5695[/TD]
     		[/TR]
     		[TR]
     			[TD="width: 33%"]32[/TD]
     			[TD="width: 33%"]10104[/TD]
     			[TD="width: 33%"]5685[/TD]
     		[/TR]
     		[TR]
     			[TD="width: 33%"]64[/TD]
     			[TD="width: 33%"]10012[/TD]
     			[TD="width: 33%"]5497[/TD]
     		[/TR]
     	 [/TABLE]
      
      Tests Below:
      
      [B]PredictionDB Server, 4 threads[/B]
      
      [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 ([B]1446.05 per sec.[/B])
      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
      
      
      [B]Replicatiion Server, 4 threads[/B]
      
      [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 ([B]109.74 per sec[/B].)
      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
      
      
      
      
      
      [B]PredictionDB Server, 8 threads[/B]
      [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 ([B]2567.32 per sec.)[/B]
      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
    
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.