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.

Hello! I also experience a similar issue. Before I served my "article" pages with just django, but since I migrated to wagtail, sometimes it will take 10 seconds or so for "child pages" of the wagtail root to load. The content on the pages has not changed beside moving to wagtail based models. Not that heavy content either. Once the long loading time has happened, reloading the same page even with forced full reload takes a fraction of this time. Is this a general issue with wagtail MySQL? Thank you and have a nice day.

In this case, the slowdown you were seeing on cache misses did line up with an issue on the MySQL server side, rather than anything you had misconfigured in Django or Wagtail. Once the MySQL issue was resolved, response times should have returned to normal.

If you’re still seeing unusually slow responses now, please send us:

• the name of your database (from the Databases tab)
• a couple of timestamps (with timezone) and the URLs that were slow

and we can investigate further.

As of now, I just tried loading all pages ( I only have a handful so far) and all loaded quickly. If it was a temporary issue, then I guess it is fixed! If I see this issue again, I will send you a message with the information you requested. Thank you very much for your response.

I think this is not a misconfiguration neither at Django or nor Wagtail end...This slow issue is usually caused by the performance issue of MySQL on the PythonAnywhere side. That's why SQL lite here can appear much faster...And as for as your datbase settings are concerned, i think they are fine. If you got this happening again, then you should report timestamps, URLs and the database name. This way it would be easy for the staff to check out the host performance of MYSQL.

We can always take a look, as on shared infrastructure behavior of the neighbor could we the cause of the problem.