Compressed schema in memory?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
16 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Compressed schema in memory?

Bob Friesenhahn
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
[hidden email], http://www.simplesystems.org/users/bfriesen/
GraphicsMagick Maintainer,    http://www.GraphicsMagick.org/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Compressed schema in memory?

Simon Slavin-3

On 16 Mar 2017, at 8:09pm, Bob Friesenhahn <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Compressed schema in memory?

Richard Hipp-3
In reply to this post by Bob Friesenhahn
On 3/16/17, Bob Friesenhahn <[hidden email]> 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
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Compressed schema in memory?

Bob Friesenhahn
On Thu, 16 Mar 2017, Richard Hipp wrote:

> On 3/16/17, Bob Friesenhahn <[hidden email]> 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
[hidden email], http://www.simplesystems.org/users/bfriesen/
GraphicsMagick Maintainer,    http://www.GraphicsMagick.org/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Compressed schema in memory?

Richard Hipp-3
On 3/16/17, Bob Friesenhahn <[hidden email]> 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
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Compressed schema in memory?

Bob Friesenhahn
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
[hidden email], http://www.simplesystems.org/users/bfriesen/
GraphicsMagick Maintainer,    http://www.GraphicsMagick.org/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Compressed schema in memory?

Bob Friesenhahn
In reply to this post by Richard Hipp-3
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
[hidden email], http://www.simplesystems.org/users/bfriesen/
GraphicsMagick Maintainer,    http://www.GraphicsMagick.org/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Compressed schema in memory?

Bob Friesenhahn
In reply to this post by Richard Hipp-3
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
[hidden email], http://www.simplesystems.org/users/bfriesen/
GraphicsMagick Maintainer,    http://www.GraphicsMagick.org/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Compressed schema in memory?

Richard Hipp-3
In reply to this post by Bob Friesenhahn
On 3/16/17, Bob Friesenhahn <[hidden email]> 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
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Compressed schema in memory?

Richard Hipp-3
In reply to this post by Bob Friesenhahn
On 3/16/17, Bob Friesenhahn <[hidden email]> 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
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Compressed schema in memory?

Dominique Devienne
On Fri, Mar 17, 2017 at 12:00 AM, Richard Hipp <[hidden email]> wrote:

> On 3/16/17, Bob Friesenhahn <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Compressed schema in memory?

Simon Slavin-3

On 17 Mar 2017, at 7:49am, Dominique Devienne <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Compressed schema in memory?

Dominique Pellé
In reply to this post by Bob Friesenhahn
On Thu, Mar 16, 2017 at 11:19 PM, Bob Friesenhahn
<[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Compressed schema in memory?

Dominique Devienne
In reply to this post by Simon Slavin-3
On Fri, Mar 17, 2017 at 9:30 AM, Simon Slavin <[hidden email]> wrote:

> On 17 Mar 2017, at 7:49am, Dominique Devienne <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Compressed schema in memory?

Richard Hipp-3
On 3/17/17, Dominique Devienne <[hidden email]> 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
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Compressed schema in memory?

Darko Volaric
In reply to this post by Richard Hipp-3
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 <[hidden email]> wrote:
>
> On 3/16/17, Bob Friesenhahn <[hidden email]> 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
> [hidden email]
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Loading...