<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Ankur Gupta on Medium]]></title>
        <description><![CDATA[Stories by Ankur Gupta on Medium]]></description>
        <link>https://medium.com/@ankur_clarisights?source=rss-7ca7c1b233f5------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/2*DwQs1JXIssPOidZR6K-6UQ.jpeg</url>
            <title>Stories by Ankur Gupta on Medium</title>
            <link>https://medium.com/@ankur_clarisights?source=rss-7ca7c1b233f5------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Thu, 19 Nov 2026 19:05:07 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@ankur_clarisights/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[Scaling Backend 100X]]></title>
            <link>https://medium.com/clarisights/scaling-backend-100x-d2da0a4dd102?source=rss-7ca7c1b233f5------2</link>
            <guid isPermaLink="false">https://medium.com/p/d2da0a4dd102</guid>
            <category><![CDATA[performance]]></category>
            <category><![CDATA[ruby]]></category>
            <category><![CDATA[scaling]]></category>
            <category><![CDATA[engineering]]></category>
            <category><![CDATA[ruby-on-rails]]></category>
            <dc:creator><![CDATA[Ankur Gupta]]></dc:creator>
            <pubDate>Tue, 12 Jun 2018 05:13:22 GMT</pubDate>
            <atom:updated>2018-10-24T08:51:26.670Z</atom:updated>
            <content:encoded><![CDATA[<h3><strong>Introduction</strong></h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*NT_T9udwCZwHTWBvCNFXfQ.jpeg" /><figcaption>Photo by <a href="https://unsplash.com/photos/Ro6oAfHPc9A?utm_source=unsplash&amp;utm_medium=referral&amp;utm_content=creditCopyText">Samuel Zeller</a> on <a href="https://unsplash.com/search/photos/scale?utm_source=unsplash&amp;utm_medium=referral&amp;utm_content=creditCopyText">Unsplash</a></figcaption></figure><p>The post will show how we scaled a lot of our time consuming background jobs 100X and more. Obviously, 100X is relative to where we were before. Our previous <a href="https://blog.adwyze.com/how-we-scaled-our-rails-backend-135d14a165ca">post</a> covered some of the optimizations we did earlier and how the setup looks like. For sake of brevity, we won’t repeat that here but briefly mention the setup and db sizes as of today. We pull data (primarily time series) from hundreds of external sources and store it in Postgres. Since our queries are analytic in nature and require heavy joins, we do roll ups and precompute those joins and store the denormalized data in MongoDB. Since our <a href="https://blog.adwyze.com/how-we-scaled-our-rails-backend-135d14a165ca">previous post</a> on scale, our Postgres has grown from ~100GB to ~1TB and Mongo from ~40GB to ~160GB. We face scale and performance issues at all stages of our ETL pipeline:</p><ul><li>Ingestion: While ingesting external data into PG,</li><li>Precomp: While computing the rollups data (at daily and hourly level) and pushing to MongoDB</li><li>Querying: While serving our analytics queries</li></ul><p>In early January this year, my co-founder got us a new customer who was interested in our product. They are in Europe and a $6 Billion dollar company. This is our first unicorn customer. While we all were super excited to get them on board, our fear was whether our backend will be able to handle their scale. Me and my co-founder did a quick back of the envelope calculation. Based on that we concluded that our new customer is <strong>more than 100 times</strong> <strong>bigger</strong> than all our existing customers combined! We were jumping in excitement at the prospect of getting such a big customer but the problem was that our backend was no way ready to handle this scale. Being an enterprise product, sales cycle are longish (3–6 months), so thankfully we had time before the customer would come onboard fully. We jumped straight to scaling our backend. Goal was clear: we need to scale at least 100X!</p><h3><strong>What to scale?</strong></h3><p>As mentioned earlier, we needed to scale all our stages — ingestion, precomp, and querying. All our background jobs are either in ingestion or precomp phase and querying phase is simply optimizing our Mongo queries. We are big fan of instrumentation &amp; monitoring and use Datadog and InfluxDB/Grafana to track job run times and query latencies. That served as a good starting point. All jobs that are taking too long to run need to be optimized. Lets start with the ingestion pipeline where we read external data, check whats in our PG and update the data.</p><h3>How to scale? — detour</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*KEZ54sMi3slhkbVaSJ6pGw.jpeg" /><figcaption>Photo by <a href="https://unsplash.com/photos/TrqQBU3xMEs?utm_source=unsplash&amp;utm_medium=referral&amp;utm_content=creditCopyText">Kees Streefkerk</a> on <a href="https://unsplash.com/search/photos/detour?utm_source=unsplash&amp;utm_medium=referral&amp;utm_content=creditCopyText">Unsplash</a></figcaption></figure><p>The first thought was Ruby is slow and MRI implementation in particular is not geared for true parallelism. In particular, because of <a href="https://wiki.python.org/moin/GlobalInterpreterLock">GIL</a>, multiple threads in a Ruby process cannot be scheduled on multiple cores — they all run on the single core on which the process was started. JRuby does not have this limitation and therefore, that seemed a good candidate and standard benchmarks show that it is 3–5X faster than MRI. We then stumbled upon <a href="https://github.com/oracle/truffleruby">Truffle</a>, which is another implementation of Ruby language and <a href="https://pragtob.wordpress.com/2017/01/24/benchmarking-a-go-ai-in-ruby-cruby-vs-rubinius-vs-jruby-vs-truffle-a-year-later/">benchmarks</a> show it to be 30X faster than MRI. However, Truffle was quickly ruled out as it is still in active development and is missing key network implementations (like nokogiri gem) that makes it unusable with Rails. So, JRuby, it is, right? But wait, lets take a step back and understand what are we trying to achieve here. Didn’t we answer that question already? We need to scale 100X, period. Still, It is important to go back and answer <strong>scale what</strong>? A typical web application involves a DB. And in our case, we have two DBs! CPU is almost never the bottleneck — it is almost always the I/O. Too many DB queries (especially in a loop), waiting for an external service to respond, network calls, reading/writing from/to disk and so on. So even if we make Ruby “faster”, it won’t help in our case (we are a data heavy company). The benchmarks are not misleading — they are typically for CPU intensive work loads and it is important to understand that before jumping the gun. Also, moving from MRI to JRuby is a non-trivial migration and it was clear that gains will be limited. Hence, we ditched changing our run time environment for Ruby.</p><p><em>A faster Ruby is not going to magically fix a “bubble sort” hiding deep in your code</em> — Sam Saffron in a <a href="https://samsaffron.com/archive/2018/06/01/an-analysis-of-memory-bloat-in-active-record-5-2">recent post</a></p><p>Throwing more machines was the other obvious solution. Apart from being an expensive affair, we strongly felt that it was premature as we ought to understand what is causing the slowness in the first place. Therefore, it was time to become<em> morbidly obsessive</em> at taming our background jobs by finding inefficiencies and nuking them.</p><h3>How to scale? — again</h3><p>This section will cover a variety of techniques we applied to identify and remove the bottlenecks. Most of them are agnostic to Ruby on Rails and can be applied to other platforms.</p><blockquote>Be extremely lazy</blockquote><p>As alluded in previous section, our bottlenecks were I/O. Lets focus on the ingestion pipeline where we need to fetch data from external APIs, compare the external data with our PG and update PG. Ingestion itself can be broken down into two parts: 1) Fetching ad objects and their attributes and dimensions, 2) Fetching the ad objects stats, also known as the report jobs. For channels like AdWords, number of ad and keyword objects can be huge — 10k-100k per customer. Fetching these many objects say every hour for hundreds of customers can’t scale. We noted that, practically, in any time delta period, only a fraction of objects change. AdWords has the wonderful CustomSyncService API, which tells the change since a given time. We started querying that first to get list of campaigns, adgroups, and ads that have changed and refresh only those. That gave a huge performance boost as instead of fetching 100% objects, we were fetching only changed objects which typically would not be more than 1% of total data. 100X gain right there! Moral here — be lazy, we mean, really lazy and fetch only that you absolutely need.</p><blockquote>Be smart, challenge status quo, don’t hesitate to undo things</blockquote><p>Now switching to reports. AdWords stats data change for up to 30 days. So even if we have fetched data in past, each day, we need to keep fetching data for the last 30 days. We call it our backfill job. Our backfill job was giving big headaches as it was supposed to run once every 12 hours and a single run itself was taking 1.5 days (soon after the new unicorn customer added a handful of their accounts). Yes, you heard it right, 1.5 days to fetch all the data. How in the world are we gonna solve it as we were already doing batch reads/writes, running multiple threads — the <em>obvious</em> parallelism had already been implemented. We then looked at what it was doing. It was fetching data from AdWords, computing the delta between what is between AdWords and our PG and then writing that to PG. During delta computation, we had a sprinkling logic that would uniformly sprinkle the delta data over all rows for any particular day. Consider it legacy code and an approximation to hide all delta being visible in a particular hour. The problem was that for each object and each day, it had to look at least 24 rows and write back the delta to them. So 24 reads and 24 writes. Since anyway it was an approximation and goal of backfill was more to make sure numbers match at day level and not necessarily at each hour level, we got rid of it and dumped all the delta in the 23rd hour. So now instead of 24 reads and 24 writes, for each object, a single read and write for each day. This single change alone brought down the time from more than 120k seconds to less than 5k seconds (a day has 86.4k seconds). A full 24X gain! Lessons here: challenge status quo, understand your DB queries — especially the ones being done in a loop (or nested loop like ours) and see which one of those loop DB queries you can get rid of.</p><blockquote>Index in past != index in future</blockquote><p>In another instance, we were pulling data from Google Analytics and the job was taking forever to finish even for fetching only last 2 days of stats. It was supposed to run every hour and it was taking 178k seconds (~2 days) in worst case. We nailed it to a missing index — even though we had indexes in place, over time the code changed and a new query was no longer using the old index. The old query had columns (A, B, C, D) in its where clause and we had index on (A, B, D). The new query had (A, B, C, E) in its where clause. We removed the old index and added the common prefix index (A, B, C). Since cardinality of D was more than that of C, by getting rid of the old index, we actually brought down the index size and were able to use it for both types of queries. This simple change brought down the run time to about 1k seconds — a cool 170X+ gain! While tools like PgHero can suggest you missing indexes, it is ultimately to the application builder to really understand what their code is doing and what is the right index to use.</p><blockquote>Reduce db ops, bulk writes</blockquote><p>One of our data decoration job was taking forever to finish — won’t finish even in 24 hours. The decoration involves running multiple annotators on each ad object and writing the annotations to PG and Mongo. For simplicity, lets say we have about 10 annotators that need to run on each object. We had the obvious optimizations in place like only run annotators for recently changed objects. The original implementation would run each annotator, generate the mongo op that will update the annotator hash that we had in the Mongo document for the object. The hash in Mongo document looks like tags: {&lt;tag name&gt;: &lt;tag value&gt;} where each annotator writes a single entry (key/value pair) in the hash. The annotators were multi-threaded as clearly there is lot of I/O involved. Yet the annotator job will take really really long time to finish (wouldn’t finish even a day). Because the annotator involved reading from PG, writing back to PG and then pushing the changes to Mongo, we thought batching the reads and writes will help. So we added batched writes both to PG as well as Mongo. Yet this didn’t help — our job will continue running for more than a day and no hope of finishing! We took a more closer look at the logs and tried identifying where it was getting stuck. Our analysis showed that it was the Mongo writes. The next question was how can we reduce the number of ops we generate. That immediately gave the idea that we do not need to generate a mongo write for each annotation to each object (corresponds to one mongo document). Instead, run all annotators on the object, write all the changes to PG and then have a single bigger update that updates all the key/value pairs in the hash in Mongo. We also instrumented the total number of ops generated (rather naively by having a log statement showing the number). In our test setup itself we saw that in a test where earlier it was generating more than 500 ops, after the simple move, the ops reduced to about 50. After this optimization launched, the job that wasn’t finishing at all finished in 10 minutes! Since we did lot of other batch write optimizations too, it is hard to isolate the exact impact of reducing the mongo ops, but we estimate that overall, we had at least 144X gain (remember the job was not finishing even in a day).</p><p>In yet another instance, one of our external API jobs was taking more than a day to finish. We were pulling last 7 days of data and the timeseries file was a JSON file as big as 100MB. The job had to parse the file, accumulate stats in-memory by a group-by key and then write that to DB. The stats are hierarchical in nature — campaigns contains adgroups and adroups contain ads and each ad have some 20 metrics for each day and OS type. We were already doing batch writes but in order to keep the code simple, we were only batching writes within an adgroup. Using <a href="https://blog.adwyze.com/ruby-hotspots-finding-avenues-for-memoization-and-caching-492c6666807a">stackdump</a> from the live job, we identified that the writes to PG were the bottleneck. We kept wondering why they are taking time as we were already batching. It then occurred to us that each campaign has about 5–6 adgroups but each adgroup has only about 1.2 ads on an average. So even though we were batching writes, the earlier batching was poor. We then started aggressively batching across adgroups and even across campaigns and job became at least 5X faster. It still takes 20k seconds to finish — is supposed to run once per day, so kind of ok for now! Interestingly, when it was taking more than a day to finish, it was also hogging as much as 40GB of memory. Now that it has become faster, memory does not peak beyond 5GB.</p><blockquote>Hash With Indifferent Access (HWIA) is a murderer</blockquote><p>The tag annotator job that we scaled 144X by reducing Mongo ops a month later again became dog slow (about 15X slow). We were really shocked to see that as all the hard work to tame it seemed to have gone wasted. After carefully looking at all commits, we isolated it to a change that was done with good intention to detect ad objects that have not changed and hence would not require re-annotation. Our initial check was comparing a JSON hash with a symbolized keys Hash. Those who know Ruby, will immediately spot that JSON hash will have string keys whereas we were comparing it against a symbolized Hash, so even if they are identical except for key type, it wont match. As a result we were unnecessarily re-annotating unchanged objects. One of our engineers spotted that and fixed it in a seemingly innocuous pull request by making the comparison to compare two <a href="http://api.rubyonrails.org/v4.2/classes/ActiveSupport/HashWithIndifferentAccess.html">HWIA</a> hashes instead. While the code was correct, it undid all the gains we made by reducing the Mongo ops. We immediately reverted the change being aware that the change was actually <em>fixing</em> an inefficiency. Post revert, the job was back to its original performant state. Till that time we were liberal with our use of HWIA. Post that <em>traumatic</em> event, we added a check in Rubocop and overcommit config to prohibit use of HWIA and religiously went through the code to exterminate HWIA from our code. We still have few places using HWIA but they have been isolated and measures are in place to make sure they don’t multiply! As an aside, 3 months later we fixed the inefficiency correctly by making the check between two symbolized Hashes. That gave another 10X boost for a combined staggering gain of 1440X (Mongo ops optimization plus the bug fix to correctly compare two symbolized Hashes)!</p><blockquote>Memoization and caching</blockquote><p>We previously mentioned how we got rid of some DB writes and it dramatically improved the performance. It is not always possible to get rid of DB queries. However, it often happens that we are doing the same query again and again in a loop. Note that it is possible that the loop may be <em>invisible</em> i.e. some method may be calling another method and that other method might be executing same query based on the passed arguments. Our CTO, <a href="https://medium.com/u/78d0c2ae2787">Ashu Pachauri</a>, made a <a href="https://blog.adwyze.com/ruby-hotspots-finding-avenues-for-memoization-and-caching-492c6666807a">cool observation</a> that we can use SimpleCov hits/line counter to find lines which are repeatedly being called. That turned out to be an awesome insight — we found many places where we could remember the result of DB query either in local memory or storing it in our distributed Redis cache. We identified <strong>more than twenty</strong> such hotspots. While we don’t have individual improvement numbers, our test suite which used to take as much as 2<strong>0 minutes</strong>, now takes less than <strong>7 minutes </strong>(there were some other optimizations too that we did to make our test pipeline faster, reserving that for a future post). That is a huge huge improvement as we already were using Parallel RSpec. For the record, we run our test job on Jenkins with 8 cores, have 670 spec files, 1.7k <em>it</em> blocks and 17k <em>expects </em>and our test coverage is about 75%. Our tests are more like module test rather than unittest and as such rely on Postgres and Mongo read/writes. We do use FactoryBot and can’t rely on its <a href="https://robots.thoughtbot.com/speed-up-tests-by-selectively-avoiding-factory-girl">in-memory <em>build</em> constructs</a> alone, we need to use<em> create</em> so that our module tests can verify the state of db and do further processing based on that. Note that the memoizations and caching improvements led to significant improvements in production too — for us being able to bring down our test run time numbers seemed even bigger achievement as it improved developer productivity — pull requests could be iterated on 3X faster now!</p><blockquote>Other optimizations</blockquote><p>Will briefly mention some of the other optimizations we did. Each has a reference to external source outlining why it makes sense to do that.</p><ul><li><a href="https://www.mikeperham.com/2018/02/28/ruby-optimization-with-one-magic-comment/">Freeze</a> all your strings with a magic comment — We wrote a handy script to do it in all 3500+ files across our code base.</li><li>Replaced ActiveSupport::Cache with <a href="https://github.com/sorentwo/readthis">Readthis</a> as we rely on Redis heavily for caching — we have <a href="http://www.mikeperham.com/2015/09/24/storing-data-with-redis/">100 databases</a> in Redis with over 13M keys.</li><li><a href="https://www.mutuallyhuman.com/blog/2016/06/28/importing-data-quickly-in-ruby-on-rails-applications">Skip validation</a> during bulk import when using <a href="https://github.com/zdennis/activerecord-import">ActiveRecord-import</a> gem — WARNING: do it only when you understand what you are doing.</li></ul><h3>Query optimizations</h3><p>So far, all the optimizations were geared towards scaling our background jobs. We also needed to make our real-time queries to Mongo faster. Apart from caching query results in Redis, we noticed that our time series data is sparse — many metrics have zero value and yet we were storing them in Mongo. This was a problem as our documents were becoming bigger and bigger with each day (day level rollups are stored in an array in each Mongo document). A quick calculation showed that by not storing zero metrics in Mongo, we could reduce our document size by as much as 60%. We did exactly that and across our 200+ Mongo collections we were able to reduce document size from 15%-60%. This was a tremendous gain as Mongo could retrieve the documents much faster. Beyond this, it was clear that our design of storing time series stats of an object in an array in the Mongo object will lead to document growing indefinitely and making our queries slower and slower each day. Further, Mongo has a <a href="https://docs.mongodb.com/manual/reference/limits/">hard limit</a> of 16MB for document size. Hence, we can’t allow the doc to grow arbitrarily. This led to the sharding project where each object gets sharded — each shard containing only 6 months of data. This was a big change and a future post will outline how we did it and its implications.</p><h3>The Impact</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*4xhZAAIA1VW5sDi9PVb9dQ.jpeg" /><figcaption>Photo by <a href="https://unsplash.com/photos/cGwfkwHmt98?utm_source=unsplash&amp;utm_medium=referral&amp;utm_content=creditCopyText">Joshua Earle</a> on <a href="https://unsplash.com/search/photos/impact?utm_source=unsplash&amp;utm_medium=referral&amp;utm_content=creditCopyText">Unsplash</a></figcaption></figure><p>In hindsight, the optimizations outlined above may look simple but it took more than 5 months to really scale our backend. The optimizations were not easy to identify and without tools like Datadog where we track per job run times, RubyProf, <a href="https://blog.adwyze.com/ruby-hotspots-finding-avenues-for-memoization-and-caching-492c6666807a">stackdump</a>, and SimpleCov, it would have been impossible. Super proud with the scale we have achieved in the past 5 months. While we can present endless graphs for each of the individual optimizations we did, we will present a couple of tables — how the run times looked for the worst jobs in January, in May and now (in June). The numbers clearly show how we were able to dramatically improve our bottom line across the board and not just a one off gain limited to a few jobs.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*yS-wmaOMOWE4nI4oo2jQMg.png" /><figcaption>Worst 50 job run time before optimizations [January 2018] — does not cover jobs that were taking forever to finish as timing metrics are reported only after they finish</figcaption></figure><p>The above graph only shows the state of affairs in the month of January. Once the unicorn customers channel accounts were integrated (in Feb and beyond), some new jobs started taking days to finish. That is not captured above because Datadog barfs out when we query job run times for longer periods than a month. In May, when we were still in the middle of optimizations, worst job times looked like this:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*y_1uw7k6cmYr8KikkJFslg.png" /><figcaption>Max job times in [May 2018] — reduced jobs count to show some of the worst performers!</figcaption></figure><p>After all the optimizations, our current worst case times looks like this:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*-_3Pb8YhA59GKXOZ1fguyg.png" /><figcaption>Worst 50 job run times after alloptimizations [June 2018]</figcaption></figure><h3>The Road Ahead</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ijyxy3W1adE3dpfnU4znoA.jpeg" /><figcaption>Photo by <a href="https://unsplash.com/photos/LPffuc1TESQ?utm_source=unsplash&amp;utm_medium=referral&amp;utm_content=creditCopyText">Lance Asper</a> on <a href="https://unsplash.com/search/photos/impact?utm_source=unsplash&amp;utm_medium=referral&amp;utm_content=creditCopyText">Unsplash</a></figcaption></figure><p>While we are super proud of what we have achieved so far and thankful to our unicorn customer for pushing us out of our comfort zone, we must admit our setup is still relatively small — we still are talking about few terabytes of data. As more customers come onboard, the current choice of MongoDB as our OLAP is not sustainable. There are better alternatives belonging to the class of columnar DBs like Druid, Clickhouse, and BigQuery that are much better suited for analytics queries and can easily scale to peta bytes of data and beyond. Would love to brainstorm with folks who have worked with these DBs and/or otherwise have scaled their analytics pipeline to petabytes of data! I can be reached at ankur [at] clarisights.com. Happy scaling!</p><p>Ciao.</p><p>PS: If you want your Mondays to be awesome, want to be in a state where you look forward to coming to office each day, and want to solve exciting problems like above then <a href="https://angel.co/clarisights/jobs">apply here</a> to join our team!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=d2da0a4dd102" width="1" height="1" alt=""><hr><p><a href="https://medium.com/clarisights/scaling-backend-100x-d2da0a4dd102">Scaling Backend 100X</a> was originally published in <a href="https://medium.com/clarisights">Clarisights</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Postgresql — mysterious bad connections issue post Rails 5.2]]></title>
            <link>https://medium.com/clarisights/postgresql-mysterious-bad-connections-issue-post-rails-5-2-c1fbc1e49cbe?source=rss-7ca7c1b233f5------2</link>
            <guid isPermaLink="false">https://medium.com/p/c1fbc1e49cbe</guid>
            <category><![CDATA[engineering]]></category>
            <category><![CDATA[monitoring]]></category>
            <category><![CDATA[scaling]]></category>
            <category><![CDATA[ruby-on-rails]]></category>
            <category><![CDATA[postgresql]]></category>
            <dc:creator><![CDATA[Ankur Gupta]]></dc:creator>
            <pubDate>Thu, 24 May 2018 12:24:56 GMT</pubDate>
            <atom:updated>2018-06-12T05:04:43.523Z</atom:updated>
            <content:encoded><![CDATA[<p><strong>The Problem</strong></p><p>We have a Rails backend and use Postgresql as our OLTP db. About a month back, we moved to Rails 5.2. Ever since we noticed that our queries to PG in our background jobs will randomly fail. Errors would look like PG::ConnectionBad: PQconsumeInput(). These errors in particular used to come when we used to do find_each on large tables. So we thought may be queries are taking time and we need to optimize them. But then the error started appearing randomly in almost every job regardless of table size. This is when we realized that it has nothing to do with our background jobs but more of a server issue. Since we have thousands of jobs running through out the day, we use PGBouncer to manage the number of connections we open to PG. In pre-Rails 5.2 world, we were using transaction based pooling in PGBouncer and our max connections to PG was a mere 25. Hence we never had this problem. But after Rails 5.2, we were forced to move to session pooling as 5.2 started throwing errors and our migrations were <a href="https://github.com/rails/rails/issues/32622">failing</a>. In session pooling mode, PGBouncer needs to open lot more connections to PG than before. Hence we changed the max_connections in postgresql.conf to 1000 post Rails 5.2 upgrade. Things were fine initially but as mentioned earlier, we started seeing PG connection bad issue repeatedly across all our jobs.</p><p><strong>Failed attempts at diagnosis</strong></p><p>The mystery was deepening and <a href="https://stackoverflow.com/search?q=PG%3A%3AConnectionBad%3A+PQconsumeInput%28%29">Stackoverflow</a>, <a href="https://www.google.co.in/search?q=PG%3A%3AConnectionBad%3A+PQconsumeInput()&amp;oq=PG%3A%3AConnectionBad%3A+PQconsumeInput()&amp;aqs=chrome..69i57j69i58j69i61l3j69i59.1024j0j7&amp;sourceid=chrome&amp;ie=UTF-8">Google</a>, Github<a href="https://github.com/rails/rails/issues/24041"> issues</a> were not turning up a concrete answer to the problem that we were facing. In a few places, we were missing indexes, so we optimized the queries by adding proper index. Still the problem won’t go away. We then tried adding retry logic around some of our batch paginated calls — it helped a bit but it was irritable that we had to put begin/rescue around each of the query blocks to catch exceptions and we were unwilling to accept that as a solution as it was more of a hack than really understanding what was going on.</p><p><strong>The Voila Moment</strong></p><p>We looked at various monitoring graphs in Grafana where we track PGBouncer and PG health. We stumbled upon two graphs that kind of nailed the culprit. The number of client connections to PGBouncer and number of active connections to PG from PGBouncer. We noticed while the former was crossing 1k, the latter seem to have a hard stop at 1k and would never cross it. Bingo! We knew something is wrong here and we found max_connections in postgresql.conf was set to 1k. Of course one month had passed since we had set the max_connections in postgresql.conf to 1k and we had completely forgotten about it!</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*NtP9C6y4x6U1Bo5a1WRpFA.png" /><figcaption>Number of client connections to PGBouncer — was crossing 1k periodically through last 7 days</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*qQSmqJGFGi7085V0l9EMiQ.png" /><figcaption>Number of active connections to PG from PGBouncer — notice how it was <strong>never</strong> crossing 1k till 23rd May 2018</figcaption></figure><p><strong>The Solution</strong></p><p>Its kinda obvious what we had to do. Just increase the max_connections in postgresql.conf to a higher number (we chose to set it to 3k, the same value that we have set in PGBouncer for max client connections). You will notice that after 23rd May evening, active server connections started going beyond 1k (we added the fix around that time) and our Sentry which was flooded with PG bad connection errors suddenly fell silent! Note that while the solution was trivial, from the errors we saw, it wasn’t obvious that PG was dropping our connections because of the max limit.</p><p><strong>Parting Thoughts — Monitoring is important!</strong></p><p>Debugging this would have been a nightmare if we didn’t have the health graphs — in particular the connections count. So highly encourage to have enough monitoring in place for your services unless your services never fail and you know they will never fail :)</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=c1fbc1e49cbe" width="1" height="1" alt=""><hr><p><a href="https://medium.com/clarisights/postgresql-mysterious-bad-connections-issue-post-rails-5-2-c1fbc1e49cbe">Postgresql — mysterious bad connections issue post Rails 5.2</a> was originally published in <a href="https://medium.com/clarisights">Clarisights</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[How we scaled our Rails backend]]></title>
            <link>https://medium.com/@ankur_clarisights/how-we-scaled-our-rails-backend-135d14a165ca?source=rss-7ca7c1b233f5------2</link>
            <guid isPermaLink="false">https://medium.com/p/135d14a165ca</guid>
            <category><![CDATA[profiling]]></category>
            <category><![CDATA[web-development]]></category>
            <category><![CDATA[ruby-on-rails]]></category>
            <category><![CDATA[performance-optimization]]></category>
            <category><![CDATA[engineering]]></category>
            <dc:creator><![CDATA[Ankur Gupta]]></dc:creator>
            <pubDate>Mon, 26 Sep 2016 13:17:55 GMT</pubDate>
            <atom:updated>2016-11-25T06:30:18.803Z</atom:updated>
            <content:encoded><![CDATA[<p>In today’s post, I will cover what all we did to optimize to our backend system which is a Ruby on Rails application.</p><p><strong>Background</strong></p><p>AdWyze uses Ruby on Rails and as a data integration platform, we talk to multiple external sources. In fact, we keep fetching data from sources like Facebook, Google Analytics, Comscore DAX, AppsFlyer etc every 15 minutes. We have 65 unique background jobs that in all run 2000 times per day to fetch and process data from external sources and/or process stored data in our databases: Postgres and MongoDB. With a Postgres db with 100+ tables and 100M rows in all with a current size of 100GB growing at 0.5GB per day and the requirement to keep data fresh with a max lag of 15 minutes, most of our jobs need to finish quickly. This presented huge challenge in scaling our Rails app — both in terms of memory as well as how fast it runs. We have done a bunch of optimizations over the past 2 years to build a system that has scaled beautifully for us so far.</p><p><strong>Optimizations</strong></p><p><strong>Reducing memory footprint</strong></p><ol><li>Piping: have one thread fetch results from external sources and have another thread that processes them. This is much better than first fetching all results from external source and then processing them. We used the <a href="https://github.com/meh/ruby-thread">ruby-thread gem</a> for this that has a very simple way of creating a pipe.</li><li>Batch read: Instead of loading lot of data from DB via Rails <a href="http://api.rubyonrails.org/classes/ActiveRecord/FinderMethods.html#method-i-find">find</a> method, we use <a href="http://apidock.com/rails/ActiveRecord/Batches/ClassMethods/find_each">find_each</a>. Latter reads data from DB in batches and dramatically reduces memory footprint</li></ol><p><strong>Speed</strong></p><ol><li>Threading — most of our jobs are I/O bound (they either fetch data from external sources or ready from DB), so one can easily parallelize by running different accounts data in parallel.</li><li>Be lazy — do only work that is absolutely required e.g. if you know an object has not changed then why process it again. Empirical evidence showed us that 85% of our data does not change at any given time. So we avoided most of the hard work by not processing unchanged entities!</li><li>Right Indexes: This is a no brainer but many folks miss it out especially when you have lot of tables and you are doing many different queries on them. We used <a href="https://github.com/ruby-prof/ruby-prof">RubyProf profiler</a> for profiling and Postgres’ <a href="https://www.postgresql.org/docs/9.4/static/using-explain.html">EXPLAIN ANALYZE</a> feature and its equivalent in Mongo to create the correct indexes. Postgres itself <a href="https://jmorano.moretrix.com/2014/02/postgresql-monitor-unused-indexes/">instruments</a> index usage and scan data for each Postgres table in pg_stat_user_indexes table and that was also very helpful in finding tables that were missing indexes. Finally, <a href="https://github.com/ankane/pghero">pghero gem</a> has been a hero for us — we found many other queries that were missing indexes.</li><li>Parallel Writes: We noticed that once we had optimized our reads and our processing was happening in parallel, writes to DB became a bottleneck. We considered two options here: 1) Batch the writes to DB 2) Where batching was hard to do, we again introduced threads and did multiple writes in parallel. In most cases, we settled for the second option as it was the easier one to do.</li></ol><p><strong>Too many open connections</strong></p><p>Threading introduces the problem of too many open connections to the DB. To solve this, we did couple of things:</p><ul><li>Gave up ActiveRecord connection as soon as we were done with a thread (by calling ActiveRecord::Base.clear_active_connections!)</li><li>added <a href="https://pgbouncer.github.io/">PgBouncer</a> for our Postgres server</li><li>We didn’t do it explicitly but internally AcitveRecord uses a <a href="https://github.com/mperham/connection_pool">connection pool</a> to pool connections to db.</li></ul><p>With all these optimizations in place, we have been able to make our background jobs super fast while making sure we do not need inordinate memory (all our VMs are &lt; 8 GB RAM).</p><p>PS: We have not been able to find a good memory profiler for Rails and Ruby in general. RubyProf memory profiler does not work (requires a patched MRI) and other solutions like Sam Saffron’s <a href="https://github.com/SamSaffron/memory_profiler">memory_profiler</a> have not been convenient either to find memory hoggers. If you know of a good one then please do share!</p><p>PS2: Yes, of course, there is the famous <a href="https://newrelic.com/">New Relic</a> profiling tool but is super expensive. Hence, apart from their free trial, we didn’t bother continue using them. Besides, we are not aware if it can find memory bloats in the code. We do use Datadog where we track all timing metrics as well as report our own custom metrics. Datadog is 1/10th the cost of NewRelic and is a good alternative to NewRelic as long as you are willing to put some effort to exploit it to your advantage!</p><p>PS3: Caching expensive stats queries didn’t work for us — we were using Redis to cache our stats queries. We serve real-time stats + live status of ads including their budget and bids and allow our customers to change the values in real-time. When we started caching, cache invalidation started becoming a nightmare and customers were confused that our platform is showing old data even though they changed it from our platform itself! Hence, we ditched caching completely.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=135d14a165ca" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Data Science at AdWyze]]></title>
            <link>https://medium.com/@ankur_clarisights/data-science-at-adwyze-5a449194cbe8?source=rss-7ca7c1b233f5------2</link>
            <guid isPermaLink="false">https://medium.com/p/5a449194cbe8</guid>
            <category><![CDATA[ppc-marketing]]></category>
            <category><![CDATA[engineering]]></category>
            <category><![CDATA[marketing-automation]]></category>
            <category><![CDATA[facebook-marketing]]></category>
            <category><![CDATA[data-science]]></category>
            <dc:creator><![CDATA[Ankur Gupta]]></dc:creator>
            <pubDate>Wed, 07 Sep 2016 07:09:29 GMT</pubDate>
            <atom:updated>2016-11-25T06:31:20.831Z</atom:updated>
            <content:encoded><![CDATA[<p>We are an analytics company and data science is naturally a core area for us. Our DS team mission is to build the best in class tools for making marketers life meaningful and productive when it comes to mining data. Today, I would like to discuss some of the cool stuff we have been upto in data sciences.</p><p><strong>WyZr — NLP interface</strong></p><p>One of our projects, codenamed WyZr, is a natural language interface for making it easy for our customers to mine the vast marketing data we have. We have built an omni search box where you can type queries like “spend today” or “adsets with cpt &gt; 1000 and spend &gt; 7000” or even “spend, roi breakdown by gender” and WyZr does the magic of understanding the query, translating it to the relevant Mongo query (yes, we use Mongo!) and displaying the results. Of course, typing out full queries can still be cumbersome. To tackle it, we built our autocomplete engine that suggests full queries based on the query you have typed so far. Latency was a big challenge as we wanted WyZr to be fast. We did multiple optimizations in our Mongo to make sure we are fast:</p><ol><li>Created the right indexes — we used <a href="https://docs.mongodb.com/manual/tutorial/manage-the-database-profiler/">Mongo query profiler</a> to find all slow queries and created the correct compound indexes.</li><li>Query data at the right level — Facebook follows a three level structure of Campaigns -&gt; AdSets -&gt; Ads i.e. a Campaign contains many adsets and each adset contains many ads. Earlier, we used to store all stats at Ad level only and do all queries at that level. The problem is that there is a 10X branching factor from Campaign -&gt; AdSet and 50X branching factor from AdSet -&gt; Ad level. Since most of our queries are at campaign and adset level, we were still paying the penalty of aggregating data at Ad level. Our average performance improved more than 10X by simply querying at the right level.</li></ol><p>With these optimizations in place, WyZr is blazing fast. This does not mean there is no scope for improvement. E.g. our autocomplete internally uses a trie structure and could benefit from caching. Curious to see WyZr in action? Check out <a href="https://www.youtube.com/watch?v=1ssFCglI8as&amp;feature=youtu.be">this video</a>.</p><p><strong>Sangria — Diffing group of ads</strong></p><p>Another classic problem that marketers face is looking at two subset of ads and wondering what could the plausible reasons be for why one group is performing differently from the other. E.g. marketers would love if the system can answer how the ads with Cost per Transaction &gt; 2000 differ from the ads with Cost per Transaction &lt; 1000. We built Sangria precisely to answer this question. This is a non-trivial problem given that the two subsets have hundreds of dimensions and automatically finding the differences that matter the most requires taking various metrics like spend into account before coming up with the top reasons. Here is a screenshot of Sangria in action!</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/896/0*vd9SX6W3VSSVifOD." /></figure><p><strong>Clusterer — Automatically cluster ads</strong></p><p>Just like diffing, marketers would love if the system can automatically cluster the ads in “appropriate” groups. The reason is simple: if the clustering is right, marketer only needs to look at 1 or 2 representative elements from each group and take similar action for the entire groups of ads. This can not only save hours and hours of looking at data and finding what action to take for each of the thousands of ads currently running, it also helps finding new patterns in the data set. E.g. a clustering might put all DPA ads in slideshow format with above par CPT in bucket 1, all other slideshow ads in bucket 2 and the remaining non-dpa non-slideshow ads in bucket 3. Whereas the bucket 1 has all the superior ads where spend can be blanket increased, bucket 2 may need a closer case by case look and bucket 3 spend could be reduced or those ads could be completely paused. We are still hashing out the details here — we have solved the clustering problem but labelling the clusters with what each cluster represent is turning out to be a harder problem. Stay tuned!</p><p><strong>Scarlett — our prediction engine</strong></p><p>Ad performance varies a lot in a day and it is useful for marketers to know how their account metrics will end up for the day at any time. This is useful because they can course correct in case projected metrics are way off from the target. This required building predictive models at account level to precisely forecast spend, revenue and other metrics. We are happy to announce that we get our spend forecast right within 1% of actual spend at account level.</p><p>Hope this gave you a gist of some of the problems we are working on in data science and if you are interested in knowing more about the opportunities in data science, then shoot us an email at <a href="/service/mailto:careers@adwyze.com">careers@adwyze.com</a>.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=5a449194cbe8" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>