How can I maximize performance on EC2 with Django/Apache/mod_wsgi/MySQL?

Question

I'm running Django-powered site on EC2 m1.medium instance (3.75 Gb memory, 2 EC2 Compute Unit, 410 Gb instance storage, moderate performance) with Ubuntu 12.04 LTS. MySQL settings on my EC2 instance are the following (from /etc/mysql/my.cnf):

key_buffer = 16M
max_allowed_packet = 16M
thread_stack = 192K
thread_cache_size = 8
query_cache_limit = 1M
query_cache_size = 16M

The mod_wsgi settings from /etc/apache2/sites-available/mysite.com:

WSGIDaemonProcess mysite.com user=me group=me processes=7 threads=20 maximum-requests=1000000

Given my configuration, what is the maximum number of processes/threads/requests I can set? I tried some values, but they led to MySQL crashing. Is it ok to set MySQL key_buffer to 0.75Gb (20% of the 3.75Gb RAM on the EC2 instance)? Thanks in advance!

Károly Nagy Károly Nagy · Accepted Answer · 2012-08-03T08:14:39

All of your MySQL variables seems very low. First of all the most important thing: what storage engine are you using? Key_buffer_size has only effect on MyISAM storage engine.

If you're using MyISAM do the followings:

> select sum(index_length) / 1024 / 1024 as index_size_MB from information_schema.Tables where Table_schema = 'knagy' and Engine = 'MyISAM';
+---------------+
| index_size_MB |
+---------------+
|    0.16992188 |
+---------------+

This will show you how big your indexes are. You should set roughly this value as key_buffer_size if you have enough RAM. But don't increase this higher than 50-60% of your free RAM.

If you're using InnoDB try to set the innodb_buffer_pool to the size of your dataset with indexes:

> select sum(index_length + data_length) / 1024 / 1024 as innodb_MB from information_schema.Tables where Table_schema = 'knagy' and Engine = 'InnoDB';
+--------------+
| innodb_MB    |
+--------------+
| 403.04687500 |
+--------------+

You can set it up to 80% of your free RAM of course consider that other application's memory usage.

Only with one of these changes you will get significant performance boost.

You can increase the thread_cache_size as well around 30-50 or more if you see threads_created status variable increasing fast.

Of course there are a lot of further optimizations but this will get you around the 80-90% of the possible maximum performance.

How can I maximize performance on EC2 with Django/Apache/mod_wsgi/MySQL?

2 Answers