Paulo Neves Paulo Neves - 3 months ago 8
MySQL Question

Mysql 5.6.10 configuration

DB server

16 cores
63Gb RAM
CentOS release 6.8


etc/my.cnf

[mysqld]
pid_file=/var/lib/mysql/fatty01.pid

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql

innodb_buffer_pool_size = 50G
innodb_log_file_size = 2G
innodb_flush_log_at_trx_commit = 0
sync_binlog = 0
innodb_flush_method = O_DIRECT
innodb_buffer_pool_instances = 16
innodb_thread_concurrency = 16
skip_name_resolve = 1
innodb_io_capacity = 4000
innodb_io_capacity_max = 6000
innodb_buffer_pool_dump_at_shutdown = 1
innodb_buffer_pool_load_at_startup = 1
query_cache_size = 0
query_cache_type = OFF
innodb_checksum_algorithm = crc32
table_open_cache_instances = 16
innodb_read_io_threads = 20
innodb_write_io_threads = 10
max_connections = 700



when we have peaks of 3000 concurrent clients the mysqld does not seem
to pull all the resources posibles from the machine.


New relic

I see the load at 40 but the cpu does not seem to overpass the 60%


That reflects in the front end server


enter image description here

**
My question is clear, how can I improve the performance without compromising the server? Also how can decrease the MYSQL waiting time in the front end server, since clearly is a problem with the configurations on the DB server side.
**

**


UPDATE After research the problem seem to be in the slow queries, so I
guess this configuration is optimal for this hardware


**

Answer

No, it is not likely to be a simple tuning change. As I said, my.cnf looks good -- based on limited information.

Based on the charts, something happened suddenly. Or a flurry of activity.

Turn on the slowlog, set long_query_time=1, wait until the problem happens again, then use pt-query-digest to tell you the naughty query.