Forums

I have a small, low-traffic Django/Wagtail-based site that I'm trying to host with PythonAnywhere on a Hacker account. I've been testing for several months now and finally moved the production site over using MySQL as the database backend. I noticed during testing that server response was rather slow, but I thought once I got the production site set up with CloudFlare as a caching proxy, it wouldn't be an issue. The site is very low-traffic and doesn't need blazing speed.

However, I'm finding that when requests need to go through to the Django server on PythonAnywhere, response times are (what I would consider) very slow: in the range of 7-10 seconds, typically. When either the CloudFlare or Django/Wagtail caches serve the content, response times are fine (~ 0.5s). I've spent a while now tweaking both the CloudFlare and Django cache settings, as well as running a cron job every hour to crawl the site and try to keep the caches fresh. This works to some extent, but inevitably (it seems) some of the time the caches will miss and it will take 10 seconds for a page to load. I have confirmed that the slowdown only occurs on cache misses. The site is small, built around mostly vanilla Wagtail, and I've used Django Debug Toolbar to check that it isn't being slowed down by too many database calls. I have also ruled out network transfer speed as an issue using various debugging tools.

For comparison, today I loaded a fresh copy of the site on an EC2 t3a.small (2 vCPUs, 2 GB RAM) instance, running with identical production settings to the PythonAnywhere install, and it is quite snappy (<1s response for most pages, ~1.5s response for the only page with a lot of dynamic, database-driven content).

I have done quite a bit of reading of this forum and others, and seen similar questions, but haven't found any solution that works for me. Is this just the expected performance on PythonAnywhere given the shared hardware in use? Is there anything I can try to improve response times to an acceptable level, other than switching hosts? I did try increasing the number of workers in my account to four, but that doesn't seem to help and I reverted it.

Thanks in advance for any help, Jeremy

Could you add some logging to your app to see what exactly is causing the slowness? E.g. is it a particular view? connection to the db or other external service? some processing...?

@pafk Thanks, I've done some debugging today by adding logging statements in some likely places. I've done fairly little customization of the default wagtail and wagtail-crx classes, so there are only so many places I can debug. I was able to account for a significant (~ 50%) fraction of the response time within a custom header template I'm using that is on every page and that is iterating over a few dozen objects and making a few dozen database calls. It doesn't seem like anything too extreme to me but it is taking ~ 5 seconds to run. Also, overall, page requests are generating around 30-50 SQL calls according to Django Debug Toolbar.

Possibly more to the point: As I was testing, I realized that one major difference between the development stage and my production setup on PythonAnywhere was moving from SQLite to a MySQL backend. Just to be sure, I reverted to SQLite and load times decreased by ~ 75% (from 7-10 seconds to 2-3 seconds). This explains why I didn't worry to much about the load times during testing: they weren't yet quite painfully slow, and I figured they might speed up a bit when switching to MySQL. Instead, using MySQL seems to have slowed down response times ~four-fold. Surely I must have something misconfigured?

Here is my production database setting:

if os.getenv('DB_BACKEND') == 'sqlite':
    DATABASES = {
        "default": {
            "ENGINE": "django.db.backends.sqlite3",
            "NAME": BASE_DIR / "db.sqlite3",
        }
    }
else:
    DATABASES = {
        "default": {
            "ENGINE": 'django.db.backends.mysql',
            "NAME": "$".join([os.getenv('PA_USERNAME'), os.getenv('PA_DATABASE')]),
            "USER": os.getenv('PA_USERNAME'),
            "PASSWORD": os.getenv('PA_MYSQL_PASS'),
            "HOST": os.getenv('PA_MYSQL_HOST'),
            "OPTIONS": {
                "init_command": "SET sql_mode='STRICT_TRANS_TABLES'",
            },
            "TEST": {
                "NAME": "$".join([os.getenv('PA_USERNAME'), 'test_' + os.getenv('PA_DATABASE')]),
            },
        }
    }

Is there anything obvious that I'm doing wrong here that would account for the observed slowdown? Again, thanks for any advice.

I'll just add that the obvious solution for now is to stick with the SQLite backend. Two second page loads for cache misses are acceptable for this site, if not ideal. The observed difference in backends just seems so counterintuitive that I wonder if I have something misconfigured.

I do not see anything weird in your configuration. Maybe add some logging around your database queries to measure them precisely and in isolation.

Since it seems pretty clear now the main issue is the database backend, and SQLite performs well enough for our purpose, I think the marginal return on doing a lot more debugging is probably small at this point and we'll just stick with SQLite. Since noticing this difference, I did more searching of the forums and dug up some related posts:

https://www.pythonanywhere.com/forums/topic/2087/

https://www.pythonanywhere.com/forums/topic/29605/

https://www.pythonanywhere.com/forums/topic/30201/

https://www.pythonanywhere.com/forums/topic/27256/

https://www.pythonanywhere.com/forums/topic/34556/

In one or two cases it seems there were server issues. The others I'm not sure were resolved.

Thanks.

I'm glad to hear that you've found a solution, at least, but I'm certainly perplexed -- normally if people have performance issues on PythonAnywhere, it's in the other direction -- they're using SQLite, and it all gets sorted if they move to MySQL. I'm wondering if it's an indexing thing -- perhaps SQLite is implicitly adding indexes that help with the queries that you're doing, while MySQL is not?

Seems backward to me, too, but I have limited time to troubleshoot it. If you have any suggested diagnostics I can print from the two databases for comparison, please advise and I'll do so. The database schemas should be identical, as I have used manage.py migrate + manage.py loaddata to initialize them both from the same dump when comparing. I don't know enough about SQL to easily check differences in indexing, etc.

The only customization I'm aware of doing at all regarding the database backend is the "init_command": "SET sql_mode='STRICT_TRANS_TABLES'" part of the MySQL config. As far as I can recall, this was suggested in some documentation but I believe it is the default in the latest releases anyway.

Thanks for all the information. It looks like we need to look at the performance of the machine hosting your MySQL database.