6 ms·
How MySQL memory table saved the day
- iamthephpguy 13y agoHaha. The Jon Snow meme got me in splits.
- Tomdarkness 13y agoOr you could actually use something designed for indexing data and searches, like Elasticsearch or Solr. Either solution would have no problem indexing all their data, rather than having to limit it to a subset to fit in a in-memory table.
- herge 13y agoDoes Elasticsearch or Solr work with tabular data, can you search across multiple columns?
- Tomdarkness 13y agoYes you can, and a whole lot more.
- Xylakant 13y agoI can't speak well for Solr, but with elasticsearch the answer depends on what you exactly mean with "search across multiple columns", but it's probably yes.
- arethuza 13y agoLucene models documents as a collection of fields, each with a textual value. http://lucene.apache.org/core/4_0_0/core/org/apache/lucene/document/Document.html http://lucene.apache.org/core/4_0_0/core/org/apache/lucene/d... At search time you can use the default field or specify the fields to be searched: http://lucene.apache.org/core/2_9_4/queryparsersyntax.html#Fields http://lucene.apache.org/core/2_9_4/queryparsersyntax.html#F...
- waterlion 13y agoYes. Read the example SOLR schema, it's very easy to understand.
- webstartupper 13y agoThanks for the info. I haven't previously come across Elastisearch or Solr. Will definitely check these out.
- jcampbell1 13y agoThose solutions assume text is tokenizable, and suck at regex. I think they are the wrong tool for anything having to do with domain search. Furthermore, they don't play nice with continuous cron indexing.
- dalore 13y agosolr has real time indexing and batch indexing
- randomnumber314 13y ago>Elasticsearch >Register to watch Any site that requires me to register an account before I can even look at their product is going to have a bad time.
- steinnes 13y agoAs far as I know you can download ElasticSearch from here without registering: http://www.elasticsearch.org/download/ http://www.elasticsearch.org/download/ ... what better way to check out the product than play around with it?
- Tomdarkness 13y agoIt is a open source project. They sell support, but elasticsearch itself is licensed under the Apache license. Just click overview if you want to kniw what it is, I'm not sure what you are clicking on that requires registration. Perhaps their training sessions?
- Spoom 13y agoTheir videos require registration but the docs do not.
- beersigns 13y agoThis was initial thought as well, seems like both would fit the needs specified, multiple column search etc. I've had good experiences with both ES and Solr. They both have pretty healthy user bases and are well documented. Only hang-up is if you use primarily reg-ex style searching; turning on n-grams could work there, but it might be too slow.
- al2o3cr 13y ago+1 for Solr - looking at the search on domcop.com, it seems like a perfect fit for the faceting stuff. There might be some messiness with the regex search (looking for user-defined patterns of consonants, vowels, etc) as I've never needed to set that up, but the rest would be really clean. See also this blog post that discusses how RoomKey uses read-only Solr instances that are repopulated daily to speed up searching for latency-insensitive data (think "does this hotel have a pool?" etc): http://www.colinsteele.org/post/23103789647/against-the-grain-aws-clojure-startup http://www.colinsteele.org/post/23103789647/against-the-grai...
- tyw 13y agoI haven't used either elasticsearch or solr, so I don't have firsthand experience on how they compare to Sphinx search (http://sphinxsearch.com http://sphinxsearch.com) but I really love Sphinx. Also integrates nicely with MySQL if you're already running that. I think Solr and Sphinx pretty much tick the same boxes in terms of features and performance though, so use what you know and like.
- jonaldomo 13y agoJust a heads up: http://dev.mysql.com/doc/refman/5.1/en/memory-storage-engine.html http://dev.mysql.com/doc/refman/5.1/en/memory-storage-engine... The MEMORY storage engine (formerly known as HEAP) creates special-purpose tables with contents that are stored in memory. Because the data is vulnerable to crashes, hardware issues, or power outages, only use these tables as temporary work areas or read-only caches for data pulled from other tables.
- Zr40 13y ago> Varchars take up the space for all the chars defined. This only applies to memory tables. For non-memory tables, the size of varchar columns depends on the actual string size. (edited. Thanks for correcting!)
- jcampbell1 13y ago> MEMORY tables use a fixed-length row-storage format. Variable-length types such as VARCHAR are stored using a fixed length.
- webstartupper 13y ago"MEMORY tables use a fixed-length row-storage format. Variable-length types such as VARCHAR are stored using a fixed length." From - http://dev.mysql.com/doc/refman/5.0/en/memory-storage-engine.html http://dev.mysql.com/doc/refman/5.0/en/memory-storage-engine... I had no idea about this either....
- saintfiends 13y agoWe did something similar at work. We had to poll for changes in a table. So instead of polling the tables we added triggers to insert events to a MEMORY table and polled that table. It performs good enough for us.
- elbac 13y agoA better solution, is just to increase your innodb buffer size, you will get virtually same performance as the 'memory' table once all the data is in memory. Plus all the data will be persisted. This is an old, but still very useful script for helping to suggest what settings to tweak: https://github.com/major/MySQLTuner-perl https://github.com/major/MySQLTuner-perl http://dev.mysql.com/doc/refman/5.5/en/innodb-buffer-pool.html http://dev.mysql.com/doc/refman/5.5/en/innodb-buffer-pool.ht...
- webstartupper 13y agoThanks for the link. Unfortunately, since there are 30 different columns that can be searched on, there are many indexes and the index size for the domains table itself is 4GB. I run this off a 2GB linode, so unless I add a lot more RAM, Innodb is not going to match the memory table speed.
- jeffdavis 13y agoI'm a little confused... the InnoDB table and memory table had the same data, but the InnoDB table was larger (at least twice as large, I presume)?
- webstartupper 13y agoThe Innodb table right now has about 9 million records and is 8GB in size (4GB index size). The memory table has a subset of the same data - 1.2 million records and is 276MB in size.
- jeffdavis 13y agoIt makes me curious what an apples-to-apples comparison would look like. What it you put the same subset in a separate innodb table and tune the memory settings so it's likely to stay resident?
- webstartupper 13y ago
- jumby 13y ago8 million records (at 7GB!) and it's slow means there is something seriously wrong with your schema. That table would entirely fit in InnoDB Buffer Pool on any modern hardware. I want to see your slow query log.
- webstartupper 13y agoThe domains table currently has 84 fields. We collect metrics from various sources, so reducing the number of fields is not feasible. All the field types are the smallest that we could use - e.g. tinyint(4) instead of an int etc. Since there are so many fields with data from multiple sources, we have queries running searching on individual fields. Due to this we need to have many indexes. 4GB of the 8GB is the size of the index itself.
- jumby 13y agooooohkay. you can run with that then.
- gngeal 13y agoThe domains table currently has 84 fields. Are you sure you've read up on your C. J. Date? I've had that once before: someone complaining that "queries take too much time" with a paltry single-digit-GB database. When I asked about the specifics, the only repeating reply was "we can't tell you". You don't mention anything of value, but querying a few million records can't possibly take a few minutes on the aging desktop computer I've bought seven years ago, much less on a modern server.
- webstartupper 13y agoI presume the reason it was slow was because the domains table was write heavy. There are multiple crons running in the background selecting data from the domains table, accessing external APIs and updating individual records. Selects per hour: 37K Updates per hour: 170K While the speed of selects or updates by the background crons was and is not important, the speed of selects run by the users on the same table was important. The easy solution was to cache the data so that the users could search domains at a good speed. The memory table just worked brilliantly as a cache. (I'm no mysql guru and its my first project where MyIsam did not work for me, so I know I definitely could do a better job of optimizing the Innodb table and Innodb settings in my.cnf)
- ww520 13y agoWhat are some typical queries look like? Several minutes for searching 7M records doesn't sound right. Are the columns indexed properly?
- mtdewcmu 13y agoI'm thinking that this could be a fairly easy problem and the RDBMS may be making it harder. This is probably a tricky indexing situation, so a lot of queries might be effectively unindexed. I'd like to see what the performance would be using really simple methods, like dumping it to a TSV file and doing regex searches with grep or awk. It might be surprisingly fast.
- willvarfar 13y agoI am always cautious of memory tables. They don't support transactions, for example, and don't work well multi user. Really, the first stop is to use tokudb backend in mysql. If its still slow, and if you have a small subset that fits in ram, just put that straight into a hash table in app space.
- mtdewcmu 13y agoAs I understood it, the memory tables are read-only. They're like a cache. So transactions aren't needed.
- willvarfar 13y agoExcept for that awkward time when the tables are refreshed?
- taf2 13y agoCould use two memory DBs and rotate them
- navatux 13y agoYgritte’s voice “You know nothing, Jon Snow” Game of thrones !!