Troubleshooting Django(Python) Application with Nginx + uWSGI on MySQL
Good thing about working at Kuliza is, I get to experiment, play and work with different stacks for our customers. Recently, we started working with one of our cloud customer, a fast growing startup based out of Bangalore. They run their SaaS application in AWS cloud with Python(Django) on nginx + uWSGI using MySQL as backend.They have two main components in the application - frontend application serves the data from DB and backend workers write to DB.The application runs on 2 servers for handling http requests with Amazon ELB, 1 MySQL DB, 1 large server for MongoDBÂ and memcache.
One of their critical issue was unable to take backups from MySQL as it was freezing system and causing application servers to fail. They were running MySQL using Amazon RDS in a single zone and as known, it will suspend read/write when you perform any snapshot or backup using RDS API. After our initial revierw, we decided to move their DB to Multi-AZ deployment so the snapshots and backups will be performed on the stand-by system and it will cause any read/write freeze on the master. While we were able to take snapshots after RDS multi-az deployment, their write latency has increased by 3x which was unusually high and Read/Write I/O was consistently increasing for quite sometime even before we moved to multi-az.
Along with this, their application was consistently getting into error mode as nginx was unable to connect to the upstream (uWSGI workers) and the impact was severe as almost 10% of their traffic could not be served because of this. They just moved to nginx+uWSGI from Apache + mod_uwsgi deployment (high CPU consumption and unable to scale due to heavy processes).
Initial focus :
Review nginx and uWSGI configurations - we found few misconfigurations related to worker_processes, worker_connections, listen queue etc and corrected them but it didn't help. Each of the app server were running in a box with 5 CPUs and 1.7 GB memory
The problem was very severe with multiple outages in a day. Nginx was reporting thousands of "unable to connect to upstream" errors while uWSGI logs didn't had any specific errors. The system health stats were looking good even during outage time - CPU was around 20% and enough free memory. There was no major Disk I/O or Swapping or Interrupts or Context Switches.
A quick look at RDS stats confirm that multi-az has not affected read performance and the write performance has increased as we have synchronous replication with a stand-by for quick failover.
This production setup runs uWSGI with master process and predefined workers(20) with each worker configured to handle max-requests of 10K before re-spawning. So reduced the max-requests to 1000 to see if quick recycling would help but it has aggravated the problem because of frequent re-spawning. So the value was increased to 2500 but there was no improvement. There was also a belief that with Apache they never had outage issues.
We decided to increased our number of app servers from 2 to 4Â to see if that would fix the problem but it caused an immediate outage so had to roll back. Initially we suspected this could be because of a centralized resource like DB or Cache server but we ruled out DB as RDS stats didn't not show any huge spikes in Read/Write Latency or IO during the outage.
The production outages become very frequent and the response times have increased to 20 to 30 seconds in few cases for 15 to 30 minutes and forced us to reload our nginx+uwsgi process.
Deep dive to figure out bottlenecks:
 At uwsgi level, we enabled harakiri for timeout and harakiri_verbose for logging the trace of system calls causing workers to hang. This is has helped us to isolate the problem to database as uwsgi was hanging on reads calls to database. You can also debug uwsgi process using "strace" utility in linux environment to identify the bottlenecks and monitor system calls.
When we looked at the MySQL process list, there were bunch of queries hanging on for 20 to 30 seconds of because of locks - this was because of contention between our write workers and read app servers. The issue was underlying tables were using MyISAM as storage engine and that causing wait locks on select queries due to frequent inserts/updates by workers. You can look at MySQL process status using "show processlist". We have moved the storage engine to "InnoDB" and that resolved most of the performance issues as uwsgi workers were no longer waiting on DB due to row level locking features in InnoDB. The below screenshots show the improvements for read/write I/O.
Also found that django_session table was having almost 35GB of data - there was no automatic purging of old records. This was causing I/O bottlenecks, increased our write latency on master DB. We are moving away from using DB as backend for storing sessions data to memcache.
Using pmap utility, we found that each uwsgi worker was using around 80MB of memory which was limiting the vertical scalability of concurrent requests within one server. Most of this memory was used by process private/working set which we later attributed to loading a separate static data as cache into each worker - However product team is now working on re-architecting application so we can read this static data from a memcache instead of loading it into each uwsgi worker. This will help us to achieve high concurrency by running more uwwsgi workers.
Useful MySQL Queries :
 Find size of your database - SELECT table_schema "Data Base Name", SUM( data_length + index_length) / 1024 / 1024 "Data Base Size in MB" FROM information_schema.TABLES GROUP BY table_schema ;Â
Find size of your tables - SELECT TABLE_NAME, table_rows, data_length, index_length, round(((data_length + index_length) / 1024 / 1024),2) "Size in MB" FROM information_schema.TABLES WHERE table_schema = "Your_Schema_Name"; Â Â Â Â Â
In our case, bottlenecks were clearly at MySQL level due to locking issues and heavy writes to session table. This application is running smoothly after above changes for last 2 weeks without any problems. One of the important lesson learned was always troubleshoot your centralized resource first when you get into performance or scalability issues.
