1

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.

4
  • 1
    Use & to execute the mysql in background.
    – White Owl
    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.
    – Sotto Voce
    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.
    – IGGt
    Oct 26, 2022 at 13:59
  • "Use & to execute the mysql in background" - as simple as that. Cheers.
    – IGGt
    Oct 26, 2022 at 14:02

1 Answer 1

2

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.

0

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .