search

morphismtech / squeal

Squeal, a deep embedding of SQL in Haskell
350 stars 32 forks source link

Added benchmarks for DB queries #204

Closed tuomohopia closed 4 years ago

tuomohopia commented 4 years ago

This PR adds benchmarks running against an actual database.

So far INSERT manipulations are only tested, but the skeleton can be used to facilitate any queries.

Other changes:

Again, the bench folder contains a lot of noise, mostly functionality for building up other benchmark groups.

CI Pipeline

If this qualifies for a merge, may I suggest adding stack bench a part of the CI pipeline? I've never used CircleCI so I don't want to go around messing with it, but a separate job for benchmarks would help us keep an eye on performance regressions.

tuomohopia commented 4 years ago

Also added parameterized SELECT queries to benchmark just now.

With this PR, there's now:

tuomohopia commented 4 years ago

It doesn't appear to be showing the benchmark result tables with the last commit. I don't see anything wrong with my code so I filed a bug report, let's see if the problem was in my code after all or not: https://github.com/vincenthz/hs-gauge/issues/99

tuomohopia commented 4 years ago

With the latest commit, the benchmark analysis printing is fixed. This should be usable for benchmarking now.

@echatav is there something else you require from this PR before this can be merged?

echatav commented 4 years ago

I'm getting a weird error when trying to run the benchmarks with stack bench. I'm probably doing something wrong. It worked fine in your last PR I think.

EEM109LMBP-1:squeal eitan$ stack bench
squeal-postgresql> benchmarks
Cabal-simple_mPHDZzAJ_2.4.0.1_ghc-8.6.5: No benchmarks enabled. Did you
remember to configure with '--enable-benchmarks'?

--  While building package squeal-postgresql-0.5.1.0 using:
      /Users/eitan/.stack/setup-exe-cache/x86_64-osx/Cabal-simple_mPHDZzAJ_2.4.0.1_ghc-8.6.5 --builddir=.stack-work/dist/x86_64-osx/Cabal-2.4.0.1 bench benchmarks
    Process exited with code: ExitFailure 1
echatav commented 4 years ago

Haha, never mind, a simple stack clean fixed it. I think this is good. I'll look more carefully and probably merge soon.

echatav commented 4 years ago

Let's add it as a CI step. In .circleci/config.yml add - run: stack bench in steps

tuomohopia commented 4 years ago

DB connection may fail, not sure if the connection string is right in the benchmarks.

Also, if I understand the pipeline correctly, if any test fails it won't even attempt to run the benchmarks with the current configuration. Which I suppose is OK.

echatav commented 4 years ago

Ok, so looking at the error in CI, it looks a lot like an error I had in hedgehog tests.

This PR has the fix.

You can read about what causes the problem here.

tuomohopia commented 4 years ago

Yeah, the db conn string is the issue now. Do you want the bench to build it from the supplied ENV vars?

tuomohopia commented 4 years ago

The benchmark results are also all over the place, locally on my own computer I get pretty homogeneous results. Do you?

echatav commented 4 years ago

For me both locally, and in CI, the connection string "host=localhost port=5432 dbname=exampledb" works. The results look maybe a bit heterogeneous to me; here's two

squeal-postgresql> benchmarks
Running 1 benchmarks...        
Benchmark benchmarks: RUNNING...
benchmarked Render Queries/createUser: weak head normal form
time                 7.626 ns   (7.393 ns .. 8.002 ns)
                     0.958 R²   (0.924 R² .. 0.983 R²)
mean                 8.883 ns   (8.569 ns .. 9.357 ns)
std dev              1.269 ns   (1.057 ns .. 1.507 ns)
variance introduced by outliers: 78% (severely inflated)

benchmarked Render Queries/createUser: normal form
time                 8.332 ns   (7.570 ns .. 9.282 ns)
                     0.963 R²   (0.942 R² .. 0.991 R²)
mean                 9.023 ns   (8.703 ns .. 9.590 ns)
std dev              1.504 ns   (1.191 ns .. 1.925 ns)
variance introduced by outliers: 84% (severely inflated)

benchmarked Render Queries/userDetails: weak head normal form
time                 8.520 ns   (8.341 ns .. 8.656 ns)
                     0.997 R²   (0.995 R² .. 0.998 R²)
mean                 8.716 ns   (8.624 ns .. 8.804 ns)
std dev              306.9 ps   (264.0 ps .. 403.6 ps)
variance introduced by outliers: 18% (moderately inflated)

benchmarked Render Queries/userDetails: normal form
time                 8.410 ns   (8.154 ns .. 8.649 ns)
                     0.991 R²   (0.985 R² .. 0.995 R²)
mean                 9.733 ns   (9.427 ns .. 10.21 ns)
std dev              1.323 ns   (966.9 ps .. 1.842 ns)
variance introduced by outliers: 77% (severely inflated)

benchmarked Render Queries/insertDeviceDetails: weak head normal form
time                 7.831 ns   (7.686 ns .. 8.009 ns)
                     0.997 R²   (0.995 R² .. 0.999 R²)
mean                 8.287 ns   (8.181 ns .. 8.414 ns)
std dev              379.5 ps   (315.6 ps .. 519.4 ps)
variance introduced by outliers: 26% (moderately inflated)

benchmarked Render Queries/insertDeviceDetails: normal form
time                 9.730 ns   (8.441 ns .. 11.06 ns)
                     0.930 R²   (0.897 R² .. 0.974 R²)
