Virtuoso doesn't respond while checkpoint

Hello.
When I run checkpoint (manually, or by checkpointInterval), the database doesn’t respond to the queries (from odbc/isql-v), and sparql endpoint and conductor are unavailable.

After checkpoint is done - everything works fine.
What is the best strategy to avoid this behavior? Do checkpoint at nights, disable remapping, or something else?

virtuoso version:

Version 7.2.6-rc1.3230-pthreads as of Aug 9 2019 (e2a413e)

virtuoso.ini :

[Database]
DatabaseFile       = /var/lib/virtuoso-opensource-7/db/virtuoso.db
ErrorLogFile       = /var/lib/virtuoso-opensource-7/db/virtuoso.log
LockFile           = /var/lib/virtuoso-opensource-7/db/virtuoso.lck
TransactionFile    = /var/lib/virtuoso-opensource-7/db/trxlog/virtuoso20190904090128.trx
xa_persistent_file = /var/lib/virtuoso-opensource-7/db/virtuoso.pxa
;DatabaseFile       = /var/lib/virtuoso/virtuoso.db
;ErrorLogFile       = /var/lib/virtuoso/virtuoso.log
;LockFile           = /var/lib/virtuoso/virtuoso.lck
;TransactionFile    = /var/lib/virtuoso/trxlog/virtuoso20181220201625.trx
;xa_persistent_file = /var/lib/virtuoso/virtuoso.pxa
ErrorLogLevel      = 7
FileExtend         = 200
MaxCheckpointRemap = 500000
Striping           = 0
TempStorage        = TempDatabase
#TransactionFile   = virtuoso20190522051400.trx

[TempDatabase]
DatabaseFile       = /var/lib/virtuoso-opensource-7/db/virtuoso-temp.db
TransactionFile    = /var/lib/virtuoso-opensource-7/db/virtuoso-temp.trx
;DatabaseFile       = /var/lib/virtuoso/virtuoso-temp.db
;TransactionFile    = /var/lib/virtuoso/virtuoso-temp.trx
MaxCheckpointRemap = 2000
Striping           = 0

;
; Server parameters
;
[Parameters]
DefaultIsolation           = 2
ServerPort                 = 1111
LiteMode                   = 0
DisableUnixSocket          = 1
DisableTcpSocket           = 0
;SSLServerPort = 2111
;SSLCertificate = cert.pem
;SSLPrivateKey = pk.pem
;X509ClientVerify = 0
;X509ClientVerifyDepth = 0
;X509ClientVerifyCAFile = ca.pem
TransactionAfterImageLimit = 99999999
MaxClientConnections       = 100
CheckpointInterval         = 60
O_DIRECT                   = 0
CaseMode                   = 2
MaxStaticCursorRows        = 5000
CheckpointAuditTrail       = 1
AllowOSCalls               = 0
SchedulerInterval          = 10
;DirsAllowed              = ., /usr/share/virtuoso/vad, /import, /EGRUL
DirsAllowed                = ., /usr/share/virtuoso-opensource-7/vad, /import, /EGRUL
ThreadCleanupInterval      = 1
ThreadThreshold            = 10
ResourcesCleanupInterval   = 1
FreeTextBatchSize          = 100000
SingleCPU                  = 0
VADInstallDir              = /usr/share/virtuoso-opensource-7/vad/
;VADInstallDir            = /usr/share/virtuoso/vad/
PrefixResultNames          = 0
RdfFreeTextRulesSize       = 100
IndexTreeMaps              = 256
;IndexTreeMaps = 256
MaxMemPoolSize             = 6000000000
;MaxMemPoolSize = 200000000
PrefixResultNames          = 0
MacSpotlight               = 0
IndexTreeMaps              = 64
MaxQueryMem                = 32G	; memory allocated to query processor
;HashJoinSpace = 8G
VectorSize                 = 10000	; initial parallel query vector (array of query operations) size
MaxVectorSize              = 3500000	; query vector size threshold.
AdjustVectorSize           = 0
ThreadsPerQuery            = 32
AsyncQueueMaxThreads       = 32
TraceOn                    = user_log, failed_log, user_names, compile, ddl_log, client_sql, errors, dsn, sql_send, transact, remote_transact, exec, soap, cursor
;;
;; When running with large data sets, one should configure the Virtuoso
;; process to use between 2/3 to 3/5 of free system memory and to stripe
;; storage on all available disks.
;;
;; Uncomment next two lines if there is 2 GB system memory free
;NumberOfBuffers = 170000
;MaxDirtyBuffers = 130000
;; Uncomment next two lines if there is 4 GB system memory free
;NumberOfBuffers = 340000
; MaxDirtyBuffers = 250000
;; Uncomment next two lines if there is 8 GB system memory free
;NumberOfBuffers = 680000
;MaxDirtyBuffers = 500000
;; Uncomment next two lines if there is 16 GB system memory free
;NumberOfBuffers = 1360000
;MaxDirtyBuffers = 1000000
;; Uncomment next two lines if there is 32 GB system memory free
;NumberOfBuffers = 2720000
;MaxDirtyBuffers = 2000000
;; Uncomment next two lines if there is 48 GB system memory free
;NumberOfBuffers = 4000000
;MaxDirtyBuffers = 3000000
;; Uncomment next two lines if there is 64 GB system memory free
;NumberOfBuffers = 5450000
;MaxDirtyBuffers = 4000000
;;
;; Note the default settings will take very little memory
;; but will not result in very good performance
;;
NumberOfBuffers            = 20710000
MaxDirtyBuffers            = 15200000
;NumberOfBuffers          = 7425000
;MaxDirtyBuffers          = 5568750

