[OpenSER-Devel] presence load tests: 1.2.1 vs trunk with mysql

[Henning Westerholt]

On Thursday 19 July 2007, Dan Pascu wrote:
> This may also be related to the recent change introduced by patch #1742425
> https://sourceforge.net/tracker/?func=detail&atid=743022&aid=1742425&group_
>id=139143
>
> That patch replaced db_mysql_store_result with db_mysql_fetch_result which
> does reduce the load on openser when doing database operations at the
> cost of increasing it on the mysql server.
>
> One negative side effect of that change is that if the whole result set is
> fetched row by row using mysql_fetch_row, it will take more time than
> when using mysql_store_result because there will be N database requests
> and the round trip time between openser and the mysql server will add up
> N times instead of just once.
>
> Another negative side effect is that if openser does a lot of processing
> on the fetched rows, during that time the result set is locked on the
> server taking up server resources. Also during this time, the result set
> being locked will prevent other mysql threads to update entries in that
> result set which may introduce delays in database operations.
>
> So depending on the type of queries performed by openser and the way they
> are processed after retrieval, the change introduced by that patch may
> improve database operations or may make them worse.
>
> Using fetch_result instead of store_result is beneficial if:
> 1. Only a few rows of a large result set are retrieved
> 2. Processing of the retrieved rows on the client side is minimal so it
> won't lock the result set on the server for long.
>
> I haven't looked in detail to the patch that did this change so some of
> the observations here may not apply (depending how it is implemented),
> but I think it is worth considering these points for finding the problem.

Hello Dan,

the patch you mentioned is not applied. It relied on the behaviour of 
fetch_result with a zero nrows parameter. I commited yesterday a additional 
check to fetch_rows. If you now want to fetch zero rows then the function 
returns immediately without a result, similar like this function in 
postgresql. This is more meaningful as the old approach, to return the whole 
result set. So this approach don't work anymore.

Because of the side effects you mentioned above i think we have even more 
reasons to stay with the old, known behavior. 

Cheers,

Henning