CODEWITHSUNDEEP
pythonmysqldjangomongodbmysql-python
Nipun Batra
Nipun Batra

Reputation: 11367

Django Persistent DB connections - A different approach

I have gone similar threads like Django persistent database connection and other stuff on same topic. However Django doesn't officially support persistent connections to MySQL and Mongo(to my limited knowledge).So i tried avoiding a lot of stuff and tried to make it simple.So what i did was in my views.py made global connection variables for both MongoDB and MySQL,something like: 

from pymongo import Connection
import MySQLdb
global mongo_connection,mongo_db,collection,mysql_connection,mysql_cursor
mysql_connection = MySQLdb.connect (host = "localhost",
                       user = "root",
                       passwd = "password",
                       db = "demo")
mysql_cursor = mysql_connection.cursor ()
mongo_connection = Connection()
mongo_db = mongo_connection.test_database
collection = mongo_db.test_collection

So after this when the required view is called as per URL requested,i dump the data in the two databases. Like:

mysql_cursor.execute('''INSERT INTO           
table_name(l,n_n,n_id,s_n,s_id,u,r) VALUES
(%s,%s,%s,%s,%s,%s,%s)''',
(l,n_n,n_id,s_name,s_id,u,re)
)

And similarly i did for saving to MongoDB.

Obviously there's this flaw in this approach that i am not closing the connection anywhere.But this approach does seem to work and work well.

Why is this sort of approach not used?

How can i measure the performance improvements i get by using this approach v/s letting Django create a new connection to DB on each call.

Also a batch insert is supposed to make things even better,by reducing calls to DB.How can such a concept be implemented within view definition?

Here is how my application behaved before i had used my method of trying to make a persistent connection and had let Django take care of it

mysql> show status like '%onn%';
+--------------------------+--------+
| Variable_name            | Value  |
+--------------------------+--------+
| Aborted_connects         | 0      |
| Connections              | 164359 |
| Max_used_connections     | 3      |
| Ssl_client_connects      | 0      |
| Ssl_connect_renegotiates | 0      |
| Ssl_finished_connects    | 0      |
| Threads_connected        | 1      |
+--------------------------+--------+
7 rows in set (0.00 sec)

After a few seconds when i ran the same query i got:

mysql> show status like '%onn%';
+--------------------------+--------+  
| Variable_name            | Value  |
+--------------------------+--------+
| Aborted_connects         | 0      |
| Connections              | 175047 |
| Max_used_connections     | 3      |
| Ssl_client_connects      | 0      |
| Ssl_connect_renegotiates | 0      |
| Ssl_finished_connects    | 0      |
| Threads_connected        | 1      |
+--------------------------+--------+
7 rows in set (0.00 sec)

http://dev.mysql.com/doc/refman/5.5/en/server-status-variables.html#statvar_Connections states that Connection:The number of connection attempts (successful or not) to the MySQL server.So is it due to some problem in which i handle saving a row using Django ORM for MySQL or is this sort of number of connections expected?

However after using my approach the number of connections didn't increase.

SOLVED

Got confused by reading the definition about Connections.So finally figured it out by doing a test. Inserted 19 records into DB,Connections increased by the same number.So i believe it means the number of times DB has been contacted.So in that case using Django's inbuilt stuff is the best way to go.

http://dev.mysql.com/doc/refman/5.5/en/too-many-connections.html tells that maximum number of connections is 151,so anyways it was a misinterpretation on my part.

Upvotes: 2

Views: 3806

Answers (1)

Filip Dupanović
Filip Dupanović

Reputation: 33670

If you want to get into such a technical topic, have a look at the django.db package and review how Django's database backends and QuerySet instances interact with each other before you make any fallacious assumptions, suh as that Django opens a new database connection every time it interacts with a database. You'll see that the database layer performs everything you've mentioned here, without allowing the developer to improperly manage database resources, like obtaining a cursor within a loop and forgetting to close it and creating memory leaks.

So it's not that the approach isn't present, it's that it's managed and abstracted from the work you perform, as it should be, for reasons mentioned above, with the added benefit that you don't need to, say, directly incorporate database management within a view.

Having said that, for unsupported databases, such as MongoDB and any other non-relational database, you'll have to assume that you do not have Django's database layer managing your database resources and how you interact with them and diligently asses what are your obligations as the developer under the conditions of interfacing with their database APIs. When you see obvious patterns emerging you will come up with a solution suitable to be applied within the context of view components.

Upvotes: 2

Related Questions