Re: [PacketFence-users] Admin GUI - Node details slow.

2016-02-25 Thread Thomas, Gregory A 
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:

$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);



--
Gregory A. Thomas
Student Life Support Specialist
University of Wisconsin-Parkside
thom...@uwp.edu
262.595.2432


-Original Message-
From: Thomas, Gregory A [mailto:thom...@uwp.edu] 
Sent: Thursday, February 25, 2016 8:53 AM
To: packetfence-users@lists.sourceforge.net
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
thom...@uwp.edu
262.595.2432


-Original Message-
From: Morris, Andi [mailto:amor...@cardiffmet.ac.uk]
Sent: Thursday, February 25, 2016 3:15 AM
To: packetfence-users@lists.sourceforge.net
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:beb...@bebbet.nl]
Sent: 23 February 2016 12:40
To: packetfence-users@lists.sourceforge.net
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
>> thom...@uwp.edu
>> 262.595.2432
>>
>> -Original Message-
>> From: Bebbet van Dinges [mailto:beb...@bebbet.nl]
>> Sent: Tuesday, February 16, 2016 6:08 AM
>> To: packetfence-users@lists.sourceforge.net
>> 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

Re: [PacketFence-users] Admin GUI - Node details slow.

2016-02-25 Thread Thomas, Gregory A 
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
thom...@uwp.edu
262.595.2432


-Original Message-
From: Morris, Andi [mailto:amor...@cardiffmet.ac.uk] 
Sent: Thursday, February 25, 2016 3:15 AM
To: packetfence-users@lists.sourceforge.net
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:beb...@bebbet.nl]
Sent: 23 February 2016 12:40
To: packetfence-users@lists.sourceforge.net
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
>> thom...@uwp.edu
>> 262.595.2432
>>
>> -Original Message-
>> From: Bebbet van Dinges [mailto:beb...@bebbet.nl]
>> Sent: Tuesday, February 16, 2016 6:08 AM
>> To: packetfence-users@lists.sourceforge.net
>> 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 so 
>>> 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 thom...@uwp.edu 262.595.2432
>>
>>
>>> -Original Message- From: Morris, Andi 
>>> [mailto:amor...@cardiffmet.ac.uk] Sent: Tuesday, January 12, 2016
>>> 7:01 AM To: packetfence-users@lists.sourceforge.net Subject: Re:
>>> [PacketFence-users] Admin GUI - Node details slow.
>>
>>> J

Re: [PacketFence-users] Admin GUI - Node details slow.

2016-02-25 Thread Morris, Andi 
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:beb...@bebbet.nl] 
Sent: 23 February 2016 12:40
To: packetfence-users@lists.sourceforge.net
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
>> thom...@uwp.edu
>> 262.595.2432
>>
>> -Original Message-
>> From: Bebbet van Dinges [mailto:beb...@bebbet.nl]
>> Sent: Tuesday, February 16, 2016 6:08 AM
>> To: packetfence-users@lists.sourceforge.net
>> 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 so 
>>> 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 thom...@uwp.edu 262.595.2432
>>
>>
>>> -Original Message- From: Morris, Andi 
>>> [mailto:amor...@cardiffmet.ac.uk] Sent: Tuesday, January 12, 2016
>>> 7:01 AM To: packetfence-users@lists.sourceforge.net 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:jake.sal...@umhb.edu] Sent: 11 January 2016 23:54 To:
>>> packetfence-users@lists.sourceforge.net 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] @

Re: [PacketFence-users] Admin GUI - Node details slow.

2016-02-23 Thread Bebbet van Dinges 
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
>> thom...@uwp.edu
>> 262.595.2432
>>
>> -Original Message-
>> From: Bebbet van Dinges [mailto:beb...@bebbet.nl] 
>> Sent: Tuesday, February 16, 2016 6:08 AM
>> To: packetfence-users@lists.sourceforge.net
>> 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 so 
>>> 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 thom...@uwp.edu 262.595.2432
>>
>>
>>> -Original Message- From: Morris, Andi 
>>> [mailto:amor...@cardiffmet.ac.uk] Sent: Tuesday, January 12, 2016
>>> 7:01 AM To: packetfence-users@lists.sourceforge.net 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:jake.sal...@umhb.edu] Sent: 11 January 2016 23:54 To:
>>> packetfence-users@lists.sourceforge.net 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.75 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

