query-performance-gauge/readme.md

# Query Performance Gauge

This is a little script to measure the performance of queries against a Trino or MySQL instance. You can use it to run
several queries and measure how long it takes for results to come back to your local machine.

## How to use

1. First, you need to install the package in your Python installation or a virtual environment. If you have our Google
   Drive Shared Drive replicated locally, you can do it like this:

```commandline
pip install "file:///g:\shared drives\data drive\90 useful\10 query_performance_gauge"
```

You will install whatever version is in the shared drive at that point. Depending on what you want to achieve, you 
might want to instead make a copy of the repository in your own local machine and install from there. That way, you 
won't be affected by someone making `git checkout` in the shared drive.

2. Afterwards, you need to make a credentials and a query config file. See below details on how to compose one.

3. Once you have your config file ready, run the following command from the terminal.

```commandline
measure_query_performance --credentials my_credentials.json --queries my_queries.json
```

4. Results will be printed in your console as they are available. If instead you would like to store them in a file, a 
   quick and easy hack is to redirect output in Powershell to a file. You can do it like this:

```commandline
measure_query_performance --credentials my_credentials.json --queries my_queries.json | Out-File -FilePath my_results.txt
```

## Composing config files

The application takes two config files: one for the credentials and connection details, one for the queries to run. 
You can mix and match several of both (as in, you can run the same query set at different dbs, or you can have 
multiple query sets run on the same database).

You can take a look at examples for different setups in `config_examples`. If you want to make a new config file, it
will probably be easier for you to start from one of those templates. The legacy examples should be ignored unless 
you want to understand outdated config files.

A few notes:
- The valid engines are `"trino"` and `"mysql"`.
- You can place as many queries as you would like in the `queries_to_measure` list.
- I advice you to make the first query a silly, fast query such as `SELECT 1` to validate your connection and
  quickly confirm that everything is set up properly.

## Other features
- The connection to the database can be made through an SSH tunnel. See the examples in `config_examples` to 
  understand how to configure it.

## A few more details

- Queries are run sequentially, as in the second query will only start after the first query is finished.
- The script will run all queries, even if there is an exception when running one or more of them. If one query 
  fails, the error traceback will be printed so you can debug and the script will move on to the next query.
- For this to work, your local machine must have access and permission to the connection you are targeting, so
  remember to set up VPNs and other necessary configs properly.
- A peculiarity: when using MySQL through an SSH tunnel, the port number used by the remote MySQL should be
  free in your local machine. That means that if the MySQL database is listening on port 3306, your local machine
  should have port 3306 free before running this.
- Queries in JSON must be stored in a single line. A bit of a headache, I know. JSON limitations. You can use [this
  webpage](https://sqlformatter.org/) to easily jump between prettified and one-line formats for any query.