Forums

Hi, I am disappointed at the slow page load time ( 20 seconds plus ) for my page: http://rugbug.pythonanywhere.com When run on a local server, the page loads in a fraction of a second, as expected.

It runs on a Hacker account, which I had hoped would be enough to serve simple pages usably fast, but that appears not to be the case.

Or am I doing something wrong ?

Hi there, we have a few pointers on investigating / diagnosing slow sites here: https://help.pythonanywhere.com/pages/MySiteIsSlow -- let us know what you discover?

I could speed it up with some code optimisation and it is now acceptable.

But it concerns me that fairly simple database queries run more than an order of magnitude slower on Pythonanywhere than locally on my not-particularly-impressive PC.

Given that I hope for much more traffic on my Pythonanywhere site than just me on my PC, I would have hoped that they would run comparably fast.

And given that I develop on my local PC, it means that the slow spots can only be seen and debugged remotely.

Is this normal and expected ?

If it's database queries that are causing things to be slow, are you using MySQL or SQLite? SQLite is quite slow on PythonAnywhere (because the filesystem is network-based) -- MySQL should be pretty quick, because it doesn't use the network filesystem.

(It's also worth noting that PythonAnywhere won't necessarily be faster than your PC. Our network connection is faster, but you don't get a whole machine to yourself. Let's say your PC cost $1200 -- that means that, over a two-year life, it's costing you $50/month. Add on costs for electricity, bandwidth, security/insurance, etc., and so on, and it can get quite expensive.)

I use Sqlite :-(

I read that it was slower but had not appreciated quite how slow.

I don't need it to be faster than local, but I need fairly simple queries to be acceptably slow, and they're not.

I also like Sqlite because I can sync my local and remote DBs with Git. I don't think that will be possible if I switch to Mysql ?

Is there no way to optimise the Sqlite file fetch, i.e. fetch it once on startup and then store it locally ? ( It's only a few MB. )

You're right, you can't sync local and remote DBs just by copying files with MySQL -- it stores everything in what amounts to its own filesystem. You can export stuff using mysqldump and then import it using mysql (help page here), but that would require you to use MySQL on your local environment too :-(

The problem with doing any kind of optimisation / caching with SQLite is that the database is shared between multiple processes, potentially on different machines -- so as soon as you start caching, you run the risk of things getting out of sync. So I'd definitely recommend moving to MySQL if you can. If you need access to your data from your local machine, you can use tricks like SSH tunnelling to access it directly from the MySQL server.

I will look into making the switch to Mysql.

But could I do local file caching of my Sqlite database if it is read-only ? ( i.e. Read-only for the python app, it will be modified only from Git. )

( Thanks very much for all your advice, BTW ! )

If it's read-only, then you could try copying the database into memory yes. I'm not quite sure how you would do that. We do provide a small ramdisk at /dev/shm, but it's only 1MB so that won't be enough I imagine.

Instead you can create an in-memory sqlite database with sqlite3.connect(':memory:') and then maybe do some kind of dump from the on-disk one and load into the in-memory one? Sounds a bit long-winded as an initialization, but it would presumably be blazing fast after that.

Do share your code if you decide to give it a go! (it sounds like fun :)

It's 1.3MB and will grow, damnit.

That does sound like fun :-)

Googling...

I got it going and it's much faster ! :-) - but much less efficient in the app :-(

It's not exactly what was on stackoverflow - I will tidy up my code and summarise here.

Nope, it's not too big to fit in the margin, here it is :-) :

This is my solution to the problem of a Django app on Pythonanywhere using Sqlite running VERY slow, due to Pythonanywhere accessing the Sqlite database file over a network every time it is read ( see above discussion ).

NB1: This solution only works if your DB is read-only for the app. I guess it could be made to write back any changes to the file DB, or use a seperate DB for read/write data with ".using('otherdb')." for example.

NB2: This works for me, but I am only came to it by googling and playing around. It is highly suboptimal and nasty, but it runs MUCH faster than accessing the Sqlite DB file every time. Caveat developer.

You need two DBs declared in settings.py, one default memory DB, the other the original DB file, that is full of data:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.sqlite3',
        'NAME': ':memory:'
        },

    'dbfile': {
        'ENGINE': 'django.db.backends.sqlite3',
        'NAME': os.path.join( BASE_DIR, 'sourcedata.db.sqlite3' ),
        },
    }

Then you need to define a function to be called on EVERY DB connection.

I put this code in apps.py:

import sqlite3
from django.conf import settings
try:
    from StringIO import StringIO
except ImportError:
    from io import StringIO
from django.dispatch import receiver
from django.db.backends.signals import connection_created

# load default (memory) db from file db on EVERY new connection to default db

strDbDump = None

@receiver(connection_created)
def onDbConnectionCreate( connection, **kwargs ):

    global strDbDump
    if ( strDbDump is None ):
        # Read a file DB into string
        connectionDbFile = sqlite3.connect( settings.DATABASES['dbfile']['NAME'] )
        stringIoDbDump = StringIO()
        for lineDbDump in connectionDbFile.iterdump():
            stringIoDbDump.write( '%s\n' % lineDbDump )
        connectionDbFile.close()
        stringIoDbDump.seek( 0 )
        strDbDump = stringIoDbDump.read()

    # Write string into memory DB
    with connection.cursor() as cursor:
        cursor.executescript( strDbDump )
        connection.commit()

    return

This means the string with the DB contents will be loaded into the memory database EVERY TIME you open a new connection.

As I say, horribly inefficient. But it works.

Comments very welcome.

Awesome, thanks for sharing that! (And LOL @ "caveat developer", I'm going to start using that.)