mean                 9.376 ns   (9.078 ns .. 9.828 ns)
std dev              1.214 ns   (915.0 ps .. 1.553 ns)
variance introduced by outliers: 72% (severely inflated)

Initialized Schema & corresponding tables for Database
benchmarking Run individual INSERTs against DB using a connection pool/INSERT: add users to the table usebenchmarked Run individual INSERTs against DB using a connection pool/INSERT: add users to the table users/Run individual INSERT statement
time                 445.2 μs   (429.5 μs .. 457.5 μs)
                     0.995 R²   (0.990 R² .. 0.999 R²)
mean                 445.1 μs   (439.7 μs .. 454.2 μs)
std dev              16.55 μs   (7.528 μs .. 29.62 μs)
variance introduced by outliers: 11% (moderately inflated)

Dropped all database tables    
Initialized Schema & corresponding tables for Database
benchmarking Run individual SELECTs against DB using a connection pool/SELECT: fetch users from the tablebenchmarked Run individual SELECTs against DB using a connection pool/SELECT: fetch users from the table users individually/Fetch a single user
time                 270.1 μs   (201.3 μs .. 323.2 μs)
                     0.942 R²   (0.894 R² .. 0.985 R²)
mean                 300.5 μs   (294.3 μs .. 306.7 μs)
std dev              10.47 μs   (8.160 μs .. 12.73 μs)

Dropped all database tables    
Benchmark benchmarks: FINISH   
squeal-postgresql> benchmarks
Running 1 benchmarks...
Benchmark benchmarks: RUNNING...
benchmarked Render Queries/createUser: weak head normal form
time                 8.585 ns   (8.009 ns .. 9.251 ns)
                     0.958 R²   (0.936 R² .. 0.975 R²)
mean                 8.581 ns   (8.257 ns .. 8.899 ns)
std dev              1.123 ns   (953.1 ps .. 1.307 ns)
variance introduced by outliers: 75% (severely inflated)

benchmarked Render Queries/createUser: normal form
time                 7.677 ns   (7.223 ns .. 8.451 ns)
                     0.954 R²   (0.927 R² .. 0.980 R²)
mean                 8.621 ns   (8.370 ns .. 8.818 ns)
std dev              787.6 ps   (643.5 ps .. 1.029 ns)
variance introduced by outliers: 59% (severely inflated)

benchmarked Render Queries/userDetails: weak head normal form
time                 12.34 ns   (11.19 ns .. 13.76 ns)
                     0.894 R²   (0.831 R² .. 0.937 R²)
mean                 13.05 ns   (12.24 ns .. 13.97 ns)
std dev              2.909 ns   (2.258 ns .. 3.866 ns)
variance introduced by outliers: 90% (severely inflated)

benchmarked Render Queries/userDetails: normal form
time                 10.56 ns   (9.485 ns .. 11.55 ns)
                     0.948 R²   (0.908 R² .. 0.973 R²)
mean                 11.93 ns   (11.50 ns .. 12.37 ns)
std dev              1.366 ns   (1.069 ns .. 1.718 ns)
variance introduced by outliers: 67% (severely inflated)

benchmarked Render Queries/insertDeviceDetails: weak head normal form
time                 9.913 ns   (8.817 ns .. 10.66 ns)
                     0.948 R²   (0.924 R² .. 0.968 R²)
mean                 9.267 ns   (8.877 ns .. 9.640 ns)
std dev              1.242 ns   (1.090 ns .. 1.443 ns)
variance introduced by outliers: 75% (severely inflated)

benchmarked Render Queries/insertDeviceDetails: normal form
time                 12.05 ns   (10.92 ns .. 13.40 ns)
                     0.956 R²   (0.935 R² .. 0.986 R²)
mean                 11.17 ns   (10.85 ns .. 11.51 ns)
std dev              1.161 ns   (896.5 ps .. 1.665 ns)
variance introduced by outliers: 64% (severely inflated)

Initialized Schema & corresponding tables for Database
benchmarking Run individual INSERTs against DB using a connection pool/INSERT: add users to the table usebenchmarked Run individual INSERTs against DB using a connection pool/INSERT: add users to the table users/Run individual INSERT statement
time                 394.7 μs   (357.5 μs .. 473.2 μs)
                     0.892 R²   (0.789 R² .. 0.962 R²)
mean                 536.3 μs   (511.6 μs .. 563.1 μs)
std dev              63.65 μs   (54.19 μs .. 76.90 μs)
variance introduced by outliers: 57% (severely inflated)

Dropped all database tables
Initialized Schema & corresponding tables for Database
benchmarking Run individual SELECTs against DB using a connection pool/SELECT: fetch users from the tablebenchmarked Run individual SELECTs against DB using a connection pool/SELECT: fetch users from the table users individually/Fetch a single user
time                 336.2 μs   (220.0 μs .. 460.1 μs)
                     0.815 R²   (0.525 R² .. 0.975 R²)
mean                 286.0 μs   (273.5 μs .. 301.0 μs)
std dev              25.13 μs   (14.40 μs .. 32.63 μs)
variance introduced by outliers: 28% (moderately inflated)

Dropped all database tables
Benchmark benchmarks: FINISH
tuomohopia commented 4 years ago

That baseline looks solid to me, that's about what I'm getting too.

As for bench results on the CI, we're still seeing some very interesting results over there.

At least they run successfully now, however.