Sqlite在不同类型磁盘下的性能测试

目录

例行废话

本文非严肃的性能测试,结果仅供娱乐wwwww

任务

测试环境

简单来说在测试过程中只有硬盘是瓶颈

臭代码

package main

import (
    "database/sql"
    "fmt"
    "log"
    "os"
    "path/filepath"
    "time"

    _ "github.com/mattn/go-sqlite3"
)

func main() {
    log.Println("Index Starting")

    // 是否是新数据库,需要进行初始化
    var needCreateTable bool
    _, err := os.Stat("fileIndex.db")
    needCreateTable = os.IsNotExist(err)

    // 打开数据库
    db, err := sql.Open("sqlite3", "fileIndex.db")
    if err != nil {
        log.Println(err)
    }

    // 如果需要,创建数据表
    if needCreateTable {
        log.Println("Create new table")
        _, err := db.Exec("CREATE TABLE file_index (folder TEXT, filename TEXT);")
        if err != nil {
            log.Println(err)
        }
        log.Println("New table created")
    }

    var counter int
    var sqlTime float64
    var walkTime float64

    // 启动观察线程---
    go WatchDog(&counter, &sqlTime, &walkTime)

    // 时间判断
    var walkStartTime time.Time
    var walkStopTime time.Time
    // 初始化时间
    walkStartTime = time.Now()

    err = filepath.Walk(
        "/data/",
        // 匿名WalkFunc函数
        func (path string, info os.FileInfo, err error) error {

        // 记录walk结束的时间
        walkStopTime = time.Now()
        walkTime += walkStopTime.Sub(walkStartTime).Seconds()

        sqlStartTime := time.Now()
        _, err = db.Exec(
            `INSERT INTO
            file_index (folder, filename)
            VALUES (?, ?);`,
            filepath.Dir(path),
            filepath.Base(path))
        if err != nil {
            return err
        }
        counter++
        sqlEndTime := time.Now()
        sqlTime += sqlEndTime.Sub(sqlStartTime).Seconds()

        // 记录开始walk的时间
        walkStartTime = time.Now()
        return nil
    })
    if err != nil {
        log.Fatal(err)
    }
}

func WatchDog(counter *int, sqlTime *float64, walkTime *float64) {
    // 用来记录上一次输出时的数字
    var oldCounter = 0
    for {
        fmt.Println(
            "Speed:", *counter-oldCounter,
            "Number:", *counter,
            "sqlTime:", fmt.Sprintf("%0.3f", *sqlTime),
            "walkTime:", fmt.Sprintf("%0.3f", *walkTime))
        oldCounter = *counter
        time.Sleep(time.Second)
    }
}

性能测试

再说明一下,程序遍历一个1T大小的5400RPM的btrfs compress-force=zlib:3的数据盘, 然后将文件路径和文件名两个字段插入到数据库中, 测的是数据库放在不同类型文件系统下的性能

然后是输出内容的结构

因为系统对磁盘文件内容有缓存,所以walkTime差别比较大

内存盘 Ramdisk

Speed: 0 Number: 0 sqlTime: 0.000 walkTime: 0.000
Speed: 13384 Number: 13384 sqlTime: 0.936 walkTime: 0.063
Speed: 13245 Number: 26630 sqlTime: 1.883 walkTime: 0.113
Speed: 13897 Number: 40528 sqlTime: 2.829 walkTime: 0.165
Speed: 13283 Number: 53811 sqlTime: 3.758 walkTime: 0.234

相同磁盘

5400RPM的compress-force:zlib:3并开启COW的btrfs,和数据盘是同一个磁盘

COW即btrfs的copy on write,写时复制的特性

结果惨不忍睹就不贴完整log了

Speed: 0 Number: 0 sqlTime: 0.000 walkTime: 0.000
Speed: 4 Number: 4 sqlTime: 0.922 walkTime: 0.000
Speed: 7 Number: 11 sqlTime: 2.000 walkTime: 0.001
Speed: 7 Number: 18 sqlTime: 2.899 walkTime: 0.001
Speed: 9 Number: 27 sqlTime: 3.966 walkTime: 0.001
Speed: 7 Number: 34 sqlTime: 4.933 walkTime: 0.001
Speed: 8 Number: 42 sqlTime: 5.966 walkTime: 0.001
Speed: 8 Number: 50 sqlTime: 6.988 walkTime: 0.001
Speed: 6 Number: 56 sqlTime: 7.933 walkTime: 0.001
Speed: 6 Number: 62 sqlTime: 8.955 walkTime: 0.001
Speed: 6 Number: 68 sqlTime: 9.933 walkTime: 0.002
Speed: 6 Number: 74 sqlTime: 10.910 walkTime: 0.002

SSD 带 btrfs COW

Speed: 0 Number: 0 sqlTime: 0.000 walkTime: 0.000
Speed: 86 Number: 86 sqlTime: 0.989 walkTime: 0.001
Speed: 88 Number: 174 sqlTime: 1.994 walkTime: 0.003
Speed: 88 Number: 262 sqlTime: 2.992 walkTime: 0.004
Speed: 86 Number: 348 sqlTime: 3.995 walkTime: 0.006
Speed: 84 Number: 432 sqlTime: 4.986 walkTime: 0.007
Speed: 87 Number: 519 sqlTime: 5.986 walkTime: 0.009
Speed: 86 Number: 605 sqlTime: 6.985 walkTime: 0.010
Speed: 86 Number: 691 sqlTime: 7.985 walkTime: 0.012
Speed: 85 Number: 776 sqlTime: 8.987 walkTime: 0.013

SSD 带 btrfs noCOW

这个是关了COW的结果…

Speed: 36 Number: 292 sqlTime: 7.989 walkTime: 0.005
Speed: 35 Number: 327 sqlTime: 8.984 walkTime: 0.005
Speed: 35 Number: 362 sqlTime: 9.991 walkTime: 0.006
Speed: 35 Number: 397 sqlTime: 10.987 walkTime: 0.006
Speed: 35 Number: 432 sqlTime: 11.972 walkTime: 0.007
Speed: 35 Number: 467 sqlTime: 12.964 walkTime: 0.007
Speed: 41 Number: 508 sqlTime: 13.993 walkTime: 0.008
Speed: 128 Number: 636 sqlTime: 14.991 walkTime: 0.010
Speed: 132 Number: 768 sqlTime: 15.988 walkTime: 0.012
Speed: 130 Number: 898 sqlTime: 16.983 walkTime: 0.014
Speed: 131 Number: 1029 sqlTime: 17.985 walkTime: 0.016
Speed: 131 Number: 1160 sqlTime: 18.981 walkTime: 0.019

nm的,为什么