[HTTPServer]
ServerPort                  = 8190
SSLPort                     = 8890
SSLCertificate              = /data/ssl/fullchain.pem
SSLPrivateKey               = /data/ssl/privkey.pem
X509ClientVerify            = 0
ServerRoot                  = /var/lib/virtuoso-opensource-7/vsp
;ServerRoot                  = /USR/lib/virtuoso/vsp
MaxClientConnections        = 10
DavRoot                     = DAV
EnabledDavVSP               = 0
HTTPProxyEnabled            = 0
TempASPXDir                 = 0
DefaultMailServer           = localhost:25
MaxKeepAlives               = 10
KeepAliveTimeout            = 10
MaxCachedProxyConnections   = 10
ProxyConnectionCacheTimeout = 15
HTTPThreadSize              = 680000;280000
HttpPrintWarningsInOutput   = 0
Charset                     = UTF-8
;HTTPLogFile = /var/log/virtuoso/http.log
MaintenancePage             = atomic.html
EnabledGzipContent          = 1

[AutoRepair]
BadParentLinks = 0

[Client]
SQL_PREFETCH_ROWS  = 100
SQL_PREFETCH_BYTES = 16000
SQL_QUERY_TIMEOUT  = 0
SQL_TXN_TIMEOUT    = 0
;SQL_NO_CHAR_C_ESCAPE = 1
;SQL_UTF8_EXECS = 0
;SQL_NO_SYSTEM_TABLES = 0
;SQL_BINARY_TIMESTAMP = 1
;SQL_ENCRYPTION_ON_PASSWORD = -1

[VDB]
ArrayOptimization           = 0
NumArrayParameters          = 10
VDBDisconnectTimeout        = 1000
KeepConnectionOnFixedThread = 0

[Replication]
;ServerName   = db-CE05D567F527
;ServerEnable = 1
;QueueMax     = 50000

;
; Striping setup
;
; These parameters have only effect when Striping is set to 1 in the
; [Database] section, in which case the DatabaseFile parameter is ignored.
;
; With striping, the database is spawned across multiple segments
; where each segment can have multiple stripes.
;
; Format of the lines below:
; Segment<number> = <size>, <stripe file name> [, <stripe file name> .. ]
;
; <number> must be ordered from 1 up.
;
; The <size> is the total size of the segment which is equally divided
; across all stripes forming the segment. Its specification can be in
; gigabytes (g), megabytes (m), kilobytes (k) or in database blocks
; (b, the default)
;
; Note that the segment size must be a multiple of the database page size
; which is currently 8k. Also, the segment size must be divisible by the
; number of stripe files forming the segment.
;
; The example below creates a 200 meg database striped on two segments
; with two stripes of 50 meg and one of 100 meg.
;
; You can always add more segments to the configuration, but once
; added, do not change the setup.
;
[Striping]
Segment1 = 100M, db-seg1-1.db, db-seg1-2.db
Segment2 = 100M, db-seg2-1.db
;...
;[TempStriping]
;Segment1 = 100M, db-seg1-1.db, db-seg1-2.db
;Segment2 = 100M, db-seg2-1.db
;...
;[Ucms]
;UcmPath = <path>
;Ucm1 = <file>
;Ucm2 = <file>
;...

