[SR-Users] db_cluster together with the registrar module = signal 11

Daniel-Constantin Mierla miconda at gmail.com
Thu Aug 30 10:09:53 CEST 2012


Hello,

On 8/29/12 9:15 PM, Øyvind Kolbu wrote:
> On 2012-08-29 at 20:16, Daniel-Constantin Mierla wrote:
>> is usrloc module configured in db only mode?
> Yes, db_mode is 3.

this might not be very suitable for db_cluster usage if one db server is 
done, because right now the reading is done to the first available server.

>
>> Read operation cannot be done in 'parallel' fashion of the cluster, it
>> is only serial or round robin, stopping at first successful query (even
>> when there is no data records in the result of the query).
> Not related to this thread, but would it be possible to force it to try
> until data is found?

No, you have to do some C coding. The target was to offer high 
availability to access a database server, when one is down, try next.

Perhaps you can add some cross replication at database server layer.

>
>> If usrloc keeps also the records in memory, there is no select done at
>> runtime, only updates, which are write operations.
>>
>> db_check_update is only for SQL UPDATE command, in MySQL you can see the
>> number of affected rows and if it is 0, then the module does an insert
>> (another write operation).
> I use Postgres, which as also returns the number of affected rows. Though
> perhaps Kamailio doesn't support it for Postgres.

I am not a postgres user, but if it exports it, then it is used 
automatically by usrloc module.

>
>  From my previous post:
>
> DEBUG: db_postgres [km_dbase.c:224]: sending query ok: 0xb7c9df08 (7) - [update [...]]
> DEBUG: <core> [db_res.c:118]: allocate 28 bytes for result set at 0xb7cd2d90
> DEBUG: db_postgres [km_dbase.c:494]: 0xb7c9df08 PQresultStatus(PGRES_COMMAND_OK) PQgetResult(0x9058e78)
> DEBUG: db_postgres [km_dbase.c:393]: PQclear(0x9058e78) result set
> DEBUG: <core> [db_res.c:81]: freeing 0 columns
> DEBUG: <core> [db_res.c:136]: freeing result set at 0xb7cd2d90
>
> but it did succed and updated one row, though not reflected in the logs.
>
>> So, the question is why do you think the check is done on one 'read
>> table', or you don't refer to the check of affected rows for UPDATE?
> Hm, perhaps I was a bit confused on how the affected rows works. Anyhow
> if a row is missing on a secondary server the UPDATE will fail and it
> will never be fixed with an INSERT.

IIRC, if the operation was successful, affected rows checks the last 
used connection. In a cluster, the operation is considered to be 
successful when int succeeded on a db server node.

I think you are looking more for replication, rather that high 
availability of the db layer. This is not done by db_cluster, if the 
nodes are up, then all should be fine, but if one goes down and then 
back up, its content is not synchronized, should be done by the db 
server itself.

Cheers,
Daniel

