3. Join Migration

Quick Start

Using the following as a guide, we will walk you through the setup of BullFrog. To make it smoother, we record terminal sessions and share them on the web.

TODO

I. Docker Image

This tutorial assumes you have a current version of Docker installed on your machine. If you do not have Docker installed, choose your preferred operating system below to download Docker:

• Download Docker Desktop for Mac
• Download Docker Desktop for Windows
• Install Docker Engine on Linux

After you installed Docker, you can issue a command to pull our docker image and start a container:

# Pulls Docker image and run it on your local machine.
$ docker run --rm -it -d  --name bullfrog gangliao/bullfrog:join_migration bash

# Output:
#
# Unable to find image 'gangliao/bullfrog:join_migration' locally    
# latest: Pulling from gangliao/bullfrog
# ......: Pull complete
# ......: Pull complete
# ......: Pull complete
# Digest: sha256:......
# Status: Downloaded newer image for gangliao/bullfrog:join_migration  

# Enters the container.
$ docker exec -u postgres -it bullfrog bash

# Output:
#
# postgres@968c58c4b04d:/$

II. BullFrog Experiments

1. Rebooting a DB instance:

   # Deploys the postgres backend
   $ cd /home/postgres/BullFrog && ./deploy.sh
   
   # Output:
   #
   # +++ case $- in
   # +++ return
   # ++ rm -rf /home/postgres/BullFrog/build/data
   # ++ initdb -D /home/postgres/BullFrog/build/data
   # The files belonging to this database system will be owned by user "postgres".
   # This user must also own the server process.
   # 
   # The database cluster will be initialized with locale "C".
   # The default database encoding has accordingly been set to "SQL_ASCII".
   # The default text search configuration will be set to "english".
   # 
   # Data page checksums are disabled.
   # 
   # creating directory /home/postgres/BullFrog/build/data ... ok
   # creating subdirectories ... ok
   # selecting default max_connections ... 100
   # selecting default shared_buffers ... 128MB
   # selecting dynamic shared memory implementation ... posix
   # creating configuration files ... ok
   # running bootstrap script ... Shared Global Bitmap created!
   # ok
   # performing post-bootstrap initialization ... ok
   # syncing data to disk ... ok
   # 
   # WARNING: enabling "trust" authentication for local connections
   # You can change this by editing pg_hba.conf or using the option -A, or
   # --auth-local and --auth-host, the next time you run initdb.
   # 
   # Success. You can now start the database server using:
   # 
   #     pg_ctl -D /home/postgres/BullFrog/build/data -l logfile start
   # 
   # ++ cp /home/postgres/postgresql.conf /home/postgres/BullFrog/build/data/
   # ++ pg_ctl -D /home/postgres/BullFrog/build/data -o '-F -p 5433' start

2. Run a TPC-C Benchmark where data is already loaded into the database.

   • Please re-execute all commands in the step 2 if the benchmark fails to run.

   $ cd /home/postgres/BullFrog-Oltpbench
   
   # Clean tuples in new tables with new schemas
   $ psql -h localhost -p 5433 tpcc -f /home/postgres/BullFrog-Oltpbench/clean_new_tables.sql
   
   # Output:
   #
   # DROP TABLE
   # CREATE TABLE
   # DROP TABLE
   # CREATE TABLE
   # CREATE INDEX
   # CREATE INDEX
   # DROP TABLE
   # CREATE TABLE
   # CREATE INDEX
   # CREATE INDEX
   # CREATE INDEX
   # CREATE INDEX
   # CREATE INDEX
   # CREATE INDEX
   # CREATE VIEW
   
   # Clean shared memory via restarting database
   $ pg_ctl -D $PGDATA restart 
   
   # Output:
   #
   # waiting for server to shut down....
   # 2021-02-15 05:22:27.763 UTC [1208] LOG:  background worker "logical replication launcher" (PID 1214) exited with exit code 1
   # 2021-02-15 05:22:27.964 UTC [1210] LOG:  shutting down
   # 2021-02-15 05:22:28.557 UTC [1208] LOG:  database system is shut down
   #  done
   # server stopped
   # waiting for server to start.....2021-02-15 05:22:29.579 UTC [1275] LOG:  listening on IPv4 address "127.0.0.1", port 5433
   # 2021-02-15 05:22:29.579 UTC [1275] LOG:  could not bind IPv6 address "::1": Cannot assign requested address
   # 2021-02-15 05:22:29.579 UTC [1275] HINT:  Is another postmaster already running on port 5433? If not, wait a few seconds and retry.
   # 2021-02-15 05:22:29.579 UTC [1275] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5433"
   # Shared Global Bitmap created!
   # 2021-02-15 05:22:29.863 UTC [1276] LOG:  database system was shut down at 2021-02-15 05:22:28 UTC
   # 2021-02-15 05:22:29.865 UTC [1275] LOG:  database system is ready to accept connections
   #  done
   # server started
   
   # run benchmark
   $ ./oltpbenchmark -b tpcc -c config/pgtpcc_lazy_join.xml  --execute=true -s 1 -o lazy_join --port=5433 --bgthread=join
   
   # Output:
   # ...
   # 05:37:19,860 (ThreadBench.java:473) INFO  - TERMINATE :: Waiting for all terminals to finish ..
   # 05:37:20,010 (ThreadBench.java:534) INFO  - Attempting to stop worker threads and collect measurements
   # 05:37:20,012 (ThreadBench.java:255) INFO  - Starting WatchDogThread
   # 05:37:20,059 (DBWorkload.java:886) INFO  - ======================================================================
   # 05:37:20,059 (DBWorkload.java:887) INFO  - Rate limited reqs/s: Results(nanoSeconds=60000173489, measuredRequests=25774) = 429.56542458540093 requests/sec
   # 05:37:20,071 (DBWorkload.java:708) INFO  - Upload Results URL: com.oltpbenchmark.util.ResultUploader@4690b489
   # 05:37:20,073 (DBWorkload.java:741) INFO  - Output Raw data into file: results/lazy_join.csv
   # 05:37:20,597 (DBWorkload.java:760) INFO  - Output summary data into file: results/lazy_join.summary
   # 05:37:20,625 (DBWorkload.java:767) INFO  - Output DBMS parameters into file: results/lazy_join.params
   # 05:37:20,647 (DBWorkload.java:774) INFO  - Output DBMS metrics into file: results/lazy_join.metrics
   # 05:37:20,720 (DBWorkload.java:781) INFO  - Output experiment config into file: results/lazy_join.expconfig
   # 05:37:20,806 (DBWorkload.java:798) INFO  - Output throughput samples into file: results/lazy_join.res
   # 05:37:20,806 (DBWorkload.java:801) INFO  - Grouped into Buckets of 1 seconds

