Anonymous | Login | 2024-11-22 23:04 EST |
Main | My View | View Issues | Change Log | Roadmap |
View Issue Details [ Jump to Notes ] | [ Issue History ] [ Print ] | |||||||
ID | Project | Category | View Status | Date Submitted | Last Update | |||
0001534 | PacketFence | performance | public | 2012-08-30 16:35 | 2012-09-10 15:12 | |||
Reporter | obilodeau | |||||||
Assigned To | fgaudreault | |||||||
Priority | low | Severity | minor | Reproducibility | random | |||
Status | resolved | Resolution | fixed | |||||
Platform | OS | OS Version | ||||||
Product Version | ||||||||
Target Version | Fixed in Version | |||||||
Summary | 0001534: RADIUS Accounting slow queries are not optimized | |||||||
Description | Sample 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. | |||||||
Tags | No tags attached. | |||||||
fixed in git revision | c49873873d5a057dd71484fdf191f99e3e34de65 | |||||||
fixed in mtn revision | ||||||||
Attached Files | ||||||||
Notes | |
(0003038) fgaudreault (viewer) 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 (viewer) 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 (viewer) 2012-09-10 15:12 |
Fixed the problematic query. Time stripped down from 25-30sec to below 1sec. |
Issue History | |||
Date Modified | Username | Field | Change |
2012-08-30 16:35 | obilodeau | New Issue | |
2012-09-10 14:46 | fgaudreault | Note Added: 0003038 | |
2012-09-10 14:53 | fgaudreault | Note Edited: 0003038 | |
2012-09-10 14:54 | fgaudreault | Note Added: 0003039 | |
2012-09-10 15:12 | fgaudreault | git revision | => c49873873d5a057dd71484fdf191f99e3e34de65 |
2012-09-10 15:12 | fgaudreault | Note Added: 0003040 | |
2012-09-10 15:12 | fgaudreault | Status | new => resolved |
2012-09-10 15:12 | fgaudreault | Resolution | open => fixed |
2012-09-10 15:12 | fgaudreault | Assigned To | => fgaudreault |
Copyright © 2000 - 2012 MantisBT Group |