Very bad performance with SQLite cache (on a Linux machine with HDD)

[ilevkivskyi]

I just tried a fresh install of mypy==1.20.0 on my machine with torch. The results are honestly disturbing:

(mypy_dev_3_12) ivan@x333:~/src/mypy$ time mypy -c 'import torch'
Success: no issues found in 1 source file

real	0m10.009s
user	0m7.900s
sys	0m0.252s

(mypy_dev_3_12) ivan@x333:~/src/mypy$ time mypy -c 'import torch' --no-sqlite-cache
Success: no issues found in 1 source file

real	0m7.975s
user	0m7.681s
sys	0m0.280s

SQLite cache makes type-checking 20% slower. I guess this is because I have an HDD as the system disk (and IIRC we only apply PRAGMA synchronous=OFF in parallel checking), but maybe there is something else.

@JukkaL @hauntsaninja just to double-check: did you actually do any performance measurements for SQLite cache vs FS cache? What are the results on your machines?

TBH at this stage I think we should revert the SQLite cache by default until it is "production-ready".

[hauntsaninja]

Yes, I did some benchmarking when we merged / when we enabled it on self check.

On my Macbook Air:

λ python -m mypy --version
mypy 1.20.0 (compiled: yes)

λ hyperfine -w 1 'python -m mypy -c "import torch" --no-incremental --sqlite-cache'
Benchmark 1: python -m mypy -c "import torch" --no-incremental --sqlite-cache
  Time (mean ± σ):      7.716 s ±  0.106 s    [User: 7.232 s, System: 0.295 s]
  Range (min … max):    7.563 s …  7.869 s    10 runs
 
λ hyperfine -w 1 'python -m mypy -c "import torch" --no-incremental --no-sqlite-cache'
Benchmark 1: python -m mypy -c "import torch" --no-incremental --no-sqlite-cache
  Time (mean ± σ):      8.563 s ±  0.223 s    [User: 7.363 s, System: 0.924 s]
  Range (min … max):    8.397 s …  9.155 s    10 runs

λ hyperfine -w 2 'python -m mypy -c "import torch" --sqlite-cache'
Benchmark 1: python -m mypy -c "import torch" --sqlite-cache
  Time (mean ± σ):     481.7 ms ±  10.9 ms    [User: 391.9 ms, System: 80.7 ms]
  Range (min … max):   467.0 ms … 499.7 ms    10 runs
 
λ hyperfine -w 2 'python -m mypy -c "import torch" --no-sqlite-cache'
Benchmark 1: python -m mypy -c "import torch" --no-sqlite-cache
  Time (mean ± σ):     484.4 ms ±   8.1 ms    [User: 377.4 ms, System: 99.5 ms]
  Range (min … max):   476.0 ms … 502.2 ms    10 runs

[hauntsaninja]

On a linux box:

λ python -m mypy --version
mypy 1.20.0 (compiled: yes)

λ hyperfine -w 1 'python -m mypy -c "import torch" --no-incremental --sqlite-cache'
Benchmark 1: python -m mypy -c "import torch" --no-incremental --sqlite-cache
  Time (mean ± σ):     10.404 s ±  0.050 s    [User: 9.875 s, System: 0.518 s]
  Range (min … max):   10.313 s … 10.482 s    10 runs
 
λ hyperfine -w 1 'python -m mypy -c "import torch" --no-incremental --no-sqlite-cache'
Benchmark 1: python -m mypy -c "import torch" --no-incremental --no-sqlite-cache
  Time (mean ± σ):     10.810 s ±  0.282 s    [User: 9.928 s, System: 0.876 s]
  Range (min … max):   10.657 s … 11.583 s    10 runs
 
  Warning: Statistical outliers were detected. Consider re-running this benchmark on a quiet system without any interferences from other programs. It might help to use the '--warmup' or '--prepare' options.
 
λ hyperfine -w 2 'python -m mypy -c "import torch" --sqlite-cache'
Benchmark 1: python -m mypy -c "import torch" --sqlite-cache
  Time (mean ± σ):     666.0 ms ±  39.0 ms    [User: 491.6 ms, System: 173.4 ms]
  Range (min … max):   645.9 ms … 775.3 ms    10 runs
 
  Warning: Statistical outliers were detected. Consider re-running this benchmark on a quiet system without any interferences from other programs. It might help to use the '--warmup' or '--prepare' options.
 
λ hyperfine -w 2 'python -m mypy -c "import torch" --no-sqlite-cache'
Benchmark 1: python -m mypy -c "import torch" --no-sqlite-cache
  Time (mean ± σ):     651.4 ms ±   3.9 ms    [User: 469.8 ms, System: 181.3 ms]
  Range (min … max):   645.3 ms … 658.5 ms    10 runs

[hauntsaninja]

I am curious if WAL changes anything on your system. SQLite also mentions we can set synchronous to normal instead of full when running in WAL.

(If you do have time to benchmark that, make sure to wipe the mypy cache between runs, because WAL is a persistent setting on the db)

[hauntsaninja]

One more timing, from my work laptop:

λ hyperfine -w 1 'python -m mypy -c "import torch" --no-incremental --sqlite-cache'
Benchmark 1: python -m mypy -c "import torch" --no-incremental --sqlite-cache
  Time (mean ± σ):      9.703 s ±  0.222 s    [User: 8.938 s, System: 0.578 s]
  Range (min … max):    9.331 s …  9.989 s    10 runs
 
λ hyperfine -w 1 'python -m mypy -c "import torch" --no-incremental --no-sqlite-cache'
Benchmark 1: python -m mypy -c "import torch" --no-incremental --no-sqlite-cache
  Time (mean ± σ):     11.737 s ±  0.760 s    [User: 9.374 s, System: 1.760 s]
  Range (min … max):   10.674 s … 13.156 s    10 runs

[ilevkivskyi]

WAL doesn't help. However PRAGMA synchronous=OFF does help (or at least makes things much better).