I am seeing the same issue with my system as this thread. Just wanted to let
you know it is not just Jake. If I set the expire IP LOG to 0 it solves it.
Would like an option to run it once a day in the middle of the if possible.
Thanks
Dan Nelson
From: Louis Munro
<lmunro@...<http://gmane.org/get-address.php?address=lmunro%2dtiT8nZ%2bZ9Bqw5LPnMra%2f2Q%40public.gmane.org>>
Subject: Re: Errors in
packetfence.log<http://news.gmane.org/find-root.php?message_id=D2571341%2dA363%2d4F18%2d9BA8%2dD5EC3F4268FB%40inverse.ca>
Newsgroups:
gmane.comp.networking.packetfence.user<http://news.gmane.org/gmane.comp.networking.packetfence.user>
Date: 2014-09-01 19:41:28 GMT (7 weeks, 18 hours and 15 minutes ago)
So I believe this is the relevant part: *** (2) TRANSACTION: TRANSACTION 0
1433006267, ACTIVE 1 sec, process no 22158, OS thread id 140245874300672
fetching rows, thread declared inside InnoDB 34 mysql tables in use 1, locked 1
2356 lock struct(s), heap size 276464, 499812 row lock(s) MySQL thread id
59643, query id 200039784 localhost pf updating delete from iplog where
unix_timestamp(end_time) < (unix_timestamp(now()) - '15552000') and end_time!=0
What it looks like to me is that the pfmon process that cleans up the iplog
table is taking too long to complete and holding a lock on the table when the
pfdhcplistener tries to update it.
I am afraid there is no way to delete without locking that table.
We could try to break down the delete into smaller increments, but that would
mean patching the code and may only kick the problem down the road.
What I suggest instead is that you turn off deletion for that table (at least
temporarily) by setting iplog expiry to 0, as in
[expire]
iplog=0D
Then we can set up a cron job to delete those rows once every night instead of
every few minutes.
That cron job could itself be broken down into a smaller number of row so as
not to lock the table too long.
PF 4.4 should bring the option to run different parts of pfmon et different
times/schedules.
At the moment it's an all or nothing proposition.
Try disabling iplog deletion first and let us know if it helps.
If that fixes the immediate problem I'll send a short script to delete the rows
from a cronjob.
Regards,
--
Louis Munro
lmunro-tiT8nZ+Z9Bqw5LPnMra/[email protected]<mailto:lmunro-tiT8nZ+Z9Bqw5LPnMra/[email protected]>
:: www.inverse.ca<http://www.inverse.ca>
+1.514.447.4918 x125 :: +1 (866) 353-6153 x125
Inverse inc. :: Leaders behind SOGo (www.sogo.nu<http://www.sogo.nu>) and
PacketFence (www.packetfence.org<http://www.packetfence.org>)
On 2014-09-01, at 15:03 , "Sallee, Jake"
<[email protected]<mailto:[email protected]>>
wrote:
mysql> show engine innodb status;
Oh goodness, that is quite a lot of output, I'll drop it in a pastebin.
http://pastebin.com/dpVhib5j
Looks like the deadlock was on the iplog 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 [lmunro-tiT8nZ+Z9Bqw5LPnMra/[email protected]]
Sent: Monday, September 01, 2014 7:27 AM
To: packetfence-users-5nwgofrqmnerv+lv9mx5uipxlwaov...@public.gmane.org
Subject: Re: [PacketFence-users] Errors in packetfence.log
On 2014-08-31, at 23:27 , "Sallee, Jake"
<[email protected]> wrote:
I'm seeing a lot of the following error in my packetfence.log file:
Aug 31 21:08:23 httpd.webservices(5542) WARN: database query failed with:
Deadlock found when trying to get lock; try restarting transaction (errno:
1213), will try again (pf::db::db_query_execute)
I haven't seen this before and I'm not sure what to do about it. I am
google-ing the error but I would like to hear from anyone who can shed some
light on it.
I can say that my server not under very heavy load and the server stats I know
to check seem fine. CPU, MEM, iostat, etc.
My initial search turned up a message in the mailing list but I could not find
any answer see here:
http://sourceforge.net/p/packetfence/mailman/message/29824991/
And a bug report here:
http://www.packetfence.org/bugs/view.php?id=1524
I did have a vlan interface like the bug mentions but I removed it and bounced
PF to no avail. I did try pfcmd configreload hard too, but no joy.
Any help is greatly appreciated.
Hi Jake,
I have sometimes seen deadlocks caused by having two pfdhcplistener processes
running for the same interface.
But to find out more about deadlocks in general you should run this from mysql:
mysql> show engine innodb status;
It should show the query that caused the last deadlock (among other things).
Please post the output and we can try to help.
Regards,
--
Louis Munro
lmunro-tiT8nZ+Z9Bqw5LPnMra/[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)
------------------------------------------------------------------------------
Slashdot TV.
Video for Nerds. Stuff that matters.
http://tv.slashdot.org/
_______________________________________________
PacketFence-users mailing list
packetfence-users-5nwgofrqmnerv+lv9mx5uipxlwaov...@public.gmane.org
https://lists.sourceforge.net/lists/listinfo/packetfence-users
------------------------------------------------------------------------------
Slashdot TV.
Video for Nerds. Stuff that matters.
http://tv.slashdot.org/
_______________________________________________
PacketFence-users mailing list
packetfence-users-5nwgofrqmnerv+lv9mx5uipxlwaov...@public.gmane.org
https://lists.sourceforge.net/lists/listinfo/packetfence-users
------------------------------------------------------------------------------
Slashdot TV.
Video for Nerds. Stuff that matters.
http://tv.slashdot.org/
_______________________________________________
PacketFence-users mailing list
PacketFence-users@...<http://gmane.org/get-address.php?address=PacketFence%2dusers%2d5NWGOfrQmneRv%2bLV9MX5uipxlwaOVQ5f%40public.gmane.org>
https://lists.sourceforge.net/lists/listinfo/packetfence-users
------------------------------------------------------------------------------
Comprehensive Server Monitoring with Site24x7.
Monitor 10 servers for $9/Month.
Get alerted through email, SMS, voice calls or mobile push notifications.
Take corrective actions from your mobile device.
http://p.sf.net/sfu/Zoho
_______________________________________________
PacketFence-users mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/packetfence-users
