MySQL tuning query – 5.6

MySQLmysql-5.6perconapercona-serverperformanceperformance-tuning

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?

Best Answer

  • 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.)

Related Question