JOSS Review – Functionality

[simon-lewis]

JOSS Review – Functionality by @simon-lewis

see JOSS

Summary: good response from main script, noting there is a lag on startup which gets reflected in the results – it is possible this requires configuration of a connection value (see “Connection Latency” in the documentation - not tested). Could not test dbmsdashboard properly; possibly due to dependencies / permissions and/or local environment. Details below.

• Hardware metrics not tested (Prometheus) • dbmsdashboard tested but failed, possibly due to local environment / un-met dependencies • Randomisation not tested • Connection Latency configuration not tested but could be significant as initial startup shows lag • Basic comparison testing completed between two MySQL schemas on single MySQL containerised instance. (Configs and DB scripts attached for info) • Tested within container with local MySQL instance • Tested in Linux VM with containerised MySQL instance • Tested in Windows PC to remote, containerised MySQL instance

Good results from scripts, with the following 3 example outputs included:

Q1: Count all rows in test - timerExecution
+-------------+-------+--------+---------+--------+----------+-------+----------+-------+-------+
| DBMS [ms]   |     n |   mean |   stdev |   cv % |   qcod % |   iqr |   median |   min |   max |
+=============+=======+========+=========+========+==========+=======+==========+=======+=======+
| MySQL-1     | 10.00 |  10.23 |   18.78 | 183.57 |    11.33 |  0.85 |     3.74 |  3.15 | **63.35** |
+-------------+-------+--------+---------+--------+----------+-------+----------+-------+-------+
| MySQL-2     | 10.00 |   3.61 |    1.07 |  29.76 |    20.57 |  1.45 |     3.03 |  2.68 |  5.57 |
+-------------+-------+--------+---------+--------+----------+-------+----------+-------+-------+
Experiment 1661736663 has been finished

Q1: Count all rows in test - timerExecution
+-------------+-------+--------+---------+--------+----------+-------+----------+-------+--------+
| DBMS [ms]   |     n |   mean |   stdev |   cv % |   qcod % |   iqr |   median |   min |    max |
+=============+=======+========+=========+========+==========+=======+==========+=======+========+
| MySQL-1     | 10.00 |  24.68 |   51.47 | 208.57 |    39.10 |  5.55 |     5.74 |  3.75 | **169.53** |
+-------------+-------+--------+---------+--------+----------+-------+----------+-------+--------+
| MySQL-2     | 10.00 |   3.15 |    0.59 |  18.58 |     8.65 |  0.54 |     2.93 |  2.46 |   4.37 |
+-------------+-------+--------+---------+--------+----------+-------+----------+-------+--------+
Experiment 1661736700 has been finished

Q1: Count all rows in test - timerExecution
+-------------+-------+--------+---------+--------+----------+-------+----------+-------+-------+
| DBMS [ms]   |     n |   mean |   stdev |   cv % |   qcod % |   iqr |   median |   min |   max |
+=============+=======+========+=========+========+==========+=======+==========+=======+=======+
| MySQL-1     | 10.00 |  10.40 |   18.16 | 174.67 |    10.02 |  0.94 |     4.54 |  3.98 | **62.05 |**
+-------------+-------+--------+---------+--------+----------+-------+----------+-------+-------+
| MySQL-2     | 10.00 |   3.82 |    0.75 |  19.74 |    15.02 |  1.10 |     3.96 |  2.77 |  4.92 |
+-------------+-------+--------+---------+--------+----------+-------+----------+-------+-------+
Experiment 1661736721 has been finished

Note the following – MAX values always come from initial query on first scheme (db01) so assumed to be a result of Connection Latency:

Run 1: INFO:singleRun:numRun 1: execution [ms]: 63.35214083082974 Run 2: INFO:singleRun:numRun 1: execution [ms]: 169.5333649404347 Run 3: INFO:singleRun:numRun 1: execution [ms]: 62.05371394753456

This skews the results so recommend documentation is updated to highlight this risk and - if it resolves the issue - to explain and recommend the use of Connection Latency values in the configuration.

dbmsdashboard -r 1661736721

Appeared to work but no results shown (note Permissions errors in output). “(“PermissionError: [Errno 13] Permission denied: '/connections.config'”)”

END. Evaluation completed with the limitations / recommendations highlighted above.

[simon-lewis]

Eval.zip

[perdelt]

Hi @simon-lewis

Many thanks for your thorough and helpful tests!

You can start the dashboard by a simple dbmsdashboard. The result folder flag -r is only necessary if you also have used it during benchmarking. It points to the (parent) folder where the results should be put at. Otherwise the result folders are just put in the current path and dbmsdashboard looks there for them.

It is common to exclude the first run in benchmarking, because of the cold data / startup lag situation. I will add a comment to the documentation.

However I myself would like to see, what part of the effect is client-sided (the package) or server-sided (the DBMS). I will think about a test.

timerExecution for sure only starts measuring when a connection has been established.

You can (kind of) verify this by upgrading to the current version (I added some output infos and changed default behaviour), and use the query file attached.

{
  'name': 'Some simple queries',
  'connectionmanagement': {
        'timeout': 5, # in seconds
        'singleConnection': False, # not single connection per stream
        'runsPerConnection': 1     # reconnect for every execution
  },
  'queries':
  [
    {
      'title': "Static query",
      'query': "SELECT 1",
      'numRun': 10,
      'timer':
      {
        'connection':
        {
          'active': True,
          'delay': 0
        },
      }
    },
    {
      'title': "Count all rows in test",
      'query': "SELECT COUNT(*) FROM posts",
      'numRun': 10,
      'timer':
      {
        'connection':
        {
          'active': True,
          'delay': 0
        },
      }
    },
  ]
}

The connectionmanagement tells the benchmarker to reconnect for every execution of every query. I will put more information about these setting in the documentation, too.

I then can see

numRun 1: connection [ms]: 1363.3746
numRun 1: execution [ms]: 156.53840000000008
numRun 2: connection [ms]: 305.8920999999999
numRun 2: execution [ms]: 31.420500000000295
numRun 3: connection [ms]: 306.9272999999999
numRun 3: execution [ms]: 31.359700000000323
numRun 4: connection [ms]: 289.99470000000025
numRun 4: execution [ms]: 30.865400000000598
numRun 5: connection [ms]: 290.58910000000003
numRun 5: execution [ms]: 30.43529999999972
numRun 6: connection [ms]: 292.0455999999998
numRun 6: execution [ms]: 30.581800000000214
numRun 7: connection [ms]: 299.7739999999993
numRun 7: execution [ms]: 33.33660000000016
numRun 8: connection [ms]: 314.39169999999984
numRun 8: execution [ms]: 31.637899999999775
numRun 9: connection [ms]: 304.4646000000002
numRun 9: execution [ms]: 33.20440000000069
numRun 10: connection [ms]: 318.0198000000001
numRun 10: execution [ms]: 33.112400000000264

so the first execution is slower and this has nothing to do with connecting.

[perdelt]

queries.zip

[simon-lewis]

Thanks Patrick. All good. Sorry for delayed response but I have been away for 3 week.

I'll close this as responded.

Just to note that the dashboard problems were probably due to my environment - it is not an issue for this review - but I did run it as you and the documentation recommends. It is just that I could not get it to function in any of my environments.