Python+MySQL performance vs PHP+MySQL

[Robert Bass]

Working on Windows 10 (development) and Windows Server 2016 (production)

Running Apache 2.4.26 (64bit), MySQL 8.0.11 (64 bit)

I am working on porting an old student information system for public schools from an ancient MS Access+ClassicASP system to MySQL.  Last summer I began the conversion using Apache+PHP+MySQL5.7 but ran out of summer (I am also a teacher).  All winter I thought about resuming the work but using Python3+MySQL8 instead, and converting my PHP work from last summer as the jumping off point.  As I began running the first several converted scripts, I noticed that they were running slower in Python than in PHP, even using the same MySQL instance and the same Apache server.

I have tried several variants of Python MySQL drivers: 

• MySQLdb (the syntax of which matches what I teach my students to use on an Ubuntu server)
  
• mysql.connector (from Oracle)
• PyMySQL

The script I am using to compare PHP and Python simply receives POST data via JQuery, constructs the appropriate SQL command, and performs an update or delete query.  The script is basically taking data from a form and when the form content changes (detected with javascript), it changes the caller's text field's background color to red (indicating the change is underway), fires off the ajax request, and when JQuery gets a successful response, the script changes the text field color to green (indicating a successful change).  The reason I mention this is that when I use PHP, the red-to-green transition is very fast (you hardly notice).  But when I use a Python script as the form's action instead of the PHP script, the delay is very noticable.

Using Chrome's developer's tools, the network option shows the time waiting for the first bytes back (TTFB) and this is the key difference I am using for performance analysis.  I ran each script (the one that does the lowest level operations) 4 times.  No other users were using the database (it is on my laptop).  No other programs were busy in the background.  I broke down the times for these 4 trials by starting with all the database parts commented out.  Then I un-commented out sections one at a time and ran the script from the calling web page multiple times to get average times.  The 4 trials were separated as follows:

1. All it did was get the POST data and build the SQL string - no actual database interaction at all.
   
2. It did the above and connected to the database.
3. It did the above and submitted the query but did not commit it.
4. It did the above AND committed the transaction.

Below is how each of these trials performed for the different methods of connecting to MySQL.  

I just kept changing a simple varchar(4) field from "1" to "2" and back again.

===================================================================================================================

With Python and PyMySQL

1. TTFB = 130 ms
   
