> The iplog_history table needs to be reworked. IMHO, we don't need a single > record for the same ip again and again and again for every renewal, the > renewal should extend the end time and keep it as one record. Iplog_history > is the single biggest performance problem in my pf setup.
I would have to disagree a bit on that and here’s why :) We intentionnally reworked iplog_history that way (insert single rows everytime a lease is being renewed since it is a trigger that does all the inserting job in that table). Why ? First, a lease is a lease, you have 3 leases of 30 minutes eaches, not one big of 1h30. iplog (including iplog, iplog_history, iplog_archive) is a representation of ip <-> mac association that happened on the network. Second, we wanted to keep as “simple” as possible, meaning, no logic of finding if a lease already exists in the table for the same ip <-> mac combination, for then, update the end_time. It is faster that way and breaks less indexes. We then introduced iplog_archive which is where “old stuff” (from iplog_history) is being stored after X amount of time / records. That is meant to be the archives of the client if needed for future use. PacketFence actively relies on the iplog table for almost all of it’s tasks. This is why we keep it light by having a unique key on the ip and each time a same IP is assigned, we move the “old” record to iplog_history and update iplog with the new one. PacketFence web admin (Nodes tab, User tab) relies on iplog_history table for the “X latest IPs”. We wanted to restrict that table to a specific amount of entries to avoid having a slow GUI by having a maintenance job which would make the iplog_history looks like a circular buffer by moving older entries to iplog_archives, but that maintenance was not “ready”. That’s why we have the database-backup-and-maintenance.sh Let me know if there’s any questions, concerns or anything. :) Thanks! Cheers! dw. — Derek Wuelfrath [email protected] :: +1.514.447.4918 (x110) :: +1.866.353.6153 (x110) Inverse inc. :: Leaders behind SOGo (www.sogo.nu) and PacketFence (www.packetfence.org) > On Oct 22, 2015, at 8:38 PM, Tim DeNike <[email protected]> wrote: > > I agree. The iplog_history table needs to be reworked. IMHO, we don't need > a single record for the same ip again and again and again for every renewal, > the renewal should extend the end time and keep it as one record. > Iplog_history is the single biggest performance problem in my pf setup. > > Sent from my iPhone > > On Oct 22, 2015, at 8:35 PM, Thomas, Gregory A <[email protected] > <mailto:[email protected]>> wrote: > >> I have found a temporary fix for this issue. The user’s device was not in >> the node table of the database and thus a correct error. However the device >> was given an address from the dhcp server but the database slow to update >> the node table with the information. >> >> In my case the iplog_history table was what was causing the problems. I have >> an instance of phpMyAdmin installed and when I tried to browse the table it >> was extremely slow to respond. I assumed this was causing problems with >> writing to the node table as it has to work on pulling info from the iplog >> and placing it into iplog_history. The history table was well over 3,000,000 >> records at 7 weeks of use and close to 300 MB all unindexed. >> >> I backed up iplog_history, truncated the table and rebooted the server. This >> time the load never got over 2 and has settled to .7 at peak time. >> >> I will be working on some process to do this type of truncation for me on a >> weekly basis to help keep the system running. >> >> -- >> Gregory A. Thomas >> Computer Professional >> University of Wisconsin-Parkside >> [email protected] >> <x-msg://15/owa/redir.aspx?C=PJoLX1MXo0SU0MLM7GrPmwxJzaMkdtAIgi4jkK-AXpCwJ307G0bt2lvFPw4WGoqQ06Tt1qwrKAA.&URL=mailto%3athomasg%40uwp.edu> >> 262.595.2432 >> >> From: Thomas, Gregory A [mailto:[email protected] <mailto:[email protected]>] >> Sent: Thursday, October 22, 2015 6:31 PM >> To: [email protected] >> <mailto:[email protected]> >> Subject: [PacketFence-users] Device not found in Database >> >> So, >> >> I am run 5.4 in complete Inline mode. >> CentOS 6.7 64bit >> 6 GB RAM with 6 Processors >> Running on an EXi server >> >> This afternoon, the server began a death spiral where the free RAM was >> getting to 500 MB free (yeah I know there is still a ton there) and the load >> was starting to creep from .8 to 7 and eventually at reboot time stuck at 20 >> + for 5 minutes straight. That is not the problem (right now). >> >> I rebooted the server with the managed NIC disabled, know that once enable >> the load would jump to handle all of the “new” requests for access. After >> about 5 minutes, the load fell to the evening average of 2-3. So I decide to >> see how the network is running. >> >> I fire up my phone, which is registered and I get the error: Your device in >> not in the Database, please reboot to solve this problem. Of course I don’t >> as I know better and renew the lease and everything else under the sun and >> eventually reboot all to no avail. After about 10 minutes and trying to calm >> residents down who are also getting this error, I get a connection and can >> register my phone. >> >> What causes this error and is there a way to somewhat eliminate it? >> >> -- >> Gregory A. Thomas >> Computer Professional >> University of Wisconsin-Parkside >> [email protected] >> <x-msg://15/owa/redir.aspx?C=PJoLX1MXo0SU0MLM7GrPmwxJzaMkdtAIgi4jkK-AXpCwJ307G0bt2lvFPw4WGoqQ06Tt1qwrKAA.&URL=mailto%3athomasg%40uwp.edu> >> 262.595.2432 >> >> ------------------------------------------------------------------------------ >> _______________________________________________ >> PacketFence-users mailing list >> [email protected] >> <mailto:[email protected]> >> https://lists.sourceforge.net/lists/listinfo/packetfence-users >> <https://lists.sourceforge.net/lists/listinfo/packetfence-users> > ------------------------------------------------------------------------------ > _______________________________________________ > PacketFence-users mailing list > [email protected] > <mailto:[email protected]> > https://lists.sourceforge.net/lists/listinfo/packetfence-users > <https://lists.sourceforge.net/lists/listinfo/packetfence-users>
------------------------------------------------------------------------------
_______________________________________________ PacketFence-users mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/packetfence-users
