ovsqlite - SQLite-based overview storage method for INN
This method uses SQLite to store overview data. It requires version 3.8.2 or
later of the SQLite library (3.20.0+ recommended).
SQLite source, documentation, etc. are available at
<
https://www.sqlite.org/>. Ones of the stated goals of the SQLite file
format are long-term stability and compatibility, which make that storage
method a perfect choice to store overview data.
Only one protocol version of the ovsqlite storage method currently exists,
implemented since INN 2.7.0.
The configure script will automatically enable ovsqlite support if it finds the
SQLite library. If the library isn't installed in a standard location, you may
have to specify the
--with-sqlite3 option to help configure find it.
For complicated cases, you can use separate
--with-sqlite3-include and
--with-sqlite3-lib options. Finally, if you
don't want ovsqlite
support even when your system has the SQLite library, you can use the
--without-sqlite3 option.
If you have a recent Linux installation, SQLite is most likely already
installed. You may have to install a separate package with a name similar to
"libsqlite3-dev" to get the required header files.
Plan on needing at least 0.93 KB for every article in your spool (not
counting crossposts). So, if you have 5 million articles, you'll need at least
4.65 GB of disk space for ovsqlite. With compression enabled, this
estimate changes to 0.4 KB per article, so you'll need at least
2 GB of disk space for 5 million articles. Plus, you'll need additional
space for transaction logs (a few MB).
To select ovsqlite as your overview method, set the
ovmethod parameter in
inn.conf to "ovsqlite". The database file will be created in
the directory specified by the
pathoverview parameter in
inn.conf. Restart INN to take the change into account (after having
rebuilt your existing overview with
makehistory, if needed).
These additional parameters are read from the
ovsqlite.conf configuration
file:
- cachesize
- The SQLite in-memory page cache size in kilobytes. The
default value is left up to the SQLite library and seems to be stable at
2000 KB.
- compress
- If INN was built with zlib support and this parameter is
true, ovsqlite will compress overview records whenever this saves space.
This parameter is consulted only when creating a new database. Enabling
compression saves about 55 % of disk space on standard overview
data. The default value is false.
- pagesize
- The SQLite database page size in bytes. Must be a power of
2, minimum 512, maximum 65536. Appropriate values include the virtual
memory page size and the filesystem allocation block size. This parameter
is consulted only when creating a new database. The default value is left
up to the SQLite library and varies between versions.
- transrowlimit
- The maximum number of article rows that can be inserted or
deleted in a single SQL transaction. The default value is 10000
articles.
- transtimelimit
- The maximum SQL transaction lifetime in seconds. The
default value is 10 seconds.
A transaction occurs every
transrowlimit articles or
transtimelimit seconds, whichever is smaller. You are encouraged to
keep the default value for row limits and, instead, adjust the time limit
according to how many articles your news server usually accepts per second
during normal operation (you can find statistics about incoming articles in
your daily Usenet reports). Inserting or deleting a database row within a
transaction is very fast whereas committing a transaction is slow, especially
on rotating storage. Setting transaction limits too low leads to poor
performance. When rebuilding overview data, it may be worth temporarily
raising these values on systems with slow CPU or rotating storage (for
instance, multiply by 10 the default values). Performance won't change much on
fast systems.
All overview database access goes through the
ovsqlite-server daemon. For
ordinary operation,
rc.news will start and stop it automatically. If
you want to touch the overview database while
innd isn't running,
you'll have to start
ovsqlite-server manually first. See
ovsqlite-server(8).
Initial implementation of ovsqlite written by Bo Lindbergh
<
[email protected]> for InterNetNews.
inn.conf(5),
makehistory(8),
ovsqlite-server(8),
rc.news(8).