From b4a681684d2fee800add540ede399bf45915fe49 Mon Sep 17 00:00:00 2001 From: "Michael L. Young" Date: Tue, 22 Jul 2014 18:56:00 +0000 Subject: core/db: Improve I/O When Updating Rows 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. This patch changes this statement to an INSERT INTO and if the insert fails because a row with that key exists, we will IGNORE the failure. Then we will attempt to perform an UPDATE on the existing row if that row wasn't just INSERTed. ASTERISK-24050 #close Reported by: Michael L. Young patches: astdb-insert-update-io-help_trunk_v2.diff uploaded by Michael L. Young (license 5026) Review: https://reviewboard.asterisk.org/r/3815/ git-svn-id: https://origsvn.digium.com/svn/asterisk/trunk@419222 65c4cc65-6c06-0410-ace0-fbb531ad65f3 --- include/asterisk/astdb.h | 9 ++++++++- main/db.c | 2 +- 2 files changed, 9 insertions(+), 2 deletions(-) diff --git a/include/asterisk/astdb.h b/include/asterisk/astdb.h index 8a870ae83..1eff7908c 100644 --- a/include/asterisk/astdb.h +++ b/include/asterisk/astdb.h @@ -50,7 +50,14 @@ int ast_db_get(const char *family, const char *key, char *value, int valuelen); */ int ast_db_get_allocated(const char *family, const char *key, char **out); -/*! \brief Store value addressed by family/key */ +/*! \brief Store value addressed by family/key + * + * Try to insert a row first. If a row already exists with the given key + * we will then perform an update on this row. + * + * The attempt here is to increase disk I/O performance by not deleting an existing + * row and then inserting a new row when one already exists. + */ int ast_db_put(const char *family, const char *key, const char *value); /*! \brief Delete entry in astdb */ diff --git a/main/db.c b/main/db.c index 5a0f17434..80af29f75 100644 --- a/main/db.c +++ b/main/db.c @@ -119,7 +119,7 @@ static void db_sync(void); #define DEFINE_SQL_STATEMENT(stmt,sql) static sqlite3_stmt *stmt; \ const char stmt##_sql[] = sql; -DEFINE_SQL_STATEMENT(put_stmt, "INSERT OR REPLACE INTO astdb (key, value) VALUES (?, ?)") +DEFINE_SQL_STATEMENT(put_stmt, "INSERT OR IGNORE INTO astdb (key, value) VALUES (?1, ?2); UPDATE astdb SET value=?2 WHERE changes()=0 AND key=?1") DEFINE_SQL_STATEMENT(get_stmt, "SELECT value FROM astdb WHERE key=?") DEFINE_SQL_STATEMENT(del_stmt, "DELETE FROM astdb WHERE key=?") DEFINE_SQL_STATEMENT(deltree_stmt, "DELETE FROM astdb WHERE key || '/' LIKE ? || '/' || '%'") -- cgit v1.2.3