Re: [PacketFence-users] Mysql database maintenance

2015-10-20 Thread Tim DeNike 
0 means keep forever.

Your redact_log/radacct is getting up there.  Check the database
maintenance script and modify the interval to keep it for X months.

For us, 2 months = about 3gig for each of those.



On Tue, Oct 20, 2015 at 4:12 PM, Pete Hoffswell <
pete.hoffsw...@davenport.edu> wrote:

> Thanks,
>
> Gotcha.  Configuration > Expiration -
>
> My expirations are set for
>
> Node - 7 days
> IP/Mac Logs - 0 days
> Trap Logs - 0 days
> Location Logs - 0 days
>
> Auto-expire settings are all active as well.
>
> What's zero mean?  Keep none, or keep all?
>
>
> Looks like it's radius -
>
> +-++
> | Tables  | Size in MB |
> +-++
> | radacct_log |5171.00 |
> | radacct |2851.16 |
> | locationlog | 404.67 |
> | iplog   |  32.28 |
> | node|   7.86 |
> | person  |   1.47 |
> | activation  |   0.11 |
> | dhcp_fingerprint|   0.09 |
>
>
> -
> Pete Hoffswell - Network Manager
> pete.hoffsw...@davenport.edu
> http://www.davenport.edu
>
>
> On Tue, Oct 20, 2015 at 4:07 PM, Louis Munro  wrote:
>
>> I would also suggest finding out exactly which table is using the most
>> space.
>> That will tell you where to bother pruning data and where it’s not worth
>> the trouble.
>>
>> I use this query to see the size of the tables:
>>
>>
>> SELECT table_name AS 'Tables',
>> round(((data_length + index_length) / 1024 / 1024), 2) 'Size in MB'
>> FROM information_schema.TABLES
>> WHERE table_schema = 'pf'
>> ORDER BY (data_length + index_length) DESC;
>>
>>
>> --
>> 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)
>>
>> On Oct 20, 2015, at 15:29 , Tim DeNike  wrote:
>>
>> The maintenance script, also shortening expiration times in
>> configuration, expiration.  Check to see how often your APs/switches are
>> sending accounting information.  If you can change them to space the
>> accounting info out further, it can be a big savings.
>>
>> On Tue, Oct 20, 2015 at 2:58 PM, Pete Hoffswell <
>> pete.hoffsw...@davenport.edu> wrote:
>>
>>> Just looking at my database... it's pretty big -
>>>
>>> pf# mysql -u pf -p
>>> Enter password:
>>> Welcome to the MySQL monitor.  Commands end with ; or \g.
>>> 
>>> mysql> use pf
>>> Reading table information for completion of table and column names
>>> You can turn off this feature to get a quicker startup with -A
>>>
>>> Database changed
>>> mysql> SELECT table_schema"DB
>>> Name",
>>> ->Round(Sum(data_length + index_length) / 1024 / 1024, 1) "DB
>>> Size in MB"
>>> -> FROM   information_schema.tables
>>> -> GROUP  BY table_schema;
>>> ++---+
>>> | DB Name| DB Size in MB |
>>> ++---+
>>> | information_schema |   0.0 |
>>> | pf |8465.4 |
>>> ++---+
>>> 2 rows in set (1.07 sec)
>>>
>>> mysql>
>>>
>>>
>>>
>>> What's the best way to clean up the database of old data?
>>>
>>> addons/database-backup-and-maintenance.sh
>>>
>>> ?
>>>
>>>
>>>
>>> -
>>> Pete Hoffswell - Network Manager
>>> pete.hoffsw...@davenport.edu
>>> http://www.davenport.edu
>>>
>>>
>>>
>>> --
>>>
>>> ___
>>> PacketFence-users mailing list
>>> PacketFence-users@lists.sourceforge.net
>>> https://lists.sourceforge.net/lists/listinfo/packetfence-users
>>>
>>>
>>
>> --
>> ___
>> PacketFence-users mailing list
>> PacketFence-users@lists.sourceforge.net
>> https://lists.sourceforge.net/lists/listinfo/packetfence-users
>>
>>
>>
>>
>> --
>>
>> ___
>> PacketFence-users mailing list
>> PacketFence-users@lists.sourceforge.net
>> https://lists.sourceforge.net/lists/listinfo/packetfence-users
>>
>>
>
>
> --
>
> ___
> PacketFence-users mailing list
> PacketFence-users@lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/packetfence-users
>
>
--
___
PacketFence-users mailing list
PacketFence-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/packetfence-users

