2

I am trying to put together a simple bash script to fire a selection of queries at a database simultaneously for load testing purposes.

So far I have a file called "input_file" that contains the following:

select sleep(10);
select sleep(11);
select sleep(12);
select sleep(13);
select sleep(14);
select sleep(15);
select sleep(16);
select sleep(17);
select sleep(18);
select sleep(19);
select sleep(20);

I then have the following code:

IFS=$'\n'
for line in $(cat input_file)
do
{
    mysql -S[socket] -u[username] -p[password] -e${line}
}
done

But instead of running the queries simultaneously, it runs them one at a time, and waits for each query to finish before starting the next one. I want it to start the first query, then move on without waiting for it to finish, so that I end up with all the queries running at the same time.

I have seen various posts about people who have the opposite problem, but I can't seem to get any of their "wrong" examples to work.

Improve this question
4
  • 1
    Use & to execute the mysql in background. Commented Oct 26, 2022 at 13:50
  • There are applications designed to load test databases or http/tcp services with multiple connections, and your best solution is to use one of those. However, if you insist on coding it yourself with shell commands/scripts, look into GNU parallels. Commented Oct 26, 2022 at 13:52
  • I use mysqlslap quite extensively, but it doesn't work for this specific use case (as far as I can tell), e.g. multiple connections all running a single (but different) query simultaneously, only multiple connections running the same batch of queries one after the other. Commented Oct 26, 2022 at 13:59
  • "Use & to execute the mysql in background" - as simple as that. Cheers. Commented Oct 26, 2022 at 14:02

1 Answer 1

Reset to default
3

In the comments section it was already mentioned that you can add & to the end of the mysql command to make it run in the background. I wanted to suggest another approach using GNU xargs:

cat input_file | xargs --no-run-if-empty --delimiter '\n' --max-procs 0 -I '{}' mysql -S[socket] -u[username] -p[password] -e'{}'

# Or with short flags...
xargs -r -d '\n' -P 0 -I '{}' mysql -S[socket] -u[username] -p[password] -e'{}' < input_file

# Or letting xargs open the file and leave its stdin alone...
xargs --arg-file input_file -r -d '\n' -P 0 -I '{}' mysql -S[socket] -u[username] -p[password] -e'{}'
  • -r / --no-run-if-empty to avoid running mysql if the input is empty.
  • --delimiter '\n' / -d '\n' - Input items are terminated by a new line. When processing the input, quotes and backslash are not special; every character in the input is taken literally. Without this option, it won't work properly in the input contains backslashes or quotes (common in SQL code)."
  • -P max-procs / --max-procs=max-procs - Run up to max-procs processes at a time; the default is 1. If max-procs is 0, xargs will run as many processes as possible at a time.
  • -I replace-str - Replace occurrences of replace-str in the initial-arguments with names read from the input.
  • -a file / --arg-file file - read the arguments from the specified file instead of stdin.

The advantage of using GNU xargs is that you can limit the number of concurrent processes by setting <max-procs> parameter in -P to any number other than 0. In that case, if your input is very long and too many concurrent processes might hammer the server, you can limit the concurrency.

Improve this answer
0

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.