1

I am facing weird behavior in our MySQL DB and need your expert guidance on this.

We have a query (simple select without join, usages index as well) which is executes in 5.8 seconds However, when we increase the load / concurrency (either by application or by mysqlslap ) its elapsed time increases up to 44 seconds.

I can also see the same behavior of increased timeline even for "select now()" using concurrency.

Is the "increased timeline with increasing load" is something which is default behavior for MySQL or I am missing any config in MySQL

Details are:

This is dedicated server for MySQL DB (community edition) version: 5.7.21-log core: 16 RAM: 128GB

innodb_thread_concurrency=20 innodb_read_io_threads=20 innodb_log_file_size=5G innodb_log_buffer_size=204217728 innodb_buffer_pool_size=72G

If I set innodb_thread_concurrency=0, CPU reaches to 99% but performance goes down as well.

Please suggest what should I look into further.

Regards, Sanjeeva

  • 2
    Please show us the DDL for the table in question, the actual query being executed and an EXPLAIN for the query. – Dave Nov 21 '18 at 12:09
  • 1
    I can see the same in my instance for "select now()" as well. – thisissanjeeva.com Nov 21 '18 at 12:31
  • 1
    The output of mysqlslap for "select now()" with concurrency 1, 20 & 40 concurrency=1 Average/minimum/maximum number of seconds to run all queries: 0.001/0.001/0.001 seconds Number of clients running queries: 1 concurrency=20 Average/minimum/maximum number of seconds to run all queries: 0.006/0.006/0.006 seconds Number of clients running queries: 20 concurrency=40 Average/minimum/maximum number of seconds to run all queries: 0.008/0.008/0.008 seconds Number of clients running queries: 40 – thisissanjeeva.com Nov 21 '18 at 17:24
  • The 'naughty' query is possibly causing the select now() to have problems. Let's start with the naughty query. – Rick James Nov 21 '18 at 20:22
  • 1
    Thanks @RickJames This server and the DB is dedicated for execution of our test scenario and nothing was running except "select now();" during this period. No other connections was there on DB. 16 Cores/128GB RAM, 70GB Innodb buffer, Disabled Q Cache, thread Conncurrency 22, maximum CPU usage on full load 70%. would you guide me finding the blocking point. – thisissanjeeva.com Nov 22 '18 at 6:08
0

Have you ever been is a store that was so crowded that you had difficulty moving around? It took you longer to get your items and purchase them than if there were a small number of customers.

The same happens with InnoDB. When you have "too many" connections, they stumble over each other. They are doing various things, taking out locks to check shared things, etc, etc. Ditto for even the network connecting your 'slap' to the server.

I tend to dislike concurrency type benchmarks because they do little more than decide when the server is likely to become unusable. "Unusable" to an end-user is often "latency has gone up".

Latency becomes 'terrible' soon after the system tries to make "too many" connections. So, I often recommend throttling the number of connections at the client (webserver?) level.

Every new release comes with fancy graphs showing how many connections that MySQL can now handle before the throughput flatlines or declines. The rise to that 'max' is always relatively steady. What it fails to show is that latency (your "increased timeline") gets really bad after that.

Is the "increased timeline with increasing load" is something which is default behavior for MySQL or I am missing any config in MySQL

Some flavor of that is the default behavior in any system with limited resources and 'customers' that contend for the resources. (Think grocery store.) It is also something that Oracle actively tackles at every release. In 5.0, the max was 4-8 connections; with 5.7 their graphs show that 64 is about where the "s... hits the fan".

Back to real life... MySQL can do thousands of queries per second. Some tests have even shown more than a million. But you must space out the queries to get such numbers.

  • 1
    Back to real life... MySQL can do thousands of queries per second. Some tests have even shown more than a million. But you must space out the queries to get such numbers. Would you please explain more about "space out the queries". How this can be achieved? Does this means I should reduce the concurrency? I want to correct this behaviour on my DB. Do I need to change any paramter to acheive this? – thisissanjeeva.com Nov 22 '18 at 7:22
  • @thisissanjeeva.com - How is the data coming in? One giant dump per day? A thousand devices reporting in once a minute but at the "top of the minute"? Other? Some LOADs are naturally spread out; yours seems to be more bursty; I would like to investigate it. There are likely to be workarounds. – Rick James Nov 22 '18 at 19:15
  • 1
    I would be happy to provide input as you require about my query/job. I will be in office on Monday and then I will provide all input. However, I would be interested in the behavior of MySQL during "select now()". why the elapsed time increases for this system function. My assumptions is "this is something different that query optimization" which I am afraid I am missing something in my environment. – thisissanjeeva.com Nov 25 '18 at 6:05
  • 1
    @thisissanjeeva.com - When you have so many connections that any resource (CPU, network, IO, etc) is saturated, each connection will take longer because it is sharing that resource. Gradually increase the number of connections as you monitor the various resources; I think you will see what I am saying. SELECT NOW() -- If you are making new connections, new threads, etc, all sorts of resources are used, though not as much as a complex query. – Rick James Nov 25 '18 at 19:37
  • 1
    Hi @RickJames,Got your point for "select now()" however, the DB behaviour is same with either my custom query or "select now()". Applicaiton with connection pool to avoid overhead The Stats (Thread concurrency 22 for both tests are in isolation): Select now() on concurrency 1 -20: CPU 3-4%, MySQL mem 67% Total RAM 70% Custom Query in isolation, active connection 40, total connections 260 CPU 72% MySQL mem 67%, Total RAM 70% only two custom query with different values, 1. select(count:2K in 12 Min) 2. replace on primary key. – thisissanjeeva.com Nov 26 '18 at 13:30

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.