Is there possibility of actually using a timeseries database?

[Tejeev]

At times we will load logs of 3-9GB+ into LNAV which challenges the system's memory and stability.
Does lnav load the entire log bundle into a virtual table held in memory? Is there some downside to actually generating a db from these logs and running against that rather than a virtual table?

One idea was that we could have a process that automatically ingests files loaded to some location into an actual database and then run an instance of LNAV against that when investigating them. Reduce the memory load. As long as we backed it with a NAND my thinking is we'd likely see an increase in performance and stability. Am I wrong?

[awienecke]

Seconded; specifically to using a real DB, it allows, not only, the ability to offload resources, but the speed and efficiencies, as well as tunability, inherent in sqlites "big brothers." It would also open the possibility of having multiple users accessing various logs at once, without each user having to re-parse the logs.

[tstack]

At times we will load logs of 3-9GB+ into LNAV

Are these logs being updated while you're viewing them?

which challenges the system's memory and stability.

Is lnav unusable with this much stuff loaded? (As in, moving around in the log view is slow/unresponsive)

Does lnav load the entire log bundle into a virtual table held in memory?

On startup, lnav parses all of the log files to generate a big index that contains an entry for each message. Each entry contains things like timestamp, log level, file offset, etc... That index is kept in memory along with buffers for each file. The SQLite virtual tables work with this message index and then read and parse the message from the file as needed. For example, when doing a SELECT log_procname FROM syslog_log, the virtual table will read all of the messages from all of the syslog files, parse them again, and return the log_procname field that was captured. Note that the results of the queries are stored in memory right now. So, if you do a SELECT on the whole table, it will store the results in memory. That is something that I need to change in the future...

The ARCHITECTURE.md file in the repo has some more information as well.

Is there some downside to actually generating a db from these logs and running against that rather than a virtual table?

The main advantage of virtual tables is that they react automatically to changes in the files.

But, one of the easiest ways to speed up queries is to just dump the virtual tables into a regular SQLite table. The easiest way to do that is by running something like:

CREATE TABLE syslog2 AS SELECT * FROM syslog_log

For large tables, the SQLite table will be much faster... although you have to be careful that a statement like the above won't carryover some of the column properties, like the collation function.

One idea was that we could have a process that automatically ingests files loaded to some location into an actual database

I see a couple of options for that:

1. Support writing the log view out as JSON/CSV directly (right now, you have to do a SELECT followed by a :write-csv-to, which would be expensive for large logs). You could then do a bulk load of those files into the DB of your choice.
2. Integrate ODBC into lnav ... somehow...

If this is intended to be used in a continuously running batch process, the main feature that would be needed is making it easy to remember where lnav left off in the last run so that only new data would be inserted.

[Tejeev]

At times we will load logs of 3-9GB+ into LNAV

Are these logs being updated while you're viewing them?

They are not. When I use lnav on live logs they are generally smaller logs, routinely rotated.

which challenges the system's memory and stability.

Is lnav unusable with this much stuff loaded? (As in, moving around in the log view is slow/unresponsive)

Not really, it is slower but for me, the main issue has been that it takes forever loading (which we would be offloading to some background ingester that would be creating the DBs), and some crashy-ness when running filters. @awienecke?
Also, I've noticed that sometimes the histograms don't actually appear to reflect the data filtered and/or filters don't seem to be working quite right, but yet not sure what's going on there and if it's truly correlated with the large logs.

Is there some downside to actually generating a db from these logs and running against that rather than a virtual table?

The main advantage of virtual tables is that they react automatically to changes in the files.

But, one of the easiest ways to speed up queries is to just dump the virtual tables into a regular SQLite table. The easiest way to do that is by running something like:

CREATE TABLE syslog2 AS SELECT * FROM syslog_log

For large tables, the SQLite table will be much faster... although you have to be careful that a statement like the above won't carryover some of the column properties, like the collation function.

I take it you are suggesting running this from inside lnav?

One idea was that we could have a process that automatically ingests files loaded to some location into an actual database

I see a couple of options for that:

Support writing the log view out as JSON/CSV directly (right now, you have to do a SELECT followed by a :write-csv-to, which would be expensive for large logs). You could then do a bulk load of those files into the DB of your choice.
Integrate ODBC into lnav ... somehow...

If this is intended to be used in a continuously running batch process, the main feature that would be needed is making it easy to remember where lnav left off in the last run so that only new data would be inserted.

I was thinking more that we could have some in-house process ingesting logs dumped in a directory into individual DB files that we could then run lnav against when we go to actually log dive.
It's nice to know where we left off and I often make use of the saved session information like filters, so I would like to keep this functionality and just be able to say, maybe run something like lnav --create-db against a list of files as part of a batch process to generate the database when we aren't waiting and then later run lnav against the db it created when we come to logdive.