CODEWITHSUNDEEP
mysqlcentosdatabase-performancemy.cnf
Paulo Almeida
Paulo Almeida

Reputation: 2199

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

**

Upvotes: 4

Views: 302

Answers (2)

Sean Fahey
Sean Fahey

Reputation: 1910

Your max_connections is only set to 700. How did you determine that you have 3000 concurrent clients? Site visits can be different from concurrent database connections. You might try increasing the connections available to your clients, as they may be experiencing slowdowns while waiting to connect.

Try checking SHOW PROCESSLIST; during peak usage to see how many connections your server is handling, and look for Too many connections in your mysql error log.

If you do increase your max_connections limit watch your CPU and RAM. MySQL will use more memory with more connections made available to clients.

Upvotes: 1

Rick James
Rick James

Reputation: 142560

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.

Upvotes: 2

Related Questions