[Spce-user] rate-o-mat

[Rene Krenn]

the index `rstatus_stime_idx` (`rating_status`,`start_time`) for the
accounting.cdr table is not in place yet in the version you are using.
it particularly addresses launching rate-o-mat in the abnormal case of
having a massive number of pending cdrs. so you can either add it or
upgrade to 4.5.

regards, rene

On Thu, 2016-11-03 at 01:56 +0100, Rene Krenn wrote:
> >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
> > > 
> > > 
> > >  
> > > 
> > > 
>