SQLite Forum

Checking database took about 350 more time as normal
Login

Checking database took about 350 more time as normal

(1) By Cecil (CecilWesterhof) on 2021-01-01 03:08:22 [link] [source]

I have a script that I use in crontab to check 26 databases. Normally this takes about 20 seconds. One particular database takes the most time: about ten seconds.

A few nights ago this database took almost two hours to be checked. The other databases needed the usual time. The nights after this everything was back to normal.

I check the following pragmas:
    INTEGRITY_CHECK
    FOREIGN_KEY_CHECK
    JOURNAL_MODE

And I use the following tcl proc to check if the database is locked (it was not):
    proc checkLocked {database} {
        if {[catch {db eval "BEGIN IMMEDIATE"} SQLError]} {
            if {${SQLError} ne "database is locked"} {
                error "UNEXPECTED ERROR: ${SQLError}"
            }
            puts "The database ${database} is locked."
        } else {
            db eval "ROLLBACK"
        }
    }

What could be the reason of this?
Is it to worry about?

(2) By Clemens Ladisch (cladisch) on 2021-01-01 10:43:57 in reply to 1 [link] [source]

Possible reasons:

  • the database was unusually large
  • the database was unusually fragmented
  • something else ate up the I/O bandwidth
  • disk errors

(3) By Cecil (CecilWesterhof) on 2021-01-01 11:57:01 in reply to 2 [source]

It is larger as the others, but not unusually much. Besides afterwards I did not get it any-more.

Everyday the same number and type of records is added. So unusually fragmented seems also not likely.

The checks on the databases before and after took the same amount of time. So I do not think it would be I/O bandwidth.

That seems to indicate I have a problem with my disk. :'-(

(4) By Simon Slavin (slavin) on 2021-01-01 12:27:51 in reply to 3 [link] [source]

Can you run the same check on the same database again and see whether it takes the unusually long time again ?

You may have had a transient problem: full cache, or something like that.

(5) By Gerry Snyder (GSnyder) on 2021-01-01 15:42:02 in reply to 1 [link] [source]

Overactive virus "protection"?

Gerry Snyder

(6) By Cecil (CecilWesterhof) on 2021-01-01 16:08:14 in reply to 4 [link] [source]

I can, but it is not very useful, because it only happened that one time. I am running it for about a year in the night batch. There are difference between the execution times, but normally not more as a factor of four. So this was really a strange instance.

I think I keep an eye on it, but for the moment think about it as a fluke.

(7) By Cecil (CecilWesterhof) on 2021-01-01 16:09:14 in reply to 5 [link] [source]

Nope: it is on a Debian system.