Experiment Results

• Note: We have simplified the experimental environment and dataset size, so the results may be different from the paper. Since Docker for Mac runs in a LinuxKit VM, to get the best performance, please run the experiment on bare metal machines.

  time(sec), throughput(req/sec), avg_lat(ms), min_lat(ms), 25th_lat(ms), median_lat(ms), 75th_lat(ms), 90th_lat(ms), 95th_lat(ms), 99th_lat(ms), max_lat(ms), tp (req/s) scaled
  0,702.000,928.315,38.456,697.523,976.097,1267.936,1370.440,1401.696,1440.723,1483.524,0.001
  1,700.000,1658.536,1439.731,1565.040,1630.787,1740.908,1815.425,1906.117,1920.100,1960.149,0.001
  2,701.000,2087.041,1899.538,2009.148,2081.767,2169.391,2249.250,2276.897,2301.010,2319.782,0.000
  3,700.000,2533.398,2289.754,2403.442,2585.453,2644.822,2703.940,2723.499,2749.547,2780.066,0.000
  4,700.000,2882.955,2729.792,2810.839,2866.627,2959.789,3004.009,3010.657,3025.494,3045.186,0.000
  5,700.000,3321.363,3001.890,3180.933,3323.507,3502.327,3563.267,3592.318,3614.519,3696.136,0.000
  6,698.000,3813.841,3601.038,3691.310,3762.677,3962.812,4032.707,4064.856,4118.255,4190.431,0.000
  7,701.000,4509.569,4097.896,4357.795,4537.078,4668.609,4733.938,4761.360,4793.593,4839.067,0.000
  8,700.000,5002.553,4784.384,4913.568,4999.545,5089.677,5174.762,5194.995,5217.089,5235.786,0.000
  9,700.000,5502.337,5203.575,5414.930,5552.101,5621.167,5675.462,5695.562,5718.253,5776.099,0.000
  10,701.000,5941.147,5690.860,5818.693,5897.037,6109.059,6170.632,6198.877,6220.482,6266.665,0.000
  11,699.000,6493.051,6202.149,6311.034,6446.042,6679.908,6788.293,6814.104,6828.125,6851.987,0.000
  12,700.000,7046.853,6813.669,6954.499,7087.048,7138.729,7174.464,7196.344,7219.812,7273.466,0.000
  13,700.000,7457.874,7209.634,7338.208,7483.448,7557.427,7626.871,7654.447,7701.682,7724.127,0.000
  14,700.000,8263.086,7687.966,7825.178,8420.131,8545.558,8617.532,8644.124,8662.302,8726.493,0.000
  15,700.000,8909.423,8636.176,8772.402,8931.004,9015.701,9139.224,9169.826,9189.842,9223.541,0.000
  16,700.000,9468.882,9177.649,9311.539,9437.688,9640.904,9717.615,9735.835,9762.755,9849.223,0.000
  17,700.000,10083.541,9739.953,9960.423,10077.704,10243.531,10294.378,10306.035,10328.387,10348.367,0.000
  18,700.000,10590.977,10305.844,10476.799,10580.305,10723.965,10789.243,10815.625,10835.043,10937.935,0.000
  19,700.000,11113.452,10824.610,10969.813,11121.476,11241.086,11332.167,11394.025,11417.705,11584.409,0.000
  20,700.000,11788.259,11399.700,11690.572,11804.955,11907.802,11960.474,11977.579,12015.616,12064.795,0.000
  21,701.000,12244.650,11996.572,12127.658,12252.249,12371.739,12437.498,12463.051,12491.376,12547.813,0.000
  22,699.000,12727.477,12481.219,12622.145,12719.150,12849.683,12938.544,12966.304,12993.866,13064.950,0.000
  23,701.000,13334.265,12985.201,13120.607,13354.366,13493.170,13611.442,13687.766,13716.250,13743.076,0.000
  24,700.000,13951.937,13704.971,13877.164,13949.301,14027.667,14118.724,14129.841,14168.841,14241.868,0.000
  25,513.000,14284.082,14153.189,14275.351,14290.757,14302.809,14314.961,14325.755,14369.386,14412.776,0.000
  26,415.000,14303.068,14285.876,14291.211,14296.987,14308.154,14323.899,14333.118,14395.023,14407.492,0.000
  27,400.000,14303.870,14284.956,14290.558,14297.726,14308.079,14322.200,14334.527,14420.859,14429.503,0.000
  28,425.000,14302.979,14286.173,14290.445,14297.012,14307.702,14323.006,14332.117,14398.113,14422.628,0.000
  29,434.000,14302.480,14283.983,14290.289,14296.159,14306.907,14320.949,14337.661,14395.376,14445.170,0.000
  30,447.000,14302.262,14283.118,14290.230,14296.646,14307.671,14319.217,14332.421,14394.876,14431.425,0.000
  31,439.000,14302.583,14282.329,14290.424,14297.673,14307.829,14323.682,14333.969,14388.292,14438.222,0.000
  32,426.000,14302.687,14281.904,14290.618,14297.673,14308.193,14321.845,14331.935,14387.666,14473.355,0.000
  33,340.000,14307.636,14283.538,14291.402,14300.480,14312.448,14332.304,14352.839,14407.714,14544.036,0.000
  34,412.000,14303.990,14284.043,14290.850,14298.043,14308.694,14322.397,14336.282,14405.653,14449.083,0.000
  35,430.000,14302.438,14282.923,14290.842,14296.303,14307.841,14321.548,14331.582,14377.487,14450.785,0.000
  36,421.000,14303.398,14285.682,14291.491,14297.427,14310.191,14321.729,14335.460,14376.606,14409.048,0.000
  37,396.000,14305.162,14286.726,14291.982,14298.067,14313.008,14327.977,14339.534,14404.909,14477.285,0.000
  38,400.000,14303.263,14284.872,14290.732,14298.049,14307.633,14318.421,14333.817,14438.377,14572.470,0.000
  39,411.000,14303.324,14282.223,14290.332,14295.368,14308.085,14323.674,14337.866,14409.489,14447.065,0.000
  40,454.000,14301.726,14275.829,14290.130,14297.529,14306.666,14321.369,14336.225,14380.579,14403.151,0.000
  41,431.000,14302.173,14281.564,14290.125,14296.793,14307.234,14320.064,14337.686,14396.163,14436.686,0.000
  42,424.000,14303.388,14285.278,14291.120,14297.284,14309.583,14321.853,14331.100,14400.899,14418.151,0.000
  43,550.000,14297.228,14278.070,14288.997,14293.444,14303.126,14308.960,14312.510,14359.562,14368.687,0.000
  44,462.000,14299.882,14277.946,14288.602,14292.767,14303.495,14311.428,14332.432,14413.141,14438.258,0.000
  45,340.000,14299.251,14278.900,14290.087,14294.001,14305.560,14314.207,14319.761,14357.679,14403.131,0.000

III. Stop Database & Container

1. You may want to shut down the database for any reason:

   $ cd /home/postgres/BullFrog && ./shutdown.sh
   
   # Output:
   #
   # ++ source /home/postgres/.bashrc
   # +++ case $- in
   # +++ return
   # ++ pg_ctl -D /home/postgres/BullFrog/build/data stop
   # waiting for server to shut down.... done
   # server stopped
   # ++ pg_ctl -D /home/postgres/BullFrog/build/data status
   # pg_ctl: no server running

2. You may stop a running container when you finish the tutorial:

   $ docker stop bullfrog