[Spce-user] rate-o-mat

[Rene Krenn]

>mysql wait timeout which is 60 sec.
the ngcp mariadb wait timeout is not 60 secs.

>SELECT * FROM accounting.cdr WHERE rating_status = 'unrated' ORDER BY
start_time ASC LIMIT 100;
>This query run by rate-o-mat takes too long.
this query is known to be fast even with hundred million cdr records.
make sure all indexes are in place.

>As workaround i removed "ORDER BY start_time ASC" from the query (on
rate-o-mat source) and everything works fine and fast.
you break the behaviour with this, as you can verify with the
test suite.

its difficult to guess which of your changes (key buffer sizes, innodb
settings) are causing this, but i agree its likely that db performance
issues can also make your rate-o-mat to struggle, especially if there is
a large number of unrated cdrs pending. be sure it's proven the rating
system works reliable in really large setups.

regards rene

On Wed, 2016-11-02 at 20:25 +0100, Medve István wrote:
> Hi,
> 
> 
> i made further investigation and noticed that the messages are real.
> 
> 
> 
> # Time: 161102  1:14:11
> # User at Host: rateomat[rateomat] @ localhost [] 
> # Query_time: 68.930217  Lock_time: 0.000057 Rows_sent: 100
>  Rows_examined: 1346093 
> SET timestamp=1478045651; 
> SELECT * FROM accounting.cdr WHERE rating_status = 'unrated' ORDER BY
> start_time ASC LIMIT 100;
> 
> 
> This query run by rate-o-mat takes too long, more than the mysql wait
> timeout which is 60 sec.
> I had to realize that this query caused very high disk utilization
> (80-90% peak time busy  i can see on munin graph)
> 
> 
> As workaround i removed "ORDER BY start_time ASC" from the query (on
> rate-o-mat source) and everything works fine and fast.
> 
> 
> Regards,
> imedve
> 
> 
> 
> 
> 
> 
> On Tue, 2016-11-01 at 23:14 +0100, Rene Krenn wrote:
> > Hi,
> > 
> >  
> > 
> > actually these messages indicate caught exceptions before reopening
> > connections,
> > 
> > which can be a regular case if the system is almost idle (hardly any
> > calls). They should
> > 
> > be suppressed in a future patch.
> > 
> >  
> > 
> > Just to make sure, do you use dedicated billing fees at all and how
> > frequently do you see
> > 
> > it? To gain more infos, the rate-o-mat log level can be increased by
> > enabling the debug
> > 
> > flag.
> > 
> >  
> > 
> > regards, rene
> > 
> >  
> > 
> > Von: Spce-user [mailto:spce-user-bounces at lists.sipwise.com] Im
> > Auftrag von Medve István
> > Gesendet: Dienstag, 1. November 2016 22:59
> > An: spce-user at lists.sipwise.com
> > Betreff: [Spce-user] rate-o-mat
> > 
> > 
> >  
> > 
> > Hi All,
> > 
> > 
> >  
> > 
> > 
> > i can see the following messages periodically:
> > 
> > 
> >  
> > 
> > 
> > rate-o-mat: DBD::mysql::db do failed: MySQL server has gone away
> > at /usr/sbin/rate-o-mat line 291. 
> > rate-o-mat: rollback ineffective with AutoCommit enabled
> > at /usr/sbin/rate-o-mat line 312. 
> > rate-o-mat: rollback ineffective with AutoCommit enabled
> > at /usr/sbin/rate-o-mat line 312. 
> > rate-o-mat: rollback ineffective with AutoCommit enabled
> > at /usr/sbin/rate-o-mat line 312.
> > 
> > 
> >  
> > 
> > 
> > Everything seems to work fine, no mysql issues. I have no ideal why
> > these messages appear.
> > 
> > 
> >  
> > 
> > 
> > I use CE mr4.4.1
> > 
> > 
> >  
> > 
> > 
> > Could someone give me any hint ?
> > 
> > 
> >  
> > 
> > 
> > Regards,
> > 
> > 
> > imedve
> > 
> > 
> >  
> > 
> >