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 >> [email protected] >> 262.595.2432 >> >> -----Original Message----- >> From: Bebbet van Dinges [mailto:[email protected]] >> Sent: Tuesday, February 16, 2016 6:08 AM >> To: [email protected] >> 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 [email protected] 262.595.2432 >> >> >>> -----Original Message----- From: Morris, Andi >>> [mailto:[email protected]] Sent: Tuesday, January 12, 2016 >>> 7:01 AM To: [email protected] 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:[email protected]] Sent: 11 January 2016 23:54 To: >>> [email protected] 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 [[email protected]] >> >>> Sent: Monday, January 11, 2016 12:29 PM >> >>> To: [email protected] >> >>> Subject: Re: [PacketFence-users] Admin GUI - Node details slow. >> >> >> >> >> >> >> >> >> >> >> >>> On Jan 11, 2016, at 9:28 , Sallee, Jake <[email protected]> >>> 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 [email protected] :: 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 [email protected] >>> 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 [email protected] >>> 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 [email protected] >>> 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 >> [email protected] >> 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 >> [email protected] >> 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 > [email protected] > https://lists.sourceforge.net/lists/listinfo/packetfence-users >
signature.asc
Description: OpenPGP digital signature
------------------------------------------------------------------------------ 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 [email protected] https://lists.sourceforge.net/lists/listinfo/packetfence-users
