The email I sent to the list before doesn't seem relevant anymore so I
wanted to start a new one with new information I have discovered.
I am having trouble with something on my system that is causing the
database to slow down. When starting packetfence, my system load seems to
skyrocket. This seems related to my database and pfdhcplistener. If I
restart the mysql service, the system load drops back to normal. Currently,
whenever I restart the packetfence service or the server itself, I must
also restart myql afterwords. Otherwise my system will eventually stop
working. I figured out that this is tied to the pfdhcplistener service by
restarting each of service one by one. When the pfdchplistener service is
restarted, my system load skyrockets and remains their until I restart
mysql. My pfdhcplistener log looks chaotic before I restart mysql to calm
things down. I get repeatable DHCPACK messages from the same clients.
My MYSQLREPORT looks like this when things are running normal:
MySQL 5.5.44-0+deb7u1-l uptime 0 0:10:51 Thu Oct 8 12:04:38 2015
__ Key _________________________________________________________________
Buffer used 5.00k of 16.00M %Used: 0.03
Current 2.92M %Usage: 18.27
Write hit 0.00%
Read hit 85.29%
__ Questions ___________________________________________________________
Total 4.77k 7.3/s
QC Hits 2.81k 4.3/s %Total: 58.87
DMS 1.92k 2.9/s 40.18
-Unknown 249 0.4/s 5.22
Com_ 187 0.3/s 3.92
COM_QUIT 107 0.2/s 2.24
Slow 500 ms 0 0/s 0.00 %DMS: 0.00 Log: ON
DMS 1.92k 2.9/s 40.18
SELECT 1.39k 2.1/s 29.15 72.55
UPDATE 359 0.6/s 7.53 18.74
INSERT 167 0.3/s 3.50 8.72
REPLACE 0 0/s 0.00 0.00
DELETE 0 0/s 0.00 0.00
Com_ 187 0.3/s 3.92
show_status 66 0.1/s 1.38
admin_comma 66 0.1/s 1.38
call_proced 51 0.1/s 1.07
__ SELECT and Sort _____________________________________________________
Scan 261 0.4/s %SELECT: 18.78
Range 49 0.1/s 3.53
Full join 0 0/s 0.00
Range check 0 0/s 0.00
Full rng join 0 0/s 0.00
Sort scan 14 0.0/s
Sort range 153 0.2/s
Sort mrg pass 0 0/s
__ Query Cache _________________________________________________________
Memory usage 556.09k of 16.00M %Used: 3.39
Block Fragmnt 7.57%
Hits 2.81k 4.3/s
Inserts 1.22k 1.9/s
Insrt:Prune 1.22k:1 1.9/s
Hit:Insert 2.31:1
__ Table Locks _________________________________________________________
Waited 0 0/s %Total: 0.00
Immediate 2.88k 4.4/s
__ Tables ______________________________________________________________
Open 98 of 400 %Cache: 24.50
Opened 105 0.2/s
__ Connections _________________________________________________________
Max used 42 of 151 %Max: 27.81
Total 109 0.2/s
__ Created Temp ________________________________________________________
Disk table 64 0.1/s
Table 292 0.4/s Size: 16.0M
File 6 0.0/s
__ Threads _____________________________________________________________
Running 1 of 42
Cached 0 of 8 %Hit: 61.47
Created 42 0.1/s
Slow 0 0/s
__ Aborted _____________________________________________________________
Clients 0 0/s
Connects 3 0.0/s
__ Bytes _______________________________________________________________
Sent 3.59M 5.5k/s
Received 1.70M 2.6k/s
__ InnoDB Buffer Pool __________________________________________________
Usage 17.83M of 128.00M %Used: 13.93
Read hit 99.93%
Pages
Free 7.05k %Total: 86.07
Data 1.12k 13.67 %Drty: 2.59
Misc 21 0.26
Latched 0 0.00
Reads 1.45M 2.2k/s
From file 1.06k 1.6/s 0.07
Ahead Rnd 0 0/s
Ahead Sql 0/s
Writes 4.97k 7.6/s
Flushes 2.64k 4.1/s
Wait Free 0 0/s
__ InnoDB Lock _________________________________________________________
Waits 0 0/s
Current 0
Time acquiring
Total 0 ms
Average 0 ms
Max 0 ms
__ InnoDB Data, Pages, Rows ____________________________________________
Data
Reads 1.12k 1.7/s
Writes 3.38k 5.2/s
fsync 820 1.3/s
Pending
Reads 0
Writes 0
fsync 0
Pages
Created 14 0.0/s
Read 1.11k 1.7/s
Written 2.64k 4.1/s
Rows
Deleted 0 0/s
Inserted 167 0.3/s
Read 499.53k 767.3/s
Updated 359 0.6/s
I have enabled log slow queries and that reads this:
Count: 305 Time=0.26s (80s) Lock=0.00s (0s) Rows=0.0 (0),
admin[admin]@localhost
SELECT radacct.callingstationid,
SUM(radacct_log.acctinputoctets) AS acctinput,
SUM(radacct_log.acctoutputoctets) AS acctoutput,
SUM(radacct_log.acctinputoctets+radacct_log.acctoutputoctets) AS accttotal
FROM radacct_log
RIGHT JOIN radacct ON radacct_log.acctsessionid = radacct.acctsessionid
WHERE MONTH(timestamp) = MONTH(CURRENT_DATE()) AND timestamp >= 'S'
GROUP BY radacct.callingstationid
HAVING accttotal >= 'S'
Count: 59 Time=0.00s (0s) Lock=0.00s (0s) Rows=0.0 (0),
admin[admin]@localhost
select tid_start,tid_end,class.vid,type,whitelisted_categories from
`trigger`,class where class.vid=`trigger`.vid and tid_start<='S' and
tid_end>='S' and type='S' and enabled="S"
Count: 297 Time=0.00s (0s) Lock=0.00s (0s) Rows=0.0 (0),
admin[admin]@localhost
select id,mac,vid,notes,status from violation where release_date !=N AND
release_date <= NOW() AND status != "S" LIMIT N
Looks like it is slow in getting the callingstationid column. This column
is populated with many mac addresses. mysqlcheck comes back OK for all
databases and tables.
I think this is related to accounting not working because these problems
seemed to start when I was trying to fix RADIUS accounting. I can see
traffic from tcpdump come in for accounting on port 1813, but the pf admin
tells me this: There's not enough data to generate this graph. Is
PacketFence in production?
When things aren't working correctly, I get a lot of "Child is hung"
messages in my radius log. These go away when I restart the mysql service.
My packetfence and http logs looks normal
I'm really not sure where to go from here. I think it has something to do
with my freeRADIUS service, but I am completely new to troubleshooting
these types of issues. Any help you can provide is greatly appreciated.
Thanks,
Chris
--
IMPORTANT NOTICE: This message and any attachments are solely for the
intended recipient and may contain confidential information, which is, or
may be, legally privileged or otherwise protected by law from further
disclosure. If you are not the intended recipient, any disclosure, copying,
use, or distribution of the information included in this email and any
attachments is prohibited. If you have received this communication in
error, please notify the sender by reply email and immediately and
permanently delete this email and any attachments.
------------------------------------------------------------------------------
_______________________________________________
PacketFence-users mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/packetfence-users
