4

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

**

Paulo Almeida
  • 2,159
  • 2
  • 17
  • 19
  • Hopefully this will get moved to the Database Administraion section of Stack – RiggsFolly Aug 04 '16 at 11:17
  • Dedicated server? When do backups occur? Was the Slowlog turned on? The values in my.cnf look good. – Rick James Aug 04 '16 at 20:23
  • @Rick yes it's a dedicated server running on KVM at Linode Singapore., the backups are done at 5am and do not interfere with the performance, the Slow Query Log is turn off. I was expecting to be told to increase the **innodb_buffer_pool_instances** or **innodb_thread_concurrency** in order to get more from the machine – Paulo Almeida Aug 05 '16 at 03:08
  • 1
    @PauloNeves, I think you may trust Ricks's suggestion about config parameters. From the charts it is not clear, where the bottleneck is. It could be that your queries are locked, that is why you don't utilize your CPU at max. You may try to check performance_schema tables for more information or analyze slow log as Rick suggested. This is starting point. Seem like you don't need such a big buffer pool. From 50G allocated only about 15G is used. – Pavel Katiushyn Aug 09 '16 at 07:20

2 Answers2

2

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.

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • We use New Relic to evaluate the slow queries, nevertheless we should be able to use more of the cpu capacity, clearly we are only using 65% os the system I would like to get it at 80% maybe even 90% without compromising the OS. – Paulo Almeida Aug 05 '16 at 04:42
  • 10 MySQL connections won't use more than 62.5% of 16 cores. Only one core can be used by any one query. How many connections were there? – Rick James Aug 05 '16 at 04:48
  • 1
    If you ever have 3000 connections, they will be too busy stumbling over each other to get any work done. Seriously, all metrics decline in such a situation. 3000 _idle_ connections is OK. But even 50 _active_ MySQL connections is threatening to cause a meltdown. Does Relic have any more metrics? What you provided does not show much of use. – Rick James Aug 05 '16 at 04:51
  • 1
    The spike in "Error rate" -- what are they measuring? – Rick James Aug 05 '16 at 04:52
1

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.

Sean Fahey
  • 1,850
  • 3
  • 26
  • 36
  • Hi the concurrent clients was determined by Google real time user, I know is not 100% correct but it's a close estimation, on the other hand I never see the max connections be more than 300 – Paulo Almeida Aug 11 '16 at 04:30
  • 1
    I assume you mean `Max_used_connections`, which is monotonically increasing until you restart? Or maybe `Threads_connected`, which fluctuates? The former cannot exceed `max_connections+1`. The latter _should not_ be more than 10-20. – Rick James Aug 11 '16 at 18:08