Re: [sqlite] Compressed schema in memory?
Is it time to break out an API for schema lookup? That would seem to be the least work for the developers and would give people the chance to implement whatever strategy they need to manage large schemas, including storing them in the database in a structured manager, or a compressed in-memory representation. > On Mar 16, 2017, at 11:57 PM, Richard Hipp wrote: > > 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 ___ 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/17/17, Dominique Devienne wrote: > > But what prevents SQLite from using the constraint's name, if one is > specified, from using it for the index? Backwards compatibility. This would change the file format, rendering database files that are corrupt in the eyes of older versions of SQLite. -- 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 Fri, Mar 17, 2017 at 9:30 AM, Simon Slavin wrote: > On 17 Mar 2017, at 7:49am, Dominique Devienne wrote: > > Richard, why is SQLite ignoring an attempt to giving these an explicit name? > —DD > > […] > >> sqlite> create table t (id constraint u1 unique); > > You are supplying a name for the constraint. But you’re still leaving it > up to SQLite to create the index it will need. So SQLite gets to choose > the name for the index. If you want to supply the name yourself you have > to define the index yourself. > But what prevents SQLite from using the constraint's name, if one is specified, from using it for the index? Constraint and index names are in different namespaces, AFAIK, so that seem entirely possible, and even desirable in the same "Great journeys begin with a single step" vein to reduce the schema size in memory, of the original OP. --DD ___ 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, Mar 16, 2017 at 11:19 PM, Bob Friesenhahn wrote: > 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". Consider upgrading to a more recent version of SQLite. In release notes at https://sqlite.org/changes.html I see: SQLite 3.12.0: - Reduce the amount of memory needed to hold the schema. No idea how much this saves though. Also defining some SQLITE_OMIT_* can save memory when storing the schema I think. I also recall that at some point, SQLite introduced memory saving by not storing constraints in memory for read-only connections. I don't see which SQLite version introduced this, but make sure that you open connections as read-only if you can. In my case, it was reducing by half the amount of memory used for the schema. I'm also interested in saving memory in schema by the way. So +1 if there is any potential improvement left. Dominique ___ 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 17 Mar 2017, at 7:49am, Dominique Devienne wrote: > Richard, why is SQLite ignoring an attempt to giving these an explicit > name? —DD > > […] > > sqlite> create table t (id constraint u1 unique); You are supplying a name for the constraint. But you’re still leaving it up to SQLite to create the index it will need. So SQLite gets to choose the name for the index. If you want to supply the name yourself you have to define the index yourself. Simon. ___ 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 Fri, Mar 17, 2017 at 12:00 AM, Richard Hipp wrote: > 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. > Richard, why is SQLite ignoring an attempt to giving these an explicit name? --DD C:\Users\ddevienne>sqlite3 SQLite version 3.17.0 2017-02-13 16:02:40 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> create table t (id unique); sqlite> select * from sqlite_master; table|t|t|2|CREATE TABLE t (id unique) index|sqlite_autoindex_t_1|t|3| sqlite> sqlite> drop table t; sqlite> select * from sqlite_master; sqlite> sqlite> create table t (id constraint u1 unique); sqlite> select * from sqlite_master; table|t|t|2|CREATE TABLE t (id constraint u1 unique) index|sqlite_autoindex_t_1|t|3| sqlite> ___ 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: > 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
