Improve scan duration

[UlrichBoehm]

Hey all,
I have a file with about 6,000 to 8,000 IDs (varying for each scan). I want to see if one (or more) of the IDs is in one of the already archived files. An archived file is a csv file containing 4 different IDs in a row (first column can be ignored). Each file has 128,000 rows. Currently there are about 370,000 files and increasing.
You see that there's a lot of data which I want to scan. One complete scan needs 5 or 6 days. And here's the way I use rg:
/usr/bin/rg -FLf ./ids-2020-08-20 ./archive1/1* >> archived.log 2>&1
Of course I repeat this command for all archive folders (archive1 to archive4) and for all archive files (1* to 9*).
Finally my question: Do you see a way to improve the scan speed?
As I know that the file count will increase on an every day base the scan duration will get longer and longer, too.
Using an SSD is not an option at the moment as we are talking about 7TBs today and about 20TBs in a few months. Every idea is welcome.
Thank you!

[krobelus]

Try using a database like SQLite.

[UlrichBoehm]

I tried it with MySQL a few months ago but I ran into problems with the index. After reaching a limit (can't remember the exact number of rows per table......600k?) the INSERT command slowed down extremely. There was now way to insert more rows in a suitable amount of time. Ripgrep is the only really working solution atm. I hoped that there are a few unknown parameters or best practices to improve the scan duration.

[krobelus]

I see. I hit a similar problem with Postgres at some point - insertion came to a grinding halt even though I used COPY FROM.

I did some small benchmarks to find out what took so long:

size (GB)	time to insert (s)	time to index (s)	time to insert with index (s)
0.1	4	13	19
0.3	13	28	56
1.0	40	84	271

You can see that insertion time and indexing time seem to scale linearly in the size of the input,
whereas doing both at the same time is definitively super-linear.
This is a btree index; I suspect that rebalancing the tree causes this quadratic(?) slowdown.
A hash index is much more expensive to compute, I don't recommend that.

What I ended up doing was to drop the index once in a while and recreate it.
Lookups are blazingly fast but then my DB is only 300GB (half of that are indices).
Not sure how this scales beyond that.

I think that since you are reading more often than you write, a database of some kind where you trade write performance for read performance is probably worth it.

[krobelus]

Also --no-ignore could reduce the number of syscalls, I'm not sure.

[UlrichBoehm]

Thank you for your help. I'll give the --no-ignore option a try and add an update after having a few results.

[BurntSushi]

ripgrep isn't an appropriate tool on corpora that large. Use a database or a fulltext index like Lucene, Tantivy, Mellisearch, SOLR or Elasticsearch. Otherwise your search time will probably just be roughly equivalent to the time it takes to read the data off your disk. At that size, it will not be short. Therefore, some kind of index is a better answer, which is what a database or a fulltext index will provide.

[UlrichBoehm]

Thank you! I'm currently importing all files to Elasticsearch. This will not be finished soon. I hope I can limit the storage size. It's increasing...