Huge SQL query reaches memcached size limit per key about django-cachalot HOT 20 CLOSED

Ok, I just now thought to use Firebug to see what happens as I type in the box, and this is what I got:

Traceback:
File "/path/to/public_html/env/lib/python3.4/site-packages/django/core/handlers/base.py" in get_response

  132.                     response = wrapped_callback(request, *callback_args, **callback_kwargs)
File "/path/to/public_html/webapp/apps/swap/views/views.py" in location_query
  285.         matching_locations.extend(matching_states)
File "/path/to/public_html/env/lib/python3.4/site-packages/django/db/models/query.py" in __iter__

  162.         self._fetch_all()
File "/path/to/public_html/env/lib/python3.4/site-packages/django/db/models/query.py" in _fetch_all

  965.             self._result_cache = list(self.iterator())
File "/path/to/public_html/env/lib/python3.4/site-packages/django/db/models/query.py" in iterator

  238.         results = compiler.execute_sql()
File "/path/to/public_html/env/lib/python3.4/site-packages/cachalot/monkey_patch.py" in inner
  45.         out = original(compiler, *args, **kwargs)
File "/path/to/public_html/env/lib/python3.4/site-packages/cachalot/monkey_patch.py" in inner
  98.             execute_query_func, cache_key, _get_table_cache_keys(compiler))
File "/path/to/public_html/env/lib/python3.4/site-packages/cachalot/monkey_patch.py" in _get_result_or_execute_query

  76.     cache.set(cache_key, (time(), result), None)
File "/path/to/public_html/env/lib/python3.4/site-packages/django/core/cache/backends/memcached
.py" in set
  91.         if not self._cache.set(key, value, self.get_backend_timeout(timeout)):

Exception Type: Error at /location_query/
Exception Value: error 37 from memcached_set: SUCCESS
Request information:
GET:
term = 'indi'

from django-cachalot.

Django 1.8 is not yet supported.
Can you test with Django 1.7 please?

from django-cachalot.

I just downgraded to 1.7.7, and I'm getting the exact same error. Looks like memcached is timing out?

from django-cachalot.

You’re making a similar report as jmoiron/johnny-cache#3 & jmoiron/johnny-cache#4.
The queries you run seem to be way too huge for memcached.
There’s nothing to do in django-cachalot apart from warning users (which will be done in Limits). But it should be memcached’s job to correctly warn the users instead of this…

By the way, your queries are totally unoptimised, they must take a lot of time & memory (especially if you just type a space in the autocompletion). They must be rewritten, but at least the easy thing you could do is matching_locations = matching_countries | matching_states | matching_cities instead of creating a list that fetches up to 13k results and then keeps 10 results. You should definitely read the docs section When QuerySets are evaluated. Django-cachalot or not, performance will be terrible if you don’t fix that.

from django-cachalot.

I actually have kept meaning to go back and fix those queries up. They run fast enough, but they're still nasty. Thanks for reminding me. I'll do that tomorrow.

Is there a way to detect when a query blows up memcached? The problem is that if I can't fix the query so that it fits in memcached, I'm left with no option except to not use cachalot, which I don't want to do because it provides a pretty nice performance boost in other areas of my site.

It would be nice if cachalot could do 1 of 2 things:

1. Detect a failure like this but continue to run the query by giving me the results without trying to cache them. And then it could remember in the future not to attempt to cache that query.
2. Maybe there could be a way for the programmer to mark a query as too big so that cachalot doesn't attempt caching.

Either way, just failing is not the right course of action.

from django-cachalot.

No, there’s no way to detect it. And that would not be a clean solution. The solutions are:

• obvious one: increase the memory limit per key in memcached (-I argument)
• fix the queries
• use Redis
• disable django-cachalot in this view using dynamic overriding of the CACHALOT_ENABLED setting

from django-cachalot.

Great! I'm going to go with options 2 and 4 if necessary. The dynamic overriding is what I was looking for. Thanks for your help!

from django-cachalot.

I cleaned up the queries. Now, I only pull exactly what I need. I was still experiencing timeouts, so I added the cache override block, which has solved the issue entirely.

