Been busy with work and life that did not have the time to explore new things. Or maybe I did just that I forgot. Either way, the plan for the weekends is to explore Caddy as an automated way for me to deploy my portfolio/landing page, either that or cheating and using Netlify instead. The current flow I’m using relies on Ansible to deploy the page, which is a little bit manual in a sense. Hoping to change that.
We started doing benchmarking on our DB because one of our search queries has been slowing down significantly lately, and it’s affecting our user experience. In order to optimize the performance, we need a way of measuring the changes that we were going to implement.
Benchmarking turns out to be more fun than I expected, my initial feeling was that it’s going to be such a chore and pain to do. But once the process is more automated, getting the results that surprises us is quite an interesting feeling. After going through a few different tools out there like using BENCHMARK() , DBT2, etc, I’ve settled on using mysqlslap command because it’s extremely straight forward and gives us just enough controls for the type of testing we’re doing.
Benchmark Average number of seconds to run all queries: 0.868 seconds Minimum number of seconds to run all queries: 0.793 seconds Maximum number of seconds to run all queries: 0.918 seconds Number of clients running queries: 50 Average number of queries per client: 1
Example of the results from the benchmarking, using the csv output of this, wrote a bash script to basically make a CLI tool that can allow us to fire off benchmarks against a single SQL or a directory worth of SQL files.
Usage: [-d <dir>] [-f <file>] [-c <concurrency>] [-i <iterations>]
Run SQL benchmark for a single file or for all files in a directory
Generates a csv report at the end of benchmark
-f <file> : benchmark single sql file
-d <dir> : benchmark all files in directory
-c <concurrency> : number of concurrent queries fired (default: 10)
-i <iterations> : number of iterations per query (default: 3)
-o <output> : output file name
(default: ./ddmmyy-HHMMSS-filepath.csv)
Will collaborate to write more about this with my colleague when this task is over cause I think that this script might be pretty useful for benchmarking whatever MySQL DB in general.