As discussed in https://phabricator.wikimedia.org/T301428#7730915, we have decided to go with MariaDB for our storage needs. After reviewing the [[ https://wikitech.wikimedia.org/wiki/MariaDB | MariaDB documentation ]], we saw that there was a Miscellaneous cluster that seems like it would fit our needs since this is not a Mediawiki project. It's unclear if containerized projects can use the MariaDB clusters, and there weren't that many examples of other projects that are doing this. We also can run our own MariaDB in a container, but for production purposes, it seems better to use a production ready cluster. However, if this is preferred, please let us know and send some examples our way and we can move forward with building our own db.
Suggested Database creation template fields
-- QPS: not sure
-- Size: ~6 GB (we're not planning to store more than 4 GB...but we might need more)
-- DB Name: ipoid
-- Accessed from server (s): Kubernetes [[ https://wikitech.wikimedia.org/wiki/Kubernetes/Clusters#wikikube_(aka_eqiad/codfw) | wikikube ]] cluster
-- Backup Policy: ΤΒD
-- Grants & Users (from T305114#8846665)
- ipoid_rw: SELECT, DELETE, UPDATE, INSERT, CREATE, CREATE TEMPORARY TABLES
- ipoid_ro: SELECT
Suggested Schema (from T305114#7897318)
```lang=sql
CREATE TABLE actor_data (
pkid BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
ip VARBINARY(128) NOT NULL,
org VARBINARY(128),
client_count INT UNSIGNED,
-- 1 = 'DESKTOP', 2 = 'HEADLESS', 3 = 'IOT', 4 = 'MOBILE'
types SET(1,2,3,4)
conc_geohash VARBINARY(16),
conc_city VARBINARY(32),
conc_state VARBINARY(32),
conc_country VARBINARY(32),
conc_skew INT,
conc_density INT,
countries INT,
location_country VARBINARY(32),
-- 1 = 'CALLBACK_PROXY', 2 = 'GEO_MISMATCH', 3 = 'LOGIN_BRUTEFORCE', 4 = 'TUNNEL', 5 = 'WEB_SCRAPING'
risks SET(1,2,3,4,5),
PRIMARY KEY (pkid)
);
CREATE TABLE behaviors (
pkid BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
behaviors VARBINARY(64),
PRIMARY KEY (pkid)
);
CREATE TABLE actor_data_behaviors (
actor_data_id BIGINT UNSIGNED,
behaviors_id BIGINT UNSIGNED
);
CREATE TABLE proxies (
pkid BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
proxy VARBINARY(32),
PRIMARY KEY (pkid)
);
CREATE TABLE actor_data_proxies (
actor_data_id BIGINT UNSIGNED,
proxies_id BIGINT UNSIGNED
);
CREATE TABLE tunnels (
pkid BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
tunnels VARBINARY(32),
PRIMARY KEY (pkid)
);
CREATE TABLE actor_data_tunnels (
actor_data_id BIGINT UNSIGNED,
tunnels_id BIGINT UNSIGNED
);```