Database architecture load tests

This repo is a test to play around with the difference between using a SQL database vs using a file for a simple app.

The test is done with a distributed setup, based on multiple testers VMs and multiple tested VMs deploying the tested service.

App API

The tested app is a simple photo-sharing app. It exposes these user-facing HTTP JSON actions:

• login (username) -> session
• list_albums (session) -> album[] (sorted by name)
• list_photos (session, album_id) -> photo[] (sorted by date)
• publish_photo (session, album_id, photo)
• take_notifications (session) -> notification[] (sorted by photo_date)

An album is {id, name}, photo is {id, date}, notification is {id, album_id, album_name, photo_id, photo_date}. A notification is generated for all other users of the album when a new photo is published.

Then, during the test, multiple simultaneous users are simulated. There are two user profiles:

• viewers (90% of them):
  1. login
  2. list all albums
  3. pick one of the albums and list photos
  4. grab notifications
• editors (10%): same as viewer, but after step 3 publishes a new photo

Each user will be attributed one of the roles, a start offset and a sleep time between each action.

Setup

The test is set up for a given scale N:

• N users are created
• N albums are created
• each album is shared with 1 to N users, based on the Zipf law
• between 1 and N photos are added to each album, based on the Zipf law

For the setup, one of the tester machines generates all data and sends to one of the tested machines using a dedicated HTTP JSON endpoint.

Distributed testers

Before running the test, the tester VMs connect to each other to create a cluster. To start the tests, send a command to one of the testers with the values of N to use. The tester machines will coordinate to prepare the tests, run each one 3 times, distributing the work, then save the results in a object bucket.

Each test run ends when all users have done their actions.

Measurements

For each VM, CPU, memory and network will be monitored. For each action, the latency will be measured.

As a result of running a load test, two csv files will be generated:

1. hosts.csv, with one row per unique (scale, repeat, host, time), and the columns:
   • concurrent_users: the test run scale
   • repeat: the index of this run compared with all runs of the same scale
   • host
   • cpu_seconds: total CPU consumed by this host
   • avg_memory_gib: average GiB of used memory
   • network_in_mib: total MiB of incoming network
   • network_out_mib: total MiB of outcoming network
2. endpoints.csv, with one row per unique (scale, repeat, endpoint)
   • concurrent_users: the test run scale
   • repeat: the index of this run compared with all runs of the same scale
   • user_visits: total of attempted user flow simulations
   • failed_visits: how many of such attempts didn't complete correctly
   • p50_ms: the median duration of the simulated flows that completed with success
   • p99_ms: the 99-percentile duration of the simulated flows that completed with success

Compile updating sqlx queries

docker compose up -d
export DATABASE_URL=postgres://database-architecture-load-tests:982cfe09c87a111dca0fe40e96e23775@localhost/database-architecture-load-tests
cargo sqlx migrate run
cargo sqlx prepare --workspace

Test running

Instances:

• STANDARD3-X2C-8G
• STANDARD3-X4C-16G
• STANDARD3-X6C-24G
• STANDARD3-X8C-32G
• STANDARD3-X12C-48G
• STANDARD3-X16C-64G
• STANDARD3-X24C-96G
• STANDARD3-X32C-128G

for N in 1 2 3 4 5 6 7 8; do
    scw instance server create \
        -p database-architecture-load-tests \
        type=STANDARD3-X4C-16G \
        image=ubuntu_resolute \
        name=tester-$N \
        cloud-init=@cloud_init_tester.yml
done

scw instance server create \
    -p database-architecture-load-tests \
    type=STANDARD3-X2C-8G \
    image=ubuntu_resolute \
    name=candidate-file-2c \
    cloud-init=@cloud_init_candidate_file.yml

scw instance server create \
    -p database-architecture-load-tests \
    type=STANDARD3-X4C-16G \
    image=ubuntu_resolute \
    name=candidate-file-4c \
    cloud-init=@cloud_init_candidate_file.yml

scw instance server create \
    -p database-architecture-load-tests \
    type=STANDARD3-X8C-32G \
    image=ubuntu_resolute \
    name=candidate-file-8c \
    cloud-init=@cloud_init_candidate_file.yml