Re: [PacketFence-users] Admin GUI - Node details slow.

2016-02-16 Thread Thomas, Gregory A 
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
thom...@uwp.edu
262.595.2432

-Original Message-
From: Bebbet van Dinges [mailto:beb...@bebbet.nl] 
Sent: Tuesday, February 16, 2016 6:08 AM
To: packetfence-users@lists.sourceforge.net
Subject: Re: [PacketFence-users] Admin GUI - Node details slow.

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA256

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 so 
> 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 thom...@uwp.edu 262.595.2432
> 
> 
> -Original Message- From: Morris, Andi 
> [mailto:amor...@cardiffmet.ac.uk] Sent: Tuesday, January 12, 2016
> 7:01 AM To: packetfence-users@lists.sourceforge.net 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:jake.sal...@umhb.edu] Sent: 11 January 2016 23:54 To:
> packetfence-users@lists.sourceforge.net 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.75 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 [lmu...@inverse.ca]
> 
> Sent: Monday, January 11, 2016 12:29 PM
> 
> To: packetfence-users@lists.sourceforge.net
> 
> Subject: Re: [PacketFence-users] Admin GUI - Node details slow.
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> On Jan 11, 2016, at 9:28 , Sallee, Jake <jake.sal...@umhb.edu>
> 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 lmu...@inverse.ca  ::  www.inverse.ca
> +1.514.447.4918 x125  :: +1 (8

Re: [PacketFence-users] Admin GUI - Node details slow.

2016-02-16 Thread Bebbet van Dinges 
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA256

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
> so 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 thom...@uwp.edu 262.595.2432
> 
> 
> -Original Message- From: Morris, Andi
> [mailto:amor...@cardiffmet.ac.uk] Sent: Tuesday, January 12, 2016
> 7:01 AM To: packetfence-users@lists.sourceforge.net 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:jake.sal...@umhb.edu] Sent: 11 January 2016 23:54 To:
> packetfence-users@lists.sourceforge.net 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.75 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 [lmu...@inverse.ca]
> 
> Sent: Monday, January 11, 2016 12:29 PM
> 
> To: packetfence-users@lists.sourceforge.net
> 
> Subject: Re: [PacketFence-users] Admin GUI - Node details slow.
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> On Jan 11, 2016, at 9:28 , Sallee, Jake <jake.sal...@umhb.edu> 
> 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 lmu...@inverse.ca  ::  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/gamp

Re: [PacketFence-users] Admin GUI - Node details slow.

2016-02-16 Thread Bebbet van Dinges 
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA256

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
> so 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 thom...@uwp.edu 262.595.2432
> 
> 
> -Original Message- From: Morris, Andi
> [mailto:amor...@cardiffmet.ac.uk] Sent: Tuesday, January 12, 2016
> 7:01 AM To: packetfence-users@lists.sourceforge.net 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:jake.sal...@umhb.edu] Sent: 11 January 2016 23:54 To:
> packetfence-users@lists.sourceforge.net 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.75 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 [lmu...@inverse.ca]
> 
> Sent: Monday, January 11, 2016 12:29 PM
> 
> To: packetfence-users@lists.sourceforge.net
> 
> Subject: Re: [PacketFence-users] Admin GUI - Node details slow.
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> On Jan 11, 2016, at 9:28 , Sallee, Jake <jake.sal...@umhb.edu> 
> 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 lmu...@inverse.ca  ::  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/gamp

Re: [PacketFence-users] Admin GUI - Node details slow.

2016-01-12 Thread Morris, Andi 
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:jake.sal...@umhb.edu]
Sent: 11 January 2016 23:54
To: packetfence-users@lists.sourceforge.net
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.75 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 [lmu...@inverse.ca]

