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
_______________________________________________
Devel mailing list
Devel@openser.org
http://openser.org/cgi-bin/mailman/listinfo/devel
