MySQL tuning query - 5.6 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:


What should be my value for innodb_buffer_pool_instances? Is it right or is a change required?
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?
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.)