packetfence 4.4.0 with Mysql5.1 have very much slow query in a big environment have 2000 machine
Count: 8 Time=176.57s (1412s) Lock=0.00s (0s) Rows=22.9 (183), pf[pf]@localhost
SELECT o.description, n.dhcp_fingerprint, COUNT(DISTINCT n.mac) AS count, ROUND(COUNT(DISTINCT n.mac)/(SELECT COUNT(_) FROM node)_N,N) AS percent
FROM (node n, iplog i)
LEFT JOIN dhcp_fingerprint d ON n.dhcp_fingerprint = d.fingerprint
LEFT JOIN os_type o ON o.os_id = d.os_id
WHERE n.mac = i.mac AND i.start_time BETWEEN 'S' AND 'S'
GROUP BY o.description
ORDER BY percent desc
Count: 204 Time=37.06s (7560s) Lock=0.00s (0s) Rows=0.0 (0), pf[pf]@localhost
update iplog set end_time = adddate(now(), interval 'S' second) where mac='S' and ip='S' and (end_time = N or end_time > now())
Count: 330 Time=35.32s (11654s) Lock=0.00s (0s) Rows=0.0 (0), pf[pf]@localhost
insert into iplog(mac,ip,start_time,end_time) values('S','S',now(),adddate(now(), interval 'S' second))
Count: 4 Time=25.07s (100s) Lock=0.00s (0s) Rows=1.5 (6), pf[pf]@localhost
SELECT ssid, COUNT(DISTINCT locationlog.mac) AS nodes, ROUND(COUNT(DISTINCT locationlog.mac)/
(SELECT COUNT(DISTINCT locationlog.mac)
FROM locationlog
INNER JOIN node ON node.mac = locationlog.mac AND locationlog.end_time IS NULL
INNER JOIN iplog ON node.mac = iplog.mac
WHERE ssid != "S" AND iplog.start_time BETWEEN 'S' AND 'S'
)*N,N) AS percent
FROM locationlog
INNER JOIN node ON node.mac = locationlog.mac AND locationlog.end_time IS NULL
INNER JOIN iplog ON node.mac = iplog.mac
WHERE ssid != "S" AND iplog.end_time BETWEEN 'S' AND 'S'
GROUP BY ssid
ORDER BY nodes
Count: 500211 Time=20.87s (10439818s) Lock=0.00s (15s) Rows=0.0 (0), pf[pf]@localhost
update iplog set end_time=now() where ip='S' and (end_time=N or end_time > now())
Count: 2 Time=20.32s (40s) Lock=0.00s (0s) Rows=0.0 (0), pf[pf]@localhost
update iplog set end_time=now() where ip='S' and end_time=N
Count: 83595 Time=19.97s (1669210s) Lock=0.00s (6s) Rows=0.0 (0), pf[pf]@localhost
delete from iplog where unix_timestamp(end_time) < (unix_timestamp(now()) - 'S') and end_time!=N
Count: 2 Time=18.55s (37s) Lock=0.00s (0s) Rows=0.0 (0), pf[pf]@localhost
UPDATE node SET
mac='S', pid='S', category_id='S', status='S', voip='S', bypass_vlan='S',
detect_date='S', regdate='S', unregdate='S', lastskip='S', time_balance=NULL, bandwidth_balance=NULL,
user_agent='S', computername='S', dhcp_fingerprint='S',
last_arp='S', last_dhcp='S',
notes=NULL, autoreg='S', sessionid='S'
WHERE mac='S'
Count: 4 Time=17.89s (71s) Lock=0.00s (0s) Rows=1.0 (4), pf[pf]@localhost
SELECT COUNT(*) as count FROM node
LEFT JOIN node_category
on node_category
.category_id
= node
.category_id
LEFT JOIN dhcp_fingerprint
on dhcp_fingerprint
.fingerprint
= node
.dhcp_fingerprint
LEFT JOIN iplog
on iplog
.mac
= node
.mac
AND ( iplog
.end_time
= 'S' OR iplog
.end_time
> NOW() ) LEFT JOIN os_type
using( os_id
) WHERE ( iplog
.ip
LIKE 'S' )