SQLite란?
Benchmark란?
Input user option
SQLiteBench stores option given by the user in Benchmark in FLAGS_* variables
// main.cc
int main (int argc, char** argv) {
init();
char* default_db_path = static_cast<char*>(malloc(sizeof(char) * 1024));
strcpy(default_db_path, "./");
for (int i = 1; i < argc; i++) {
double d;
int n;
char junk;
char journal_mode[100];
if (starts_with(argv[i], "--benchmarks=")) {
FLAGS_benchmarks = argv[i] + strlen("--benchmarks=");
} else if (sscanf(argv[i], "--histogram=%d%c", &n, &junk) == 1 &&
(n == 0 || n == 1)) {
FLAGS_histogram = n;
} else if (sscanf(argv[i], "--raw=%d%c", &n, &junk) == 1 &&
(n == 0 || n == 1)) {
FLAGS_raw = n;
} else if (sscanf(argv[i], "--compression_ratio=%lf%c", &d, &junk) == 1) {
FLAGS_compression_ratio = d;
} else if (sscanf(argv[i], "--use_existing_db=%d%c", &n, &junk) == 1 &&
(n == 0 || n == 1)) {
FLAGS_use_existing_db = n;
}
...
}
Run Benchmark
// bench.h
class Benchmark {
public:
sqlite3* db_;
int num_;
// ellipsis
void setJournalMode();
void benchmark_open();
};
// bench.cc
void Benchmark::benchmark_open() {
assert(db_ == NULL);
int status;
char file_name[100];
char* err_msg = NULL;
db_num_++;
/* Open database */
char *tmp_dir = FLAGS_db;
snprintf(file_name, sizeof(file_name),
"%s/sqlitebench_test-%d.db",
tmp_dir,
db_num_);
// (jhpark): always use fresh db
if (!FLAGS_use_existing_db) {
remove(file_name);
}
status = sqlite3_open(file_name, &db_);
if (status) {
fprintf(stderr, "open error: %s\n", sqlite3_errmsg(db_));
exit(1);
}
void Benchmark::benchmark_run() {
print_header();
benchmark_open();
char* benchmarks = FLAGS_benchmarks;
while (benchmarks != NULL) {
char* sep = strchr(benchmarks, ',');
char* name;
if (sep == NULL) {
name = benchmarks;
benchmarks = NULL;
} else {
name = static_cast<char*>(calloc(sizeof(char), (sep - benchmarks + 1)));
strncpy(name, benchmarks, sep - benchmarks);
benchmarks = sep + 1;
}
bytes_ = 0;
// measure the time elapsed.
start();
bool known = true;
bool write_sync = false;
if (!strcmp(name, "fillseq")) {
benchmark_write(write_sync, SEQUENTIAL, FRESH, num_, FLAGS_value_size, 1);
wal_checkpoint(db_);
} // xxx(homework)
else if (!strcmp(name, "directfillrandom")) {
benchmark_directFillRand(num_);
}
...
if (known) {
stop(name);
}
}
Show Benchmark results
~/SWE3033/cmake-project-template/build$ ./sqliteBench --journal_mode=wal --page_size=16384 --benchmarks=directfillrandom
journal_mode = wal
SQLite: version 3.36.0
Date: Sun Nov 7 22:50:27 2021
CPU: 16 * Intel(R) Xeon(R) CPU E5-2640 v3 @ 2.60GHz
CPUCache: 20480 KB
Keys: 16 bytes each
Values: 0 bytes each
Entries: 100000
RawSize: 1.5 MB (estimated)
------------------------------------------------
directfillrandom : 69.857 micros/op;
benchmark_setJournalMode()
int Benchmark::benchmark_setJournalMode() {
int status;
if (strcmp(FLAGS_journal_mode, "DELETE") == 0){
const char* stmt = "PRAGMA journal_mode = DELETE";
status = sqlite3_exec(db_, stmt, NULL, NULL, NULL);
}
else if (strcmp(FLAGS_journal_mode, "TRUNCATE") == 0){
const char* stmt = "PRAGMA journal_mode = TRUNCATE";
status = sqlite3_exec(db_, stmt, NULL, NULL, NULL);
}
else if (strcmp(FLAGS_journal_mode, "PERSIST") == 0){
const char* stmt = "PRAGMA journal_mode = PERSIST";
status = sqlite3_exec(db_, stmt, NULL, NULL, NULL);
}
else if (strcmp(FLAGS_journal_mode, "MEMORY") == 0){
const char* stmt = "PRAGMA journal_mode = MEMORY";
status = sqlite3_exec(db_, stmt, NULL, NULL, NULL);
}
else if (strcmp(FLAGS_journal_mode, "WAL") == 0){
const char* stmt = "PRAGMA journal_mode = WAL";
status = sqlite3_exec(db_, stmt, NULL, NULL, NULL);
}
else if (strcmp(FLAGS_journal_mode, "OFF") == 0){
const char* stmt = "PRAGMA journal_mode = OFF";
status = sqlite3_exec(db_, stmt, NULL, NULL, NULL);
}
else{
return -2;
}
return status;
}
benchmark_setPageSize()
int Benchmark::benchmark_setPageSize() {
int status;
char page_size[10];
sprintf(page_size, "%d", FLAGS_page_size);
char stmt_tmp[50] = "";
strcat(stmt_tmp, page_size);
const char* stmt = stmt_tmp;
status = sqlite3_exec(db_, stmt, NULL, NULL, NULL);
return status;
}
direct SQL execution mode
int Benchmark::benchmark_directFillRand(int num_) {
for (int i = 0; i < num_; i++){
// DO NOT MODIFY HERE //
const char* value = gen_.rand_gen_generate(FLAGS_value_size);
char key[100];
const int k = gen_.rand_next() % num_;
snprintf(key, sizeof(key), "%016d", k);
char fill_stmt[100];
snprintf(fill_stmt, sizeof(fill_stmt), "INSERT INTO test values (%s , x'%x')", key ,value);
////////////////////////////////
int status = sqlite3_exec(db_, fill_stmt, NULL, NULL, NULL);
// execute SQL statement
// please fill this function
}
return 0;
}
benchmark_example()
int Benchmark::benchmark_example() {
//fprintf(stderr, "example functions works!\n");
int N = 1000; // how many times to repeat INSERT
sqlite3_exec(db_, "CREATE TABLE TABLE1(A TEXT, B TEXT, C TEXT);", NULL, NULL, NULL);
sqlite3_exec(db_, "BEGIN TRANSACTION", NULL, NULL, NULL);
for (int i = 0; i < 1000; i++){
char sK[100]; // key
const int k = gen_.rand_next() % N;
snprintf(sK, sizeof(sK), "%016d", sK);
const char* sA = gen_.rand_gen_generate(FLAGS_value_size); // value1
const char* sB = gen_.rand_gen_generate(FLAGS_value_size); // value2
char stmt[100];
snprintf(stmt, sizeof(stmt), "INSERT INTO TABLE1 values (%s, x'%x', x'%x')", sK, sA, sB);
int status = sqlite3_exec(db_, stmt, NULL, NULL, NULL);
}
sqlite3_exec(db_, "END TRANSACTION", NULL, NULL, NULL);
return 0;
}
위에서부터 첫번째는 truncate mode, 두번째는 memory mode, 세번째는 persist mode, 네번째는 wal mode 이다. benchmark 결과에서 알 수 있듯이, wal mode가 가장 성능이 좋게 나왔음을 관찰할 수 있다. 앞의 세가지 Journal mode는 roll back Journal mode의 종류이다. Roll back journal mode는 disk에 flush하는 fsync()함수가 자주 호출되고, 매번 file을 만들었다 지웠다하는 과정에서 성능 저하가 발생한다. 반면 wal mode, 즉 Write ahead mode는 일단 Log에 모든 내용을 기록하고 나중에 한번에 flush하기 때문에, Roll back journal mode에 비하여 overhead가 적어, 좋은 성능을 보임을 확인할 수 있다.