| Summary: | ASTERISK-24050: [patch] Improve AstDb I/O When Updating Rows | ||
| Reporter: | Michael L. Young (elguero) | Labels: | |
| Date Opened: | 2014-07-16 17:35:18 | Date Closed: | 2014-07-24 16:05:24 |
| Priority: | Minor | Regression? | No |
| Status: | Closed/Complete | Components: | Core/AstDB |
| Versions: | Feature Tracker | Frequency of Occurrence | |
| Related Issues: | |||
| Environment: | Attachments: | ( 0) astdb-insert-update-io-help_trunk_v2.diff ( 1) fix_astdb_update_prob.diff | |
| 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. Some testing that was performed on an older server with older IDE storage and few peers: _before patch_ Re-Register | INSERT OR REPLACE INTO | Avg. 404 ms Register | INSERT OR REPLACE INTO | Avg. 442 ms _after patch_ Re-Register | INSERT OR IGNORE; UPDATE | Avg. 361.5 ms Register | INSERT OR IGNORE; UPDATE | Avg 419 ms | ||
| Comments: | By: Joshua C. Colp (jcolp) 2014-07-23 05:21:58.818-0500 This change has caused a regression. The test: https://bamboo.asterisk.org/bamboo/browse/AST-ATTSCD-C632TE-474/test/case/2457931 Is failing due to it. I traced it down to the specific commit, upon reverting it the test now passes fine. Further examination shows that when the registration is refreshed it's not actually updated, causing the removal to remove the wrong contact which the test catches. By: Michael L. Young (elguero) 2014-07-23 08:50:34.740-0500 Yep, I suck at sqlite3 it would seem. Looks like sqlite3_prepare() does not handle multiple SQL statements. It will only handle the first one if there are multiple ones present. What is frustrating is that I was pretty sure I tested that updates were happening and now when I tested today I see that this is not happening like Josh pointed out due to the failing test. I turned on tracing in sqlite3 and saw that the INSERT was the only statement being handled and not the UPDATE. I have a patch to fix this regression and I have tested it... I am very sure I have tested it... been testing and testing it.... This better work now :) By: Michael L. Young (elguero) 2014-07-23 11:51:58.943-0500 Ran the test that failed and it now passes with this patch. | ||