8. Schema Migration (Page Level)

Quick Start

Using the following as a guide, we will walk you through the setup of BullFrog.

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:page_level_migration bash

# Output:
#
# Unable to find image 'gangliao/bullfrog:page_level_migration' locally    
# latest: Pulling from gangliao/bullfrog
# ......: Pull complete
# ......: Pull complete
# ......: Pull complete
# Digest: sha256:......
# Status: Downloaded newer image for gangliao/bullfrog:page_level_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 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_bullfrog_page_level.xml  --execute=true -s 1 -o page_level --port=5433 --bgthread=proj
   
   # 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=60001761285, measuredRequests=18859) = 314.307440250335 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/page_level.csv
   # 05:37:20,597 (DBWorkload.java:760) INFO  - Output summary data into file: results/page_level.summary
   # 05:37:20,625 (DBWorkload.java:767) INFO  - Output DBMS parameters into file: results/page_level.params
   # 05:37:20,647 (DBWorkload.java:774) INFO  - Output DBMS metrics into file: results/page_level.metrics
   # 05:37:20,720 (DBWorkload.java:781) INFO  - Output experiment config into file: results/page_level.expconfig
   # 05:37:20,806 (DBWorkload.java:798) INFO  - Output throughput samples into file: results/page_level.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,712.000,2364.868,168.407,1550.659,2567.713,3230.291,3589.528,3711.937,3779.423,3860.039,0.000
  1,691.000,4512.069,3787.171,4206.411,4485.847,4869.090,5000.621,5032.597,5057.929,5069.224,0.000
  2,758.000,5603.326,5039.139,5256.477,5577.461,5931.733,6134.997,6210.070,6272.212,6436.690,0.000
  3,704.000,6897.870,6278.219,6606.939,6895.471,7206.108,7349.909,7402.137,7444.071,7457.448,0.000
  4,709.000,7896.188,7440.805,7693.878,7900.688,8099.032,8223.158,8274.115,8312.713,8325.400,0.000
  5,703.000,8710.400,8292.102,8520.471,8723.015,8902.306,9050.818,9079.188,9104.334,9123.162,0.000
  6,654.000,9561.448,9097.063,9359.371,9585.408,9770.970,9878.478,9889.810,9902.142,9919.502,0.000
  7,726.000,10421.813,9890.172,10149.577,10413.059,10715.573,10847.449,10899.646,10930.480,10943.374,0.000
  8,657.000,11378.734,10914.177,11230.721,11400.640,11577.784,11673.898,11684.782,11694.149,11707.746,0.000
  9,682.000,12088.521,11682.674,11836.498,12068.099,12318.707,12465.025,12516.511,12551.864,12577.518,0.000
  10,664.000,13081.353,12551.863,12785.998,13038.559,13327.614,13659.423,13712.227,13747.654,13763.128,0.000
  11,609.000,14211.017,13743.022,13992.495,14223.733,14457.768,14542.945,14549.282,14562.897,14579.992,0.000
  12,330.000,14541.301,14492.022,14527.956,14539.435,14555.876,14565.886,14571.603,14582.368,14607.227,0.000
  13,330.000,14504.595,14462.681,14488.573,14501.432,14519.885,14534.590,14539.572,14549.646,14562.862,0.000
  14,318.000,14548.003,14498.121,14535.395,14550.862,14560.629,14569.306,14574.509,14581.304,14590.410,0.000
  15,322.000,14530.863,14449.359,14516.643,14540.421,14550.608,14556.985,14561.601,14573.355,14588.410,0.000
  16,309.000,14497.081,14441.554,14486.186,14500.232,14508.739,14516.190,14520.049,14527.254,14528.015,0.000
  17,256.000,14482.925,14425.675,14469.761,14485.479,14498.860,14506.921,14513.514,14526.631,14535.859,0.000
  18,301.000,14408.866,14312.092,14383.714,14422.376,14437.961,14446.607,14450.587,14457.513,14462.052,0.000
  19,335.000,14358.220,14308.169,14340.713,14364.033,14374.913,14382.317,14389.875,14395.678,14399.694,0.000
  20,318.000,14372.542,14306.900,14362.487,14376.682,14388.650,14398.019,14401.806,14408.993,14413.250,0.000
  21,309.000,14328.303,14294.121,14317.902,14327.720,14337.173,14348.568,14353.268,14360.336,14375.276,0.000
  22,311.000,14332.280,14287.586,14318.136,14332.195,14345.657,14356.493,14361.840,14377.681,14394.399,0.000
  23,301.000,14328.462,14293.160,14319.216,14329.985,14338.755,14345.900,14350.595,14355.165,14361.921,0.000
  24,319.000,14343.716,14307.518,14333.045,14342.581,14353.327,14363.061,14367.730,14377.107,14400.200,0.000
  25,329.000,14350.836,14301.812,14335.366,14345.923,14366.806,14384.961,14393.809,14403.298,14414.365,0.000
  26,350.000,14344.637,14306.253,14334.369,14343.984,14352.204,14363.476,14371.283,14385.795,14393.126,0.000
  27,331.000,14382.473,14315.158,14365.453,14385.529,14400.250,14414.819,14420.223,14432.356,14437.734,0.000
  28,313.000,14345.150,14312.267,14334.006,14344.017,14354.983,14364.021,14372.626,14386.780,14399.992,0.000
  29,320.000,14325.798,14285.010,14316.853,14326.170,14335.142,14345.099,14350.840,14359.973,14373.023,0.000
  30,331.000,14351.509,14303.711,14337.861,14352.213,14365.538,14375.359,14379.273,14387.266,14395.671,0.000
  31,339.000,14308.739,14272.128,14295.982,14305.215,14320.107,14334.368,14342.577,14351.452,14356.311,0.000
  32,320.000,14282.706,14220.242,14256.431,14288.230,14306.289,14317.626,14322.935,14340.255,14381.106,0.000
  33,348.000,14272.050,14232.066,14253.013,14264.184,14292.799,14305.240,14312.364,14322.746,14325.832,0.000
  34,309.000,14282.062,14231.775,14269.504,14281.517,14294.825,14306.884,14313.688,14325.816,14332.125,0.000
  35,309.000,14215.383,14175.384,14202.106,14212.331,14229.288,14243.813,14248.557,14255.194,14261.181,0.000
  36,335.000,14240.480,14181.003,14223.329,14242.341,14259.160,14270.195,14275.763,14284.742,14300.630,0.000
  37,277.000,14234.545,14128.872,14211.168,14232.835,14262.191,14275.861,14283.701,14299.669,14307.979,0.000
  38,288.000,14133.160,14086.159,14119.076,14133.888,14147.588,14157.132,14161.571,14174.608,14176.356,0.000
  39,284.000,14111.319,14042.671,14091.726,14110.292,14128.209,14139.899,14146.988,14368.589,14387.573,0.000
  40,247.000,14091.127,14047.764,14075.438,14085.672,14099.049,14113.836,14124.375,14278.464,14324.139,0.000
  41,286.000,14074.985,14011.052,14059.013,14077.538,14091.197,14100.971,14107.810,14116.460,14120.817,0.000
  42,290.000,14061.488,14004.342,14036.378,14056.461,14089.411,14102.422,14106.294,14115.457,14118.169,0.000
  43,330.000,14053.367,14003.378,14041.377,14052.995,14065.134,14078.776,14086.231,14097.099,14105.993,0.000
  44,283.000,14013.372,13969.957,13998.241,14012.300,14028.634,14038.717,14044.430,14058.448,14068.239,0.000
  45,301.000,13996.808,13939.726,13974.018,13999.695,14017.481,14033.973,14039.837,14048.049,14055.781,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