0

I'm using Percona Server 5.6.31 on RHEL 6. 64G RAM/16vCPU - VMware. It's Completely InnoDB and the database size is 300G.

I have set Innodb_buffer_pool to 52G, innodb_buffer_pool_instances to default (8) and innodb_flush_method to O_DIRECT. Innodb_read_io_threads and write_io threads are set to 64.

Memory utilization is above 90% most of the time. CPU utilization varies. Sometimes spikes. vm.swappiness is set to default (60).

I have two queries:

  1. What should be my value for innodb_buffer_pool_instances? Is it right or is a change required?

  2. I read many articles suggesting that swappiness needs to be set to 1 or 0 or 10 for MySQL database servers. But I do not observe any swapping on the OS. Do I need to still swappiness to 0 or 1. Is it better to change the value on proactive basis or I should change only when it swaps?

I can provide more info if required.

Update:

Show variables and Show global status output can be seen here Config details

CPU utilization is often above 80%. Does CPU utilization only have to do with query tuning?

Improve this question
4
  • 2
    @RaymondNijland All InnoDB data is read through the buffer pool including data, not just secondary indexes. Technically the data is in a clustered index, but I think the point of your comment was that only secondary indexes are stored in the buffer pool, which is incorrect.
    – G-Nugget
    Commented Dec 20, 2016 at 17:38
  • @G-Nugget your right ive made a error innodb_buffer_pool_size hold table data and index data InnoDB maintains a storage area called the buffer pool for caching data and indexes in memory read dev.mysql.com/doc/refman/5.6/en/innodb-buffer-pool.html.. ive edited my comment
    – Raymond Nijland
    Commented Dec 20, 2016 at 17:41
  • As a rule of thumb, set your innodb_buffer_pool_size to approximately 70 – 80% of the RAM available.this value should be between 44,8 Gb and 51.2 Gb but 52GB should also work..
    – Raymond Nijland
    Commented Dec 20, 2016 at 17:42
  • I think my question was not about what gets stored in buffer pool or what should be the buffer pool sizing.
    – tesla747
    Commented Dec 21, 2016 at 1:07

1 Answer 1

Reset to default
1

  • swappiness:

    • = 0 --> crash if you run out of memory -- not nice
    • small --> best
    • large --> might swap mysql preemptively, leading to poor performance

  • pool_instances -- should not be more than pool_size/1G; the specific value makes only a small difference in performance. (Don't worry about it.) Some say 16 should be the max number of instances, but I have not heard 'why'.

  • CPU spikes -- Usually a missing index or a poorly phrased query. Find it and fix it. (And/or ask for advice)

  • The buffer_pool(s) cache data and indexes. It is (roughly) a least-recently-used cache. It is also used for delaying index updates due to writes.

  • 52G/64G -- reasonable. It obviously can't hold the entire 300GB, but that may not be an issue -- especially if most of the action is in isolated portions. Example: Only "recent" data is actively accessed. Counter example: UUIDs have terrible performance when the data is bigger than the buffer_pool.

For further analysis of settings, post SHOW VARIABLES; and SHOW GLOBAL STATUS;. (Not here; there is a size limitation.)

Improve this answer
1
  • and just one more! VMware(!!!) it good product, but high loading database server and virtualisation - it what need to avoid! You never will know - where the problem? it is "server" spike? or it is something other? I have a lot of examples about this.
    – a_vlad
    Commented Dec 23, 2016 at 6:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.