Re: [PacketFence-users] Mysql database maintenance

2015-10-20 Thread Pete Hoffswell 
Thanks,

Gotcha.  Configuration > Expiration -

My expirations are set for

Node - 7 days
IP/Mac Logs - 0 days
Trap Logs - 0 days
Location Logs - 0 days

Auto-expire settings are all active as well.

What's zero mean?  Keep none, or keep all?


Looks like it's radius -

+-++
| Tables  | Size in MB |
+-++
| radacct_log |5171.00 |
| radacct |2851.16 |
| locationlog | 404.67 |
| iplog   |  32.28 |
| node|   7.86 |
| person  |   1.47 |
| activation  |   0.11 |
| dhcp_fingerprint|   0.09 |


-
Pete Hoffswell - Network Manager
pete.hoffsw...@davenport.edu
http://www.davenport.edu


On Tue, Oct 20, 2015 at 4:07 PM, Louis Munro  wrote:

> I would also suggest finding out exactly which table is using the most
> space.
> That will tell you where to bother pruning data and where it’s not worth
> the trouble.
>
> I use this query to see the size of the tables:
>
>
> SELECT table_name AS 'Tables',
> round(((data_length + index_length) / 1024 / 1024), 2) 'Size in MB'
> FROM information_schema.TABLES
> WHERE table_schema = 'pf'
> ORDER BY (data_length + index_length) DESC;
>
>
> --
> 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)
>
> On Oct 20, 2015, at 15:29 , Tim DeNike  wrote:
>
> The maintenance script, also shortening expiration times in configuration,
> expiration.  Check to see how often your APs/switches are sending
> accounting information.  If you can change them to space the accounting
> info out further, it can be a big savings.
>
> On Tue, Oct 20, 2015 at 2:58 PM, Pete Hoffswell <
> pete.hoffsw...@davenport.edu> wrote:
>
>> Just looking at my database... it's pretty big -
>>
>> pf# mysql -u pf -p
>> Enter password:
>> Welcome to the MySQL monitor.  Commands end with ; or \g.
>> 
>> mysql> use pf
>> Reading table information for completion of table and column names
>> You can turn off this feature to get a quicker startup with -A
>>
>> Database changed
>> mysql> SELECT table_schema"DB
>> Name",
>> ->Round(Sum(data_length + index_length) / 1024 / 1024, 1) "DB
>> Size in MB"
>> -> FROM   information_schema.tables
>> -> GROUP  BY table_schema;
>> ++---+
>> | DB Name| DB Size in MB |
>> ++---+
>> | information_schema |   0.0 |
>> | pf |8465.4 |
>> ++---+
>> 2 rows in set (1.07 sec)
>>
>> mysql>
>>
>>
>>
>> What's the best way to clean up the database of old data?
>>
>> addons/database-backup-and-maintenance.sh
>>
>> ?
>>
>>
>>
>> -
>> Pete Hoffswell - Network Manager
>> pete.hoffsw...@davenport.edu
>> http://www.davenport.edu
>>
>>
>>
>> --
>>
>> ___
>> PacketFence-users mailing list
>> PacketFence-users@lists.sourceforge.net
>> https://lists.sourceforge.net/lists/listinfo/packetfence-users
>>
>>
>
> --
> ___
> PacketFence-users mailing list
> PacketFence-users@lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/packetfence-users
>
>
>
>
> --
>
> ___
> PacketFence-users mailing list
> PacketFence-users@lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/packetfence-users
>
>
--
___
PacketFence-users mailing list
PacketFence-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/packetfence-users

Re: [PacketFence-users] Mysql database maintenance

