961209 - Consider reworking Seer DB schema to save disk space

Status: RESOLVED WONTFIX

(Core :: Networking, defect)

Description

[u408661]

Lots of comments in bug 947745 were centered around the inefficiency of the seer DB schema, instead of the bug's intended purpose (limiting disk space used by the DB, regardless of schema). This bug exists to continue the thinking/work around making a more efficient database schema.

A couple things to consider while thinking about the new schema:
 * We can NOT regress CPU usage (a lot of the space currently used by the schema is as a result of some of the fixes for bug 945779)
 * We should at least be open to dropping use of sqlite in favor of a different storage scheme (perhaps tied to the disk cache - sqlite is not the favored child of the perf team these days)
 * If we do stay with sqlite, D Richard Hipp (of sqlite fame) is very helpful and responsive on issues like this. For example, see bug 947745 comment 53 (duplicated here for ease of access):

Consider changing the schema to the following:

CREATE TABLE moz_subresources (
  id integer NOT NULL,
  pid INTEGER NOT NULL,
  uri TEXT NOT NULL,
  hits INTEGER DEFAULT 0,
  last_hit INTEGER DEFAULT 0,
  PRIMARY KEY(pid, uri),
  FOREIGN KEY(pid) REFERENCES moz_pages(id) ON DELETE CASCADE
) WITHOUT ROWID;
CREATE INDEX subresource_id_index ON moz_subresources (id);

The change converts the PRIMARY KEY from the "id" field to the combination of "pid,uri", thus obviating the need for an index on pid,uri, and saving the corresponding space.  You'll lose autoincrement on the ID column - I don't know if that causes problems for you or not.

Note that in order to achieve the space savings, you must use the WITHOUT ROWID keywords at the end of the CREATE TABLE statement.  That feature is only available in SQLite 3.8.2 and later.

Comment 1

[(dormant account)]

I made a few comments in 947745 (eg https://bugzilla.mozilla.org/show_bug.cgi?id=947745#c22 re timestamp precision).  
In general one should never have a raw index on a large-ish key(eg > 64bit word-sized with a lot of these). One should always hash and consider avoiding the index in those cases.
You should note that specifying data types in sqlite is mostly ignored by engine and you can store large values in any of the columns. Trim large numeric values, strings, to the minimum needed.

Another thing to note is that if your db has a lot of heavy traffic, the backing file for it will get internally fragmented and need to have a 'VACUUM' operation run on it periodically.  I'm not sure if this has been hooked up yet. Ask :mak re infra for that.

Less data is a good solution, storing stuff in a bunch of lz4-compressed text files might be worthwhile.

Comment 2

[Marco Bonardo [:mak]]

(In reply to Taras Glek (:taras) from comment #1)
> I made a few comments in 947745 (eg
> https://bugzilla.mozilla.org/show_bug.cgi?id=947745#c22 re timestamp
> precision).  
> In general one should never have a raw index on a large-ish key(eg > 64bit
> word-sized with a lot of these). One should always hash and consider
> avoiding the index in those cases.

That's right, we are going to do the same in Places with the index on uri. Though, the WITHOUT ROWID is an equivalent that can be applied if we don't care much about backwards compatibility of the database, it even saves more than the hashing solution, since with the hash you need to store the hash twice (column+index), while here there is no data duplication at all.
Though, if you need an id to join to, you must ensure to increment it monotonically in your code, that works if you don't need a quick way to id <=> uri, otherwise you will again need a large index. I didn't experiment with WITHOUT_ROWID yet, I guess the right solution may be half way, with both an hash to join and WITHOUT_ROWID, but it really depends on the schema.

Btw, Sqlite 3.8.2 is in version 29, so if one cares about backwards compatibility of at least one version it may be fine to use it from version 30.

> Another thing to note is that if your db has a lot of heavy traffic, the
> backing file for it will get internally fragmented and need to have a
> 'VACUUM' operation run on it periodically.  I'm not sure if this has been
> hooked up yet. Ask :mak re infra for that.

There is the VacuumManager but its API is main-thread only and looks like you are off the main thread. you may create a small XPCOM component just to hook up with it. VACUUM is needed, though it's expensive (will block your queries until done), causes fsyncs, and if done too often will make your INSERT performance worse.

Btw, just briefly looking at the existing schema, there is definitely a size problem with indices on uris.

Some other comments:

1. do the uri columns in moz_pages, moz_redirects, moz_startup_pages, moz_subresources overlap regarding the contained data? That would be another obvious size issue, large text should never be duped in a schema.

2. What's the host_id_origin_index for? I can see the need for an id index but I can't see a query gaining from duping origin data.. and id already has an index.

3. what's page_id_uri_index for? id has an index, uri has an index, I can't find a query on both or getting uri for an id

4. what's redirect_id_index for? that column already has an index since it's a primary key...

5. ditto on subhost_id_index

6. ditto for subresource_id_index

Note: I just skimmed fast through the schema and queries, so I may have missed something that makes my comments moot, though I hope these may be useful, there are definitely possible gains in this schema.

Comment 3

[u408661]

OK, so we're not going to do this, as the whole backend is being rewritten to not use sqlite at all over in bug 1009122 (and the seer is disabled in the meantime).