Reputation: 2199
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.
I see the load at 40 but the cpu does not seem to overpass the 60%
That reflects in the front end server
** 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
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
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