volume state store experiments

sqlite.schema

% sudo sqlite3 piwall2.db
SQLite version 3.27.2 2019-02-25 16:06:06
Enter ".help" for usage hints.
sqlite> .schema
CREATE TABLE schema_version (version INTEGER);
CREATE TABLE playlist_videos (
                playlist_video_id INTEGER PRIMARY KEY,
                create_date DATETIME  DEFAULT CURRENT_TIMESTAMP,
                url TEXT,
                thumbnail TEXT,
                title TEXT,
                duration VARCHAR(20),
                status VARCHAR(20),
                is_skip_requested INTEGER DEFAULT 0,
                settings TEXT DEFAULT ''
            );
CREATE INDEX status_idx ON playlist_videos (status, playlist_video_id ASC);
CREATE TABLE volume_state_store (
    vol_pct REAL,
    update_date DATETIME DEFAULT CURRENT_TIMESTAMP
);

test.results

[piwall1] ~/piwall2 % time sudo ./vol_speed_test
Setting volume 100 times took 1147.33 ms with VolumeController
Getting volume 100 times took 1039.82 ms with VolumeController
Setting volume 100 times took 5376.99 ms with volume_state_store
sudo ./vol_speed_test  1.02s user 1.35s system 30% cpu 7.729 total
[piwall1] ~/piwall2 % time sudo ./vol_speed_test
Setting volume 100 times took 900.36 ms with VolumeController
Getting volume 100 times took 913.77 ms with VolumeController
Setting volume 100 times took 1849.06 ms with volume_state_store
sudo ./vol_speed_test  0.90s user 1.43s system 60% cpu 3.868 total
[piwall1] ~/piwall2 % time sudo ./vol_speed_test
Setting volume 100 times took 870.23 ms with VolumeController
Getting volume 100 times took 934.4 ms with VolumeController
Setting volume 100 times took 5587.84 ms with volume_state_store
sudo ./vol_speed_test  0.96s user 1.33s system 30% cpu 7.586 total
[piwall1] ~/piwall2 % time sudo ./vol_speed_test
Setting volume 100 times took 917.75 ms with VolumeController
Getting volume 100 times took 1015.27 ms with VolumeController
Setting volume 100 times took 9815.27 ms with volume_state_store
sudo ./vol_speed_test  1.09s user 1.37s system 20% cpu 11.959 total
[piwall1] ~/piwall2 % time sudo ./vol_speed_test
Setting volume 100 times took 894.85 ms with VolumeController
Getting volume 100 times took 864.26 ms with VolumeController
Setting volume 100 times took 1824.63 ms with volume_state_store

[piwall1] ~/piwall2 % time sudo ./vol_speed_test
Setting volume 100 times took 922.04 ms with VolumeController
Getting volume 100 times took 1208.38 ms with VolumeController
Setting volume 100 times took 1838.92 ms with volume_state_store
sudo ./vol_speed_test  0.90s user 1.77s system 4% cpu 1:04.18 total

[piwall1] ~/piwall2 % time sudo ./vol_speed_test
Setting volume 100 times took 912.14 ms with VolumeController
Getting volume 100 times took 919.14 ms with VolumeController
Setting volume 100 times took 1333.08 ms with volume_state_store
sudo ./vol_speed_test  1.01s user 1.26s system 67% cpu 3.367 total
[piwall1] ~/piwall2 % time sudo ./vol_speed_test
Setting volume 100 times took 888.16 ms with VolumeController
Getting volume 100 times took 886.44 ms with VolumeController
Setting volume 100 times took 1466.85 ms with volume_state_store
sudo ./vol_speed_test  0.98s user 1.30s system 65% cpu 3.463 total
[piwall1] ~/piwall2 % time sudo ./vol_speed_test
Setting volume 100 times took 953.04 ms with VolumeController
Getting volume 100 times took 1085.99 ms with VolumeController
Setting volume 100 times took 1273.34 ms with volume_state_store
sudo ./vol_speed_test  1.07s user 1.47s system 72% cpu 3.529 total
[piwall1] ~/piwall2 % time sudo ./vol_speed_test
Setting volume 100 times took 883.08 ms with VolumeController
Getting volume 100 times took 994.59 ms with VolumeController
Setting volume 100 times took 8574.66 ms with volume_state_store
sudo ./vol_speed_test  0.99s user 1.36s system 22% cpu 10.638 total

