4cb9da06dc
- 동시접속자 기반 월간 대역폭 자동 추정 - DAU(일일활성사용자) 추정치 표시 (동접 × 10-14) - 대역폭 기반 Linode/Vultr 자동 선택 로직 - 비용 분석에 대역폭 비용 포함 - 지역 미선택시 서울/도쿄/오사카/싱가포르 기본 표시 - 지역별 서버 분리 표시 (GROUP BY instance + region) Co-Authored-By: Claude Opus 4.5 <noreply@anthropic.com>
80 lines
3.8 KiB
SQL
80 lines
3.8 KiB
SQL
-- Benchmark data schema for server-recommend
|
|
-- Real performance data from OpenBenchmarking.org / Phoronix Test Suite
|
|
|
|
-- Benchmark types (test suites)
|
|
CREATE TABLE IF NOT EXISTS benchmark_types (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
name TEXT NOT NULL UNIQUE,
|
|
category TEXT NOT NULL, -- cpu, web, database, io, compression
|
|
description TEXT,
|
|
unit TEXT, -- requests/sec, MIPS, ms, etc.
|
|
higher_is_better INTEGER DEFAULT 1
|
|
);
|
|
|
|
-- Processor/Hardware info
|
|
CREATE TABLE IF NOT EXISTS processors (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
proc_id TEXT NOT NULL UNIQUE, -- /s/AMD+Ryzen+5+5600X+6-Core
|
|
name TEXT NOT NULL, -- AMD Ryzen 5 5600X 6-Core
|
|
vendor TEXT, -- AMD, Intel
|
|
cores INTEGER,
|
|
created_at TEXT DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Benchmark results
|
|
CREATE TABLE IF NOT EXISTS benchmark_results (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
processor_id INTEGER NOT NULL,
|
|
benchmark_type_id INTEGER NOT NULL,
|
|
score REAL NOT NULL,
|
|
score_deviation REAL, -- +/- value
|
|
percentile INTEGER, -- 100, 98, 95, etc.
|
|
sample_count INTEGER, -- number of tests
|
|
created_at TEXT DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (processor_id) REFERENCES processors(id),
|
|
FOREIGN KEY (benchmark_type_id) REFERENCES benchmark_types(id),
|
|
UNIQUE(processor_id, benchmark_type_id)
|
|
);
|
|
|
|
-- CPU to instance mapping (link benchmark CPUs to cloud instances)
|
|
CREATE TABLE IF NOT EXISTS cpu_instance_mapping (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
processor_id INTEGER NOT NULL,
|
|
instance_type_id INTEGER NOT NULL,
|
|
confidence REAL DEFAULT 0.8, -- how confident we are about this mapping
|
|
FOREIGN KEY (processor_id) REFERENCES processors(id),
|
|
FOREIGN KEY (instance_type_id) REFERENCES instance_types(id),
|
|
UNIQUE(processor_id, instance_type_id)
|
|
);
|
|
|
|
-- Insert benchmark types
|
|
INSERT OR IGNORE INTO benchmark_types (name, category, description, unit, higher_is_better) VALUES
|
|
('pts-nginx', 'web', 'Nginx HTTP server performance', 'requests/sec', 1),
|
|
('pts-apache', 'web', 'Apache HTTP server performance', 'requests/sec', 1),
|
|
('pts-apache-siege', 'web', 'Apache Siege load test', 'transactions/sec', 1),
|
|
('pts-node-octane', 'web', 'Node.js Octane benchmark', 'score', 1),
|
|
('pts-node-express-loadtest', 'web', 'Node.js Express load test', 'requests/sec', 1),
|
|
('pts-phpbench', 'web', 'PHP benchmark', 'score', 1),
|
|
('pts-redis', 'database', 'Redis in-memory database', 'operations/sec', 1),
|
|
('pts-mysqlslap', 'database', 'MySQL load test', 'queries/sec', 1),
|
|
('pts-compress-7zip', 'compression', '7-Zip compression', 'MIPS', 1),
|
|
('pts-compress-pbzip2', 'compression', 'Parallel BZIP2 compression', 'MB/s', 1),
|
|
('pts-compress-gzip', 'compression', 'GZIP compression', 'MB/s', 1),
|
|
('pts-postmark', 'io', 'PostMark filesystem benchmark', 'transactions/sec', 1),
|
|
('pts-compilebench', 'io', 'Compile benchmark', 'MB/s', 1),
|
|
('pts-c-ray', 'cpu', 'C-Ray raytracing', 'seconds', 0),
|
|
('pts-coremark', 'cpu', 'CoreMark embedded CPU benchmark', 'iterations/sec', 1),
|
|
('pts-byte', 'cpu', 'BYTE Unix benchmark', 'score', 1),
|
|
('pts-encode-mp3', 'encoding', 'MP3 encoding', 'seconds', 0),
|
|
('pts-encode-flac', 'encoding', 'FLAC encoding', 'seconds', 0),
|
|
('pts-x264', 'encoding', 'x264 video encoding', 'fps', 1),
|
|
('pts-build-imagemagick', 'build', 'ImageMagick build time', 'seconds', 0),
|
|
('pts-build-nodejs', 'build', 'Node.js build time', 'seconds', 0),
|
|
('pts-build-php', 'build', 'PHP build time', 'seconds', 0);
|
|
|
|
-- Create indexes for performance
|
|
CREATE INDEX IF NOT EXISTS idx_benchmark_results_processor ON benchmark_results(processor_id);
|
|
CREATE INDEX IF NOT EXISTS idx_benchmark_results_type ON benchmark_results(benchmark_type_id);
|
|
CREATE INDEX IF NOT EXISTS idx_processors_name ON processors(name);
|
|
CREATE INDEX IF NOT EXISTS idx_cpu_mapping_instance ON cpu_instance_mapping(instance_type_id);
|