mysqlslap test with random insertions and deletions concurrently

Question

I have a table as

create table slap(ind char(4) primary key, name varchar(50));

I want to test this with a sequence of random inserts and delete, queries will be provided by a sql file containing 10000 inserts/delete statements which looks like -

insert into slap values('gdvx', 'gdvx');
delete from slap where ind='gdvx';
insert into slap values('sbkq', 'sbkq');
delete from slap where ind='sbkq';
insert into slap values('gdmb', 'gdmb');
insert into slap values('vnka', 'vnka');
insert into slap values('mytw', 'mytw');
delete from slap where ind='gdmb';

I am running mysqlslap command as

mysqlslap --concurrency=50 --iterations=1 --query=./slaptest.sql --create-schema=slaptest -uroot -p

I get error as duplicate key insertion as I believe every thread execute queries from start of the file, but I want threads to execute queries from this file sequentially i.e. first thread execute first statement and second thread second statement etc.

Please let me know if mysqlslap supports such thing?

Thanks

Answer 1

mysqlslap won't do what you want: skip some lines based on thread number:

But:

You can put this in your SQL script and get random strings of four alphanumeric characters to use.

set @randomval := substring(to_base64(
                      concat(char(round(rand()*25)+97),
                      char(round(rand()*25)+97),
                      char(round(rand()*25)+97),
                      char(round(rand()*25)+97))),1,4)
insert ignore into slap values(@randomval , @randomval );
delete from slap where ind=@randomval ;

Repeat those lines as many times as required.

With only four characters in your ind keys you will get some collisions: some random values coming up identical. Hence the insert ignore.