DB memo

[bird0401]

Reference

• MySQL 5.6 リファレンスマニュアル
• MySQL 5.7 Reference Manual

Design

• [x] RAID5
• [x] merge temporary and log file to one storage
• [x] use full + increasing backup
• [x] set for PITR
• [x] make DB 4th normalization
• [x] define primary key
• [x] use CHAR to primary key
• [x] saved directory path column
• [x] auto increment linked to URI
• [x] unique restriction
• [x] create index
• [x] collect statistics
• [x] partitioning
• [x] use PMP to audit
• [x] output error log to slack
• [x] store image files not as BLOB in DB but as phantom files, because these are needed on ML as the state of files
• [x] backup mysql and images simultaneously after scraping

Query

• [x] not use wildcard in SELECT statement

[bird0401]

command to delete binary files except newest one

mysql> PURGE BINARY LOGS TO 'mysql-bin.000142';

[bird0401]

I cant apply DB backup periodically using cron command because I dont have root authentification on the server

[bird0401]

binary log

to find my.conf,

mysql --help | grep my.cnf

[bird0401]

Main purpose of using DB is not to download existing images by searching DB if there is the image URI

need columns

• wikidata_id: CHAR(20) NOT NULL
• name: VARCHAR(200) NOT NULL
• img_url: VARCHAR(2048) NOT NULL
• img_id: MEDIUMINT NOT NULL AUTO_INCREMENT
• path: VARCHAR(50) NOT NULL

tables

img_urls

• img_id (PK)
• wikidata_id
• img_url

names

• wikidata_id (PK)
• name

img_wikidata_id

• img_id (PK, FK) ON UPDATE CASCADE ON DELETE CASCADE
• wikidata_id (PK, FK) ON UPDATE CASCADE ON DELETE CASCADE

img_path

• img_id (PK, FK) ON UPDATE CASCADE ON DELETE CASCADE
• path

Others

• I didnt make it 5th normalization because it creates too many tables that wouldnt need and takes more time to write.
• URI can be long and this is not suitable to use it for PK. I use surrogate key to express URI.
• use InnoDB
• innodb_stats_persistent_sample_pages=20
• not do partitioning because there will be no client and almost none element suitable for partitioning
• img_urls are sometimes duplicate owing to multiple entities in a image -> in img_urls table, img_id takes a pair of wikidata_id and img_url

[bird0401]

dont use zabbix because I dont have root authenticaton on ist-cluster and no client

[bird0401]

CREATE DATABASE scraping_dog_breeds_by_name CHARACTER SET utf8;

CREATE TABLE names(
    wikidata_id CHAR(20) NOT NULL PRIMARY KEY,
    name VARCHAR(200) CHARACTER SET utf8 NOT NULL 
);

CREATE TABLE img_urls (
    img_id MEDIUMINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    wikidata_id CHAR(20) NOT NULL,
    img_url VARCHAR(2048) CHARACTER SET utf8 NOT NULL
);

CREATE TABLE img_wikidata_id (
    img_id MEDIUMINT NOT NULL,
    wikidata_id CHAR(20) NOT NULL,
    PRIMARY KEY(img_id, wikidata_id),
    FOREIGN KEY(img_id) 
        REFERENCES img_urls(img_id)
        ON UPDATE CASCADE ON DELETE CASCADE,
    FOREIGN KEY(wikidata_id) 
        REFERENCES names(wikidata_id)
        ON UPDATE CASCADE ON DELETE CASCADE
);

CREATE TABLE img_path (
    img_id MEDIUMINT NOT NULL PRIMARY KEY,
    path VARCHAR(50) NOT NULL,
    FOREIGN KEY(img_id) 
        REFERENCES img_urls(img_id)
        ON UPDATE CASCADE ON DELETE CASCADE
);

CREATE USER 'scraper' IDENTIFIED BY $mysql_pass;
GRANT ALL ON *.* TO 'scraper';

[bird0401]

mysql or BQ

• use mysql to temporal database for scraping and transfer them to BQ via csv file
• because I am familiar with mysql control considering regularization, column type definition, backup, etc.
• also transferring makes almost no cost and the system is not needed to proceed in real time