3 ms·
I was aware, but definitely worth reiterating. My post should have a disclaimer - "unless you have several years using many sql and nosql datastores and some b
by jgord 11y ago
I was aware, but definitely worth reiterating.
My post should have a disclaimer - "unless you have several years using many sql and nosql datastores and some background in algorithms and data-structures - DONT DO THIS"
My reason for _not_ using postgres and its GIST style indexes and full search primitives ( which I like and use often, btw ) was that I could find no way to configure postgres to hold the full top level index in RAM. I could run thru a query over all data to load it into the working set, but ideally I would like to mark it 'preload' - do you know a way to do this ? I did consider putting the inverted index in its own postgres DB on a RAM disk, but it seemed non-recommended.
The spirit of the post was : in most cases yes you can find something good off the open source shelf.. [ particularly redis which was very close to being what I needed ] but sometimes rolling a custom data structure that fits the problem can be a surprising win.
Particularly now that SSDs give a really different performance profile to what we are used to. I think DB implementations will adapt to fit SSD characteristics better over the next couple years.. just as they have made the desktop much more snappy.
New DB engines such as rethink and aerospike are very likely tuned more towards SSDs out of the box .. I haven't spent much time with those yet.
Theres a class of problems can be solved by scaling vertically, if you approach it by creatively applying the basics of algorithms and data structures. This is why many people use redis, its a superb bag of useful data-structures you can co-opt to your needs.
- fizx 11y agoDid you benchmark against GIST? Its hard to tell because you posted very loose benchmarks on a shared VM, but the performance wasn't particularly impressive. Is there a reason that you're unwilling to trust Postgres's query planner/buffer cache to keep your index in RAM? Even if it didn't, the premise of the article is that SSDs are fast. You're spending an order of magnitude more time retrieving content than searching for it, so why optimize the fast part? Also, consider that GIST or another fulltext aware index (e.g. Lucene/Sphinx/etc) should compress your index (with vints, etc) far better than your approach, and therefore both query faster and save you precious RAM. The article itself was sparse on details, but until I learn more, this seems to me to be a classic example of optimizing before measuring.
- spyder 11y agoWith a quick google search I found this stackexchange answer to preload data into cache with the pg_prewarm extension (from Postgres 9.4) http://dba.stackexchange.com/a/116536 http://dba.stackexchange.com/a/116536 But this doesn't guarantees that the data will stay in the cache because: "Prewarmed data also enjoys no special protection from cache evictions, so it is possible for other system activity may evict the newly prewarmed blocks..." In MySQL you can create tables with the MEMORY storage engine: https://dev.mysql.com/doc/refman/5.5/en/memory-storage-engine.html https://dev.mysql.com/doc/refman/5.5/en/memory-storage-engin...
- jgord 11y agoprewarm extension sounds interesting, Ill take a look.
- fleitz 11y agoThat's the exactly way to 'preload', turn the firewall on at boot, 'preload' by running a query and/or reading the index then open the firewall to normal connections.