Sent: Monday, January 11, 2016 12:29 PM

To: packetfence-users@lists.sourceforge.net

Subject: Re: [PacketFence-users] Admin GUI - Node details slow.











On Jan 11, 2016, at 9:28 , Sallee, Jake <jake.sal...@umhb.edu>
 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
lmu...@inverse.ca  ::  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=/4140
___
PacketFence-users mailing list
PacketFence-users@lists.sourceforge.net
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=/4140
___
PacketFence-users mailing list
PacketFence-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/packetfence-users

Re: [PacketFence-users] Admin GUI - Node details slow.

2016-01-12 Thread Thomas, Gregory A 
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 so 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
thom...@uwp.edu
262.595.2432


-Original Message-
From: Morris, Andi [mailto:amor...@cardiffmet.ac.uk] 
Sent: Tuesday, January 12, 2016 7:01 AM
To: packetfence-users@lists.sourceforge.net
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:jake.sal...@umhb.edu]
Sent: 11 January 2016 23:54
To: packetfence-users@lists.sourceforge.net
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.75 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 [lmu...@inverse.ca]

Sent: Monday, January 11, 2016 12:29 PM

To: packetfence-users@lists.sourceforge.net

Subject: Re: [PacketFence-users] Admin GUI - Node details slow.











On Jan 11, 2016, at 9:28 , Sallee, Jake <jake.sal...@umhb.edu>
 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
lmu...@inverse.ca  ::  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=/4140
___
PacketFence-users mailing list
PacketFence-users@lists.sourceforge.net
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

Re: [PacketFence-users] Admin GUI - Node details slow.

2016-01-12 Thread Morris, Andi 
That sounds really interesting. I think I had something similar setup on an old 
box, but not on my current one. I don't think I need the iplog_history to go 
back more than a week either. I'm wondering whether something like this might 
actually take some load off my box too.

-Original Message-
From: Thomas, Gregory A [mailto:thom...@uwp.edu] 
Sent: 12 January 2016 15:24
To: packetfence-users@lists.sourceforge.net
Subject: Re: [PacketFence-users] Admin GUI - Node details slow.

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 so 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
thom...@uwp.edu
262.595.2432


-Original Message-
From: Morris, Andi [mailto:amor...@cardiffmet.ac.uk]
Sent: Tuesday, January 12, 2016 7:01 AM
To: packetfence-users@lists.sourceforge.net
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:jake.sal...@umhb.edu]
Sent: 11 January 2016 23:54
To: packetfence-users@lists.sourceforge.net
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.75 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 [lmu...@inverse.ca]

Sent: Monday, January 11, 2016 12:29 PM

To: packetfence-users@lists.sourceforge.net

Subject: Re: [PacketFence-users] Admin GUI - Node details slow.











On Jan 11, 2016, at 9:28 , Sallee, Jake <jake.sal...@umhb.edu>
 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
lmu...@inverse.ca  ::  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=/4140
___
PacketFence-users mailing list
PacketFence-users@lists.sourceforge.net

Re: [PacketFence-users] Admin GUI - Node details slow.

2016-01-11 Thread Sallee, Jake 
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.75 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 [lmu...@inverse.ca]

Sent: Monday, January 11, 2016 12:29 PM

To: packetfence-users@lists.sourceforge.net

Subject: Re: [PacketFence-users] Admin GUI - Node details slow.











On Jan 11, 2016, at 9:28 , Sallee, Jake <jake.sal...@umhb.edu>
 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
lmu...@inverse.ca  ::  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=/4140
___
PacketFence-users mailing list
PacketFence-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/packetfence-users

Re: [PacketFence-users] Admin GUI - Node details slow.

2016-01-11 Thread Louis Munro 


> On Jan 11, 2016, at 9:28 , Sallee, Jake  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
lmu...@inverse.ca  ::  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=/4140___
PacketFence-users mailing list
PacketFence-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/packetfence-users