18 ms·
We saved $50k/year with a Go microservice coded in a hackathon
- pbnjay 8y agoCool post - Definitely an improvement and a good fit for Go services. I'm curious - did you run performance comparisons on optimizing the SQL itself as compared to adding this additional service? Maybe I'm crazy, but just looking at that query it seems like there's definitely room for improvement with the SQL alone. Unless the "..." is hiding something I'm missing?
- wasd 8y agoGreat story, thanks for sharing. I wanted to ask a quick question about something: > Refreshing caches automatically How do people usually handle this? Is this something done on the application layer or database layer? Where is the cache stored?
- owenmarshall 8y agoCaches could be stored in the database in a materialized view, in an external service like memcache or redis, or even in the application itself. Expiry can take a few different forms. Some caches have a defined space and use a replacement scheme like "fill the cache up, then remove the least recently accessed value". Some don't have defined sizes but instead remove entries based on timestamps (cache for n minutes). Some depend on invalidation messages from the application. It all depends on the applications needs. The most important thing to remember is that caching means your system becomes inherently a distributed one. State can become split across multiple sources, the cache can return stale data, invalidation might not happen when you expect, ... That's fine, but you have to program accordingly.
- hnov 8y agoElasticsearch works for this use-case quite well, you'd store a fairly straightforward representation of the MySQL row as a document, query by the fields you're interested in and ask for aggregations on the matching documents. Common bitsets get cached automatically.
- strukturedkaos 8y agoThis exactly how we implemented the rules engine in Kevy. We construct an Elasticsearch query based on the rules selected in the UI. Then we use the scroll API to retrieve the matching documents.
- EpicEng 8y agoOr maybe just write a less insane SQL query to begin with?
- baud147258 8y agoIf I remember right, elasticsearch query are pretty insane to begin with, so it's another problem.
- hajile 8y agoThis seems a lot less about go and much more about changing the approach to the problem.
- misja111 8y agoBut it is the 'Go' in the headline that probably got this article promoted to the frontpage of HN ..
- pryelluw 8y agoI once built a micro service in Go that saved about as much. Took me a couple of days (meetings and all that) from start to deployed. It still runs on the same cheap aws instance. Go is really good at that sort of thing.
- wiradikusuma 8y agoJust to be sure, any language will do right? Because I thought it was about Go vs (put your slow programming language here).
- tytytytytytytyt 8y agoIt seems like it from his explanation. He says he'll explain why he thinks only Go could have done it, but then nothing specific to Go really materializes. This is how it seems to usually go with posts like these.
- oblio 8y agoIt's like those infamous enterprise benchmarks from yesteryear. "NYSE moves from Solaris to RHEL and gains a 800% performance benefit". While I don't doubt a brand new RHEL has more performance optimizations than what is actually a SunOS 5.2, the guys benchmarking should have also said that the original hardware was the equivalent of a PIII and now they're moving to the latest Xeons. I'm not kidding, I've actually seen a press release like this.
- orf 8y agoI don't quite get this. How fast was running this query: Select loyaltyMemberID from table WHERE gender = x AND (age = y OR censor = z) Why the random complexity with individual unions and a group? Of course that's going to be dog slow. Sure, the filters can be arbitrary but with an ORM it's really really simple to build them up from your app code. The Django ORM with Q objects is particularly great at this. Obviously I'm armchairing hard here but it smells like over engineering from this post alone. Stuff like this is bread and butter SQL. Edit: I've just read the query in the post again and I really can't understand why you would write it like that. Am I missing something here? Seems like a fundamental misunderstanding of SQL rather than a particularly hard problem to solve.
- BinaryIdiot 8y agoDamn, you're not kidding. I wonder why they needed more than one query here plus UNION is slowwwwwwww. They never mention how frequent this query needs to run either, only the amounts of data involved in some aspects of this table.
- deleted 8y ago[deleted]
- owenmarshall 8y ago> Stuff like this is bread and butter SQL. Ten or fifteen years ago, sure - a DBA would look at a query plan and figure out how to do it properly. Worse case you'd slap a materialized view in and query that. But this is 2018! Programmers don't want to treat the database as anything but one big key value store ;)
- cookiecaper 8y agoYeah, sadly, this is not too much of an exaggeration. I've worked on teams that insisted they needed DynamoDB, because, well, Dynamo is for "Big Data", and they certainly wouldn't work somewhere that had "Small Data"! Replace the buzzwords/products as applicable; you could actually probably just scramble them and it'd work just as well, since someone out there thinks "RabbitMQ means Web Scale", etc. SQL databases are amazing, robust examples of engineering. They are your friends and they're the appropriate choice for the vast majority of software. They are not outmoded or passe. Though I acknowledge there is a separate use case for K-V stores, I almost want to make policy preventing their use just because I know so many developers will abuse them badly and then stare back at you blankly during the semi-annual massive downtime event, muttering something like "Well, it's based on research at Google, so I'm sure there's a way to recover the data..."
- hippich 8y agoWhat else it shows - how expensive AWS hardware vs hosting own hardware. I guess you have to consider how often you have to scale, but hetzner offers dedicated servers with 64Gb and NVMe drives starting from 54 euros per month - https://www.hetzner.com/dedicated-rootserver?country=us https://www.hetzner.com/dedicated-rootserver?country=us - compare that to $580 per month these guys were paying for i3.2xlarge instance..
- bobwaycott 8y agoI’ve heard mention of Hetzner no less than a dozen times in the last couple days. What’s their deal? I’m not quite sure I grok this server auction thing they do, or how they’re so cheap.
- hippich 8y agoI am not talking about auction thing here, just their regular dedicated servers offerings. They are bare metal and it is up to you to set it up, but savings are in the 5x - 10x range if you can avoid waste by underutilizing hardware (i.e. cases where you have to scale daily 1 to 100 instances might not be financially advantageous)
- CSDude 8y agoIf all you need is Virtual Machines or dedicated machines, you are good to host your databases, services etc. using AWS is very expensive. You could literally buy from 3-4 different vendors to maintain availability in disaster case and still be cheaper. Hetzner is one of the most affordable providers and both dedicadted and cloud offerings are fast enough.
- foepys 8y agoHetzner got in the news recently because they now offer a "cloud" product for VPS. Not in the sense like AWS where you can shut down instances and pay less but in the sense that you can buy, provision, and delete VPS via an API and pay per hour. They are also dirt cheap and offer 20 TB egress traffic with even their cheapest VPS. How they do it? I don't know. They are using Xeon processors and not i7 like some others.
- sankyo 8y agowhat did the Go solution replace? I only read about DB changes, and cannot draw any conclusions.
- fleitz 8y agoIt replaced a couple sub queries/ common table expression. Somehow using the same data store makes it a microservice rather than a distributed monolith. Of course it’s a lot less 2018/webscale to just optimize your DB rather than replace something with Go
- firasd 8y agoRight. The blog post is a good narrative over time but isn't super clear about how they solved the problem. If I understand correctly they broke down the big MySQL query into separate queries that the Go service processes/caches?
- maloga 8y agoBlogpost author here. That is correct. Sorry if the explanation isn't super clear; for this particular question, you can consider the two diagrams as a before and after. They pretty much convey what you have explained here.
- mlvljr 8y agoshhhhhhhhhhhh, people are raving!
- f1notformula1 8y agoI found this linked from the article https://movio.co/en/blog/migrate-Scala-to-Go/ https://movio.co/en/blog/migrate-Scala-to-Go/ So if the alternative was Scala I can see why Go may have helped tighten things up a bit.
- deleted 8y ago[deleted]
- mikeryan 8y agoThis is cool but it seems strange (to me) that this was a “Hackathon” project as opposed to just a stand-alone problem to be addressed as a normal course of doing business. It doesn’t make the solution less cool. It just seems like a strange distinction on what a Hackathon is.
- neuromantik8086 8y agoA hackathon is something that used to be a cool party for geeks (i.e., a Mathletics competition or an ACM programming contest) until the corporate overlords bastardized it and converted a good thing into unpaid overtime with free beer.
- afterburner 8y agoWhile simultaneously "proving" that all projects could get done in one tenth of the time.
- meesterdude 8y agoWell, what would be the argument against that - if in fact it does deliver code that solves a problem in a short period of time? Why can't you just do that all the time?
- mighty_atomic_c 8y agoSame reason you can't drug up or taze the special goose to produce more golden eggs. Long term high intensity output will lead to burnout, even if the salary is 10x people would struggle and crash. Pushing at 100% full enthusiasm is like sprinting, it is not possible to maintain that intensity for very long. It can be fun, it can be productive, but the wiser approach has the long-term and end in mind.
- raquo 8y agoHaving a fairly small, well defined problem, working in a small self-selected team, with no outside interference and typically no clients outside the team. No managers, no PMs, no bug reports. This is not how day-to-day projects get done. Regardless of all of that, in my experience a typical impressive hackathon project is still just a barely working demo that benefitted from a significant amount of research and planning beforehand, and will require an even greater amount of hardening and polish afterwards. There is no magic, it's just a vastly different kind of work environment with both inputs and outputs incomparable to day-to-day work.
- partycoder 8y agoUnless you can directly see how a query can be optimized, first thing you do is get the execution plan (e.g: EXPLAIN query). The execution plan will tell you how expensive is each bit of your query and help you adjust it. From there, if things are not getting better, you have a lot of alternatives: - Consider creating an index - If the value doesn't change often, consider writing it into another table or caching it. - Replication, partitioning, sharding, changing the schema. - Reconsider the requirement being implemented in order to have a more scoped query or to perform the query less often. Then... OLAP is not OLTP. If you can, do reporting in another database. Finally, creating your own project in the end may not save you $50,000. How about maintenance? tooling built around it? integration costs? documentation? usability? new hires having to learn about it? You can hire people that already know SQL without having to incur that cost yourself. All the tooling is built, battle-tested and readily available. Plus, skills related to internal tools are harder to trade in the market because they're harder to verify and less transferable.
- attaboyjon 8y agoI've come to the conclusion that the problem in tech is that all the people doing the work are in their early twenties and have no idea what they are doing. Once they get some experience they are quickly promoted to the CTO position. Rinse and repeat. What we have here is a classic dbms problem and no one at Movio seems to know how to deal with that. Instead of migrating from Mysql to something serious (Postgres) they move to some columnar DB no one has heard of. Nevermind that postgres and a reasonably priced DBA and a little thought put into their data model/queries could probably handle all their issues. Sorry for the snark, cheers on a successful product.
- matte_black 8y agoAgreed, I’m sure if this is the kind of problems they are having I could probably be saving them even more than $50k a year if they were on Postgres.
- quest88 8y agoWhy isn't MySQL serious? It has powered many popular sites.
- attaboyjon 8y agoYou are right. It is a solid DB. I was more pointing out that if you have to move off Mysql, there are excellent options other than adopting a new columnar datastore.
- progval 8y agoshort version: they used the right data structure for their problem instead of a generic one.
- bufferoverflow 8y agoThe fact that their slow query scanned billions of rows means that they didn't bother to simply shard by customer.
- epse 8y agoClearly they did, since they had to run a DB per customer. Those customers just had a lot of customers
- silveroriole 8y agoGotta agree with others and say that they’re clearly skimming over the facts that: - they didn’t have the expertise to actually fix the SQL. That query smells bad. The data model smells bad. For some reason HN is always superstitiously afraid of letting developers touch the database, but if you don’t let devs touch the database enough you end up with this sort of thing; or that crap data model with properties in rows instead of columns, because oh god, we can’t let devs actually do DDL so we’d better make it all really flexible (and incredibly slow because it’s a misuse of the database). I mean, implementing your own result caching mechanism? I don’t know about MySQL but surely it has its own caching mechanism (Oracle does) that isn’t being used because the query is bad. - project management probably had no interest in fixing the performance/incorrect data problems, and devs were expected to do it in their own time. In a way though this makes me feel better, other people are dealing with these problems too and their overengineered solutions work and keep the company running, I guess mine will too :)
- mjburgess 8y agoThere's no CTO on https://movio.co/en/company/ https://movio.co/en/company/ Seems like a red flag. I'm all for companies releasing technical blog posts, but there's some really strange framing here. This is actually a story about how decisions get made, and how better ones can be made. Reading a company's mea cupla tells you they are well-informed and well-intentioned. This is not a story which presumes good decisions were made and "the (tiny, startup) database company went bust". That's their framing. Yikes.
- TeeWEE 8y agoIts not that a Go Microservice solved their problem. Its the different algorithm they use for querying. That has nothing todo with Go, or Microservices.
- eeZah7Ux 8y agosssh, don't break the spell...
- z3t4 8y agomySQL is very slow when it comes to joins, groups, etc. You are always better off with simple select's. If you are using for example PHP the only viable solution is to have the db crunch it. But when using Go, NodeJS et al. you can pull the data out as a stream/array-like and apply filter/map/reduce and the logic would probably be easier to manage, rather then generating a complex SQL query. And would also allow you to stream the result to the client, instead of having the user wait for it all before they see anything. A lot of money could probably be saved by having the data on the client side, in for example web db, and only use the servers for backups and syncing the data between clients.
- amq 8y agoIn my experience, if you have a proper schema, even very complex, but thought-out, queries are instant on virtually any database size.
- z3t4 8y agoStuff like the group builder in the article is hard to reason about as there are so many combinations. MySQL is bad at optimizing O(n2), no matter how you design the schema it will be slow. The solution, like they probably did in the article, is to break it down into separate more simple O(n) queries.
- amichal 8y agoi have a 10+year old project on mysql doing 6+ way joins, groups, etc against 100million row tables in sub second time. It all depends on the indexing, disk layout and size of intermediate products etc. We dont know why they did "UNION ALL" with huge intermediate products (it seems like the query could be a single index/table scan on its face) but that is likely the slow down "EXPLAIN SELECT" would tell us
- hordeallergy 8y agoHackathons... https://www.wired.com/story/sociologists-examine-hackathons-and-see-exploitation/ https://www.wired.com/story/sociologists-examine-hackathons-...
- twic 8y agoOkay, before i've even read the article, i'm going to guess that they were doing something egregously expensive in the cloud, and the microservice helped them do it more efficiently - but still much more expensively than doing it in a simple, old-fashioned way. Now i'll read the article ... EDIT: I would say i'm no more than 30% right. They were doing heavyweight data crunching in the cloud, and so paying more for it than if they were doing it on rented hardware. But that's a constant-factor thing; it's not like they were downloading gigabytes of CSVs from S3 on every request or some such. Their query looks suspect to me: couldn't it be written to do one big scan, rather than unioning a load of things? Or is this the right way to write queries on column stores? Still, there is no glaring obvious (to me) old-school fix for this.
- deleted 8y ago[deleted]
- maloga 8y agoBlogpost author here. Thank you so much for all the attention, comments, upvotes, likes, retweets, etc! I've done a pass over the comments and can't really answer them all but I'd like to clarify a few things: There seems to be a general opinion trend that the queries generated by the group builder algorithm are very inefficient, that it'd be easy to come up with a solution with much better response times, and that that would be achievable in any reasonable programming language in roughly the same time with similar results. The language argument will always be controversial and I won't address it here; we have a point of view that is expressed in the Conclusion and on this blogpost: https://movio.co/en/blog/migrate-Scala-to-Go/ https://movio.co/en/blog/migrate-Scala-to-Go/ I can imagine that seeing a query with JOINs, subqueries, GROUP BYs and UNIONs can raise some eyebrows, but there is some lacking context in that story, and that's on me. Here's some of that context: * The schema that the group builder algorithm operates on is not uniform in nature or composed of simple yes/no fields; it's an incredibly complex legacy schema that to a large degree wasn't even up to Movio: it's been up to the film industry as a whole, and it has evolved over the years, as is the case everywhere. Note that every different kind of filter translates to a very different kind of query, and we have more than 120 different filters, sometimes with dynamic parameters, and sometimes even bespoke for a particular customer! * The group builder algorithm predates the team that built this service (myself included), as well as predating the first commercial release of Elasticsearch, MariaDB, mainstream Go success, etc. Nevertheless, it's still very fast and is being used today by ~88% of our customers (i.e. all the non-behemoths). It's been successful for many years, and continues to be, for the most part. * But I don't like it because it's fast: I like it because it's simple and flexible. It allows our customers to build a really complex (and arbitrary) tree of filters to segment their loyalty member base, and it compiles all of that into one big SQL query, that in most cases is quite performant. That's pretty awesome. But yes; it doesn't scale to several million members. * Migrating the very engine of the main product of a company is not a decision that is taken lightly. As is the case with every big company I can remember (e.g. Twitter, SoundCloud), behind a big success story there's always a legacy monolith, and our case is no exception. From that standpoint, achieving such breakthrough (i.e. cost reduction + significant response time improvement) within one hackathon day is really not all that common in my experience. Definitely something worth sharing, IMO. Hopefully that clarifies some of the questions :) Cheers.
- manigandham 8y agoThis is a great example of how to do things wrong. I'm surprised they couldn't find any other columnstore database to take the place of InfiniDB in 2018. The numbers they quote (5M members, 100M transactions) are tiny for any modern data warehouse. Many solutions would run these in sub-second speeds without changing the SQL at all, and it would be far better than building a quasi-SQL engine in Go. Actually for the occasional querying + caching that they have, something like BigQuery or Snowflake data would be even cheaper with basically 0 operational effort.
- sAbakumoff 8y agoInteresting, why would anyone use movio instead of Facebook for targeted ads, I am serious?
- akx 8y ago> Movio Cinema's core functionality is to send targeted marketing campaigns to a cinema chain's loyalty members. That's a different use case c.f. Facebook Ads.
- tzahola 8y agoSo, instead of fixing your messed up data model, you wrote a service (sorry, microservice) which tries to keep your main DB in sync with a columnar cache, so you can keep using that awful query with multiple UNIONs. Throw in some "Go" and boy, you have your Medium post going!
- icedchai 8y agoIf they said "we added a couple of indexes to some database tables for 10x improvement", it wouldn't be too exciting, right? ;)
- shiftoutbox 8y agoI trained a monkey to type away at my keyboard, and I saved One Billion dollars . So now I can vacation in aruba all day.
- productionx 8y agoThis thread proves 95% of IT tards have no fucking clue about configuring Mysql.