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

[Victor Gamov]

Henning Westerholt wrote:
> 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.

Let's explain my point of view.

Now we have two functions making the same job by some different way:
-- db_mysql_store_result() retrieves a complete result set and call many
functions from res.c to process it
-- db_mysql_fetsh_result() returns a partial result set and process it
internally. It use nrows param to return nrows lines from this result set.

Both of them use mysql_store_result() to get result set from DB-server
to client so there is no problems on server-side (locking or some other)
as I understand.

For db_mysql_fetch result() when nrows==0 function do nothing and return
0.  But in this situation if user makes SELECT-like query and then call
db_mysql_fetch_rows() with nrows=0 no mysql_store_result() called and it
wrong because "you _must_ call mysql_store_result() or
mysql_use_result() for every statement that successfully retrieves data"
(http://dev.mysql.com/doc/refman/5.1/en/mysql-store-result.html)

And it's really strange don't get any data and call
db_mysql_fetch_rows() with nrows=0 after SELECT-like query

Also there are no problems or "any notable performance degradation if
you call mysql_store_result()  in all cases".

So we can use nrows to realize different behavior for db_mysql_fetch_rows():

-- nrows == 0 -- get complete result set. If called after non SELECT-like
query it will call mysql_store_result() and mysql_field_count() and return 0

-- nrows > 0 -- get result set and return nrows lines only

-- if we want to fetch zero rows then we can use nrows < 0 (now looks like
wrong parameter value), call mysql_store_result() and
mysql_free_result() and return 0.

IMHO

If I'm wrong in some basics please correct me. Thanks!

-- 
CU,
Victor Gamov