[sqlite] SQLite v3.27.2 memory usage
Hello - I have a question on SQLite memory usage. I'm successfully using SQLite v3.27.2 amalgamation on an embedded ARM processor from STMicro with SD card and no OS. The database file size is about 3.8GB. The file system is Segger emFile FAT32. I've configured SQLite to use 3.7MB RAM for heap and 2MB RAM for cache. My application RTree queries the db every 1 second. After about 25-30 minutes of operation, the query returns SQLITE_ERROR with error message "out of memory". It appears that there is a leak somewhere. Does anyone know why this error occurs? Do I have to periodically call the release memory functions or is that inherent to SQLite? Any other ideas? Compilation flags:SQLITE_MUTEX_NOOP=1SQLITE_OS_OTHER=1SQLITE_DISABLE_LFS=1SQLITE_THREADSAFE=0SQLITE_ENABLE_RTREE=1SQLITE_ENABLE_MEMSYS5=1HAVE_MALLOC_USABLE_SIZE=1Thanks in advance!Dave ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] R*Trees query data cached?
Thanks Gunter. Below are the execution times:
pragma table_value(pragma table_info(adas_link_geometry) 2mspragma
table_value(pragma table_info(idx_adas_link_geometry) 1msSELECT 1.1 secondsSELECT 9msSELECT 9msSELECT
9msSELECT 9ms
It appears that the bulk of the time is taken up in the initial query.
Presumably, there is buffering that occurs both in SQLite and my file system to
assist in future queries. I've changed some buffer parameters in my file
system initialization and was able to reduce the initial query time from 1.6
seconds to 1.1 seconds. I've also asked Segger file system tech support for
some help understanding the initial query time as it applies to file
reads/buffering. Using sqlite3_config() I've changed some SQLite page cache
and heap parameters, and disabled a few unwanted #defines which has decreased
initial query time. Do the above times give you any clues about the initial
query time? Any other ideas would be helpful.
Thanks again.
From: Hick Gunter <h...@scigames.at>
To: 'SQLite mailing list' <sqlite-users@mailinglists.sqlite.org>
Sent: Wednesday, March 21, 2018 1:29 AM
Subject: Re: [sqlite] [EXTERNAL] R*Trees query data cached?
The .describe is just to make sure the SQLite has loaded the table definition.
Try pragma table_info();
-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im
Auftrag von David Ashman - Zone 7 Engineering, LLC
Gesendet: Dienstag, 20. März 2018 22:45
An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
Betreff: Re: [sqlite] [EXTERNAL] R*Trees query data cached?
Thank you for the quick reply Hick. I've implemented your script file in C
since I'm running this application in the embedded world with no OS. I don't
see a .describe in the SQLite documentation. I've tried to use .schema but
that returns an error. Do you have another suggestion to obtain the table
information you had in mind?
From: Hick Gunter <h...@scigames.at>
To: 'SQLite mailing list' <sqlite-users@mailinglists.sqlite.org>
Sent: Tuesday, March 20, 2018 9:04 AM
Subject: Re: [sqlite] [EXTERNAL] R*Trees query data cached?
SQLite does not have "query caching". It does have a "page cache" that will
keep heavily used pages iin memory. There is also the possibility of a
file-system/os-level cache. To break down the 1.6 seconds required for the
first query, try executing an sql script. In linux this would be along the
lines of:
> date; sqlite3 file.db < script.sql;date
With script.sql containing:
Select strftime('%Y-%m-%d %H:%M:%S.%f'); Select strftime('%Y-%m-%d
%H:%M:%S.%f'); .describe adas_link_geometry Select strftime('%Y-%m-%d
%H:%M:%S.%f'); .describe idx_adas_link_geometry Select strftime('%Y-%m-%d
%H:%M:%S.%f'); Select ; Select strftime('%Y-%m-%d
%H:%M:%S.%f'); Select ; Select strftime('%Y-%m-%d
%H:%M:%S.%f'); Select ; Select strftime('%Y-%m-%d %H:%M:%S.%f');
This should give you an idea of where the time is being spent.
-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im
Auftrag von David Ashman - Zone 7 Engineering, LLC
Gesendet: Dienstag, 20. März 2018 16:34
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] R*Trees query data cached?
Hello -
I have a question on SQLite query data buffering.
I'm successfully using SQLite v3.22.0 on an embedded ARM processor from ST with
SD card. The database file size is about 750MB. The file system is Segger
emFile FAT32. I've configured SQLite to use 6MB RAM for heap. I've done some
query time benchmarking and found that the very first R*Trees query takes about
1.6 seconds to complete. Each successive R*Trees query (same query string with
slightly different search parameters) takes about 11ms to complete. Being new
to SQLite and spatial queries, I'm trying to understand the substantial query
time differences... does SQLite cache data from each query for future queries?
The initial query:SELECT LINK_ID, FROM_REF_ELEVATION, TO_REF_ELEVATION FROM
adas_link_geometry, idx_adas_link_geometry WHERE adas_link_geometry.ROWID =
idx_adas_link_geometry.id AND minLat > 454760320 AND maxLat < 454800320 AND
minLong > -1226807072 AND maxLong < -1226767072;
Thanks in advance,Dave
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
Gunter Hick | Software Engineer | Scientific Games International GmbH |
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43
1 80100 - 0
May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
Re: [sqlite] [EXTERNAL] R*Trees query data cached?
Thank you for the quick reply Hick. I've implemented your script file in C
since I'm running this application in the embedded world with no OS. I don't
see a .describe in the SQLite documentation. I've tried to use .schema but
that returns an error. Do you have another suggestion to obtain the table
information you had in mind?
From: Hick Gunter <h...@scigames.at>
To: 'SQLite mailing list' <sqlite-users@mailinglists.sqlite.org>
Sent: Tuesday, March 20, 2018 9:04 AM
Subject: Re: [sqlite] [EXTERNAL] R*Trees query data cached?
SQLite does not have "query caching". It does have a "page cache" that will
keep heavily used pages iin memory. There is also the possibility of a
file-system/os-level cache. To break down the 1.6 seconds required for the
first query, try executing an sql script. In linux this would be along the
lines of:
> date; sqlite3 file.db < script.sql;date
With script.sql containing:
Select strftime('%Y-%m-%d %H:%M:%S.%f');
Select strftime('%Y-%m-%d %H:%M:%S.%f');
.describe adas_link_geometry
Select strftime('%Y-%m-%d %H:%M:%S.%f');
.describe idx_adas_link_geometry
Select strftime('%Y-%m-%d %H:%M:%S.%f');
Select ;
Select strftime('%Y-%m-%d %H:%M:%S.%f');
Select ;
Select strftime('%Y-%m-%d %H:%M:%S.%f');
Select ;
Select strftime('%Y-%m-%d %H:%M:%S.%f');
This should give you an idea of where the time is being spent.
-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im
Auftrag von David Ashman - Zone 7 Engineering, LLC
Gesendet: Dienstag, 20. März 2018 16:34
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] R*Trees query data cached?
Hello -
I have a question on SQLite query data buffering.
I'm successfully using SQLite v3.22.0 on an embedded ARM processor from ST with
SD card. The database file size is about 750MB. The file system is Segger
emFile FAT32. I've configured SQLite to use 6MB RAM for heap. I've done some
query time benchmarking and found that the very first R*Trees query takes about
1.6 seconds to complete. Each successive R*Trees query (same query string with
slightly different search parameters) takes about 11ms to complete. Being new
to SQLite and spatial queries, I'm trying to understand the substantial query
time differences... does SQLite cache data from each query for future queries?
The initial query:SELECT LINK_ID, FROM_REF_ELEVATION, TO_REF_ELEVATION FROM
adas_link_geometry, idx_adas_link_geometry WHERE adas_link_geometry.ROWID =
idx_adas_link_geometry.id AND minLat > 454760320 AND maxLat < 454800320 AND
minLong > -1226807072 AND maxLong < -1226767072;
Thanks in advance,Dave
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
Gunter Hick | Software Engineer | Scientific Games International GmbH |
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43
1 80100 - 0
May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] R*Trees query data cached?
Hello - I have a question on SQLite query data buffering. I'm successfully using SQLite v3.22.0 on an embedded ARM processor from ST with SD card. The database file size is about 750MB. The file system is Segger emFile FAT32. I've configured SQLite to use 6MB RAM for heap. I've done some query time benchmarking and found that the very first R*Trees query takes about 1.6 seconds to complete. Each successive R*Trees query (same query string with slightly different search parameters) takes about 11ms to complete. Being new to SQLite and spatial queries, I'm trying to understand the substantial query time differences... does SQLite cache data from each query for future queries? The initial query:SELECT LINK_ID, FROM_REF_ELEVATION, TO_REF_ELEVATION FROM adas_link_geometry, idx_adas_link_geometry WHERE adas_link_geometry.ROWID = idx_adas_link_geometry.id AND minLat > 454760320 AND maxLat < 454800320 AND minLong > -1226807072 AND maxLong < -1226767072; Thanks in advance,Dave ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
