Hello Gregory, Thank you very much for the script, if i have questions regarding it, i'll give a shout!
Bebbet On 16-2-2016 16:05, Thomas, Gregory A wrote: > The script is attached. > > I know it is not the prettiest or fastest. I was not looking for that, I > needed something now, so here it is. > > Let me know offline if you have any questions. > > -- > Gregory A. Thomas > Student Life Support Specialist > University of Wisconsin-Parkside > thom...@uwp.edu > 262.595.2432 > > -----Original Message----- > From: Bebbet van Dinges [mailto:beb...@bebbet.nl] > Sent: Tuesday, February 16, 2016 6:08 AM > To: packetfence-users@lists.sourceforge.net > Subject: Re: [PacketFence-users] Admin GUI - Node details slow. > > Hello Gregory, > > I'm quite interested in this script you wrote, can you mail it to the list, > or to me? > > Yours sincerely, > Bebbet > > On 12-1-2016 15:59, Thomas, Gregory A wrote: >> I had the same problem when I was running on a box with somewhat >> limited resources and it did appear that it was the iplog_history that >> was slowing it down. > >> I decided that I did not need the extended history in the GUI, but >> wanted to keep it somewhere. I created a script that takes any entries >> from the iplog_history over 8 days old and move them to a new table >> with the same structure named iplog_archive The task was a bit slow, >> so I added an index on iplog_history.start_time. The final line of the >> script is to optimize iplog_history. > >> This job runs every morning at 4AM. Since the putting this in place, >> the GUI interface when looking at users and their devices is sooooo >> much faster. I know I lose some ability to find instant history, but I >> still have it on the system if I really need to find it. To gain easy >> access to the DB, I have an older version of phpMyAdmin (4.2.2 because >> newer version are not compatible with the CentOS Source Distribution >> of MySQL) > >> -- Gregory A. Thomas Student Life Support Specialist University of >> Wisconsin-Parkside thom...@uwp.edu 262.595.2432 > > >> -----Original Message----- From: Morris, Andi >> [mailto:amor...@cardiffmet.ac.uk] Sent: Tuesday, January 12, 2016 >> 7:01 AM To: packetfence-users@lists.sourceforge.net Subject: Re: >> [PacketFence-users] Admin GUI - Node details slow. > >> Just to note, I also see this issue. If as a result of Jake's question >> there comes some routine maintenance that should be done on the >> database, please let me know. > >> I already run the built in maintenance script, but I notice that I >> have several fragmented tables in the sql tuner results, I'm not sure >> whether this is also having an adverse effect. > >> Cheers, Andi > >> -----Original Message----- From: Sallee, Jake >> [mailto:jake.sal...@umhb.edu] Sent: 11 January 2016 23:54 To: >> packetfence-users@lists.sourceforge.net Subject: Re: >> [PacketFence-users] Admin GUI - Node details slow. > >> My iplog_hostory table is sitting @ 87,910,019 records : ) > >> Slow query log shows this: ==================================== # >> Time: 160111 17:40:33 # User@Host: pf[pf] @ localhost [] # >> Query_time: 81.359632 Lock_time: 0.000075 Rows_sent: 25 >> Rows_examined: 4597 use pf; SET timestamp=1452555633; SELECT * FROM >> (SELECT *, UNIX_TIMESTAMP(start_time) AS start_timestamp, >> UNIX_TIMESTAMP(end_time) AS end_timestamp FROM iplog WHERE mac = >> '70:1a:04:ba:f4:a1' ORDER BY start_time DESC) AS a UNION ALL SELECT >> * FROM (SELECT *, UNIX_TIMESTAMP(start_time) AS start_timestamp, >> UNIX_TIMESTAMP(end_time) AS end_timestamp FROM iplog_history WHERE mac >> = '70:1a:04:ba:f4:a1' ORDER BY start_time DESC) AS b ORDER BY >> start_time DESC LIMIT 25; >> ================================================= > >> I think it may be time to truncate this table. > >> Jake Sallee Godfather of Bandwidth System Engineer University of Mary >> Hardin-Baylor WWW.UMHB.EDU > >> 900 College St. Belton, Texas 76513 Fone: 254-295-4658 Phax: >> 254-295-4221 > > >> From: Louis Munro [lmu...@inverse.ca] > >> Sent: Monday, January 11, 2016 12:29 PM > >> To: packetfence-users@lists.sourceforge.net > >> Subject: Re: [PacketFence-users] Admin GUI - Node details slow. > > > > > > > > > > > >> On Jan 11, 2016, at 9:28 , Sallee, Jake <jake.sal...@umhb.edu> >> wrote: > > >> At this moment my radacct table is sitting at 4,439,397 rows and my >> radacct_log table is 18,638,591. Could this be causing the issue? > > > > > > >> Quite possibly. I am saying this without checking but it seems like a >> likely culprit. > >> Can you turn on slow query logging on your database? That should tell >> us more. > > >> See http://dev.mysql.com/doc/refman/5.7/en/slow-query-log.html for >> some details about that. > >> Regards, -- Louis Munro lmu...@inverse.ca :: www.inverse.ca >> +1.514.447.4918 x125 :: +1 (866) 353-6153 x125 Inverse inc. :: >> Leaders behind SOGo (www.sogo.nu) and PacketFence >> (www.packetfence.org) > > > > > >> ---------------------------------------------------------------------- > -------- > > > Site24x7 APM Insight: Get Deep Visibility into Application Performance APM + > Mobile APM + RUM: Monitor 3 App instances at just $35/Month Monitor > end-to-end web transactions and take corrective actions now Troubleshoot > faster and improve end-user experience. Signup Now! >> http://pubads.g.doubleclick.net/gampad/clk?id=267308311&iu=/4140 >> _______________________________________________ PacketFence-users >> mailing list PacketFence-users@lists.sourceforge.net >> https://lists.sourceforge.net/lists/listinfo/packetfence-users >> ________________________________ > >> [Cardiff Metropolitan University - 150 years of nurturing >> talent]<http://www.cardiffmet.ac.uk/cardiffmet150> > >> ---------------------------------------------------------------------- > -------- > > > Site24x7 APM Insight: Get Deep Visibility into Application Performance APM + > Mobile APM + RUM: Monitor 3 App instances at just $35/Month Monitor > end-to-end web transactions and take corrective actions now Troubleshoot > faster and improve end-user experience. Signup Now! >> http://pubads.g.doubleclick.net/gampad/clk?id=267308311&iu=/4140 >> _______________________________________________ PacketFence-users >> mailing list PacketFence-users@lists.sourceforge.net >> https://lists.sourceforge.net/lists/listinfo/packetfence-users > >> ---------------------------------------------------------------------- > -------- > > > Site24x7 APM Insight: Get Deep Visibility into Application Performance >> APM + Mobile APM + RUM: Monitor 3 App instances at just $35/Month >> Monitor end-to-end web transactions and take corrective actions now >> Troubleshoot faster and improve end-user experience. Signup Now! >> http://pubads.g.doubleclick.net/gampad/clk?id=267308311&iu=/4140 >> _______________________________________________ PacketFence-users >> mailing list PacketFence-users@lists.sourceforge.net >> https://lists.sourceforge.net/lists/listinfo/packetfence-users > > > > ------------------------------------------------------------------------------ > Site24x7 APM Insight: Get Deep Visibility into Application Performance APM + > Mobile APM + RUM: Monitor 3 App instances at just $35/Month Monitor > end-to-end web transactions and take corrective actions now Troubleshoot > faster and improve end-user experience. Signup Now! > http://pubads.g.doubleclick.net/gampad/clk?id=272487151&iu=/4140 > _______________________________________________ > PacketFence-users mailing list > PacketFence-users@lists.sourceforge.net > https://lists.sourceforge.net/lists/listinfo/packetfence-users > > > > ------------------------------------------------------------------------------ > Site24x7 APM Insight: Get Deep Visibility into Application Performance > APM + Mobile APM + RUM: Monitor 3 App instances at just $35/Month > Monitor end-to-end web transactions and take corrective actions now > Troubleshoot faster and improve end-user experience. Signup Now! > http://pubads.g.doubleclick.net/gampad/clk?id=272487151&iu=/4140 > > > > _______________________________________________ > PacketFence-users mailing list > PacketFence-users@lists.sourceforge.net > https://lists.sourceforge.net/lists/listinfo/packetfence-users > ------------------------------------------------------------------------------ Site24x7 APM Insight: Get Deep Visibility into Application Performance APM + Mobile APM + RUM: Monitor 3 App instances at just $35/Month Monitor end-to-end web transactions and take corrective actions now Troubleshoot faster and improve end-user experience. Signup Now! http://pubads.g.doubleclick.net/gampad/clk?id=272487151&iu=/4140 _______________________________________________ PacketFence-users mailing list PacketFence-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/packetfence-users