[asterisk-dev] [Code Review] 3815: Improve AstDb I/O When Updating Rows

[Michael Young]

-----------------------------------------------------------
This is an automatically generated e-mail. To reply, visit:
https://reviewboard.asterisk.org/r/3815/
-----------------------------------------------------------

(Updated July 17, 2014, 1:57 p.m.)


Review request for Asterisk Developers.


Changes
-------

Fixed my failure to bind parameters properly. Not sure what was up with my original reading of the documentation for sqlite3 prepared statements from many months ago.  Hopefully I got it this time.  Obviously, the update was not functioning as planned and my numbers looked better with the patch because it was just ignoring inserts that had a conflict.

Tested this patch and the performance benefits are still there as expected.


Bugs: ASTERISK-24050
    https://issues.asterisk.org/jira/browse/ASTERISK-24050


Repository: Asterisk


Description
-------

When updating a row, we are currently doing an INSERT OR REPLACE INTO.  The downside to this is that the row is deleted if it exists and then a new row is inserted.  So, we are hitting the disk twice.  One for the deletion and one for the insertion.

The proposed patch will attempt to do an INSERT INTO and if it fails because a row with that key exists, we will ignore that.  Then we will attempt to perform an UPDATE on the existing row.  If a record was INSERTED, the UPDATE statement will end up doing nothing.


Diffs (updated)
-----

  /trunk/main/db.c 418608 
  /trunk/include/asterisk/astdb.h 418608 

Diff: https://reviewboard.asterisk.org/r/3815/diff/


Testing
-------

Local pbx with a couple of peers.  Did see a slight I/O increase.

before patch
Re-Register  Avg. 404 ms
Register     Avg. 442 ms

after patch
Re-Register  Avg. 361.5 ms
Register     Avg. 419 ms


Thanks,

Michael Young

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.digium.com/pipermail/asterisk-dev/attachments/20140717/5a85e75b/attachment.html>