DimitriK's (dim) Weblog

MySQL Performance: 5.6 Notes, part 2 - Under full dbSTRESS workload...

This is the second part of the MySQL 5.6 Performance Notes started from here .

Now it's about performance results on a full dbSTRESS test workload scenarios..

• Workload: Read-Only, Read+Update, Read+Write (Writes=Delete, Insert, Update)
• Configurations: 16, 24, 32, 48 cores
• Concurrent users: 1, 2, 4, 8.. 1024 (4min on each step, growing load)
• Think time: 0 sec
• Points of interest:
  • identify current and potential contentions
  • try to reduce contentions by limiting thread concurrency via CPU taskset (binding "mysqld" process to a fixed list of cores)

• MySQL 5.5.9:
  • most optimal performance on 32 cores
  • max 30.000 TPS
  • main contentions: btr_search_latch (yellow) and kernel_mutex (green)

• MySQL 5.6.2:
  • most optimal performance on 32 cores
  • max 31.000 TPS
  • main contentions: trx_sys (yellow), btr_search_latch (green)
  • probably by reducing the contention on trx_sys we may obtain a more stable TPS on 32cores
  • still no answer on: why the btr_serach_latch mutex still remains hot while there is no more pages loaded into the buffer pool?..

• MySQL 5.5.9:
  • most optimal on 24 cores
  • max 37.000 TPS
  • main contentions on: kernel_mutex (rose), btr_search_latch (blue)

• MySQL 5.6.2:
  • most optimal on 32 cores
  • max 40.000 TPS
  • main contentions on: lock_sys (green), trx_sys (magenta)

• MySQL 5.5.9:
  • most optimal on 32 cores
  • max 19.000 TPS
  • main contentions on: index lock, kernel_mutex

• MySQL 5.6.2:
  • most optimal on 48 cores
  • max 21.000 TPS
  • main contentions on: index mutex, lock_sys
  • NOTE: interesting, that on all configurations except 48 cores 5.6 showing a lower performance comparing to 5.5..

• Same as the previous test, just on the READ transactions only one SELECT query is used (SEL1)
• The main interest here that the SEL1 is blocked only by the kernel_mutex contention on a high concurrency workload..
• Workload: Read-Only, Read+Update, Read+Write (Writes=Delete, Insert, Update)
• Concurrent users: 1, 2, 4, 8.. 1024
• Think time: 0 sec

• MySQL 5.5.9:
  • most optimal performance on 16 cores
  • max 85.000 TPS
  • main contentions: kernel_mutex (magenta)

• MySQL 5.6.2:
  • most optimal performance on 16 cores, most stable on 24 cores
  • max 81.000 TPS
  • main contentions: trx_sys (yellow)

• MySQL 5.5.9:
  • most optimal performance on 24 or 32 cores
  • max 57.000 TPS
  • main contentions: kernel_mutex (magenta), rollback segment mutex (hm, interesting)..

• MySQL 5.6.2:
  • most optimal performance on 24 cores
  • max 64.000 TPS
  • main contentions: lock_sys (green)

• MySQL 5.5.9:
  • most optimal performance on 24 or 32 cores
  • max 21.000 TPS
  • main contentions: index mutex (magenta) and kernel_mutex (magenta)

• MySQL 5.6.2:
  • most optimal performance on 48 cores
  • max 22.000 TPS
  • main contentions: index mutex, lock_sys mutex
  • NOTE: here again on all configurations except 48 cores 5.6 showing a lower performance comparing to 5.5..
    
  • need to be investigated...