0

I am noticing during peak traffic the average mysql response time is 20-40 ms longer consistently as reported by new relic. I have tried turning on query cache and making the innodb buffer pool larger with no luck. The total response time is still around 125ms; but am concerned that I am missing a setting that could help.

I don't recall having this issue with Rackspace with the same set of data and traffic. Could it just come down to the fact there is just a bit more load on the database and is acceptable?

I have an 8GB Amazon RDS instance with a 4gb buffer pool and I turned on the query cache with a 75% hit rate which didn't move the needle.

Could the issue be related to being in a shared environment at all? My CPU usage is under 20% (usually a lot less).

here are my settings: https://gist.github.com/blasto333/9e82b6261681303f7369c3a2652e03f2

I am just looking for ideas to try. My two ideas were making the buffer pool larger was 3gb now 4gb; and turning the query cache on (As I had with rackspace). So far those didn't move the needle.

0

20-40ms is about a thousand miles of latency. Where are the client and server, relative to each other? Is Amazon moving the Server around? Or the Client?

innodb_buffer_pool_size is set reasonably by Amazon (I think). Do not raise it without raising the amount of RAM? There is no benefit in making the buffer_pool much bigger than the entire dataset (data & indexes).

For an 8GB VM (which is probably only 7.5GB), 6G is about the max that is safe -- assuming that only MySQL is running in that VM.

Turning on the QC is usually a waste on production systems (except on Aurora). Also, do not set query_cache_size bigger than about 50M -- else it could slow things down.

20% CPU -- percent of one core? Of all cores? 20% of all cores seems high. What is your slowest query? Also SHOW CREATE TABLE.

1
  • The query cache actually helped now the difference between peak and off peak is 5-10ms. The time I was referring to is MySQL response time as reported by newrelic for server to process all sql statements. Mar 10 '17 at 3:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.