PacketFence - BTS - PacketFence
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0001534PacketFenceperformancepublic2012-08-30 16:352012-09-10 15:12
Reporterobilodeau 
Assigned Tofgaudreault 
PrioritylowSeverityminorReproducibilityrandom
StatusresolvedResolutionfixed 
PlatformOSOS Version
Product Version 
Target VersionFixed in Version 
fixed in git revisionc49873873d5a057dd71484fdf191f99e3e34de65
fixed in mtn revision
Summary0001534: RADIUS Accounting slow queries are not optimized
DescriptionSample from the slow query log:

# Query_time: 27.180624  Lock_time: 0.000208 Rows_sent: 1  Rows_examined: 3064889
SET timestamp=1346357116;
SELECT CONCAT(SUBSTRING(callingstationid,1,2),':',SUBSTRING(callingstationid,3,2),':',SUBSTRING(callingstationid,5,2),':',
               
SUBSTRING(callingstationid,7,2),':',SUBSTRING(callingstationid,9,2),':',SUBSTRING(callingstationid,11,2)) 
AS mac,
               username,IF(ISNULL(acctstoptime),'connected','not connected') AS status,acctstarttime,acctstoptime,FORMAT(acctsessiontime/60,2) 
AS acctsessiontime,
               nasipaddress,nasportid,nasporttype,acctinputoctets AS acctoutput,
               acctoutputoctets AS acctinput,(acctinputoctets+acctoutputoctets) AS accttotal,
               IF(ISNULL(acctstoptime),'',acctterminatecause) AS acctterminatecause
        FROM (SELECT * FROM radacct ORDER BY acctstarttime DESC) AS tmp
        GROUP BY callingstationid
        HAVING callingstationid = 'E4D53D39968D';


Examined 3064889 and took 27 seconds. I wonder if indexing could help.
Steps To Reproduce
Additional Information
TagsNo tags attached.
Relationships
Attached Files
Issue History
Date ModifiedUsernameFieldChange
2012-08-30 16:35obilodeauNew Issue
2012-09-10 14:46fgaudreaultNote Added: 0003038
2012-09-10 14:53fgaudreaultNote Edited: 0003038
2012-09-10 14:54fgaudreaultNote Added: 0003039
2012-09-10 15:12fgaudreaultgit revision => c49873873d5a057dd71484fdf191f99e3e34de65
2012-09-10 15:12fgaudreaultNote Added: 0003040
2012-09-10 15:12fgaudreaultStatusnew => resolved
2012-09-10 15:12fgaudreaultResolutionopen => fixed
2012-09-10 15:12fgaudreaultAssigned To => fgaudreault

Notes
(0003038)
fgaudreault   
2012-09-10 14:46   
(edited on: 2012-09-10 14:53)
That part:
SELECT * FROM radacct ORDER BY acctstarttime DESC

I am pretty sure it's the one that caused issues. Ordering 3 millions rows is not fun.
(0003039)
fgaudreault   
2012-09-10 14:54   
Gne...

Try with this instead:
SELECT CONCAT(SUBSTRING(callingstationid,1,2),':',SUBSTRING(callingstationid,3,2),':',SUBSTRING(callingstationid,5,2),':',SUBSTRING(callingstationid,7,2),':',SUBSTRING(callingstationid,9,2),':',SUBSTRING(callingstationid,11,2)) AS mac,username,IF(ISNULL(acctstoptime),'connected','not connected') AS status,acctstarttime ,acctstoptime,FORMAT(acctsessiontime/60,2) AS acctsessiontime, nasipaddress,nasportid,nasporttype,acctinputoctets AS acctoutput, acctoutputoctets AS acctinput,(acctinputoctets+acctoutputoctets) AS accttotal, IF(ISNULL(acctstoptime),'',acctterminatecause) AS acctterminatecause FROM (SELECT * FROM radacct WHERE callingstationid = 'E4D53D39968D' ORDER BY acctstarttime DESC) AS tmp GROUP BY callingstationid HAVING callingstationid = 'E4D53D39968D';
(0003040)
fgaudreault   
2012-09-10 15:12   
Fixed the problematic query. Time stripped down from 25-30sec to below 1sec.