Taking a step back a bit, have you considered something like a materialized view at the database level for your complex queries?
On Fri, Jun 17, 2016 at 4:28 PM, Jonathan Vanasco <[email protected]> wrote: > I've run into a peculiar issue on one of our systems. > > A handful of sql queries have become quite expensive [database growth]. > Even with aggressive optimizations, some queries can take 45 seconds. > [Migrating to a different database or hardware infrastructure is likely the > only avenue for improvement] > > I've started to sketch out an idea to handle this, and wondering if anyone > else here has had a similar need or has come up with a better solution. > > The general idea is that instead of the pyramid app using dogpile.cache to > generate an expensive element, it messages celery to do that; and > immediately returns a temporary value. > > The current setup > > * `n1` number of servers running Pyramid (currently anywhere from 3-9 > servers) > * 1 server runs Celery > > Rough Idea: > > * A `dogpile.cache` backed value generator will have a default value which > is defined as`default = las_computed_value or initial_default`. > * `initial_default` is in the source code. > * `last_computed_value` might be a database or filesystem backed value. > * the idea is to deploy with an acceptable value, but have any system > restarts persist a better value. > > * A `dogpile.cache` miss will immediately return `default`, and send a > message to `Celery` for value generation > > * Most subsequent cache misses will see the dogpile lock, and return > `default` > > * Some dogpile cache misses will not see the lock, and message Celery. > (this could be from a lock timeout or cache reset. this annoyingly > happens) > > * When `Celery` receives a message, it uses it's own dogpile lock to > ensure only one value generator is called. > > * When `Celery` generates a value, it not only populates the cache, but > also saves the data as the `computed_default` to persist across shutdowns > > > > > > -- > You received this message because you are subscribed to the Google Groups > "pylons-discuss" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > To post to this group, send email to [email protected]. > To view this discussion on the web visit > https://groups.google.com/d/msgid/pylons-discuss/cfc52891-f7a3-4e64-a9a4-6dc362c249ff%40googlegroups.com > <https://groups.google.com/d/msgid/pylons-discuss/cfc52891-f7a3-4e64-a9a4-6dc362c249ff%40googlegroups.com?utm_medium=email&utm_source=footer> > . > For more options, visit https://groups.google.com/d/optout. > -- Vincent Catalano Software Engineer and Web Developer, (520).603.8944 -- You received this message because you are subscribed to the Google Groups "pylons-discuss" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/pylons-discuss/CAEhnOsy95%2B9VvCnvwu%2Bx895HBdOi7buJ_HfovvT4a8e5YtYzUg%40mail.gmail.com. For more options, visit https://groups.google.com/d/optout.