def location_query(request):
    # first handle the location autocomplete
    if request.is_ajax():
        term = request.GET['term']

        max_locations = 10
        matching_locations = list()

        with override_settings(CACHALOT_ENABLED=False):
            # I want to explicitly order matching countries at the front of the list
            matching_countries = Location.get_countries().filter(full_name__icontains=term)[:max_locations]
            matching_locations.extend(matching_countries)
            if len(matching_locations) < max_locations:
                # if there's still room, add states
                remaining_length = max_locations - len(matching_locations)
                matching_states = Location.get_states().filter(full_name__icontains=term)[:remaining_length]
                matching_locations.extend(matching_states)
                if len(matching_locations) < max_locations:
                    # if there's still room, add cities
                    remaining_length = max_locations - len(matching_locations)
                    matching_cities = Location.get_cities().filter(full_name__icontains=term)[:remaining_length]
                    matching_locations.extend(matching_cities)

        locations_json = list()
        for matching_location in matching_locations:
            location_json = dict()
            location_json['id'] = matching_location.pk
            location_json['label'] = '%s (%s)' % (matching_location.full_name, matching_location.admin_level)
            location_json['value'] = matching_location.pk
            locations_json.append(location_json)

        return JsonResponse(locations_json, safe=False)

This is returning results twice as fast as my unoptimized code above.

Thanks for your help. I'll close this issue now.

from django-cachalot.

You can still improves your queries, but they are way better now.
I reopen the issue since something still needs to be mentioned in the docs.

from django-cachalot.

How do you suggest I improve them?

from django-cachalot.

Instead of your whole list creation, you can simply combine the querysets before filtering and slicing. Something like:

matching_locations = (Location.get_countries() | Location.get_states() | Location.get_cities())
matching_locations = matching_locations.filter(full_name__icontains=term)[:max_locations]

from django-cachalot.

Ohhhhhh, that's killer. I didn't realize that could be done in Django.

from django-cachalot.

Actually, I don't quite think that does what I want. I need all country results to be ordered before all states, which in turn need to be before all cities. This just lumps them all together. That's the reason I had done 3 separate queries and then added them to a list. I'm not sure of a way to keep the ordering like I need.

from django-cachalot.

Then you probably want to use a PositiveSmallIntegerField for admin_level, with the choices parameter. That way, you’re able to sort results by admin_level then name without having to do 3 queries.

from django-cachalot.

That's a clever idea. Thanks!

from django-cachalot.

For what it's worth, increasing the item size (the -I command line argument) completely solved my issues. I just set it to 10mb (the default is 1mb). I no longer need to selectively disable cachalot.

Also, I figured I'd try it again on Django 1.8, and from what I can tell, it's working as intended. I'll report any issues I find.

And I also took your advice by adding a PositiveSmallIntegerField to maintain an integer representation of the admin level, and that worked great.

from django-cachalot.

Great news! :)
That mean I can write this doc section and tell people to use -I. By the way, this argument name is horrible, I didn’t see it was an I and not l or 1 x(

from django-cachalot.

Yeah, it's awful. I thought it was an L at first. Once I figured out it stood for "item size", it made more sense. Here's how my current memcached config looks:

PORT="11211"
USER="memcached"
MAXCONN="1024"
CACHESIZE="1024"
OPTIONS="-I 10m"

from django-cachalot.

Thanks!

from django-cachalot.

Hi, although I understand the reasoning behind not gracefully handling this error within cachalot, I wanted to find a way to ensure our app didn't go down because of this issue under any scenario. i.e. Even if I increase the max memory size for memcached, an object can exceed it causing application errors.

I found two libraries that provide a graceful way of handling this error:

1. python-memcached (https://github.com/linsomniac/python-memcached/blob/master/memcache.py#L1012). this library doesn't scale as well as pylibmc, but will do the trick if your app is small.
2. pylibmc + django_pylibmc (https://github.com/django-pylibmc/django-pylibmc/blob/master/django_pylibmc/memcached.py#L145). From what I can tell, using django_pylibmc wraps pylibmc, catches errors, and fails silently.

Hope this helps anyone else digging into this issue down the road.

from django-cachalot.