By using the suggested queries, I was able to move 120,000 records and optimize
the table in less than 1 minute (20ish seconds for the move and delete)
Pretty much replace the code with this:
<snip>
$move_log = "
INSERT INTO `pf`.`iplog_archive`
SELECT * FROM `pf`.`iplog_history`
WHERE start_time < (NOW() - INTERVAL 8 DAY)
";
run_query($move_log, $pfConn);
############################
$delete_log = "
DELETE FROM `pf`.`iplog_history`
WHERE
start_time <=
(SELECT start_time FROM `pf`.`iplog_archive` ORDER BY start_time DESC
LIMIT 1)
";
run_query($delete_log, $pfConn);
############################
$optimize = "OPTIMIZE TABLE iplog_history";
run_query($optimize, $pfConn);
</snip>
--
Gregory A. Thomas
Student Life Support Specialist
University of Wisconsin-Parkside
[email protected]
262.595.2432
-----Original Message-----
From: Thomas, Gregory A [mailto:[email protected]]
Sent: Thursday, February 25, 2016 8:53 AM
To: [email protected]
Subject: Re: [PacketFence-users] Admin GUI - Node details slow.
I am pretty sure the answer to the question is yes and no.
Since the script is looking at iplog_history there should be a start and end
time. The only way a record is supposed to be in iplog_history is if a new
entry for the IP is added to iplog, as iplog has the column ip as the primary
key. As I have DHCP setup for 4 hour renewals, and I have access to the
iplog_archives table, I can easily do some extra digging if I had to.
Also, at the request of Bebbet, I have tried his queries and they are so much
faster that I am going to run a couple of test before placing them into
production and sharing them again.
--
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: Thursday, February 25, 2016 3:15 AM
To: [email protected]
Subject: Re: [PacketFence-users] Admin GUI - Node details slow.
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:[email protected]]
Sent: 23 February 2016 12:40
To: [email protected]
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
>> [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
>
------------------------------------------------------------------------------
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
