db.commit() taking too long to update records

[Luciano Laporta Podazza]

Hello,

I'm having a strange issue, I have a website that queries data through an ajax call from the server using web2py json services every 5 seconds. Then from the client I can update some data to the server with this:

@service.json
def block_user():
    data = json.loads(request.body.read())
    user = db(db.users.id==data['id']).select().first()
    user.update_record(banned=True, banned_by=auth.user_id)
    alerts = db(db.alerts.alerts_id==data['id']).update(archived=True)
    db.commit()
    return "ok"

There's like 10 records in the database to be updated but the query takes like 15-30seconds to process. I have no idea why is happening this.

Any help will be appreciated. Thanks!

P.S.: I'm using web2py 2.9.8-stable+timestamp.2014.09.06.23.09.41

[Leonel Câmara]

My guess would be that you're using sqlite and the database is locking you. Which is easy to happen if you have a few people requesting updates every 5 seconds and you're trying to write on it, as the write will only go forward once sqlite gets its process an EXCLUSIVE lock which requires all readers which have SHARED locks to stop reading first.

One thing you can do is to cache the result returned in the first case so it doesn't hit the database. Then you can clear the cache when you make changes that would change the result. If this doesn't work then it's time to change databases.

[Luciano Laporta Podazza]

Sorry, I forgot to mention that I'm using MySQL as db engine.

Thanks.

On Tue, Sep 9, 2014 at 6:45 PM, Leonel Câmara <leonel...@gmail.com> wrote:

My guess would be that you're using sqlite and the database is locking you. Which is easy to happen if you have a few people requesting updates every 5 seconds and you're trying to write on it, as the write will only go forward once sqlite gets its process an EXCLUSIVE lock which requires all readers which have SHARED locks to stop reading first.

One thing you can do is to cache the result returned in the first case so it doesn't hit the database. Then you can clear the cache when you make changes that would change the result. If this doesn't work then it's time to change databases.

--
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/6z13PTcZ5io/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/d/optout.

--
Atte

Luciano Laporta Podazza

[Leonel Câmara]

Then I'm sorry but I have no idea why it's taking so long. You need to profile it.

It may just be a question of tuning MySQL, optimizing the tables, etc. Have you tried mysqltuner? 

[Luciano Laporta Podazza]

Thanks for your help Leonel. I didn't tried it but I'm talking about a dead simple db structure, it's just updating a few records( like 10 rows or so) on a single table.

On Tue, Sep 9, 2014 at 7:24 PM, Leonel Câmara <leonel...@gmail.com> wrote:

Then I'm sorry but I have no idea why it's taking so long. You need to profile it.

It may just be a question of tuning MySQL, optimizing the tables, etc. Have you tried mysqltuner? 

--

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/6z13PTcZ5io/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/d/optout.

[villas]

If this is in a controller,  do you need the line:  db.commit()  ??

[Luciano Laporta Podazza]

Perhaps I misunderstood this, but following the web2py manual it says that no recordset is updated until you do db.commit().

Is that right?.

On Tue, Sep 9, 2014 at 7:29 PM, villas <vill...@gmail.com> wrote:

If this is in a controller,  do you need the line:  db.commit()  ??

--
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/6z13PTcZ5io/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/d/optout.

[Leonel Câmara]

Well that's true, but web2py automatically calls db.commit for you after running the controller. That would not cause the slowdown anyway.

[Niphlod]

BTW, log somewhere db._timings before returning and try to replay the query in mysql to see what's going on.

[Luciano Laporta Podazza]

Hi Niphlod,

I did what you say and if I try doing the query through console it works perfectly and fast:

>>> db._timings

[('SET FOREIGN_KEY_CHECKS=1;', 0.0002541542053222656), ("SET sql_mode='NO_BACKSLASH_ESCAPES';", 0.0002338886260986328)]

>>> db((db.alerts.alerts_id==1)&(db.alerts.archived != True)).update(archived=True, crime="Robo", operator_id=1)

23

>>> db._timings

[('SET FOREIGN_KEY_CHECKS=1;', 0.0002541542053222656), ("SET sql_mode='NO_BACKSLASH_ESCAPES';", 0.0002338886260986328), ("UPDATE alerts SET archived='T',operator_id=1,crime='Robo' WHERE ((alerts.alerts_id = 1) AND (alerts.archived <> 'T'));", 0.010937213897705078)]

>>> db.commit()

>>> db._timings

[('SET FOREIGN_KEY_CHECKS=1;', 0.0002541542053222656), ("SET sql_mode='NO_BACKSLASH_ESCAPES';", 0.0002338886260986328), ("UPDATE alerts SET archived='T',operator_id=1,crime='Robo' WHERE ((alerts.alerts_id = 1) AND (alerts.archived <> 'T'));", 0.010937213897705078)]

But again, if I try sending the ajax request(a simple one, really), it gets stucked as I mentioned before, then after 20-30seconds the record gets updated.

I'm using MySQL 5.5 with less than 50 records on the affected table.

Any help will be appreciated. Thanks

[Luciano Laporta Podazza]

By the way, I've already tried the SQL query manually and it works perfectly. I'm using Web2py 2.9.11 and I'm starting to think that perhaps there's an issue with web2py services.

Any ideas?. Thanks

--

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/6z13PTcZ5io/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/d/optout.

[Anthony]

Instead of checking the timings in the console, check the timings during the actual Ajax request (maybe return the timings in some HTML and display it in the browser instead of just returning "OK"; or print the timings to the console).

Are there any other Ajax requests that get fired before this one that could be causing a delay? If you are using file based sessions, a prior Ajax request would block a new request while waiting for the session file to be unlocked (unless you call session.forget(response)).

Anthony

[Luciano Laporta Podazza]

Hi Anthony!,

Indeed, the ajax call was the issue, after reading this: http://web2py.com/books/default/chapter/29/04#session

I realised that storing sessions in database solved my problem(there's no more blocking calls issue).

Thanks a lot for your help!!!!

Cheers.

--

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/6z13PTcZ5io/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/d/optout.

[Anthony]

Keep in mind that the benefit of locking the session is that you avoid race conditions, so you now have to make sure there is no possibility of a race condition with the session if you have multiple calls happening asynchronously. The other option is to continue using file base sessions, but call session.forget(response) in any request that doesn't need the session.

Anthony

To unsubscribe from this group and all its topics, send an email to web2py+unsubscribe@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

[Leonel Câmara]

Doesn't the database take care of that? I mean isn't db session handling inside a transaction anyway?

[Anthony]

On Thursday, October 23, 2014 12:52:33 PM UTC-4, Leonel Câmara wrote:

Doesn't the database take care of that? I mean isn't db session handling inside a transaction anyway?

Yes, but that doesn't help across requests (e.g., request A reads session > request B reads session > request A updates session > request B overwrites request A's update). The session table in the db does include a "locked" field, but as far as I can tell, it is not actually used. Another option would be to do a select-for-update, which would result in the db locking the record until the transaction completes (though in the case of SQLite, I think the whole db gets locked).

Anthony

[Luciano Laporta Podazza]

Thanks for the headsup Anthony!, luckly it's not my case.

Cheers!

--

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/6z13PTcZ5io/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/d/optout.