Re: [sqlite] Huge RAM usage when sqlite is started from another thread
Yes, I can see that SQLite is doing just small allocations using memory
methods.
I also tried to limit the size of database using following two methods
rc = sqlite3_config(SQLITE_CONFIG_HEAP, buffer, 500, 64);
if(rc != SQLITE_OK)
{
printf("Failed to set custom heap memory\n");
}
rc = sqlite3_soft_heap_limit64(100);
printf("Prev: %d\n", rc);
When I use both of them as in this example, I get usage of DB of around
22MB, what is not so bad.
Using of small heap (array in my case) should be fine for my application,
as it is going to use just small databases (1 - 3MB).
What do you think about this technique ? I think, it could be very
dangerous when size of database grows, but that should not happen.
Thank you and Regards,
Martin
2018-07-13 20:58 GMT+02:00 Richard Hipp :
> On 7/13/18, Martin Vystrčil wrote:
> >
> > But still, I would like to solve this somehow, could you please suggest
> me
> > some point, where to start ? Are there any possible compilation options,
> to
> > limit this allocation ?
>
> It is not SQLite that is doing this allocation. I suspect it is
> something happening inside of the malloc() in your libc.
>
> You can see the SQLite is never doing a huge allocation by calling
> sqlite3_memory_highwater(0) in place of sqlite3_memory_used().
>
> You are probably better off letting malloc() do whatever allocations
> it wants. However, if you really want to work around this, there are
> ways of getting SQLite to use a memory allocator other than the
> default system malloc(). See https://www.sqlite.org/malloc.html for
> further information. These are sharp techniques, so be careful. But
> if you do it right, you can limit the memory usage of SQLite to
> whatever you want.
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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
Re: [sqlite] Huge RAM usage when sqlite is started from another thread
On 7/13/18, Martin Vystrčil wrote: > > But still, I would like to solve this somehow, could you please suggest me > some point, where to start ? Are there any possible compilation options, to > limit this allocation ? It is not SQLite that is doing this allocation. I suspect it is something happening inside of the malloc() in your libc. You can see the SQLite is never doing a huge allocation by calling sqlite3_memory_highwater(0) in place of sqlite3_memory_used(). You are probably better off letting malloc() do whatever allocations it wants. However, if you really want to work around this, there are ways of getting SQLite to use a memory allocator other than the default system malloc(). See https://www.sqlite.org/malloc.html for further information. These are sharp techniques, so be careful. But if you do it right, you can limit the memory usage of SQLite to whatever you want. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Huge RAM usage when sqlite is started from another thread
Thank you both for help. What Richar Hipp wrote is truth and something what I also saw using valgrind - memory allocation (overall consumption) of around 300 - 500 kB. To answer at least some one Bob's question from first e-mail, I'm trying to open in memory database (:memory:), so size of database should be almost nothing. By checking smaps, I can see big allocation there. It seems that all your assumptions are correct, memory doesn't seem to be used. 7fdd28021000-7fdd2c00 ---p 00:00 0 Size: 65404 kB Rss: 0 kB Pss: 0 kB Shared_Clean: 0 kB Shared_Dirty: 0 kB Private_Clean: 0 kB Private_Dirty: 0 kB Referenced:0 kB Anonymous: 0 kB AnonHugePages: 0 kB Shared_Hugetlb:0 kB Private_Hugetlb: 0 kB Swap: 0 kB SwapPss: 0 kB KernelPageSize:4 kB MMUPageSize: 4 kB Locked:0 kB VmFlags: mr mw me nr sd But still, I would like to solve this somehow, could you please suggest me some point, where to start ? Are there any possible compilation options, to limit this allocation ? It would be completely fine for me, to allocate like 1MB or so, but this is way too much, although not used. Thank you for your help, Best Regards, Martin 2018-07-13 17:31 GMT+02:00 Bob Friesenhahn : > On Fri, 13 Jul 2018, Richard Hipp wrote: > >> >> The OP's test program (with a bug fix, various whitespace changes, and >> the addition of a call to sqlite3_memory_used()) is show below. >> sqlite3_memory_used() reports no difference in memory allocation. >> > > The usage is uninitialized/unmodified virtual memory which could be from a > heap allocation (with no subsequent writes to it) or due to memory mapping > something such as a file. The cause of the the usage may be deduced by > inspecting the /proc/[pid]/smaps content on a Linux system where '[pid]' is > the process id of the program. > > Bob > -- > Bob Friesenhahn > bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/ > GraphicsMagick Maintainer,http://www.GraphicsMagick.org/ > ___ > 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
Re: [sqlite] SQLite .dump
On Jul 13, 2018, at 10:15 AM, dmp wrote:
>
> Seems .dump uses a short output of skipping the column names.
To call that a problem requires that you justify why you’d need the column
names to be specified in the INSERT statements.
If you take the .dump file as-given and just run it, the INSERT statements are
fine as-is because the .dump file has a CREATE TABLE immediately above. There
can be no mismatch if you do not change the .dump file.
If you’re intending to edit the CREATE TABLE statements out of the .dump file
and run those INSERT statements on a SQLite DB with a different schema, then
yes, you’d need the column names to be explicitly provided, but that seems like
quite a special case. And of course, you could just edit the INSERT statements
at the same time.
> Always specifying the column names list
That’s good practice whenever there can be significant drift between the code
that does the CREATE TABLE call and the code that does the INSERT. For
example, if your application’s DB is initialized with a script and then
potentially years of software updates do ALTER TABLE calls on it on upgrades,
then yes, it’s important to qualify the column names in your INSERT statements.
> and using the database's
> identifier quoting character.
It does that at need already:
sqlite> create table "x y" ("a b" INTEGER);
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS "x y" ("a b" INTEGER);
COMMIT;
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Idea: defining table-valued functions directly in SQL
On 07/07/18 19:47, E.Pasma wrote:
But the ideas allow a parameter name to be identical to a column name,
which must be an error.
While I might prefer that to be the case, it's actually not. We do not
have the ability for column expressions to reference other columns, so
there is no ambiguity even if a parameter were to be named the same as a
column. It's also currently permissible for multiple columns to have
the same name.
CREATE TABLE x (a, b);
INSERT INTO x VALUES (1, 2), (3, 4);
CREATE VIEW y AS SELECT a, b AS a FROM x;
CREATE VIEW z (a, a) AS SELECT * FROM x;
SELECT a, b AS a FROM x;
a a
-- --
1 2
3 4
SELECT * FROM y;
a a:1
-- --
1 2
3 4
SELECT * FROM z;
a a:1
-- --
1 2
3 4
The handling varies a little bit between bare SELECT statements and
SELECTs wrapped in views. The latter appends a suffix to identical
column names, but the former does not.
I think the only rule would be that no two parameters can have the same
name.
So why not a single column list and a modifier to turn a column into
an argument?
CREATE VIEW double (result, arg HIDDEN) AS SELECT arg * 2;
SELECT * FROM numbers, double(x);
I did mention HIDDEN as an option in my original post, though not with
the syntax you suggest:
http://sqlite.1065341.n5.nabble.com/Idea-defining-table-valued-functions-directly-in-SQL-td102368.html
At the time I wasn't considering the optional parenthesized output
column-name list following the view name, since frankly I never use it.
I was thinking only of the SELECT statement, which also supplies the
output column names by means of AS clauses, or defaulting to using the
input expressions themselves as the names. Thus I allowed HIDDEN as a
column modifier in the SELECT clause, roughly copying the way virtual
table-valued functions currently work. (I also suggested saying
PARAMETER instead of HIDDEN to be a bit clearer about its function.)
If I understand you correctly, your idea is essentially to take this
approach but move the HIDDEN columns (i.e., parameters) from the SELECT
statement to the output column name list.
I have two issues with this. One is the fact that the output column
name list is optional, but now it would become required, at the cost of
the user having to retype the output column names even in cases where
the default is already good, or even if the user would have preferred to
use AS to name the output columns (which is what I do).
The second issue is one I had with my own idea as well, though I was
inarticulate about it. Whether the parameters are listed in the output
column name list or in the output column expression list, either way
we're interspersing outputs with inputs.
All that said, I do prefer your idea over mine. Having the parameter
names in the same list as the output column names may be mildly
confusing though overall okay with me, but I find my original idea of
having the parameter names in the same list as the output column
expressions to be off-the-charts weird, which is why I said in my
original email that I do not recommend using HIDDEN.
The bottom line for me is that at this point, my preference still goes
to the following:
CREATE VIEW double (result) PARAMETERS (arg) AS SELECT arg * 2;
SELECT * FROM numbers, double(x);
WITH double (result) PARAMETERS (arg) AS (SELECT arg * 2)
SELECT * FROM numbers, double(x);
What if a parameter name happens to match a column name in one (or
more) of the tables being pulled in by a FROM clause? A table name
qualifier is needed to disambiguate. It could be the function name,
though it could be clearer to do like upsert ("excluded") and have a
special token, e.g. "parameters". (cf.
https://sqlite.org/lang_UPSERT.html)
This side idea is more powerful than just to disambiguate column
names. It tells me that a column is pulled from outside the view. An
explicit parameter list is not even needed.
That's really not what I had in mind. I wasn't thinking of FROM clauses
outside the view, rather those inside.
CREATE TABLE people (name, age);
INSERT INTO people VALUES ('Bob', 33), ('Jen', 19), ('Liz', 30);
CREATE VIEW older PARAMETERS (name, otherName) AS
SELECT t1.age > t2.age AS older
FROM people AS t1 WHERE t1.name = parameters.name
, people AS t2 WHERE t2.name = parameters.name;
SELECT t1.name AS name
, t2.name AS otherName
, older(t1.name, t2.name)
FROM people AS t1
, people AS t2
WHERE t1.name != t2.name;
name otherName older
- -
Bob Jen1
Bob Liz1
Jen Bob0
Jen Liz0
Liz Bob0
Liz Jen1
SELECT t1.name AS senior
, t2.name AS junior
FROM people AS t1
, people AS t2
WHERE older(t1.name, t2.name);
senior junior
-- --
Bob Jen
Bob Liz
Liz Jen
The column must only exist in the view
Re: [sqlite] SQLite .dump
I'm sorry, but I don't see a question in your post. Are you suggesting that the SQLite command-line tool has a bug ? Are you suggesting that Ajqvue has a bug ? Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite .dump
Hello,
Recently in testing my GUI tool I made a comparison from the
tool's dump and SQLite's command line .dump tool.
Seems .dump uses a short output of skipping the column names.
According to some of my research for various databases I use
one of these as options for SQL dump output:
http://ajqvue.com/docs/Manual/Ajqvue_Manual.html#Preferences_Data_Export_SQL
Preferences Data Export SQL
Always specifying the column names list and using the database's
identifier quoting character.
danap.
===
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE datatypes (data_type_id INTEGER PRIMARY KEY AUTOINCREMENT,
int_type INTEGER, real_type REAL, text_type TEXT,
blob_type BLOB, date_type DATE, time_type TIME,
datetime_type DATETIME, timestamp_type TIMESTAMP);
INSERT INTO datatypes VALUES(1,100,200.18864,'some text',
X'6162630a',153051120,68715000,1530343358000,
1530559371079);
===
GUI tool:
--
-- Dumping data for table "datatypes"
--
INSERT INTO "datatypes" ("data_type_id", "int_type", "real_type",
"text_type",
"blob_type", "date_type", "time_type",
"datetime_type",
"timestamp_type") VALUES(1, 100, 200.2, 'some text',
x'6162630a', 153051120, 68715000, 1530343358000,
1530559371079);
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] (no subject)
On 7/12/18, danap wrote: >> I use a dump >> in my interface which I used with diff to compare changes in my >> personal expense database. This was to insure changes introduced in work >> on the interface were not screwing things up. Very helpful to insure >> your not introducing bugs. D. Richard Hipp wrote: > I am glad that has been working for you. But there is a caveat: The > ".dump" format can (and does) change slightly from one release of > SQLite to the next. So you are welcomed to continue using ".dump" > this way, but just be careful that you do not compare the .dump output > from two different versions of SQLite. Well, sorry Mr. Hipp, but the dump was from my own SQLite GUI. So what I was doing is insuring some coding changes to the GUI's dump had not changed anything, by comparing to prior dumps of data with the same tool. So I was really doing exactly what you caution about to debug between versions. Idea being, dump ASCII data, compare over time with some kind of diff tool to see changes. So to user's question, be sure to use same version of the dump tool as you indicate. danap. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Huge RAM usage when sqlite is started from another thread
On Fri, 13 Jul 2018, Richard Hipp wrote: The OP's test program (with a bug fix, various whitespace changes, and the addition of a call to sqlite3_memory_used()) is show below. sqlite3_memory_used() reports no difference in memory allocation. The usage is uninitialized/unmodified virtual memory which could be from a heap allocation (with no subsequent writes to it) or due to memory mapping something such as a file. The cause of the the usage may be deduced by inspecting the /proc/[pid]/smaps content on a Linux system where '[pid]' is the process id of the program. Bob -- Bob Friesenhahn bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/ GraphicsMagick Maintainer,http://www.GraphicsMagick.org/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Huge RAM usage when sqlite is started from another thread
On 7/13/18, Bob Friesenhahn wrote:
> On Fri, 13 Jul 2018, Martin Vystrčil wrote:
>
>> Hello everyone,
>>
>> I have a problem using sqlite in one of my project. When I create instance
>> of sqlite (sqlite_open) from main thread, memory consumption is in normal
>> (a few megabytes). But when I start sqlite from another thread,
>> immediately
>> around 70 - 80 MB of memory is allocated.
>>
>> Here is the smallest example, which can reproduce this problem. Link to
>> pastebin where source code is: https://pastebin.com/BkU3uMCb.
>
> I can not be bothered to visit such a site.
The OP's test program (with a bug fix, various whitespace changes, and
the addition of a call to sqlite3_memory_used()) is show below.
sqlite3_memory_used() reports no difference in memory allocation.
-
#include
#include
#include
#include
using namespace std;
void *poll(void *data){
int rc = 0;
char *errMsg = 0;
sqlite3 *database;
int i;
rc = sqlite3_open(":memory:", );
if(rc != SQLITE_OK){
std::cout << "Cannot open memory database" << std::endl;
}
rc = sqlite3_exec(database, "create table if not exists xyz(id int,
descr text)",0,0,);
if(rc != SQLITE_OK){
std::cout << "Cannot create table in SQLiteDB: " << errMsg << std::endl;
}
sqlite3_free(errMsg);
for(i=0; i<10; i++){
std::cout << "Thread safe: " << sqlite3_threadsafe() << std::endl;
rc = sqlite3_exec(database, "pragma page_count",0,0,);
if(rc != SQLITE_OK){
std::cout << "Select page count from DB failed " << errMsg << std::endl;
sqlite3_free(errMsg);
}
std::cout << "memory used: " << sqlite3_memory_used() << std::endl;
usleep(10);
std::cout << "Select from DBSQLite" << std::endl;
}
}
int main(int argc, char *argv[]){
if(argc > 1){
poll(NULL);
}else{
cout << "Starting of sqlite DB threaded mode ..." << endl;
pthread_t p;
if(pthread_create(, NULL, poll, NULL)){
cerr << "Error creating thread" << endl;
}
if(pthread_join(p, NULL)){
cerr << "Cannot join thread" << endl;
}
}
}
--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] CSV import deletes trailing zeroes on text fields
People double clicking .csv's to edit them in Excel has caused so many headaches. Leading 0's dropped, things like "4E3" turned into 4000, "3-12" turned into "12-Mar", mixups between Windows encoding and UTF-8 mangling characters, etc. If you have to or prefer to view things in Excel, the way I recommend is opening a new blank session, then using the Data, Get External Data, From Text button. Then you can select the encoding, delimiter, quoting, and mark all the columns as text. Then it's "less likely" to have destroyed things. -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Robert Weiss Sent: Thursday, July 12, 2018 6:01 PM To: SQLite mailing list Subject: Re: [sqlite] CSV import deletes trailing zeroes on text fields 1. Rename .csv to .txt2. Excel will now ask for column treatment on import3. Specify the problem column(s) as "text" not "general" There are other problems with csv recognizing text as numbers. I had a column with content, say 123D4. Excel recognized the old FORTRAN double precision format and called it 123. On Thursday, July 12, 2018, 2:39:09 PM PDT, David Burgess wrote: "CSV import deletes /leading/ zeroes on text fields" excel does this. Quite difficult to stop it from doing so. On Fri, Jul 13, 2018 at 6:52 AM, R Smith wrote: > I believe your subject should read: "CSV import deletes /leading/ zeroes on > text fields" - Your trailing Zero is in tact. > > And your declaration is wrong - in SQL the column name is first, then the > Type, so it must be: > CREATE TABLE foo(bar TEXT NOT NULL); > > Opening the csv file in Excel or CALC will probably do the exact same thing > - but SQLite should be better than that. > > > > On 2018/07/12 10:47 AM, Simon Leo Hafner wrote: >> >> To reproduce: >> >> create table foo ( >> text bar not null >> ); >> >> .import test.csv foo >> >> select * from foo; >> >> With test.csv: >> >> test >> 01230 >> >> Expected result: >> >> test >> 01230 >> >> Actual result: >> >> test >> 1230 >> ___ >> 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-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-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Huge RAM usage when sqlite is started from another thread
On Fri, 13 Jul 2018, Martin Vystrčil wrote: Hello everyone, I have a problem using sqlite in one of my project. When I create instance of sqlite (sqlite_open) from main thread, memory consumption is in normal (a few megabytes). But when I start sqlite from another thread, immediately around 70 - 80 MB of memory is allocated. Here is the smallest example, which can reproduce this problem. Link to pastebin where source code is: https://pastebin.com/BkU3uMCb. I can not be bothered to visit such a site. What is the size of the sqlite database file? Is memory-mapping enabled on the file? Is WAL-mode enabled? There is also some more info about memory usage. The growth seems to be almost entirely virtual memory, which could be due to the size of the database file and the options used (e.g. mmap of the file leads to more virtual memory used). The VmRSS value (in conjunction with VmSwap=0) is more interesting since it indicates how much memory is actually being used. Bob -- Bob Friesenhahn bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/ GraphicsMagick Maintainer,http://www.GraphicsMagick.org/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Huge RAM usage when sqlite is started from another thread
Hello everyone, I have a problem using sqlite in one of my project. When I create instance of sqlite (sqlite_open) from main thread, memory consumption is in normal (a few megabytes). But when I start sqlite from another thread, immediately around 70 - 80 MB of memory is allocated. Here is the smallest example, which can reproduce this problem. Link to pastebin where source code is: https://pastebin.com/BkU3uMCb. There is also some more info about memory usage. There are results of memory usage (from proc/pid/status) when database is opened from main thread. VmPeak:19780 kB VmSize:19636 kB VmLck: 0 kB VmPin: 0 kB VmHWM: 1756 kB VmRSS: 1756 kB VmData: 308 kB VmStk: 132 kB VmExe: 8 kB VmLib: 4744 kB VmPTE:60 kB VmSwap:0 kB And there are results when sqlite is opened from other thread VmPeak: 158904 kB VmSize:93368 kB VmLck: 0 kB VmPin: 0 kB VmHWM: 1636 kB VmRSS: 1636 kB VmData:74040 kB VmStk: 132 kB VmExe: 8 kB VmLib: 4744 kB VmPTE:60 kB VmSwap:0 kB Most notable difference is VmData, which is like 80MB in case when the other thread is started. Is there any reason, why sqlite consumes so much memory when started from other thread ? Or do I have any error in my application ? In parallel with this e-mail I also asked on stackoverflow. https://stackoverflow.com/questions/51317823/sqlite-high-ram-usage-when-connected-from-other-thread?noredirect=1#comment89612124_51317823 Every help is highly appreciated. Best Regards, Martin ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
