Return to Question

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.

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?

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

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

Memory utilization is most of the times is high above 90%. 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.

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.