How can I configure my OSQA installation to be as fast as possible?

So, the first thing to optimise / tweak performance is to actually measure the performance. django-debug-toolbar is your friend!

You install it on your command line by:
easy-install-2.6 django-debug-toolbar (how to do it on webfaction)

Next, open up your settings_local.py file and set:

1. DEBUG = True

2. INTERNAL_IPS = ('your_ip_address_here',)

Save the file and restart apache(or whatever your webserver is).

Refresh your website and you will now see the tab on the right hand side for Django Debug Toolbar (http://robhudson.github.com/django-debug-toolbar/)

Now you can see and measure various parameters that you would like to tweak: Django Version, Time, Settings, HTTP Headers, Request Vars, SQL, Templates, Signals and Logging.

1. First thing to do is server your website media and admin media via a different webserver (how to on webfaction)

2. Add more RAM

3. Use MEMCACHED

4. Use profiling tools to find specific bottlenecks.

Use the Django Debug Toolbar to see what the bottleneck is and iterate.

Just enabled memcached as this post explains. The effort/reward ratio of doing that one single thing must be x50 that of any of the other possibles. Bet 99% of people are happy with this one thing and it takes 4-5 minutes, max.

IIS Performance Epic Battle of 2011

IIS is the standard where I work so we're trying our best to make it happen.

Basic Suggestions

• Use a dedicated SQL Server
• Consider using a read only slave database - maybe this would help: django_replicated project
• Look at this: scaling django presentation
• For IIS, because the session is kept in the database, you can enable web garden by bumping up the max worker process app pool setting. Not sure what the optimal setting is or whether it buys you much performance-wise. The general idea is to prevent thread pool starvation.

Don't Use PyISAPIe

In general it seems like PyISAPIe is the preferred way to go to serve Django from IIS as it's a prominent in the django docs:

• Django Wiki - Windows Setup
• SF - Django/IIS & PyISAPIe

We tried it out. Getting PyISAPIe to work is slightly complicated by the fact that you have to compile it yourself if you're using anything later than Python 2.6 at the time of this writing.

Unfortunately then we found out it breaks all authentication methods in OSQA (except we didn't check local auth, that might still work). We tried it out and confirmed this behavior also mentioned in comments on this post. It's surely possible to fix but it is over my head.

I was able to use our OSQA instance on PyISAPIe before I logged out and performance-wise it seemed comparable to isapi_wsgi, but spinning up the process for the first time was much slower, over 10 seconds usually.

Verify NTFS Permissions

Make sure NTFS permissions allow the app pool identity to write inside the main osqa directory. This will allow the default filesystem caching and the python bytecode files (.pyc) to be written to disk. Once the bytecode is generated it takes less time to spin up. At minimum you need write permissions in the [path to osqa]\logs and [path to osqa]\cache directories.

Let IIS Serve the Static Content

Create a virtual directory called “m” that points to the [path-to-osqa]\forum\skins folder.

Create a web.config file that looks like this and place it in the skins folder:

<?xml version="1.0" encoding="UTF-8"?>
<configuration>
    <system.webServer>
        <handlers>
            <clear />
            <add name="StaticFile" path="*" verb="*" type="" modules="StaticFileModule,DefaultDocumentModule,DirectoryListingModule" scriptProcessor="" resourceType="Either" requireAccess="Read" allowPathInfo="false" preCondition="" responseBufferLimit="4194304" />
        </handlers>
    </system.webServer>
</configuration>

This will remove our wild-card mapping for the isapi_wsgi handler and replace it with the IIS static content handler.

There’s also static content for the built-in Django admin interface, which can optionally be used. That content will be located where django was installed, in our case D:\Python27\Lib\site-packages\django\contrib\admin\media. Normally you would create a symlink to this on *nix but you could also create an IIS virtual directory pointing there. While a virtual directory might be the most maintainable way to set this up, you do have to alter the folder permissions so the IIS user can read into that other folder. In our case we just copied the media folder into the OSQA root, and then created a virtual directory called “admin_media” that points to the folder inside OSQA. A final step here is to add the above web.config to the admin media folder so that IIS serves the files.

Last but not least, the uploaded files folder. Create a virtual directory called “upfiles” and point it to [path-to-osqa]\forum\upfiles. Finally add above web.config to the upfiles folder.

The way to verify whether IIS is handling files there is to watch the response codes. Before IIS was configured to handle them, django was returning 200 response codes every time. Afterwards you should see some 304's.

Comparing local requests on my dev box here for static files, IIS serves them around 4x faster. On a loaded server the difference would probably be more dramatic.

Memcached on Windows

It's somewhat difficult to find up-to-date binaries for Windows. If you don't mind running an older version of memcached, then the best choice for deploying memcached to Windows is this little guy called MemCacheD Manager. It includes a 1.2.6 binary and will deploy it for you as a windows service in a few clicks.

Heres the best selection of Win32 binaries I could find:

• http://blog.elijaa.org/index.php?post/2010/10/15/Memcached-for-Windows
• http://splinedancer.com/memcached-win32/

Unfortunately in post 1.2.6 memcached builds the "-d install" option doesn't register the windows service correctly and otherwise doesn't speak to SCM properly, so you lose some of the benefits of managing it as a windows service.

If you would like cobble through it and run a newer/64 bit memcached as a service anyway you can stop the service MemCacheD created, drop the newer executable in place over the old one (including pthreadGC2.dll in my case with memcached 1.4.5 64-bit). SCM will report the service didn't start properly even though it did (look for memcached in task manager). Also using the newer executable MemCacheD Manager won't work afterwards (it likes 1.2.6 though). Be mindful when you start the service SCM thinks it fails, so when you start it subsequent times it will keep spawning more memcached processes which don't do anything.

So it seems the best way to go if you have the flexibility and don't want to run an outdated memcached build is to stand up a separate *nix machine and run memcached there. There is something called Membase Server for windows (looks nice), but apparently django can't read from it correctly. There's also a django in-memory cache but it's less reliable than memcached (What happens when your process is killed after idling? etc.) and won't share data cross-process.

Having memcached running opens up another nice option. You can set up write-through session caching then by changing this line in settings_local.py:

SESSION_ENGINE = 'django.contrib.sessions.backends.cached_db'

You should only use session caching with memcached. Prefer the cached_db backend over the cache backend for reliability.

Use SQL Server on Windows

Getting OSQA working on SQL Server did seem to make a difference but I'm not sure what specifically is working better. Our SQL Server is a dedicated box so of course that will make a difference, but I'm also fairly certain that the ADO provider does connection pooling for you, so that is also helpful.

Prerequisites

Install django-mssql from trunk by running pip install hg+https://django-mssql.googlecode.com/hg/#egg=django-mssql.

Set your connection string in settings_local.py like so:

DATABASES = {
    'default': {
        'ENGINE': 'sqlserver_ado',
        'NAME': 'OSQA',
        'USER': 'OSQAUser',
        'PASSWORD': 'Password',
        'HOST': 'ServerName',
        'OPTIONS': {
            'use_mars': True
        },
    }
}

SQL Server Compatibility Hacks

OSQA has some problems inserting and updating things (these would be critical features) when speaking to SQL Server, and it could be a problem with django-mssql, South, or OSQA - or all three...

Inserting things won’t work because some columns with unique indexes are set to null initially (for unique zero-to-many relationships). By default SQL Server will complain if there is more than one record with this initial null value, because of the way it treats nullity (as a value versus “unknown”). So we have to change that unique index to be a filtered unique index so multiple null values can exist. Filtered indexes are a new feature in SQL Server 2008. In earlier versions the fix is to create a computed column that coalesces null values with a new unique value, and then define your unique constraint on that...

So far we’ve only identified one column affected by this problem, dbo.forum_node.last_edited_id. The fix is to run the following TSQL:

USE [OSQA]
GO

-- Initially it will be defined as a unique constraint
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[forum_node]') AND name = N'UQ__forum_no__127805EC1332DBDC')
ALTER TABLE [dbo].[forum_node] DROP CONSTRAINT [UQ__forum_no__127805EC1332DBDC]
GO

-- We'll re-define the constraint as an index (same thing) below so this keeps this script re-entrant
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[forum_node]') AND name = N'UQ__forum_no__127805EC1332DBDC')
DROP INDEX [UQ__forum_no__127805EC1332DBDC] ON [dbo].[forum_node] WITH ( ONLINE = OFF )
GO

-- create filtered unique index
CREATE UNIQUE NONCLUSTERED INDEX [UQ__forum_no__127805EC1332DBDC] ON [dbo].[forum_node] 
(
    [last_edited_id] ASC
)
WHERE ([last_edited_id] IS NOT NULL)
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

Updating things won’t work because the ORM-generated SQL code for update statements sometimes includes a statement that sets the identity column to the same value it was. This is legal to do in MySQL but SQL Server doesn’t like it. The fix is to alter OSQA code to exclude identity columns from the list of columns to update so that django-mssql won’t emit SQL statements for them.

To fix this, fire up your trusty text editor and open the OSQA base model, which is found at D:\Apps\OSQA\forum\models\base.py. Edit the _get_update_kwargs(self) method near line 267 as follows:

def _get_update_kwargs(self):
    return dict([
        (f.name, getattr(self, f.name)) for f in self._meta.fields if self._original_state[f.attname] != self.__dict__[f.attname] and type(f) is not models.AutoField
    ])

This adds a type-check when building lists of update fields to exclude something called AutoField, which is a Django type for auto-incrementing (Identity) columns.

Further Reading

I've stumbled on many articles but don't have time right now to pull out details:

• Django Book - Deploying Django
• SO - IIS isapi_wsgi & SQL Server Slowness
• geographika - IIS & PyISAPIe
• geographika - Compiling a 64 bit PyISAPIe
• SO - Python Windows Performance
• SO - Django & IIS
• SO - django-pyodbc vs django-mssql
• MySQL Connection Pooling, is it worth it?
• SF - Django/IIS & PyISAPIe
• Django docs - Django's cache framework
• menendez - Django Connection Pooling with SQLAlchemy
• menendez - Launching a High Performance Django Site
• SO - Django Connection Pooling