scw instance server create \
    -p database-architecture-load-tests \
    type=STANDARD3-X2C-8G \
    image=ubuntu_resolute \
    name=candidate-postgres-2c \
    cloud-init=@cloud_init_candidate_postgres_2c.yml
scw instance server create \
    -p database-architecture-load-tests \
    type=STANDARD3-X2C-8G \
    image=ubuntu_resolute \
    name=candidate-postgres-db-2c \
    cloud-init=@cloud_init_candidate_postgres_db.yml

scw instance server create \
    -p database-architecture-load-tests \
    type=STANDARD3-X4C-16G \
    image=ubuntu_resolute \
    name=candidate-postgres-4c \
    cloud-init=@cloud_init_candidate_postgres_4c.yml
scw instance server create \
    -p database-architecture-load-tests \
    type=STANDARD3-X4C-16G \
    image=ubuntu_resolute \
    name=candidate-postgres-db-4c \
    cloud-init=@cloud_init_candidate_postgres_db.yml \
    root-volume=b:40GB

scw instance server create \
    -p database-architecture-load-tests \
    type=STANDARD3-X8C-32G \
    image=ubuntu_resolute \
    name=candidate-postgres-8c \
    cloud-init=@cloud_init_candidate_postgres_8c.yml
scw instance server create \
    -p database-architecture-load-tests \
    type=STANDARD3-X8C-32G \
    image=ubuntu_resolute \
    name=candidate-postgres-db-8c \
    cloud-init=@cloud_init_candidate_postgres_db.yml \
    root-volume=b:40GB

TESTERS=(62.210.87.48 51.15.255.229 51.158.77.77 163.172.6.71)
FILE_2C=51.15.214.120
FILE_4C=163.172.185.173
FILE_8C=62.210.77.107
POSTGRES_2C=163.172.169.220
POSTGRES_DB_2C=62.210.87.110
POSTGRES_4C=51.15.208.178
POSTGRES_DB_4C=51.15.220.240
POSTGRES_8C=51.15.132.188
POSTGRES_DB_8C=62.210.77.107

# Done
cargo run --release -p test_control -- --candidate $FILE_4C --testers $TESTERS --total-users 10k --concurrent-users 1 2 4 8 16 32 64 128 --repeat 3 --name file-4c-10k
cargo run --release -p test_control -- --candidate $FILE_2C --testers $TESTERS --total-users 10k --concurrent-users 1 2 4 8 16 32 64 128 --repeat 3 --name file-2c-10k
cargo run --release -p test_control -- --candidate $FILE_8C --testers $TESTERS --total-users 100k --concurrent-users 1 2 4 8 16 32 64 --repeat 3 --name file-8c-100k
cargo run --release -p test_control -- --candidate $FILE_4C --testers $TESTERS --total-users 100k --concurrent-users 1 2 4 8 16 32 64 --repeat 3 --name file-4c-100k
cargo run --release -p test_control -- --candidate $POSTGRES_2C --database $POSTGRES_DB_2C --testers $TESTERS --total-users 10k --concurrent-users 1 2 4 8 16 32 64 128 --repeat 3 --name postgres-2c-10k
cargo run --release -p test_control -- --candidate $POSTGRES_4C --database $POSTGRES_DB_4C --testers $TESTERS --total-users 100k --concurrent-users 1 2 4 8 16 32 64 --repeat 3 --name postgres-4c-100k
cargo run --release -p test_control -- --candidate $POSTGRES_8C --database $POSTGRES_DB_8C --testers $TESTERS --total-users 100k --concurrent-users 1 2 4 8 16 32 64 --repeat 3 --name postgres-8c-100k
cargo run --release -p test_control -- --candidate $POSTGRES_4C --database $POSTGRES_DB_4C --testers $TESTERS --total-users 10k --concurrent-users 1 2 4 8 16 32 64 128 --repeat 3 --name postgres-4c-10k

# Doing

# TODO

# Quick ssh as root
function scw-ssh() { ssh-keygen -R "$1"; ssh "root@$1" }

# Recompile and restart
su - app -c "cd /home/app/repo && git pull && cargo build --release -p candidate" && systemctl restart app