[OpenSER-Devel] FUNCTION ser.bitand does not exist

[Henning Westerholt]

On Tuesday 13 May 2008, Dan Pascu wrote:
> I have to agree with Juha here. While you may know that I'm not a fanatic
> about performance and most of the time I would favor more functionality
> or consistency and simplified code even if they incur a small performance
> penalty, you may also know that I always advocated that performance
> optimizations should be made where they really matter, not where they
> make little to no difference. And I think we can all agree that the
> database is one of the major bottlenecks in openser. Which means that
> making it even slower than it is, doesn't make for a good move.
>
> I do not have any benchmarks here, but I would expect that userspace
> functions to be slower. Especially if they are interpreted.

Hi Dan,

i don't think that the difference is that big, that was the reason that i've 
applied Iouries patch to usrloc in the first place. But i was not aware that 
the function gets executed every second.

> > why is the emulation function defined in mysql and not in oracle?  i
> > would prefer it to be defined in oracle, because so far majority of
> > openser users is using mysql.
>
> Unfortunately this is not that simple. The BIT_AND function you mentioned
> from mysql is a group (aggregate) function. You cannot use it to make a
> bitwise and between 2 values, only between values in different rows for
> the same column. It is used in conjunction with GROUP BY to make a
> bitwise and over the values of a column in multiple rows.
>
> MySQL however, has an operator for this, which is like in C: &
> But Oracle doesn't have the operator and it can't define one.
>
> However given that mysql and postgres are the most used databases, we
> shouldn't cripple their abilities just to support other less used
> databases. Maybe a different approach is needed here. Like have the
> database backends provide functions that would encode certain operations
> in the appropriate way for each database. Or do the bitwise operations in
> C instead of the database (if possible).

For postgres in the stable branch a similar approach is used. The emulation 
function (basically a stored procedure) defined for mysql is just a small 
wrapper around the '&' operator. Using a user defined procedure could be 
faster, but its not that easy to deploy them, as its necessary to implement 
them as object files [1]. I found a nice benchmark around the different 
function implementation, stored procedures are about 50% slower then using an 
internal operator (see 'adition benchmark', simplifing somewhat..) [2]. But 
even then its possible to execute over 50k of them every second.

The fastest solution would be to implement this bitand in C in the usrloc. But 
i think that the overhead we get for this case is managable.

Cheers,

Henning


[1] http://dev.mysql.com/doc/refman/5.0/en/adding-functions.html
[2] http://rpbouman.blogspot.com/2008/03/udfs-at-mysql-users-conference.html