5 ms·

I tend to use postgresql in my daily work now but I will always carry a flame for sqlite, my first DB experience. I still use it for rapidly parsing and normal

by bshipp 8y ago

I tend to use postgresql in my daily work now but I will always carry a flame for sqlite, my first DB experience. I still use it for rapidly parsing and normalizing huge text files, as well as the backend for numerous one-off webscrapers. I contend there are few faster ACID-compliant database options for high-speed input, without moving toward a complex multi-machine distributed solution. Combined with a high-speed SSD or an in-memory database, that single write-worker can often put any high-concurrency single-machine database to shame, as long as some thought is put into the code. There is something deeply satisfying about writing a short python script and then watching a 5gb text file or a huge directory of log files get chewed up and spit out into a tidy little perfectly normalized and portable database. Bliss. Just between you and me, sometimes I download a hundred thousand files to process just for fun. It's not right.

MuffinFlavored 8y ago
Do you know how Elastisearch/Splunk are all the rage these days? I feel like those are super heavy. What are your thoughts on https://github.com/brandonros/log-indexer https://github.com/brandonros/log-indexer?
- bshipp 8y ago
  As with anything, it depends on what you want to do. One of my SQLite databases has a text column that's 500 million rows (it's an 80GB file). Even with a standard COLLATE NOCASE b-tree index, searching out a word in the middle of a tuple takes a ridiculously long time (although finding that word at the beginning of the tuple is amazingly quick). A solution like Elasticsearch, while indeed heavier, is designed to deal with that kind of issue so, in my humble opinion, makes sense when you actually need a broad search capability. As for the solution in https://github.com/brandonros/log-indexer https://github.com/brandonros/log-indexer, it seems like a tidy little solution to the problem of having to parse log files later. Not only does it provide a rapid way of looking up information when you're trying to solve a problem, but SQLite also acts as a compression archive of sorts as well. I didn't look all that closely at the internals of the index.js script in that repo, but if it's still storing any of the records as JSON you can still access the internal data held inside the log record with SQLite's own JSON1 extension (https://www.sqlite.org/json1.html https://www.sqlite.org/json1.html). That would save a pile of time compared to a broad-based directory search for text file contents. A few lines for a SQL query and instantly you'd have every occurrence of that error since the program started logging. I'd think that's a very useful tool!
  - MuffinFlavored 8y ago
    > Even with a standard COLLATE NOCASE b-tree index, searching out a word in the middle of a tuple takes a ridiculously long time (although finding that word at the beginning of the tuple is amazingly quick). > A solution like Elastisearch, while indeed heavier, is designed to deal with that kind of issue so, in my humble opinion, makes sense when you actually need a broad search capability. Hmm... at a high level, how do Elasisearch a) consist huge amounts of data to disk and b) making searching 80gb and way way up of logs fast?
    - bshipp 8y ago
      Elasticsearch uses Lucene at the core of it's search capability. https://lucene.apache.org/core/ https://lucene.apache.org/core/ Basically it examines each record you want to index and generates a set of features and metadata to aid in rapidly finding what you're looking for. Some of the features listed on that site are: ranked searching -- best results returned first many powerful query types: phrase queries, wildcard queries, proximity queries, range queries and more fielded searching (e.g. title, author, contents) sorting by any field multiple-index searching with merged results allows simultaneous update and searching flexible faceting, highlighting, joins and result grouping fast, memory-efficient and typo-tolerant suggesters pluggable ranking models, including the Vector Space Model and Okapi BM25 configurable storage engine (codecs) Basically, a standard non-index database search (i.e. using LIKE) is fairly stupid and generally defaults to a full-text scan of each row in the table. A b-tree index (https://en.wikipedia.org/wiki/B-tree https://en.wikipedia.org/wiki/B-tree), for columns with only one word, dramatically reduces the scope of that search field and makes such searches almost instantaneous. However, it doesn't help with multi-word text fields and the database engine has to go back to the very slow full-table scan and check each record individually. Lucene notes the position of each word in a text string and has a number of techniques to figure out which records have words similar to the ones you're looking for, at relatively similar positions (i.e. close to each other, in the same order, etc) and narrows the search scope to those records. In short, it's not searching the individual records, it's searching it's own database for what it already knows about the contents of that record. EDIT: however, for a log search a b-tree would still be fine, because every log entry generally would have a similar structure. For example, if you're looking for a specific error message, that message is not going to dramatically change from one moment to the next. So having that table/column indexed with a b-tree would allow you to search for that specific error string and quickly pull up all the results, regardless of size. Just make sure you set up your SQL query to have a line like: WHERE column.errorvalue LIKE 'Generic Error Code 1%' instead of: WHERE column.errorvalue LIKE '%Code 1%' As soon as you put that first '%' sign in front of the first letter SQLite ignores any index you have for that table and does a full table scan, which is very slow. (https://www.sqlite.org/optoverview.html#the_like_optimization https://www.sqlite.org/optoverview.html#the_like_optimizatio...) That said, if you think you're going to get to 80GB you might want to look at an alternatively solution to SQLite or, at the very least, version your databases by month and then use the ATTACH DATABASE command if you need to mine historical data (or even write a small script to search each database individually). SQLite isn't really designed to separate tables across different disks and it's not fun to regularly have to back up 80GB files.

by bshipp 8y ago

MuffinFlavored 8y ago

Do you know how Elastisearch/Splunk are all the rage these days? I feel like those are super heavy. What are your thoughts on https://github.com/brandonros/log-indexer https://github.com/brandonros/log-indexer?

bshipp 8y ago
As with anything, it depends on what you want to do. One of my SQLite databases has a text column that's 500 million rows (it's an 80GB file). Even with a standard COLLATE NOCASE b-tree index, searching out a word in the middle of a tuple takes a ridiculously long time (although finding that word at the beginning of the tuple is amazingly quick). A solution like Elasticsearch, while indeed heavier, is designed to deal with that kind of issue so, in my humble opinion, makes sense when you actually need a broad search capability. As for the solution in https://github.com/brandonros/log-indexer https://github.com/brandonros/log-indexer, it seems like a tidy little solution to the problem of having to parse log files later. Not only does it provide a rapid way of looking up information when you're trying to solve a problem, but SQLite also acts as a compression archive of sorts as well. I didn't look all that closely at the internals of the index.js script in that repo, but if it's still storing any of the records as JSON you can still access the internal data held inside the log record with SQLite's own JSON1 extension (https://www.sqlite.org/json1.html https://www.sqlite.org/json1.html). That would save a pile of time compared to a broad-based directory search for text file contents. A few lines for a SQL query and instantly you'd have every occurrence of that error since the program started logging. I'd think that's a very useful tool!
- MuffinFlavored 8y ago
  > Even with a standard COLLATE NOCASE b-tree index, searching out a word in the middle of a tuple takes a ridiculously long time (although finding that word at the beginning of the tuple is amazingly quick). > A solution like Elastisearch, while indeed heavier, is designed to deal with that kind of issue so, in my humble opinion, makes sense when you actually need a broad search capability. Hmm... at a high level, how do Elasisearch a) consist huge amounts of data to disk and b) making searching 80gb and way way up of logs fast?
  - bshipp 8y ago
    Elasticsearch uses Lucene at the core of it's search capability. https://lucene.apache.org/core/ https://lucene.apache.org/core/ Basically it examines each record you want to index and generates a set of features and metadata to aid in rapidly finding what you're looking for. Some of the features listed on that site are: ranked searching -- best results returned first many powerful query types: phrase queries, wildcard queries, proximity queries, range queries and more fielded searching (e.g. title, author, contents) sorting by any field multiple-index searching with merged results allows simultaneous update and searching flexible faceting, highlighting, joins and result grouping fast, memory-efficient and typo-tolerant suggesters pluggable ranking models, including the Vector Space Model and Okapi BM25 configurable storage engine (codecs) Basically, a standard non-index database search (i.e. using LIKE) is fairly stupid and generally defaults to a full-text scan of each row in the table. A b-tree index (https://en.wikipedia.org/wiki/B-tree https://en.wikipedia.org/wiki/B-tree), for columns with only one word, dramatically reduces the scope of that search field and makes such searches almost instantaneous. However, it doesn't help with multi-word text fields and the database engine has to go back to the very slow full-table scan and check each record individually. Lucene notes the position of each word in a text string and has a number of techniques to figure out which records have words similar to the ones you're looking for, at relatively similar positions (i.e. close to each other, in the same order, etc) and narrows the search scope to those records. In short, it's not searching the individual records, it's searching it's own database for what it already knows about the contents of that record. EDIT: however, for a log search a b-tree would still be fine, because every log entry generally would have a similar structure. For example, if you're looking for a specific error message, that message is not going to dramatically change from one moment to the next. So having that table/column indexed with a b-tree would allow you to search for that specific error string and quickly pull up all the results, regardless of size. Just make sure you set up your SQL query to have a line like: WHERE column.errorvalue LIKE 'Generic Error Code 1%' instead of: WHERE column.errorvalue LIKE '%Code 1%' As soon as you put that first '%' sign in front of the first letter SQLite ignores any index you have for that table and does a full table scan, which is very slow. (https://www.sqlite.org/optoverview.html#the_like_optimization https://www.sqlite.org/optoverview.html#the_like_optimizatio...) That said, if you think you're going to get to 80GB you might want to look at an alternatively solution to SQLite or, at the very least, version your databases by month and then use the ATTACH DATABASE command if you need to mine historical data (or even write a small script to search each database individually). SQLite isn't really designed to separate tables across different disks and it's not fun to regularly have to back up 80GB files.