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. -----BEGIN PGP SIGNED MESSAGE----- Hash: SHA256 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 > -----BEGIN PGP SIGNATURE----- Version: GnuPG v2 iQIcBAEBCAAGBQJWwxEZAAoJEK4DrpKfQ+uowDYP/R/nI+Pd2+lBmBzQ52P+mB6s 4RHHeWgK3ZGhd6t/10P0YBMbONmt1Y6eiWbmii/Mxi0pkzYb4ztgkjuwVifmrh4i 0M9kKzpPtxS4Q3pB7RSqYzSDF3Q4CiInCmCpLPTuKvkJU+0Y0dlFH+LcCzByT0mD szrNWCr9nCq/FGtTbIDTU5+G3g6MrzyyT8L+FZyIwADCJqLt8d2mxiELpmxzB1dL 4co7puspKBwWXdixDxB94tY/+MM9NNoa4Hn/2VRllFQaku8Q1pUFJ5eGgITACR7F CDqMaa5XjXUyNuH/WbHx+X0KWRlHRZfXJjn2uCZ6eoW2I5BIVVKG/2spPtXIezKE qfO9K8PM942uuSg5AM5yIlLnhfaozqGT6Yqk6Nchm1wTg63PLqjfytNUmSBAo/lw cXiw3EAAIHMU3vNDq3bkVc+VeeBc/c6jRaXlx4ECd+S/ub8azz4JFkbyR6JJXjmx YgbiPO8wQgf8sldc++ejIlE0CRVS9mOPm9PqVVf3nXbbA+NSzDWIHVQPG3Fu6s5A mq8PvYsF/jSzmKL9wX0AC1m4yJW77ZMOeXOeW4/ITGvh7AWSZdOnVYT4OWUGo8HQ LWacKw5Tz+dnv97gTKxZizQezhnt3kh3YCnFm4aCmbOVbvUhMIu4RWl1E4oh5oVf CnDN5thNADiSkwx8fXxz =TxHw -----END PGP 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
<<attachment: archive.php>>
------------------------------------------------------------------------------ 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
