Page with four LOAD components is too slow

[Bernardo Leon]

Hi, I am facing a problem with performance. I have a view with four LOAD calls that is taking almost 15 seconds to load. I know that two of this components are loaded after the two first LOAD calls but 15 seconds is still too much time. So I profiled the execution of my view and end up with the runpythonrun image I am attaching.

This is my DAL definition:

db = DAL(gth_dev_db_connection_string,

         lazy_tables=True,

         pool_size=15,

         migrate=False)

And I don't have functions on my model file (I am importing them from modules).

What could be the problem?

Thank you!

[Anthony]

If you are using the default file based sessions, the first thing you can do is unlock the session within each component request if you don't need to write to the session:

session.forget(response)

Otherwise, each component must wait for the previous to load.

Anthony

[Leonel Câmara]

You are probably doing too much stuff. See if you can pre-compute stuff.  

  

For instance. Imagine you have a debate table, then you have a comment table that refers to the debate where the comment is made. If you want to show a list of debates with the comment count, then you will have to make an extra query for each debate to get their comment count. OR you can have a comment_counter on each debate that starts at zero and is increased each time a comment is created there and decreased when a comment is deleted (_after_insert and _before_delete callbacks are good for this). If you do it this way you will only need to do the first query that gets you the debate list.  

  

Adding cacheable=True to your selects can also speedup things a tiny tiny bit.

If it's still too slow then you need to cache things.  

[Bernardo Leon]

Thank you to both of you. Anthony I was using redis to handle my sessions hence I can discard that bottleneck besides I need the session info on my components. 

It seems that web2py performs more querys to the database than expected. I have the database on digitalocean but was using web2py on my machine. I tried deploying the app in the same server of the database and now it is more responsive. I have reduced the time a component is loaded from 5-6 seconds to 1.30 seconds max. 

I wasn't expecting this since I think the same quantity of information travels through internet on both cases 

[(local browser + local web2py ---- internet db) ~ (local browser ---- internet web2py + internet db)]

unless web2py makes more calls to the db than I supposed it to do.

Finally, is 1.30 seconds a good speed? My components perform only selects to tables with no more than 5 records so I don't think it is an application issue. Is it ok? Thank you

[Leonel Câmara]

That still seems slow actually. Is redis on that same machine or could you have latency issues there too? If it is then it's hard to tell what's causing the slowness now that everything is in the same machine. We would need to know the machine specs and what your code is doing exactly. You may simply need more RAM. 

[Bernardo Leon]

Yes, the application, redis and the database are in the same server. This server has 512 MB of RAM but I don't think I am using all the ram. I have 48mb free right now. Regarding the code, it just makes simple selects on one or two tables (the querys arent doing anything weird). Do you still think it is a RAM issue?

What should be a normal load time for a component? Thank you!

[Leonel Câmara]

Yes I'm pretty sure it's a RAM issue, 48 free MB is way too low. You easily start having to use virtual memory with a couple of requests.   

  

To give you an idea, on a similar setup but a much better machine, I have a much much more complex page than yours appears to be, with quite a lot of ajax calls and complex db queries loading everything in 700ms.  

[Bernardo Leon]

It seems that you are right, I set up a virtual machine with 4gb ram only for the database and I ran the code along with redis on my laptop with 16gb ram and my times now are nearly the 600ms. I think it is good enough. Having some components that don't perform selects rendering only a simple html taking nearly 500ms is suspicious though.

Thank you Leonel! Using redis, keeping the webserver and the database in the same network and assigning more RAM seems to be the best tips to improve the request performance (besides the book recommendations like lazy_tables, pool_size, extraction functions to modules, etc)

Just for curiosity, what are the hardware specs of your server? Thank you

[Leonel Câmara]

I'm using a 32GB quad core server, which is way **way** more than I need at the moment, but soyoustart is ridiculously cheap and I have this stupid idea that before scaling horizontally you scale vertically as much as possible.  

[黄祥]

@leonel, might i know what do you mean with scaling horizontally and scaling vertically?

thanks and best regards,

stifan

[Leonel Câmara]

What I mean is that nowadays it's not that expensive to have a 256GB server with 20 cores. So instead of having a load balancer, a lot of machines running web and application servers, a lot of other machines running databases, etc. You can start with a small server and keep improving it for quite a long time without ever needing more than a single machine. That's what I mean by vertical scaling.  

  

Nowadays when a single computer isn't enough, well that's a good problem to have, and you can afford to have people solve all the problems of horizontal scaling for you,