Forums

I have a Web2Py on MySQL on a free account. Locally (on my Mac) it is very fast / responsive. On PythonAnywhere ... not so fast / responsive.

If I upgrade to the Web account ($12/month) will my web site performance improve ?

Thanks

The main thing that limits the perceived speed of free account web apps is that they only process one request at a time. So if you're serving static assets (like images, javascript and css) from your web app, your web app will only serve one at a time. Have a look at the docs about static files and see if that helps.

The website consists of mostly queries and simply displaying the results. No images or fancy CSS. Locally a query is sub second. On PythonAnywhere the same query may take 7-8 seconds ! As you mentioned, upgrading would allow more than "one process request at a time". Do you mean requests to the web site or requests to the MySQL database or both ? Thanks

For such a big difference where you're only making one request at a time, my guess is that your code has inefficeincies that are hidden from you when everything is local. For example, if you transfer large amounts of data from the database, on PythonAnywhere, it has to go across the network, but on your local machine it doesn't.

I mean requests to the web site.

w

Massimo's thoughts: "I have not experience performance issues on pythonanywere but I have not used MySQL. One possibility is that on your local machine the database and the server run on the same machine. On pythonanywhere (and most hosting) they may run on different machines therefore you have network latency. Moreover I do not know if the mysql instance if dedicated or shared between many users. "

1. Is the MySQL instance shared between many users ?
2. Is there a paid solution, where I can get my own dedicated MySQL instance ?

1. Yes
2. No

The issue that Massimo is talking about is not about databases being shared it's about databases being on different machines so there is a network step in any request. If you're making requests that have a large result set and then filtering in your web app code, there will be a long wait while the data is transfered across the network.

We have large, busy sites running on PythonAnywhere that use MySQL and don't have this issue.

There is something that bothers me still, even after all the above clarifications... The performance is fluctuating: the same query (try "chronic heart failure") at icd10doc.com... It sometimes takes 3-5 seconds and sometimes the same query takes 60 seconds. On the DB side, running the SQL directly - it is 0.08 sec. Again locally - it is sub second.

It appears the bottle is between the db server and the web server (the fluctuating performance) and thus - there is not much I can do to improve it.

Can you please help me on this issue ? Maybe check it out once more from your side ? Thanks for your patience

Can you tell us a bit more about what kind of query you're running? What's the direct SQL? And what kind of processing do you do in Python?

1. I have installed the EXACT configuration Web2Py app on MySQL DB with a different email on your machines. Performance still is unacceptable - but it is twice as better than the one on the original ICD10 installation ! I am talking about AVERAGE of 4-6 seconds on the new installation vs. average of 15 -30 seconds on the original (I've checked tens of same queries on both).

2. I've run several queries DIRECTLY on the MySQL instance on your side. Consistently 0.04-0.08 sec. Very reasonable I'd say. Again - locally on my Mac - ANY query on this configuration - is sub second. I think we can safely say the problem is NOT in my SQL code.

3. The above plus the fluctuations in performance during the time of day - means that Massimo was probably right - the bottle neck is somewhere between the MySQL and the Web2Py servers. BTW - what is the webserver you guys use - Rocket or something else ?

4. Now to your question, specifically. As you can check yourself at icd10doc.com: user enters either free text or a specific code in to a search box. Problem of latency is most annoying with free text where it may take 15-60 seconds (?!?) for response. With that being said - even 2-3 seconds response time on a search with a code (not free text) for a disease on an indexed table, when directly on MySQL it is measured in milliseconds and locally is always sub second - still shows there is a problem. The MySQL response to the query is just being parsed by Web2Py into a view, usually with links to other pages.

5. And here is another issue - when user clicks on one link on page - even though it is a simple query (on an indexed table) which responds with another W2P view- it still may take 2-5 seconds ! Why ?

Please - HELP !

Can you provide an example of a SQL query you're using? What do you then do, in Python, to manipulate the results of the query?

In the Controller: sqlstring = "SELECT * FROM DiagnosisTable WHERE Diagnosis LIKE '%"+heart failure+"%'" DiagnosisResults = db.executesql(sqlstring, as_dict = True) return dict(DiagnosisResults = DiagnosisResults)

The above is actually multiplied by 6-7 times (various tables being queried with the term "heart failure"

In the View: {{ if not DiagnosisResults: response.write('ICD-10 Diagnosis: Nothing found searching...') =session.SearchTerm else: response.write(H3('ICD-10 Diagnosis')) for i in range(len(DiagnosisResults)): =(A(DiagnosisResults[i]['ICD10Code'], XML(' '*3) , DiagnosisResults[i]['ICD10Diagnosis'], _href=URL("DiagCodeDetails", args=DiagnosisResults[i]['id']))) response.write(BR()) pass pass }}

and sometimes the results are displayed in a much nicer way using a table (tr, td, etc)... As mentioned before - nothing fancy.

That's it

Tables size vary between 10k -150k rows All indexed on several columns - those that are being queried...

Glenn -- can you share the list of large/busy sites using PAW?

I don't know what has changed - but right now, the response time is actually very good ! Did you guys changed anything on your side ?

Hmm, that's interesting. There was a out-of-control query on the MySQL server associated with your account, which we killed yesterday mid-afternoon UTC. So perhaps that was what we affecting your site. But there are a bunch of other databases on that server, and the web sites associated with them were performing normally.

I wonder if there was some weird MySQL thing going on where that query was somehow affecting your DB queries more than other people's? I can't see any obvious connection, but the correlation is definitely suspicious.

@gojiberrypi unfortunately we don't have permission from the site owners (though we should fix that -- I'm sure some of them would be happy to share stats!) but I can tell you that our busiest customer site right now is delivering about 40 hits/second.

Guys

I would like to thank you all for taking care of this performance issue. Now - everything is working fine, response time is very good and I am happy as a clam.

Many thanks

Excellent, glad it's all working! We'll have to spend some time spelunking the MySQL logs to try to work out whether and why the out-of-control queries were disproportionately affecting your queries.