Re: [sqlite] Compressed schema in memory?
On 3/16/17, Bob Friesenhahn wrote: > In sqlite_master I see quite a lot of "sql_autoindex" indexes. Do > these auto indexes consume the same RAM as explicit indexes? Yes. Those indexes are implementing UNIQUE constraints. -- 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] Compressed schema in memory?
On 3/16/17, Bob Friesenhahn wrote: > > I just checked and the total character count for the trigger and index > names themselves is only 23k, which is not even a tiny dent in 1.58MB. > Is there a muliplying factor somewhere which would make this worth > doing? I did say it was a "small step" :-) Great journeys begin with a single step. > > Storing original SQL text such as SQL keywords surely consumes a lot > of space (I am assuming this is what is done). If SQL command and > verb text is converted into a more concise specification for internal > use, then less memory should be consumed. The schema is stored as a parse tree. But it still needs to store the names of objects (triggers, indexes, tables, columns) in order to look them up by name in response to various SQL commands. -- 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] Compressed schema in memory?
In sqlite_master I see quite a lot of "sql_autoindex" indexes. Do these auto indexes consume the same RAM as explicit indexes? 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] Compressed schema in memory?
On Thu, 16 Mar 2017, Richard Hipp wrote: One thing you can do right away to save space is pick shorter names for your 650 triggers an d indexes. SQLite stores the full name. But as these names are not (normally) used by DML statements, you can call them whatever you want. I'm showing your average trigger and index name length is 34 characters. Why not shorten them all to 4 characters? That isn't a lot of space, but it is a start. I just checked and the total character count for the trigger and index names themselves is only 23k, which is not even a tiny dent in 1.58MB. Is there a muliplying factor somewhere which would make this worth doing? Storing original SQL text such as SQL keywords surely consumes a lot of space (I am assuming this is what is done). If SQL command and verb text is converted into a more concise specification for internal use, then less memory should be consumed. 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] Compressed schema in memory?
On Thu, 16 Mar 2017, Richard Hipp wrote: Your 664K is a conservative estimate. On my (64-bit linux) desktop, I'm showing 1.58MB of heap space used to store the schema. (Hint: bring up the database in the command-line shell, load the schema by doing something like ".tables", then type ".stats". There will be a line that shows you the total amount of heap memory devoted to storing the schema. I'm showing 1583864 bytes.) We are still using version 3.10.2 and when I type ".stats" I get only "Usage: .stats on|off". One thing you can do right away to save space is pick shorter names for your 650 triggers an d indexes. SQLite stores the full name. But as these names are not (normally) used by DML statements, you can call them whatever you want. I'm showing your average trigger and index name length is 34 characters. Why not shorten them all to 4 characters? That isn't a lot of space, but it is a start. This seems like a good idea and does not limit human comprehension much, particularly if it is done as a step while preparing the final database. 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] Compressed schema in memory?
On 3/16/17, Bob Friesenhahn wrote: > > I shared our database privately with Richard via email. > Your 664K is a conservative estimate. On my (64-bit linux) desktop, I'm showing 1.58MB of heap space used to store the schema. (Hint: bring up the database in the command-line shell, load the schema by doing something like ".tables", then type ".stats". There will be a line that shows you the total amount of heap memory devoted to storing the schema. I'm showing 1583864 bytes.) One thing you can do right away to save space is pick shorter names for your 650 triggers an d indexes. SQLite stores the full name. But as these names are not (normally) used by DML statements, you can call them whatever you want. I'm showing your average trigger and index name length is 34 characters. Why not shorten them all to 4 characters? That isn't a lot of space, but it is a start. -- 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] Compressed schema in memory?
On Thu, 16 Mar 2017, Richard Hipp wrote: On 3/16/17, Bob Friesenhahn wrote: The schema (already stripped to remove white space and comments) for our database has reached 664K Yikes. That's about 10x or 20x what we typically see. Are you able to share your schema with us? I shared our database privately with Richard via email. 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] Compressed schema in memory?
On 3/16/17, Bob Friesenhahn wrote: > > The schema (already stripped to remove white space and comments) for > our database has reached 664K Yikes. That's about 10x or 20x what we typically see. Are you able to share your schema with us? -- 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] Compressed schema in memory?
On 16 Mar 2017, at 8:09pm, Bob Friesenhahn wrote: > Would it be reasonably feasible to compress the per-connection schema data > (stored in RAM) and decompress it as needed? This would make > prepared-statement and possibly other operations a bit slower but if objects > are compressed at sufficiently small granularity, then the per-connection > memory footprint would be reduced. > > The schema (already stripped to remove white space and comments) for our > database has reached 664K and with several processes (with one or more > connections), the memory budget attributed to redundant sqlite connection > schema data is high. The schema stored in memory until the connection is closed is not a copy of the CREATE statements stored in the sqlite_master table. It’s in a format closer to the result you get when you use PRAGMAs like PRAGMA table_info() and PRAGMA index_info(). Also in memory are hashed lists of all table names and other details needed for fast searching, which, of course, cannot be compressed because they need to be searched every time a new SQLite command mentions a table name. What you might be seeing is that initially sqlite_master is read into memory, so it survives in the cache until other SQLite operations overwrite it. But you should not be seeing permanent allocation of storage equivalent to the size of sqlite_master. If you are seeing 664K of storage set aside, and if this increases proportional to the size of sqlite_master that’s not how I thought SQLite worked. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Compressed schema in memory?
Would it be reasonably feasible to compress the per-connection schema data (stored in RAM) and decompress it as needed? This would make prepared-statement and possibly other operations a bit slower but if objects are compressed at sufficiently small granularity, then the per-connection memory footprint would be reduced. The schema (already stripped to remove white space and comments) for our database has reached 664K and with several processes (with one or more connections), the memory budget attributed to redundant sqlite connection schema data is high. Using gzip compression, the database schema reduces to just 62k so there is a 10X benefit. With 10 processes/connections, almost 6MB could be saved with our database. It is likely that the compression ratio is less when compressing many small fragments of text. Thoughts? 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] Is it possible to change "synchronous" flag of ZIPVFS pager?
On 03/16/2017 05:00 PM, Tomasz Maj wrote: I have an application which work with many zipped databases in WAL journal mode and the databases are stored on a media of low write bandwidth. There is a problem that that fsync operations invoked from one thread are blocking other fsync operations invoked from another thread. I think that changing the synchronous mode to NORMAL on some connections may make the blocking issue less painful. May the possibility of changing ZIPVFS synchronous mode be implemented in sqlite? Latest commit in the zipvfs project fixes things so that "PRAGMA synchronous" works with zipvfs databases. Dan. Cheers, Tomasz M From: sqlite-users on behalf of Tomek Maj Sent: Thursday, March 16, 2017 10:50:37 AM To: SQLite mailing list Subject: Re: [sqlite] Is it possible to change "synchronous" flag of ZIPVFS pager? 2017-03-16 9:51 GMT+01:00 Dan Kennedy : On 03/16/2017 01:40 PM, Tomasz Maj wrote: Hi, According to my observations, "PRAGMA synchronous=...;" query affects only the standard sqlite pager. But for zipped databases sqlite uses additional ZIPVFS pager layer which actually decide whether and when to sync content of files associated with a database. It looks like "PRAGMA synchronous=...;" query doesn't have any effect on zipped databases. Are my observations right? If so, is it possible to manipulate "synchronous" flag of ZIPVFS pager? It looks like there is not. Is it something you need to do? Dan. ___ 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] Shared cache mode and busy wait
Today I saw a APSW note about shared cache mode at "https://rogerbinns.github.io/apsw/tips.html#shared-cache-mode";, which led me to "https://sqlite.org/src/tktview/ebde3f66fc64e21e61ef2854ed1a36dfff884a2f";. Reading the sqlite page at "https://sqlite.org/sharedcache.html"; I see that section "2.2. Table Level Locking" is not very clear or straight-forward. One must read between the lines and surmise in order to understand that the sqlite connection timeout (or callback) mechanisms become useless in this mode in a multi-threaded program. It would be useful if there was a statement that the normal mechanism is rendered useless and that the calling thread must implement its own busy-wait if it must succeed. The problem report was closed as "works as designed" (works as implemented?). If the normal busy-wait or callback mechanisms are not supported, then it becomes prohibitive to enable this mode on an existing code base. Given that each sqlite connection duplicates the entire database schema in RAM, this shared cache mode becomes quite useful on limited memory systems. It would be good if it worked `properly'. 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] Unable to clone fossil repo
I was running 1.33, which was the version available in Ubuntu 16.04's repos. Doing a quick download form the website took care of the issue. Thank you! On Thu, Mar 16, 2017 at 11:51 AM, Richard Hipp wrote: > On 3/16/17, Richard Hipp wrote: > > > > As of 2017-03-12, you need Fossil 2.0 or later. What does "fossil -v" > > say for you? > > > > I gave you the wrong command. "fossil version", not "fossil -v". > What does it show? > > -- > 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] Unable to clone fossil repo
On 3/16/17, Richard Hipp wrote: > > As of 2017-03-12, you need Fossil 2.0 or later. What does "fossil -v" > say for you? > I gave you the wrong command. "fossil version", not "fossil -v". What does it show? -- 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] Unable to clone fossil repo
On 3/16/17, Charles Leifer wrote: > Hi, I'm attempting to clone the source tree using the instructions from the > website (http://www.sqlite.org/getthecode.html#clone), but I'm getting an > error when "Artifacts received" hits 58416: > > $ fossil clone http://www.sqlite.org/cgi/src sqlite.fossil As of 2017-03-12, you need Fossil 2.0 or later. What does "fossil -v" say for you? -- 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] Unable to clone fossil repo
Nevermind, I just needed to upgrade from 1.33 to 2.0. On Ubuntu 16.04 the system version is 1.33, but I was able to download the binary from the fossil-scm site and everything worked like a charm. Thanks for the amazing, free software! On Thu, Mar 16, 2017 at 7:02 AM, Charles Leifer wrote: > Hi, I'm attempting to clone the source tree using the instructions from > the website (http://www.sqlite.org/getthecode.html#clone), but I'm > getting an error when "Artifacts received" hits 58416: > > $ fossil clone http://www.sqlite.org/cgi/src sqlite.fossil > > Round-trips: 7 Artifacts sent: 0 received: 58284 > malformed cfile line > Round-trips: 8 Artifacts sent: 0 received: 58416 > Clone done, sent: 1939 received: 35121878 ip: 67.18.92.124 > server returned an error - clone aborted > > I get errors when I try www2 and www3, or if I try HTTPS. > > With verbose logging, the output isn't much different: > > Bytes Cards Artifacts Deltas > Sent: 53 1 0 0 > Received: 5000352 26717293 13096 > Sent: 72 2 0 0 > Received: 5069927 24897257 12181 > Sent: 57 1 0 0 > Received: 5000173 15434836 6892 > Sent: 57 1 0 0 > Received: 5000385 19604834 8987 > Sent: 57 1 0 0 > Received: 5002648 19884874 9081 > Sent: 57 1 0 0 > Received: 5046326 9825299 4623 > Sent: 57 1 0 0 > processed: 40% > malformed cfile line > Received: 5000745263 3128 > Sent: 43 0 0 0 > Received: 32 1 0 0 > Clone done, sent: 1936 received: 35121878 ip: 67.18.92.124 > server returned an error - clone aborted > > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Unable to clone fossil repo
Hi, I'm attempting to clone the source tree using the instructions from the website (http://www.sqlite.org/getthecode.html#clone), but I'm getting an error when "Artifacts received" hits 58416: $ fossil clone http://www.sqlite.org/cgi/src sqlite.fossil Round-trips: 7 Artifacts sent: 0 received: 58284 malformed cfile line Round-trips: 8 Artifacts sent: 0 received: 58416 Clone done, sent: 1939 received: 35121878 ip: 67.18.92.124 server returned an error - clone aborted I get errors when I try www2 and www3, or if I try HTTPS. With verbose logging, the output isn't much different: Bytes Cards Artifacts Deltas Sent: 53 1 0 0 Received: 5000352 26717293 13096 Sent: 72 2 0 0 Received: 5069927 24897257 12181 Sent: 57 1 0 0 Received: 5000173 15434836 6892 Sent: 57 1 0 0 Received: 5000385 19604834 8987 Sent: 57 1 0 0 Received: 5002648 19884874 9081 Sent: 57 1 0 0 Received: 5046326 9825299 4623 Sent: 57 1 0 0 processed: 40% malformed cfile line Received: 5000745263 3128 Sent: 43 0 0 0 Received: 32 1 0 0 Clone done, sent: 1936 received: 35121878 ip: 67.18.92.124 server returned an error - clone aborted ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is it possible to change "synchronous" flag of ZIPVFS pager?
I have an application which work with many zipped databases in WAL journal mode and the databases are stored on a media of low write bandwidth. There is a problem that that fsync operations invoked from one thread are blocking other fsync operations invoked from another thread. I think that changing the synchronous mode to NORMAL on some connections may make the blocking issue less painful. May the possibility of changing ZIPVFS synchronous mode be implemented in sqlite? Cheers, Tomasz M From: sqlite-users on behalf of Tomek Maj Sent: Thursday, March 16, 2017 10:50:37 AM To: SQLite mailing list Subject: Re: [sqlite] Is it possible to change "synchronous" flag of ZIPVFS pager? 2017-03-16 9:51 GMT+01:00 Dan Kennedy : > On 03/16/2017 01:40 PM, Tomasz Maj wrote: > >> Hi, >> >> >> According to my observations, "PRAGMA synchronous=...;" query affects >> only the standard sqlite pager. But for zipped databases sqlite uses >> additional ZIPVFS pager layer which actually decide whether and when to >> sync content of files associated with a database. It looks like "PRAGMA >> synchronous=...;" query doesn't have any effect on zipped databases. Are my >> observations right? If so, is it possible to manipulate "synchronous" flag >> of ZIPVFS pager? >> > > It looks like there is not. Is it something you need to do? > > Dan. > > > ___ > 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] Is it possible to change "synchronous" flag of ZIPVFS pager?
2017-03-16 9:51 GMT+01:00 Dan Kennedy : > On 03/16/2017 01:40 PM, Tomasz Maj wrote: > >> Hi, >> >> >> According to my observations, "PRAGMA synchronous=...;" query affects >> only the standard sqlite pager. But for zipped databases sqlite uses >> additional ZIPVFS pager layer which actually decide whether and when to >> sync content of files associated with a database. It looks like "PRAGMA >> synchronous=...;" query doesn't have any effect on zipped databases. Are my >> observations right? If so, is it possible to manipulate "synchronous" flag >> of ZIPVFS pager? >> > > It looks like there is not. Is it something you need to do? > > Dan. > > > ___ > 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] Is it possible to change "synchronous" flag of ZIPVFS pager?
On 03/16/2017 01:40 PM, Tomasz Maj wrote: Hi, According to my observations, "PRAGMA synchronous=...;" query affects only the standard sqlite pager. But for zipped databases sqlite uses additional ZIPVFS pager layer which actually decide whether and when to sync content of files associated with a database. It looks like "PRAGMA synchronous=...;" query doesn't have any effect on zipped databases. Are my observations right? If so, is it possible to manipulate "synchronous" flag of ZIPVFS pager? It looks like there is not. Is it something you need to do? Dan. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] last_insert_rowid and FTS in 3.17
On Thu, Mar 16, 2017, at 03:25 AM, Keith Medcalf wrote: > > Head of trunk certainly fixes it ... > > SQLite version 3.18.0 2017-03-15 19:11:29 > Enter ".help" for usage hints. > Connected to a transient in-memory database. > Use ".open FILENAME" to reopen on a persistent database. > sqlite> select sqlite_source_id(); > 2017-03-15 19:11:29 > b1b1aa8b69aa80c83aec3380565f0b4ec0b6a6e033537becee098872da362e9a > sqlite> CREATE TABLE IF NOT EXISTS Foo( >...> id_foo INTEGER PRIMARY KEY AUTOINCREMENT, >...> bar TEXT >...> ); > sqlite> > sqlite> CREATE VIRTUAL TABLE IF NOT EXISTS FooFts >...> USING FTS4(bar); > sqlite> > sqlite> CREATE TRIGGER IF NOT EXISTS insert_fts >...> AFTER INSERT ON Foo >...> WHEN new.bar IS NOT NULL >...> BEGIN >...> INSERT INTO FooFts(rowid, bar) VALUES(new.id_foo, >new.bar); >...> END; > sqlite> > sqlite> > sqlite> BEGIN; > sqlite> INSERT INTO Foo(id_foo) VALUES(NULL); > sqlite> SELECT last_insert_rowid(); > 1 > sqlite> INSERT INTO Foo(id_foo) VALUES(NULL); > sqlite> SELECT last_insert_rowid(); > 2 > sqlite> COMMIT; > sqlite> SELECT last_insert_rowid(); > 2 > sqlite> > sqlite> BEGIN; > sqlite> INSERT INTO Foo(id_foo, bar) VALUES(NULL, "otter"); > sqlite> COMMIT; > sqlite> SELECT last_insert_rowid(); > 3 > sqlite> > sqlite> SELECT * FROM sqlite_sequence; > Foo|3 > sqlite> > sqlite> > > > -Original Message- > > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > > On Behalf Of Richard Hipp > > Sent: Wednesday, 15 March, 2017 18:01 > > To: SQLite mailing list > > Subject: Re: [sqlite] last_insert_rowid and FTS in 3.17 > > > > On 3/15/17, Hugo Beauzée-Luyssen wrote: > > > Hi, I'm having some issues with last_insert_rowid starting with 3.17 > > > Basically it seems to be returning the row inserted by a trigger, > > > instead of the explicitly inserted row. > > > > Please try the latest pre-release snapshot at > > https://www.sqlite.org/download.html and let us know whether or not it > > fixes your problem. > > -- > > D. Richard Hipp > > d...@sqlite.org > > ___ Indeed it does fix it! Do you have a rough estimate on a 3.17.1 or 3.18 release? Thanks a lot, -- Hugo Beauzée-Luyssen h...@beauzee.fr ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
