Interesting point, i didn't think of that yet. The query is based on the already existing script, but with a little more checking (not null end time, for both the insert and delete) it will be possible. I'll check into that later.
For now radius<=>eDirectory is a ***.. Bebbet On 25-2-2016 10:14, Morris, Andi wrote: > This looks good, thanks! > > However, wouldn't you want to remove the logs by end date, rather than start > date? Presuming some could still be open after 7 days. > > Cheers, > Andi > > -----Original Message----- > From: Bebbet van Dinges [mailto:beb...@bebbet.nl] > Sent: 23 February 2016 12:40 > To: packetfence-users@lists.sourceforge.net > Subject: Re: [PacketFence-users] Admin GUI - Node details slow. > > I've taken a look into it, and while it does what it is supposed to do. > Indeed it was quite cumbersome to run it like this in PHP. > > After a deep dive into some MYSQL-FU this is what i came up with: > > INSERT INTO `pf`.`iplog_archive` > SELECT * FROM `pf`.`iplog_history` > WHERE start_time < (NOW() - INTERVAL 7 DAY) > > Insert old rows into iplog_archive, based on the SELECT query. For the > deletion: > > DELETE FROM `pf`.`iplog_history` > WHERE start_time <= > (SELECT start_time FROM `pf`.`iplog_archive` ORDER BY start_time DESC LIMIT > 1) > > Delete from iplog_history, based on start time (the oldest.. well newest > entry) in iplog_archive, inline query > > Can you test this aswell? My (still small) database does both in about > 0,01 second, for 5000+ rows > > > > On 17-2-2016 10:27, Bebbet van Dinges wrote: >> 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 >> > > > > ------------------------------------------------------------------------------ > 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
