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

[Dan Pascu]

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.

On Tuesday 17 July 2007, Toni Barata wrote:
> Hi Anca, Daniel,
>
> From the results of the load tests it seems the issue is directly
> related with the size of presentity table:
>
> N of presentities in Presentity table	N publish msgs -> mysqld:99,9%CPU
> 14000							11/s
> 55000							6/s
> 180000						3/s
>
> The result is the same for tests with force_active= 1 or with xcap
> authorization
>
> Best regards,
> Toni
>
> -----Original Message-----
> From: Anca-Maria Vamanu [mailto:anca at voice-system.ro]
> Sent: terça-feira, 17 de Julho de 2007 12:03
> To: Toni Barata
> Cc: devel at openser.org
> Subject: Re: [OpenSER-Devel] presence load tests: 1.2.1 vs trunk with
> mysql
>
> Hello Toni,
>
> Have you run the tests with force_active= 1 or with xcap authorization?
> (the generalization introduced some database query inefficiency when
> force_active is not set).
>
> regards,
>
> Anca Vamanu
>
> Toni Barata wrote:
> > Hi all,
> >
> >
> >
> > I am using MySQL 5.0.22 database for persistent storage and I have
> > preformed some presence load tests for publish messages, and observed
> > that in trunk version if I have more than 2 messages per second the
> > mysqld process runs out the CPU with 99,9% of his capacity.
> >
> > In the 1.2.1 version there is no such problem when performing the
> > same load tests.
> >
> >
> >
> > What difference between version 1.2.1 and trunk have made the mysqld
> > process became obsessive?
> >
> >
> >
> > Regards,
> >
> > Toni
> >
> >
> >
> >
> >
> >----------------------------------------------------------------------
> >--
> >
> >_______________________________________________
> >Devel mailing list
> >Devel at openser.org
> >http://openser.org/cgi-bin/mailman/listinfo/devel
>
> _______________________________________________
> Devel mailing list
> Devel at openser.org
> http://openser.org/cgi-bin/mailman/listinfo/devel



-- 
Dan