[piwall1] ~/piwall2 % time sudo ./vol_speed_test
Setting volume 100 times took 883.22 ms with VolumeController
Getting volume 100 times took 874.55 ms with VolumeController
Setting volume 100 times took 1805.13 ms with volume_state_store
sudo ./vol_speed_test  1.02s user 1.26s system 60% cpu 3.813 total
[piwall1] ~/piwall2 % time sudo ./vol_speed_test
Setting volume 100 times took 937.7 ms with VolumeController
Getting volume 100 times took 921.23 ms with VolumeController
Setting volume 100 times took 1547.3 ms with volume_state_store
sudo ./vol_speed_test  0.99s user 1.41s system 66% cpu 3.636 total



Sep 15 03:34:18 piwall1 PIWALL2_QUEUE[556]:   File "/home/pi/piwall2/bin/queue", line 11, in <module>
Sep 15 03:34:18 piwall1 PIWALL2_QUEUE[556]:     Queue().run()
Sep 15 03:34:18 piwall1 PIWALL2_QUEUE[556]:   File "/home/pi/piwall2/piwall2/broadcaster/queue.py", line 35, in run
Sep 15 03:34:18 piwall1 PIWALL2_QUEUE[556]:     self.__maybe_skip_broadcast()
Sep 15 03:34:18 piwall1 PIWALL2_QUEUE[556]:   File "/home/pi/piwall2/piwall2/broadcaster/queue.py", line 68, in __maybe_skip_broadcast
Sep 15 03:34:18 piwall1 PIWALL2_QUEUE[556]:     if self.__playlist.should_skip_video_id(self.__playlist_item['playlist_video_id']):
Sep 15 03:34:18 piwall1 PIWALL2_QUEUE[556]:   File "/home/pi/piwall2/piwall2/broadcaster/playlist.py", line 116, in should_skip_video_id
Sep 15 03:34:18 piwall1 PIWALL2_QUEUE[556]:     current_video = self.get_current_video()
Sep 15 03:34:18 piwall1 PIWALL2_QUEUE[556]:   File "/home/pi/piwall2/piwall2/broadcaster/playlist.py", line 71, in get_current_video
Sep 15 03:34:18 piwall1 PIWALL2_QUEUE[556]:     self.__cursor.execute("SELECT * FROM playlist_videos WHERE status = ? LIMIT 1", [self.STATUS_PLAYING])
Sep 15 03:34:18 piwall1 PIWALL2_QUEUE[556]: sqlite3.OperationalError: database is locked

vol_speed_test

#!/usr/bin/env python3

import random
import time
from piwall2.volumecontroller import VolumeController
import piwall2.broadcaster.database

vc = VolumeController()

iters = 100

vol_pcts = []
for i in range(0, iters):
    vol_pcts.append(random.randrange(0, 100))

start = time.time()
for i in range(0, iters):
    vc.set_vol_pct(vol_pcts[i])
elapsed_ms = round((time.time() - start) * 1000, 2)
print(f"Setting volume {iters} times took {elapsed_ms} ms with VolumeController")

start = time.time()
for i in range(0, iters):
    vc.get_vol_pct()
elapsed_ms = round((time.time() - start) * 1000, 2)
print(f"Getting volume {iters} times took {elapsed_ms} ms with VolumeController")


cursor = piwall2.broadcaster.database.Database().get_cursor()
start = time.time()
for i in range(0, iters):
    cursor.execute("UPDATE volume_state_store SET vol_pct = ?, update_date = datetime()", [vol_pcts[i]])
elapsed_ms = round((time.time() - start) * 1000, 2)
print(f"Setting volume {iters} times took {elapsed_ms} ms with volume_state_store")