After using new relic, i found there is query that took more than long time to finihed. After investigate, I found that “update query” on django session is the most expensive and slowest query. Here is the query:
1 | UPDATE `django_session` SET `session_data` = %s, `expire_date` = %s WHERE `django_session`.`session_key` = %s |
I’m wondering what the reason that queries need a lot of time to be finished. Fyi, there is a million records under this table. Since i don’t have deep understanding on how MySQL works, then I start reading a few stuff that related with this problem.
1. Internal Locking
There are 2 action of locking, read and write locking. First case, we want to make select query on a table which mean “read”. MySQL will check if there is lock on selected table or not. If no lock found, then MySQL will put “read lock” on this table. But, ff there is a lock on this table, then this “read lock” will put into queue.
Same thing with “write”, for instance we want to update records. MySQL will looking for lock on table, if no lock, then it will put “write lock” on this table or put into queue if lock found. The difference here that “write lock” have higher priorities than “read lock”.
Detail : http://dev.mysql.com/doc/refman/5.0/en/internal-locking.html
2. Primary Indexes and Data Type
In django session, it will match the “session_key” and update it’s value. Fyi, “session_key” is the primary key in django session table which that’s mean this column is being used as pointer. Primary key can be INT, CHAR and VARCHAR. Is that the data type of this primary key is matter? My answer is yes it is. In this cases, i’ll refering for MyISAM engine.
When we make query, then MyISAM will retrieve the primary keys, put this list of primary keys into index blocks (to minimize disk I/O) and arrange them into Binary tree. For instance, 1 index block have 1024 byte. If our primary keys is INT (4 bytes) (http://dev.mysql.com/doc/refman/5.0/en/integer-types.html), that’s mean each single index can hold 256 items. Meanwhile, for varchar that have 11 character, it’s have size about 12-13 bytes which need 1-2 bytes as prefix that contain length of value. That means 1 index block only can hold about 85 items.
Based on this two documentation about how MySQL works, I’m understand why “update” query on session table takes long time. Then I start to thinking about solution :
1. Reduce records
“Session” table might have the most requests in Django Apps. Because mostly each action need to be validated and that’s mean requests on this session table. Since I see more than million records on this table, reduce it into 100K records will give a big difference. Better run clean-up scripts from session that expired since one days ago. Some documentation related with this clean-up:
https://docs.djangoproject.com/en/dev/topics/http/sessions/#s-clearing-the-session-store
2. Optimize MySQL
MySQL configuration have options that we can tune and increasing performance by configure it correctly. Also use MySQL query cache wisely ( http://www.mysqlperformanceblog.com/2006/07/27/mysql-query-cache/ )
Anyway, if our session table is too big, then we can delete it manually from MySQL-client by :
1 | delete from django_session where expire_date <= NOW(); |