Hi Rich, On 04/11/2012 05:02 AM, Rich Graves wrote: > Are you kidding me? > > How can I optimize or eliminate this query every time a violation sends > email, or a verbose node lookup is done? It deadlocks for several > minutes at a time. > > 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 = '<mac address>';
The query is doing exactly what it should do. The only thing missing are the proper indexes. We confirmed the issue two weeks ago and the only reason it has not been implemented yet is that we are waiting on database changes from another feature branch to be merged in. See #1414: Improve performance of node accounting http://www.packetfence.org/bugs/view.php?id=1414 As said in the ticket, log into mysql cli as root with: $ mysql -u root -p pf then issue: > CREATE INDEX callingstationid ON radacct ( callingstationid ); and this will result in quantum leap in performance (seconds/minutes to milliseconds). If not, let us know. You might also be interested in the backup script changes that trim the old data. It's at: https://github.com/inverse-inc/packetfence/commit/3c9f6fd30ea95f2f6b49a5840f12d1ee9adb2bd5 Regards, -- Olivier Bilodeau [email protected] :: +1.514.447.4918 *115 :: www.inverse.ca Inverse inc. :: Leaders behind SOGo (www.sogo.nu) and PacketFence (www.packetfence.org) ------------------------------------------------------------------------------ Better than sec? Nothing is better than sec when it comes to monitoring Big Data applications. Try Boundary one-second resolution app monitoring today. Free. http://p.sf.net/sfu/Boundary-dev2dev _______________________________________________ Packetfence-users mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/packetfence-users