[Zero Config]
ServerName = virtuoso (CE05D567F527)
;ServerDSN = ZDSN
;SSLServerName =
;SSLServerDSN =

[Mono]
;MONO_TRACE = Off
;MONO_PATH = <path_here>
;MONO_ROOT = <path_here>
;MONO_CFG_DIR = <path_here>
;virtclr.dll =

[URIQA]
DynamicLocal = 0
DefaultHost  = localhost:8890

[SPARQL]
;ExternalQuerySource = 1
;ExternalXsltSource = 1
;DefaultGraph = http://localhost:8890/dataspace
;ImmutableGraphs = http://localhost:8890/dataspace
ResultSetMaxRows           = 1000
MaxQueryCostEstimationTime = 60000	; in seconds
MaxQueryExecutionTime      = 0	; in seconds
DefaultQuery               = PREFIX crm2: <http://sp7.ru/ontology/> SELECT * WHERE { ?a ?sp crm2:Organization. ?sp rdf:singletonPropertyOf rdf:type. } LIMIT 100
DeferInferenceRulesInit    = 0	; controls inference rules loading
;PingService = http://rpc.pingthesemanticweb.com/

[Plugins]
LoadPath = /usr/lib/virtuoso-opensource-7/hosting
;LoadPath = /usr/lib/virtuoso/hosting
Load1    = plain, wikiv
Load2    = plain, mediawiki
Load3    = plain, creolewiki
Load4    = plain, im
;Load5 = plain, wbxml2
;Load6 = plain, hslookup
;Load7 = attach, libphp5.so
;Load8 = Hosting, hosting_php.so
;Load9 = Hosting,hosting_perl.so
;Load10 = Hosting,hosting_python.so
;Load11 = Hosting,hosting_ruby.so
;Load12 = msdtc,msdtc_sample

Triple count: 5 142 593 917
Checkpoint time: 80-180s

@_shedy: When you say the checkpoint takes 80-180 secs, is the Virtuoso database inaccessible for this period, or is this the time indicated in the Virtuoso log that the checkpoint took? As the checkpoint atomic phase when the database is inaccessible should only be for a few seconds, as indicated in the Checkpoint Duration documentation.

I note you have MaxCheckpointRemap = 500000 in the INI file, which I presume is 25% of the the database pages as indicated in the Checkpoint Duration documentation, which should assist in speeding checkpoint duration.

Performing checkpoints at night if the service is not in use at this time is one way to avoid the atomic state of a checkpoint from affecting user access during usage. Or you can equally decrease the CheckpointInterval in the INI file to make checkpoints more frequent, which would result in less data needing to be checkpointed, thereby reducing the time for the checkpoint including the duration of the atomic state.

@hwilliams

The database is inaccessible for 80-180s and the message in the log same:

23:04:33 Checkpoint finished, new log is /var/lib/virtuoso-opensource-7/db/trxlog/virtuoso20191022230109.trx
23:01:52 Checkpoint started

Checkpoint atomic time: 712 s (from status()), but in logs ~ 3mins

Total Pages: 45 472 512
Was set the MaxCheckpointRemap = 11 200 000 (It gave no changes)

Now we do the checkpoint once at night (by logs - it takes about 3 mins)

Also, when I run manually (isql-v) checkpoint after checkpoint immediately, the database is inaccessible for the constant time ~ 2-3mins:

SQL> checkpoint;
Connected to OpenLink Virtuoso
Driver: 07.20.3230 OpenLink Virtuoso ODBC Driver
Done. -- 172984 msec.
SQL> checkpoint;
Done. -- 141484 msec.