2015-10-20 Thread Louis Munro 
I would also suggest finding out exactly which table is using the most space.
That will tell you where to bother pruning data and where it’s not worth the 
trouble.

I use this query to see the size of the tables:


SELECT table_name AS 'Tables', 
round(((data_length + index_length) / 1024 / 1024), 2) 'Size in MB' 
FROM information_schema.TABLES 
WHERE table_schema = 'pf' 
ORDER BY (data_length + index_length) DESC;


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

> On Oct 20, 2015, at 15:29 , Tim DeNike  wrote:
> 
> The maintenance script, also shortening expiration times in configuration, 
> expiration.  Check to see how often your APs/switches are sending accounting 
> information.  If you can change them to space the accounting info out 
> further, it can be a big savings.
> 
> On Tue, Oct 20, 2015 at 2:58 PM, Pete Hoffswell  > wrote:
> Just looking at my database... it's pretty big -
> 
> pf# mysql -u pf -p
> Enter password:
> Welcome to the MySQL monitor.  Commands end with ; or \g.
> 
> mysql> use pf
> Reading table information for completion of table and column names
> You can turn off this feature to get a quicker startup with -A
> 
> Database changed
> mysql> SELECT table_schema"DB Name",
> ->Round(Sum(data_length + index_length) / 1024 / 1024, 1) "DB Size in 
> MB"
> -> FROM   information_schema.tables
> -> GROUP  BY table_schema;
> ++---+
> | DB Name| DB Size in MB |
> ++---+
> | information_schema |   0.0 |
> | pf |8465.4 |
> ++---+
> 2 rows in set (1.07 sec)
> 
> mysql>
> 
> 
> 
> What's the best way to clean up the database of old data?  
> 
> addons/database-backup-and-maintenance.sh
> 
> ?
> 
> 
> 
> -
> Pete Hoffswell - Network Manager
> pete.hoffsw...@davenport.edu  
> http://www.davenport.edu 
> 
> 
> --
> 
> ___
> PacketFence-users mailing list
> PacketFence-users@lists.sourceforge.net 
> 
> https://lists.sourceforge.net/lists/listinfo/packetfence-users 
> 
> 
> 
> --
> ___
> PacketFence-users mailing list
> PacketFence-users@lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/packetfence-users

--
___
PacketFence-users mailing list
PacketFence-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/packetfence-users

Re: [PacketFence-users] Mysql database maintenance

2015-10-20 Thread Tim DeNike 
The maintenance script, also shortening expiration times in configuration,
expiration.  Check to see how often your APs/switches are sending
accounting information.  If you can change them to space the accounting
info out further, it can be a big savings.

On Tue, Oct 20, 2015 at 2:58 PM, Pete Hoffswell <
pete.hoffsw...@davenport.edu> wrote:

> Just looking at my database... it's pretty big -
>
> pf# mysql -u pf -p
> Enter password:
> Welcome to the MySQL monitor.  Commands end with ; or \g.
> 
> mysql> use pf
> Reading table information for completion of table and column names
> You can turn off this feature to get a quicker startup with -A
>
> Database changed
> mysql> SELECT table_schema"DB
> Name",
> ->Round(Sum(data_length + index_length) / 1024 / 1024, 1) "DB Size
> in MB"
> -> FROM   information_schema.tables
> -> GROUP  BY table_schema;
> ++---+
> | DB Name| DB Size in MB |
> ++---+
> | information_schema |   0.0 |
> | pf |8465.4 |
> ++---+
> 2 rows in set (1.07 sec)
>
> mysql>
>
>
>
> What's the best way to clean up the database of old data?
>
> addons/database-backup-and-maintenance.sh
>
> ?
>
>
>
> -
> Pete Hoffswell - Network Manager
> pete.hoffsw...@davenport.edu
> http://www.davenport.edu
>
>
>
> --
>
> ___
> PacketFence-users mailing list
> PacketFence-users@lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/packetfence-users
>
>
--
___
PacketFence-users mailing list
PacketFence-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/packetfence-users