Load test with databases. Best practices , tools [closed]

Question

I need to design a way to stress a clon of my production databases. At the moment, I'm using mysql percona xtradb with tree nodes running in google cloud.

I'm using haproxy as load database balancer.

All backend microservices in my company are configured to connect through haproxy to the node1 for writes and to the nodes 2 and 3 for select.

I want to make my database three times bigger.

I have cloned my production database using backups in other google instances now I'm going to populate this database inserting more records.

I want to stress my new database x3 bigger, using in my load test the top queries with the poor performance that I have found using my tuning tool (Percona monitoring and management PMM)

I have thinking in three options:

• Use mysqlslap
• Use Apache JMeter using jdbc module
• Design my own stress tool using python with flask to expose a API with my custom loadTest and then I can use JMeter or soapUi to stress with different multithread profiles my API and it runs the queries.

I would like to have feedback on how to approach this or to ask if anybody knows other tools to stress database in a multithread environment.

Thanks.

Answer 1

Take a snapshot of your production database and at the same time enable the general log. Record 24 hours of a general log on what is a fairly typical day.

Restore the data to clean server.

Write a few lines of your favourite $scripting_language to parse out the general log and split it up into n files where n is at least the number of CPU threads you have in the server. Also, separate SELECTs, INSERTs, UPDATEs, and DELETEs into separate sets of files.

Replay them in parallel, SELECTs only, to prime the caches. Then do it again and time the 2nd run.

Now you have a baseline to measure everything else against. From there on, triple up the number of rows in the table by adding more rows, and replay it again to measure the performance.

No generic benchmark will ever come close to producing as meaningful a benchmark number as this method.