Re: Unusual behavior of storage.db/storage.sqlite file

[Anthony]

Well, you must be doing something that's inserting data into the db on every request. Hard to say what that could be without seeing the code.

Anthony

On Sunday, September 2, 2012 11:50:39 AM UTC-4, entropist wrote:

Hello, I've just started with web2py and I'm trying to learn as much as I can to build functional web applications.
I'm almost done with my first web2py app, it's a basic photo gallery where one can upload photos visible to other users, with functionalities like comment and like along with photo ratings etc.
But I noticed a rather peculiar behavior, which slowed down my app tremendously. The storage.sqlite/storage.db file increases in its size mightily with every page load of my app!
Within a few minutes of using the app, its size reaches around 1GB and goes onto 4-5GB after a while!

With regard to the inner workings, I have images, users and comments databases. Besides, I have heavily tweaked the CSS file, added my own stylesheets and have used some extra jquery modules extensively.

What could be the reason behind such a behavior and how can I get it fixed? Please guide.

[entropist]

if not request.env.web2py_runtime_gae:
    db = DAL('sqlite://storage.db')
else:
    db = DAL('google:datastore')
    session.connect(request, response, db = db)

response.generic_patterns = ['*'] if request.is_local else []
from gluon.tools import Auth, Crud, Service, PluginManager, prettydate
auth = Auth(db, hmac_key=Auth.get_or_create_key())
crud, service, plugins = Crud(db), Service(), PluginManager()

auth.define_tables(username=True)

mail=auth.settings.mailer
mail.settings.server = 'logging' or 'smtp.gmail.com:587'
mail.settings.sender = 'y...@gmail.com'
mail.settings.login = 'username:password'

## configure auth policy
auth.settings.registration_requires_verification = False
auth.settings.registration_requires_approval = False
auth.settings.reset_password_requires_verification = True
auth.settings.register_next=URL('Gallery')
auth.settings.login_next=URL('Gallery')
auth.settings.logout_next=URL('index')
name=''
mail=''
if auth.user:
    name = auth.user.username
    mail = auth.user.email
else:
    pass

from gluon.contrib.login_methods.rpx_account import use_janrain
use_janrain(auth,filename='private/janrain.key')

 
db.define_table('image',
   Field('title', unique=True),
   Field('file', 'upload',autodelete=True),
   Field('votes', 'integer',readable=False,writable=False, default=0),
   Field('voted','list:string',readable=False,writable=False,default=[]),
   Field('uploader',readable=False,writable=False,default=name),
   format = '%(title)s')

db.define_table('comment',
   Field('image_id', db.image),
   Field('author',writable=False,default=name),
   Field('email',writable=False,default=mail),
   Field('body', 'text'))

db.image.title.requires = IS_NOT_IN_DB(db, db.image.title)
db.comment.image_id.requires = IS_IN_DB(db, db.image.id, '%(title)s')
db.comment.author.requires = IS_NOT_EMPTY()
db.comment.email.requires = IS_EMAIL()
db.comment.body.requires = IS_NOT_EMPTY()

db.comment.image_id.writable = db.comment.image_id.readable = False

If it helps, this is the db.py module that I have for my app.
Please guide.

[Niphlod]

this seems not to be the problem. If you see the db getting larger for a particular requested page, please post the code relative to the function called in the controller to generate the page.

[entropist]

from gluon.tools import Crud
crud = Crud(db)

auth.settings.login_next=URL('Gallery')
auth.settings.logout_next=URL('index')

auth.settings.register_next = URL('Gallery')
crud.settings.delete_next = URL('Gallery')

group_id=auth.add_group('manager', 'can access the manage action')
auth.add_permission(group_id, 'access to manage')
auth.add_permission(group_id, 'create', db, 0)
auth.add_permission(group_id, 'read', db, 0)
auth.add_permission(group_id, 'delete', db, 0)
auth.add_permission(group_id, 'update', db, 0)
auth.add_permission(group_id, 'select', db, 0)
auth.add_membership(group_id, 7)

@auth.requires_permission('access to manage')
def manage():
    grid = SQLFORM.smartgrid(db.image)
    return dict(grid=grid)
   
def index():
    images = db().select(db.image.ALL, orderby=db.image.title)
    return dict(images=images)

@auth.requires_login()
def Gallery():
    form = SQLFORM(db.image)
    if form.process().accepted:
        response.flash = 'The image is uploaded!'
    images = db().select(db.image.ALL, orderby=db.image.title)
    return dict(images=images, form=form)
   
def show():
    image = db.image(request.args(0)) or redirect(URL('index'))
    db.comment.image_id.default = image.id
    form = crud.create(db.comment, next=URL(args=image.id),
                     message='Your comment is posted!')
    comments = db(db.comment.image_id==image.id).select()
    return dict(image=image, comments=comments, form=form)
   
def delete():
    crud.delete(db.image, request.args(0))
    return dict()
   
def list_items():
    items = db().select(db.image.ALL, orderby=~db.image.votes)
    counter = db(db.auth_user.id > 0).count()
    return dict(items=items,counter=counter)

def download():
    return response.download(request, db)

def vote():
    item = db.image[request.vars.id]
    new_votes = item.votes
    l = item.voted
    if auth.user.username not in l:
        new_votes = item.votes + 1
        l = l + [str(auth.user.username)]
    item.update_record(voted=l)
    item.update_record(votes=new_votes)
    return str(new_votes)

def user():
    return dict(form=auth())


def download():
    return response.download(request,db)


def call():
    return service()


@auth.requires_signature()
def data():
    return dict(form=crud())

This is the default.py controller module. It seems each and every page load causes the sqlite/db file to increase by 2-3 MB, though I'm cannot be sure about any specific page.
Thanks in advance.

[Massimo Di Pierro]

group_id=auth.add_group('manager', 'can access the manage action')
auth.add_permission(group_id, 'access to manage')
auth.add_permission(group_id, 'create', db, 0)
auth.add_permission(group_id, 'read', db, 0)
auth.add_permission(group_id, 'delete', db, 0)
auth.add_permission(group_id, 'update', db, 0)
auth.add_permission(group_id, 'select', db, 0)
auth.add_membership(group_id, 7)

should be

if db(db.auth_group.role=='manager').isempty():

    group_id=auth.add_group('manager', 'can access the manage action')
    auth.add_permission(group_id, 'access to manage')
    auth.add_permission(group_id, 'create', db, 0)
    auth.add_permission(group_id, 'read', db, 0)
    auth.add_permission(group_id, 'delete', db, 0)
    auth.add_permission(group_id, 'update', db, 0)
    auth.add_permission(group_id, 'select', db, 0)
    auth.add_membership(group_id, 7)

else you keep creating a new group and many memberships at every request.

[Anthony]

Though that shouldn't be adding 2-3Mb per request.

[Massimo Di Pierro]

No it shouldn't.