Improving database performance

[Arto Huhtala]

Hi,

I am developing a test application with web2py to test database performance. The idea is to see how long does it take to fetch information from database on different loads.

So far I have tested with SQLite and I've made test databases of size ~1K and 10K.

Now I'm trying to test a ~100K and ~1M databases, but once I have populated the database to ~100K, the app is really slow. It takes forever to load the plain index page.

I tried to solve this by using PostgreSQL instead but still it takes forever to load the index page. What gives?

I have been looking advice on improving the performance and some have come across (Deployment recipes from web2py book), but I also have questions concerning them:

• Minimize the code in models: do not define functions there, define functions in the controllers that need them or - even better - define functions in modules, import them and use those functions as needed.

Q: I need to manipulate database contents in separate functions but if I place the code to a separate module it can't connect to the database.

How can I access the database from separate module? Should I use the same connection string in them as used in model files (db.py)?

• Do not put many functions in the same controller but use many controllers with few functions.

Q: Do I need to create a view for every separate controller? Or can I use the same view in many controllers?

- Use indexes

Q: Should indexes be defined in model file after defining tables or somewhere else? So far I have found no difference in performance when defining indexes.

Thanks!

[黄祥]

Q: I need to manipulate database contents in separate functions but if I place the code to a separate module it can't connect to the database.

How can I access the database from separate module? Should I use the same connection string in them as used in model files (db.py)?

please create module and import it. 

for started, you can move your code into module and add the current for (request, response, session, cache, db). 

e.g. 

models/db.py

# for track changes module

from gluon.custom_import import track_changes; track_changes(True)

# for use in modules

from gluon import current

current.auth = auth

current.db = db

current.mail = mail

if request.controller == 'default' and request.function == 'index' :

response.models_to_run = ['db_wizard_0_blog.py', 'menu.py']

elif request.controller == 'createindextable' and request.function == 'index' :

response.models_to_run = ['db_wizard_0_blog']

elif request.controller == 'populate' and request.function == 'index' :

response.models_to_run = ['db_wizard_0_blog']

else:

response.models_to_run = ['.*']

modules/module_for_default.py

from gluon import *

def show_0(table):

current.session.forget(current.response)

row = table(current.request.args(0)) or redirect(URL('index'))

return dict(row = row)

after that please import it.

e.g.

controllers/default.py

import module_for_default

@cache.action(time_expire = 60, cache_model = cache.ram, session = True, 

 vars = True, lang = True, user_agent = False, public = True)

def index():

    return module_for_default.show_0(db.blog)

ref :

http://web2py.com/books/default/chapter/29/04/the-core#Accessing-the-API-from-Python-modules

Q: Do I need to create a view for every separate controller? Or can I use the same view in many controllers?

it depend, is the view is reusable by another functions in controller or not. if reusable, i think you can use block or function in view.

ref:

http://web2py.com/books/default/chapter/29/05/the-views#Functions-in-views

http://web2py.com/books/default/chapter/29/05/the-views#Blocks-in-views

- Use indexes

Q: Should indexes be defined in model file after defining tables or somewhere else? So far I have found no difference in performance when defining indexes.

i think yes, you should create table index, 

Indexes are used to quickly locate data without having to search every row in a database table every time a database table is accessed. 

taken from wikipedia :

http://en.wikipedia.org/wiki/Database_index

please don't define it on models because it always execute (except you use response.models_to_run or module to elimate it). i usually define it on controller and access it when needed.

e.g.

controllers/createindextable.py

def index():

if db._dbname == 'sqlite':

db.executesql('CREATE INDEX IF NOT EXISTS idx_blog ON blog (id, title);')

elif db._dbname == 'mysql':

db.executesql('CREATE INDEX idx_blog ON blog (id, title);')

redirect(URL('default', 'index'))

best regards,

stifan

[Jim S]

Can you post some code to review?

-Jim

[Paolo Valleri]

Hi,

if the index is slow with 100k dummy data you should create an index and on the other hand review your code. 

Create indexes on columns used by the query that generates the index; for what concerns the query, can you please post that code?

Finally, be aware that for the moment you should create the indexes outside web2py, there is a plan to add this feature in the upcoming releases see: https://code.google.com/p/web2py/issues/detail?id=1665&sort=-id

Paolo

[Niphlod]

BTW, this post is "shady".
Just connecting to a database holding 100K records or 2M takes the exact same amount of time.
Of course fetching and representing on a page 10 records or 2M makes a difference, but it has nothing to do with web2py's (optional) optimizations.

[Jim Steil]

Hoping he will pay some code so we can see where the problem is...

Jim

--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to a topic in the Google Groups "web2py-users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/web2py/vIsm1ojjzDk/unsubscribe.
To unsubscribe from this group and all its topics, send an email to web2py+un...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.