0

We have 25 queries, which are simple select statements, that we need to run concurrently on the same table, to test load.

How do I run multiple select statements at the exact same time? I have tried a bash script, but it runs the selects in order, and not at the same time

5
  • 1
    we need to run concurrently on the same table, to test load You MUST execute them using different connections to MySQL server. 25 queries - 25 connections. I have tried a bash script One bash session - one connection. Start 25 separate sessions (for example from one "main" session). – Akina Sep 27 '18 at 9:34
  • Show us the bash script. – Gerard H. Pille Sep 27 '18 at 9:56
  • @Akina, thank you, will try a few sessions at the same time – rezizter Sep 27 '18 at 10:33
  • If your goal is to tesl load without network transfer influence use queries with a minimal output (for example, to test load of some SELECT use it as a subquery with SELECT COUNT(some_fileld) in outer query). – Akina Sep 27 '18 at 10:37
  • You might find this of interest? – Vérace Sep 28 '18 at 10:52
1

The workload needs to open sessions in parallel, instead of executing all queries serially from one bash script.

There is a MySQL tool aimed at just this, please see mysqlslap.

https://dev.mysql.com/doc/refman/8.0/en/mysqlslap.html

0
0

At some point, any query will grab a Mutex that prevents other queries from proceeding. That is, it is impossible to run queries at exactly the same time.

What you can do (and mysqlslap and other tools do) is to keep pumping queries into the system, not quite in parallel, but "continuously". From that you can get "tps" or "qps".

Another thing to be cautious about is the number of connections that are feeding queries into mysql. In the old days, 4-8 would saturate the system, tps would stall or decline and latency would go through the roof. Current versions probably get past 64 connections before such happens.

When there are a hundred queries running at the same time, the contention for resources is so high that none of the connections is getting much done. It becomes a tradeoff between concurrency and latency.

When latency is too high, users complain that the system is "hung", and the novice DBA decides that the only cure is to reboot.

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.