2. TTFB = 170 ms   (actually varied 150 - 200 ms : I did this multiple times to be sure it wasn't recompiling after my change)
   
3. TTFB = 180 ms
   
4. TTFB = 200 ms (as high as 250 ms)
   

Code used:

import pymysql.cursors

connection = pymysql.connect(host='myhost',user='me',password='something',db='mydb',charset='utf8mbr4',cursorclass=pymysql.cursors.DictCursor)

with connection.cursor() as cursor:

cursor.execute(SQL)

connection.commit()

===================================================================================================================

With Python and MySQLdb

1. TTFB = 130 ms
   
2. TTFB = 190 ms
   
3. TTFB = 193 ms
   
4. TTFB = 230 ms (as high as 270 ms)
   

Code used:

import MySQLdb

import MySQLdb.cursors as cursors

dbconn = MySQLdb.connect(host='myhost',user='me',password='something',database='mydb',cursorclass=cursors.DictCursor)

cursor = dbconn.cursor()

cursor.execute(SQL)

dbconn.commit()

===================================================================================================================

With Python and mysql.connector

1. TTFB = 130 ms
   
2. TTFB = 440ms!  A huge jump.
   
3. TTFB = 450 ms
   
4. TTFB = 500 ms (as high as 580 ms)
   

Code used:

import mysql.connector

dbconn = mysql.connector.connect(host='myhost',user='me',password='something',database='mydb')

cursor = dbconn.cursor(dictionary=True)

cursor.execute(SQL)

dbconn.commit()

===================================================================================================================

With PHP and mysqli, the TTFB = 270ms  (about 170ms for the update and 100ms for the commit)

1. TTFB =     3 ms
   
2. TTFB =     6 ms
   
3. TTFB =     N/A   (it does not need a separate commit())
   
4. TTFB =   50 ms (as low as 10 ms and as high as 70 ms)
   

Code used:

$dbc = @mysqli_connect('myhost', 'me', 'something', 'mydb');

if (mysqli_connect_errno()) {

printf("Connect failed: %s\n", mysqli_connect_error());

exit();

}

if ($results = mysqli_query($dbc, $SQL))

{

etc...

PHP is so much faster that the Python scripts.  I have to think developers must have noticed.  I want to use Python going forward and I keep hearing that it is the way to do for so many big company websites.  I have to assume...

1. there is some magic setting either in Apache or Python to make the scripts start up faster (and this may be outside the scope of this group), 
2. and that there is some additional trick to make the database connections faster in the MySQL+Python system  (which is the question that has led me to this group).
   

Any suggestions are welcome.

[INADA Naoki]

PHP is so much faster that the Python scripts.  I have to think developers must have noticed.  I want to use Python going forward and I keep hearing that it is the way to do for so many big company websites.  I have to assume...

1. ​​
   there is some magic setting either in Apache or Python to make the scripts start up faster (and this may be outside the scope of this group), 

​I assume you're using cgi.

PHP is faster than CGI.  And CGI is much slower on Windows especially.  Windows is very slow

about creating process, and AntiVirus softwares can make it more slower.​

On the other hand, when using "application server" daemon, Python can be faster than PHP.

Especially, when using web framework, Python can much faster than PHP.  Python application

can startup once and handle many request.  PHP must load entire application including framework

for each request.

For example, compare "Django", "Flask", "Laravel", and "Symfony"

https://www.techempower.com/benchmarks/#section=data-r16&hw=ph&test=fortune&l=hr9y4f&f=zhb2t3-zik073-zik0zj-zik0zj-zik0zj-zik0zj-zik0zj-4u6f3

Sadly speaking, many application server for Python works only on Unix systems.

I hope mod_wsgi can work on Windows, but I'm not sure.

1. and that there is some additional trick to make the database connections faster in the MySQL+Python system  (which is the question that has led me to this group).
   

​I don't know how php connect to MySQL.  Do you used named pipe?  or persistent connection?

Anyway, if you used "application server" model, you can pool DB connection and reuse it

over HTTP requests.  For example, SQLAlchemy provides such pool.

Regards,​

Any suggestions are welcome.

--

INADA Naoki  <songof...@gmail.com>

[Robert Bass]

Thank you.  Yep, I'm still in CGI-mode, and that is my 1st mistake in this new century.  I need to re-think my application model.  I have looked at a variety of frameworks, and I will likely make the mistake of trying to make my own (I'm currently trying to make sense/use of mod_wsgi from scratch which I have installed - which took an hour to fumble my way through, but have yet to figure out how to keep my database connection persistent).  That must be the way to go, because the repeated re-connecting to the database is definitely a performance killer).

I do have the option to re-host on Ubuntu, but we have this nice, shiny Win2016 server running our old IIS/Classic ASP/MS Access application which I want to utilize without wiping out the OS and starting over.  Since it is hosting our current system, I cannot just take it offline for a week to play with.  I need the cutover from old ASP to new Python to occur overnight before mid-August.  May be a bad choice in the end, but I have other services I want to host on it so...  

I'm not sure why the PHP connection is so fast out of the box.  I'm using TCP/IP to connect to the MySQL server (even though it is running on the same machine - for now).  I don't think it is a named pipe, but perhaps behind the scenes it is keeping a persistent connection via a pipe or something else.  There is nothing in the PHP config file that has account information needed to connect to the MySQL server - it gets it all for the CGI PHP script.

I'll keep digging with mod_wsgi and see if I can figure out how to keep the db connection persistent across a user's session (which I am also managing myself with a cookie and a record in the database - which the frameworks also can handle for me - but where's the fun in that?).  I'll probably give in eventually and try a ready-made framework in after a week more of trying to do this from scratch.

Thanks for your help.