>
> Similar problem if the inital SELECT from the read-only sql-server returns
> noting, it will perform an INSERT on each of the write-servers, without
> first attempting to UPDATE. This will create duplicate contacts if the contact
> already exists on the write-servers. The stale entry will of course be
> pruned after a while..
>
> Below is from another experiment where I deleted the row in the location-table
> on the primary server, but left it on the secondary server:
>
> DEBUG: auth [api.c:218]: check_response: Authorization is OK
> [...]
> DEBUG: db_cluster [dbcl_api.c:436]: use table (voip_location) - cluster [data2]
> DEBUG: db_cluster [dbcl_api.c:455]: set write table (voip_location) - cluster [data2] (9/0)
> DEBUG: db_cluster [dbcl_api.c:455]: set write table (voip_location) - cluster [data2] (9/1)
> DEBUG: db_cluster [dbcl_api.c:445]: set read table (voip_location) - cluster [data2] (8/0)
> DEBUG: db_cluster [dbcl_api.c:445]: set read table (voip_location) - cluster [data2] (7/0)
> DEBUG: db_cluster [dbcl_api.c:299]: executing db cluster query command
> DEBUG: db_cluster [dbcl_api.c:300]: serial operation - cluster [data2] (8/0)
> DEBUG: db_postgres [km_val.c:163]: PQescapeStringConn: in: 7 chars, out: 7 chars
> DEBUG: db_postgres [km_val.c:163]: PQescapeStringConn: in: 20 chars, out: 20 chars
> DEBUG: db_postgres [km_dbase.c:224]: sending query ok: 0xb7d03fd0 (7) - [select [... long statement ...]]
> [...]
> DEBUG: db_postgres [km_res.c:211]: no rows returned from the query
> DEBUG: db_postgres [km_dbase.c:393]: PQclear(0x933fe78) result set
> DEBUG: usrloc [udomain.c:599]: aor 2549619 at hometest.voip.uio.no not found in table voip_location
> DEBUG: db_cluster [dbcl_api.c:284]: executing db cluster free-result command
> [...]
> DEBUG: <core> [sruid.c:176]: new sruid is [uloc-503e5fe0-26a8-4] (4 / 20)
> DEBUG: db_cluster [dbcl_api.c:436]: use table (voip_location) - cluster [data2]
> DEBUG: db_cluster [dbcl_api.c:455]: set write table (voip_location) - cluster [data2] (9/0)
> DEBUG: db_cluster [dbcl_api.c:455]: set write table (voip_location) - cluster [data2] (9/1)
> DEBUG: db_cluster [dbcl_api.c:445]: set read table (voip_location) - cluster [data2] (8/0)
> DEBUG: db_cluster [dbcl_api.c:445]: set read table (voip_location) - cluster [data2] (7/0)
> DEBUG: db_cluster [dbcl_api.c:333]: executing db cluster insert command
> DEBUG: db_cluster [dbcl_api.c:334]: parallel operation - cluster [data2] (9/0)
> DEBUG: db_postgres [km_val.c:163]: PQescapeStringConn: in: 7 chars, out: 7 chars
> DEBUG: db_postgres [km_val.c:163]: PQescapeStringConn: in: 26 chars, out: 26 chars
> DEBUG: db_postgres [km_val.c:163]: PQescapeStringConn: in: 41 chars, out: 41 chars
> DEBUG: db_postgres [km_val.c:163]: PQescapeStringConn: in: 42 chars, out: 42 chars
> DEBUG: db_postgres [km_val.c:163]: PQescapeStringConn: in: 24 chars, out: 24 chars
> DEBUG: db_postgres [km_val.c:163]: PQescapeStringConn: in: 20 chars, out: 20 chars
> DEBUG: db_postgres [km_val.c:163]: PQescapeStringConn: in: 20 chars, out: 20 chars
> DEBUG: db_postgres [km_dbase.c:224]: sending query ok: 0xb7d03f08 (7) - [insert into voip_location [... long statment ..]]
> DEBUG: <core> [db_res.c:118]: allocate 28 bytes for result set at 0xb7d076b8
> DEBUG: db_postgres [km_dbase.c:494]: 0xb7d03f08 PQresultStatus(PGRES_COMMAND_OK) PQgetResult(0x933fe78)
> DEBUG: db_postgres [km_dbase.c:393]: PQclear(0x933fe78) result set
> DEBUG: <core> [db_res.c:81]: freeing 0 columns
> DEBUG: <core> [db_res.c:136]: freeing result set at 0xb7d076b8
> DEBUG: db_cluster [dbcl_api.c:334]: parallel operation - cluster [data2] (9/1)
> DEBUG: db_postgres [km_val.c:163]: PQescapeStringConn: in: 7 chars, out: 7 chars
> DEBUG: db_postgres [km_val.c:163]: PQescapeStringConn: in: 26 chars, out: 26 chars
> DEBUG: db_postgres [km_val.c:163]: PQescapeStringConn: in: 41 chars, out: 41 chars
> DEBUG: db_postgres [km_val.c:163]: PQescapeStringConn: in: 42 chars, out: 42 chars
> DEBUG: db_postgres [km_val.c:163]: PQescapeStringConn: in: 24 chars, out: 24 chars
> DEBUG: db_postgres [km_val.c:163]: PQescapeStringConn: in: 20 chars, out: 20 chars
> DEBUG: db_postgres [km_val.c:163]: PQescapeStringConn: in: 20 chars, out: 20 chars
> DEBUG: db_postgres [km_dbase.c:224]: sending query ok: 0xb7d03fd0 (7) - [insert into voip_location [ ... another blind insert ]]
> DEBUG: <core> [db_res.c:118]: allocate 28 bytes for result set at 0xb7d076b8
> DEBUG: db_postgres [km_dbase.c:494]: 0xb7d03fd0 PQresultStatus(PGRES_COMMAND_OK) PQgetResult(0x933fe78)
> DEBUG: db_postgres [km_dbase.c:393]: PQclear(0x933fe78) result set
> DEBUG: <core> [db_res.c:81]: freeing 0 columns
> DEBUG: <core> [db_res.c:136]: freeing result set at 0xb7d076b8
>

-- 
Daniel-Constantin Mierla - http://www.asipto.com
http://twitter.com/#!/miconda - http://www.linkedin.com/in/miconda
Kamailio Advanced Training, Berlin, Nov 5-8, 2012 - http://asipto.com/u/kat




More information about the sr-users mailing list