Mailing List Archive

limit mysql storage
Hi,

I've setup ntopng to dump flow data in a MySQL db, with 30 days retention.
It's been over 45 days and the data is still there.

Furthermore, even if I manually delete the old data, the space occupied
is not freed. It grows at a rate of 3GB/day.

What is the correct procedure of reusing free space when using MySQL
storage?

Best regards,
Dan Craciun
_______________________________________________
Ntop mailing list
Ntop@listgateway.unipi.it
http://listgateway.unipi.it/mailman/listinfo/ntop
Re: limit mysql storage [ In reply to ]
On 3/8/19 11:50 AM, Dan Craciun wrote:
> Hi,
>
> I've setup ntopng to dump flow data in a MySQL db, with 30 days retention.
> It's been over 45 days and the data is still there.
>
> Furthermore, even if I manually delete the old data, the space occupied
> is not freed. It grows at a rate of 3GB/day.
>
> What is the correct procedure of reusing free space when using MySQL
> storage?
>
> Best regards,
> Dan Craciun

What you're describing suggests the mysql DB was configured with myisam tables spaces for the storage.  It's a pretty common default.

Have a look at the optimize command and perhaps look at converting the table space to innodb with file-per-table. To reconfigure for innodb-file-per-table, the db has to be
restarted and this should be done before converting the tables to innodb. Once to make this change, the myisam to innodb conversion can be done live.

You might also want to look up mysqltuner.  It's a perl script that can do some crude analysis of your mysql instances performance.  It's actually fairly useful.


_______________________________________________
Ntop mailing list
Ntop@listgateway.unipi.it
http://listgateway.unipi.it/mailman/listinfo/ntop
Re: limit mysql storage [ In reply to ]
Actually, I haven't configured anything, just started ntopng with the -F
option and it created the tables.
They are innodb.

ls -lh
total 117G
-rw-r----- 1 mysql mysql 2.8G Mar  9 06:30 aggrflowsv4#P#p0.ibd
-rw-r----- 1 mysql mysql 2.8G Mar  9 06:30 aggrflowsv4#P#p1.ibd
-rw-r----- 1 mysql mysql 2.8G Mar  9 06:30 aggrflowsv4#P#p2.ibd
-rw-r----- 1 mysql mysql 2.8G Mar  9 06:30 aggrflowsv4#P#p3.ibd
-rw-r----- 1 mysql mysql 2.8G Mar  9 06:30 aggrflowsv4#P#p4.ibd
-rw-r----- 1 mysql mysql 2.8G Mar  9 06:30 aggrflowsv4#P#p5.ibd
-rw-r----- 1 mysql mysql 2.8G Mar  9 06:30 aggrflowsv4#P#p6.ibd
-rw-r----- 1 mysql mysql 2.8G Mar  9 06:30 aggrflowsv4#P#p7.ibd
-rw-r----- 1 mysql mysql 176K Jan 15 15:45 aggrflowsv6#P#p0.ibd
-rw-r----- 1 mysql mysql 176K Jan 15 15:45 aggrflowsv6#P#p1.ibd
-rw-r----- 1 mysql mysql 176K Jan 15 15:45 aggrflowsv6#P#p2.ibd
-rw-r----- 1 mysql mysql 176K Jan 15 15:45 aggrflowsv6#P#p3.ibd
-rw-r----- 1 mysql mysql 176K Jan 15 15:45 aggrflowsv6#P#p4.ibd
-rw-r----- 1 mysql mysql 176K Jan 15 15:45 aggrflowsv6#P#p5.ibd
-rw-r----- 1 mysql mysql 176K Jan 15 15:45 aggrflowsv6#P#p6.ibd
-rw-r----- 1 mysql mysql 176K Jan 15 15:45 aggrflowsv6#P#p7.ibd
-rw-r----- 1 mysql mysql  12G Mar  9 06:35 flowsv4#P#p0.ibd
-rw-r----- 1 mysql mysql  12G Mar  9 06:35 flowsv4#P#p1.ibd
-rw-r----- 1 mysql mysql  12G Mar  9 06:35 flowsv4#P#p2.ibd
-rw-r----- 1 mysql mysql  12G Mar  9 06:35 flowsv4#P#p3.ibd
-rw-r----- 1 mysql mysql  12G Mar  9 06:35 flowsv4#P#p4.ibd
-rw-r----- 1 mysql mysql  12G Mar  9 06:35 flowsv4#P#p5.ibd
-rw-r----- 1 mysql mysql  12G Mar  9 06:35 flowsv4#P#p6.ibd
-rw-r----- 1 mysql mysql  12G Mar  9 06:35 flowsv4#P#p7.ibd
-rw-r----- 1 mysql mysql 176K Mar  2 22:54 flowsv6#P#p0.ibd
-rw-r----- 1 mysql mysql 176K Jan 15 15:45 flowsv6#P#p1.ibd
-rw-r----- 1 mysql mysql 176K Jan 15 15:45 flowsv6#P#p2.ibd
-rw-r----- 1 mysql mysql 176K Jan 15 15:45 flowsv6#P#p3.ibd
-rw-r----- 1 mysql mysql 176K Jan 15 15:45 flowsv6#P#p4.ibd
-rw-r----- 1 mysql mysql 176K Jan 15 15:45 flowsv6#P#p5.ibd
-rw-r----- 1 mysql mysql 176K Jan 15 15:45 flowsv6#P#p6.ibd
-rw-r----- 1 mysql mysql 176K Jan 15 15:45 flowsv6#P#p7.ibd

Best regards,
Dan

On 09-Mar-19 03:48, Bruce Ferrell wrote:
> On 3/8/19 11:50 AM, Dan Craciun wrote:
>> Hi,
>>
>> I've setup ntopng to dump flow data in a MySQL db, with 30 days
>> retention.
>> It's been over 45 days and the data is still there.
>>
>> Furthermore, even if I manually delete the old data, the space occupied
>> is not freed. It grows at a rate of 3GB/day.
>>
>> What is the correct procedure of reusing free space when using MySQL
>> storage?
>>
>> Best regards,
>> Dan Craciun
>
> What you're describing suggests the mysql DB was configured with
> myisam tables spaces for the storage.  It's a pretty common default.
>
> Have a look at the optimize command and perhaps look at converting the
> table space to innodb with file-per-table. To reconfigure for
> innodb-file-per-table, the db has to be restarted and this should be
> done before converting the tables to innodb. Once to make this change,
> the myisam to innodb conversion can be done live.
>
> You might also want to look up mysqltuner.  It's a perl script that
> can do some crude analysis of your mysql instances performance.  It's
> actually fairly useful.
>
>
> _______________________________________________
> Ntop mailing list
> Ntop@listgateway.unipi.it
> http://listgateway.unipi.it/mailman/listinfo/ntop

_______________________________________________
Ntop mailing list
Ntop@listgateway.unipi.it
http://listgateway.unipi.it/mailman/listinfo/ntop
Re: limit mysql storage [ In reply to ]
That being the case, take a row count before pruning and again after pruning.

    select count(*) from <table name>

The row count SHOULD drop after pruning.  The space used by the deleted rows will be filled by new rows as they are stored... That's what "the book" says anyway.



On 3/8/19 8:49 PM, Dan Craciun wrote:
> Actually, I haven't configured anything, just started ntopng with the -F
> option and it created the tables.
> They are innodb.
>
> ls -lh
> total 117G
> -rw-r----- 1 mysql mysql 2.8G Mar  9 06:30 aggrflowsv4#P#p0.ibd
> -rw-r----- 1 mysql mysql 2.8G Mar  9 06:30 aggrflowsv4#P#p1.ibd
> -rw-r----- 1 mysql mysql 2.8G Mar  9 06:30 aggrflowsv4#P#p2.ibd
> -rw-r----- 1 mysql mysql 2.8G Mar  9 06:30 aggrflowsv4#P#p3.ibd
> -rw-r----- 1 mysql mysql 2.8G Mar  9 06:30 aggrflowsv4#P#p4.ibd
> -rw-r----- 1 mysql mysql 2.8G Mar  9 06:30 aggrflowsv4#P#p5.ibd
> -rw-r----- 1 mysql mysql 2.8G Mar  9 06:30 aggrflowsv4#P#p6.ibd
> -rw-r----- 1 mysql mysql 2.8G Mar  9 06:30 aggrflowsv4#P#p7.ibd
> -rw-r----- 1 mysql mysql 176K Jan 15 15:45 aggrflowsv6#P#p0.ibd
> -rw-r----- 1 mysql mysql 176K Jan 15 15:45 aggrflowsv6#P#p1.ibd
> -rw-r----- 1 mysql mysql 176K Jan 15 15:45 aggrflowsv6#P#p2.ibd
> -rw-r----- 1 mysql mysql 176K Jan 15 15:45 aggrflowsv6#P#p3.ibd
> -rw-r----- 1 mysql mysql 176K Jan 15 15:45 aggrflowsv6#P#p4.ibd
> -rw-r----- 1 mysql mysql 176K Jan 15 15:45 aggrflowsv6#P#p5.ibd
> -rw-r----- 1 mysql mysql 176K Jan 15 15:45 aggrflowsv6#P#p6.ibd
> -rw-r----- 1 mysql mysql 176K Jan 15 15:45 aggrflowsv6#P#p7.ibd
> -rw-r----- 1 mysql mysql  12G Mar  9 06:35 flowsv4#P#p0.ibd
> -rw-r----- 1 mysql mysql  12G Mar  9 06:35 flowsv4#P#p1.ibd
> -rw-r----- 1 mysql mysql  12G Mar  9 06:35 flowsv4#P#p2.ibd
> -rw-r----- 1 mysql mysql  12G Mar  9 06:35 flowsv4#P#p3.ibd
> -rw-r----- 1 mysql mysql  12G Mar  9 06:35 flowsv4#P#p4.ibd
> -rw-r----- 1 mysql mysql  12G Mar  9 06:35 flowsv4#P#p5.ibd
> -rw-r----- 1 mysql mysql  12G Mar  9 06:35 flowsv4#P#p6.ibd
> -rw-r----- 1 mysql mysql  12G Mar  9 06:35 flowsv4#P#p7.ibd
> -rw-r----- 1 mysql mysql 176K Mar  2 22:54 flowsv6#P#p0.ibd
> -rw-r----- 1 mysql mysql 176K Jan 15 15:45 flowsv6#P#p1.ibd
> -rw-r----- 1 mysql mysql 176K Jan 15 15:45 flowsv6#P#p2.ibd
> -rw-r----- 1 mysql mysql 176K Jan 15 15:45 flowsv6#P#p3.ibd
> -rw-r----- 1 mysql mysql 176K Jan 15 15:45 flowsv6#P#p4.ibd
> -rw-r----- 1 mysql mysql 176K Jan 15 15:45 flowsv6#P#p5.ibd
> -rw-r----- 1 mysql mysql 176K Jan 15 15:45 flowsv6#P#p6.ibd
> -rw-r----- 1 mysql mysql 176K Jan 15 15:45 flowsv6#P#p7.ibd
>
> Best regards,
> Dan
>
> On 09-Mar-19 03:48, Bruce Ferrell wrote:
>> On 3/8/19 11:50 AM, Dan Craciun wrote:
>>> Hi,
>>>
>>> I've setup ntopng to dump flow data in a MySQL db, with 30 days
>>> retention.
>>> It's been over 45 days and the data is still there.
>>>
>>> Furthermore, even if I manually delete the old data, the space occupied
>>> is not freed. It grows at a rate of 3GB/day.
>>>
>>> What is the correct procedure of reusing free space when using MySQL
>>> storage?
>>>
>>> Best regards,
>>> Dan Craciun
>> What you're describing suggests the mysql DB was configured with
>> myisam tables spaces for the storage.  It's a pretty common default.
>>
>> Have a look at the optimize command and perhaps look at converting the
>> table space to innodb with file-per-table. To reconfigure for
>> innodb-file-per-table, the db has to be restarted and this should be
>> done before converting the tables to innodb. Once to make this change,
>> the myisam to innodb conversion can be done live.
>>
>> You might also want to look up mysqltuner.  It's a perl script that
>> can do some crude analysis of your mysql instances performance.  It's
>> actually fairly useful.
>>
>>

_______________________________________________
Ntop mailing list
Ntop@listgateway.unipi.it
http://listgateway.unipi.it/mailman/listinfo/ntop
Re: limit mysql storage [ In reply to ]
Yes, the row count is dropping. I guess I can make a script that deletes
everything older than 30 days and run it daily with cron.

But then, what is the purpose of the retention days field in the ntopng
interface?
It's something that's on the to do list, or simply ornamental?



Best regards,
Dan Craciun

On 09-Mar-19 10:05, Bruce Ferrell wrote:
>
> That being the case, take a row count before pruning and again after
> pruning.
>
>     select count(*) from <table name>
>
> The row count SHOULD drop after pruning.  The space used by the
> deleted rows will be filled by new rows as they are stored... That's
> what "the book" says anyway.
>
>
>
> On 3/8/19 8:49 PM, Dan Craciun wrote:
>> Actually, I haven't configured anything, just started ntopng with the -F
>> option and it created the tables.
>> They are innodb.
>>
>> ls -lh
>> total 117G
>> -rw-r----- 1 mysql mysql 2.8G Mar  9 06:30 aggrflowsv4#P#p0.ibd
>> -rw-r----- 1 mysql mysql 2.8G Mar  9 06:30 aggrflowsv4#P#p1.ibd
>> -rw-r----- 1 mysql mysql 2.8G Mar  9 06:30 aggrflowsv4#P#p2.ibd
>> -rw-r----- 1 mysql mysql 2.8G Mar  9 06:30 aggrflowsv4#P#p3.ibd
>> -rw-r----- 1 mysql mysql 2.8G Mar  9 06:30 aggrflowsv4#P#p4.ibd
>> -rw-r----- 1 mysql mysql 2.8G Mar  9 06:30 aggrflowsv4#P#p5.ibd
>> -rw-r----- 1 mysql mysql 2.8G Mar  9 06:30 aggrflowsv4#P#p6.ibd
>> -rw-r----- 1 mysql mysql 2.8G Mar  9 06:30 aggrflowsv4#P#p7.ibd
>> -rw-r----- 1 mysql mysql 176K Jan 15 15:45 aggrflowsv6#P#p0.ibd
>> -rw-r----- 1 mysql mysql 176K Jan 15 15:45 aggrflowsv6#P#p1.ibd
>> -rw-r----- 1 mysql mysql 176K Jan 15 15:45 aggrflowsv6#P#p2.ibd
>> -rw-r----- 1 mysql mysql 176K Jan 15 15:45 aggrflowsv6#P#p3.ibd
>> -rw-r----- 1 mysql mysql 176K Jan 15 15:45 aggrflowsv6#P#p4.ibd
>> -rw-r----- 1 mysql mysql 176K Jan 15 15:45 aggrflowsv6#P#p5.ibd
>> -rw-r----- 1 mysql mysql 176K Jan 15 15:45 aggrflowsv6#P#p6.ibd
>> -rw-r----- 1 mysql mysql 176K Jan 15 15:45 aggrflowsv6#P#p7.ibd
>> -rw-r----- 1 mysql mysql  12G Mar  9 06:35 flowsv4#P#p0.ibd
>> -rw-r----- 1 mysql mysql  12G Mar  9 06:35 flowsv4#P#p1.ibd
>> -rw-r----- 1 mysql mysql  12G Mar  9 06:35 flowsv4#P#p2.ibd
>> -rw-r----- 1 mysql mysql  12G Mar  9 06:35 flowsv4#P#p3.ibd
>> -rw-r----- 1 mysql mysql  12G Mar  9 06:35 flowsv4#P#p4.ibd
>> -rw-r----- 1 mysql mysql  12G Mar  9 06:35 flowsv4#P#p5.ibd
>> -rw-r----- 1 mysql mysql  12G Mar  9 06:35 flowsv4#P#p6.ibd
>> -rw-r----- 1 mysql mysql  12G Mar  9 06:35 flowsv4#P#p7.ibd
>> -rw-r----- 1 mysql mysql 176K Mar  2 22:54 flowsv6#P#p0.ibd
>> -rw-r----- 1 mysql mysql 176K Jan 15 15:45 flowsv6#P#p1.ibd
>> -rw-r----- 1 mysql mysql 176K Jan 15 15:45 flowsv6#P#p2.ibd
>> -rw-r----- 1 mysql mysql 176K Jan 15 15:45 flowsv6#P#p3.ibd
>> -rw-r----- 1 mysql mysql 176K Jan 15 15:45 flowsv6#P#p4.ibd
>> -rw-r----- 1 mysql mysql 176K Jan 15 15:45 flowsv6#P#p5.ibd
>> -rw-r----- 1 mysql mysql 176K Jan 15 15:45 flowsv6#P#p6.ibd
>> -rw-r----- 1 mysql mysql 176K Jan 15 15:45 flowsv6#P#p7.ibd
>>
>> Best regards,
>> Dan
>>
>> On 09-Mar-19 03:48, Bruce Ferrell wrote:
>>> On 3/8/19 11:50 AM, Dan Craciun wrote:
>>>> Hi,
>>>>
>>>> I've setup ntopng to dump flow data in a MySQL db, with 30 days
>>>> retention.
>>>> It's been over 45 days and the data is still there.
>>>>
>>>> Furthermore, even if I manually delete the old data, the space
>>>> occupied
>>>> is not freed. It grows at a rate of 3GB/day.
>>>>
>>>> What is the correct procedure of reusing free space when using MySQL
>>>> storage?
>>>>
>>>> Best regards,
>>>> Dan Craciun
>>> What you're describing suggests the mysql DB was configured with
>>> myisam tables spaces for the storage.  It's a pretty common default.
>>>
>>> Have a look at the optimize command and perhaps look at converting the
>>> table space to innodb with file-per-table. To reconfigure for
>>> innodb-file-per-table, the db has to be restarted and this should be
>>> done before converting the tables to innodb. Once to make this change,
>>> the myisam to innodb conversion can be done live.
>>>
>>> You might also want to look up mysqltuner.  It's a perl script that
>>> can do some crude analysis of your mysql instances performance.  It's
>>> actually fairly useful.
>>>
>>>
>
> _______________________________________________
> Ntop mailing list
> Ntop@listgateway.unipi.it
> http://listgateway.unipi.it/mailman/listinfo/ntop
Re: limit mysql storage [ In reply to ]
"You can use OPTIMIZE TABLE to reclaim the unused space and to defragment the data file. "

https://dev.mysql.com/doc/refman/8.0/en/optimize-table.html <https://dev.mysql.com/doc/refman/8.0/en/optimize-table.html>

Simone

> On 8 Mar 2019, at 20:50, Dan Craciun <dany.craciun@gmail.com> wrote:
>
> Hi,
>
> I've setup ntopng to dump flow data in a MySQL db, with 30 days retention.
> It's been over 45 days and the data is still there.
>
> Furthermore, even if I manually delete the old data, the space occupied
> is not freed. It grows at a rate of 3GB/day.
>
> What is the correct procedure of reusing free space when using MySQL
> storage?
>
> Best regards,
> Dan Craciun
> _______________________________________________
> Ntop mailing list
> Ntop@listgateway.unipi.it
> http://listgateway.unipi.it/mailman/listinfo/ntop
Re: limit mysql storage [ In reply to ]
"You can use OPTIMIZE TABLE to reclaim the unused space and to defragment the data file. "

https://dev.mysql.com/doc/refman/8.0/en/optimize-table.html <https://dev.mysql.com/doc/refman/8.0/en/optimize-table.html>

Simone

> On 8 Mar 2019, at 20:50, Dan Craciun <dany.craciun@gmail.com> wrote:
>
> Hi,
>
> I've setup ntopng to dump flow data in a MySQL db, with 30 days retention.
> It's been over 45 days and the data is still there.
>
> Furthermore, even if I manually delete the old data, the space occupied
> is not freed. It grows at a rate of 3GB/day.
>
> What is the correct procedure of reusing free space when using MySQL
> storage?
>
> Best regards,
> Dan Craciun
> _______________________________________________
> Ntop mailing list
> Ntop@listgateway.unipi.it
> http://listgateway.unipi.it/mailman/listinfo/ntop
Re: limit mysql storage [ In reply to ]
Thank you Simone.

I figured out how to get the space back.

The question is why doesn't ntopng delete the old data?
The "Duration in days of data retention" doesn't do anything?

Best regards,
Dan

On 11-Mar-19 16:35, Simone Mainardi wrote:
> "You can use?OPTIMIZE TABLE?to reclaim the unused space and to
> defragment the data file. "
>
> https://dev.mysql.com/doc/refman/8.0/en/optimize-table.html
>
> Simone
>
>> On 8 Mar 2019, at 20:50, Dan Craciun <dany.craciun@gmail.com
>> <mailto:dany.craciun@gmail.com>> wrote:
>>
>> Hi,
>>
>> I've setup ntopng to dump flow data in a MySQL db, with 30 days
>> retention.
>> It's been over 45 days and the data is still there.
>>
>> Furthermore, even if I manually delete the old data, the space occupied
>> is not freed. It grows at a rate of 3GB/day.
>>
>> What is the correct procedure of reusing free space when using MySQL
>> storage?
>>
>> Best regards,
>> Dan Craciun
>> _______________________________________________
>> Ntop mailing list
>> Ntop@listgateway.unipi.it <mailto:Ntop@listgateway.unipi.it>
>> http://listgateway.unipi.it/mailman/listinfo/ntop
>
>
> _______________________________________________
> Ntop mailing list
> Ntop@listgateway.unipi.it
> http://listgateway.unipi.it/mailman/listinfo/ntop
Re: limit mysql storage [ In reply to ]
Dan,

> On 11 Mar 2019, at 19:48, Dan Craciun <dany.craciun@gmail.com> wrote:
>
> Thank you Simone.
>
> I figured out how to get the space back.
>
> The question is why doesn't ntopng delete the old data?
> The "Duration in days of data retention" doesn't do anything?

It does the delete - you won't be able to find records older than the retention if you query the db - but deleting old records doesn't automatically translate into mysql recycling unused space.

Anyway, we have seen several shortcomings of MySQL when it comes to store flows and decided to build a special-purpose database nIndex. I would encourage you to try it out: https://www.ntop.org/ntopng/say-hello-to-nindex-personal-big-data-system-for-network-flows/

Simone

>
> Best regards,
> Dan
>
> On 11-Mar-19 16:35, Simone Mainardi wrote:
>> "You can use OPTIMIZE TABLE to reclaim the unused space and to defragment the data file. "
>>
>> https://dev.mysql.com/doc/refman/8.0/en/optimize-table.html <https://dev.mysql.com/doc/refman/8.0/en/optimize-table.html>
>>
>> Simone
>>
>>> On 8 Mar 2019, at 20:50, Dan Craciun <dany.craciun@gmail.com <mailto:dany.craciun@gmail.com>> wrote:
>>>
>>> Hi,
>>>
>>> I've setup ntopng to dump flow data in a MySQL db, with 30 days retention.
>>> It's been over 45 days and the data is still there.
>>>
>>> Furthermore, even if I manually delete the old data, the space occupied
>>> is not freed. It grows at a rate of 3GB/day.
>>>
>>> What is the correct procedure of reusing free space when using MySQL
>>> storage?
>>>
>>> Best regards,
>>> Dan Craciun
>>> _______________________________________________
>>> Ntop mailing list
>>> Ntop@listgateway.unipi.it <mailto:Ntop@listgateway.unipi.it>
>>> http://listgateway.unipi.it/mailman/listinfo/ntop <http://listgateway.unipi.it/mailman/listinfo/ntop>
>>
>>
>>
>> _______________________________________________
>> Ntop mailing list
>> Ntop@listgateway.unipi.it <mailto:Ntop@listgateway.unipi.it>
>> http://listgateway.unipi.it/mailman/listinfo/ntop <http://listgateway.unipi.it/mailman/listinfo/ntop>
> _______________________________________________
> Ntop mailing list
> Ntop@listgateway.unipi.it
> http://listgateway.unipi.it/mailman/listinfo/ntop
Re: limit mysql storage [ In reply to ]
Dan,

> On 11 Mar 2019, at 19:48, Dan Craciun <dany.craciun@gmail.com> wrote:
>
> Thank you Simone.
>
> I figured out how to get the space back.
>
> The question is why doesn't ntopng delete the old data?
> The "Duration in days of data retention" doesn't do anything?

It does the delete - you won't be able to find records older than the retention if you query the db - but deleting old records doesn't automatically translate into mysql recycling unused space.

Anyway, we have seen several shortcomings of MySQL when it comes to store flows and decided to build a special-purpose database nIndex. I would encourage you to try it out: https://www.ntop.org/ntopng/say-hello-to-nindex-personal-big-data-system-for-network-flows/

Simone

>
> Best regards,
> Dan
>
> On 11-Mar-19 16:35, Simone Mainardi wrote:
>> "You can use OPTIMIZE TABLE to reclaim the unused space and to defragment the data file. "
>>
>> https://dev.mysql.com/doc/refman/8.0/en/optimize-table.html <https://dev.mysql.com/doc/refman/8.0/en/optimize-table.html>
>>
>> Simone
>>
>>> On 8 Mar 2019, at 20:50, Dan Craciun <dany.craciun@gmail.com <mailto:dany.craciun@gmail.com>> wrote:
>>>
>>> Hi,
>>>
>>> I've setup ntopng to dump flow data in a MySQL db, with 30 days retention.
>>> It's been over 45 days and the data is still there.
>>>
>>> Furthermore, even if I manually delete the old data, the space occupied
>>> is not freed. It grows at a rate of 3GB/day.
>>>
>>> What is the correct procedure of reusing free space when using MySQL
>>> storage?
>>>
>>> Best regards,
>>> Dan Craciun
>>> _______________________________________________
>>> Ntop mailing list
>>> Ntop@listgateway.unipi.it <mailto:Ntop@listgateway.unipi.it>
>>> http://listgateway.unipi.it/mailman/listinfo/ntop <http://listgateway.unipi.it/mailman/listinfo/ntop>
>>
>>
>>
>> _______________________________________________
>> Ntop mailing list
>> Ntop@listgateway.unipi.it <mailto:Ntop@listgateway.unipi.it>
>> http://listgateway.unipi.it/mailman/listinfo/ntop <http://listgateway.unipi.it/mailman/listinfo/ntop>
> _______________________________________________
> Ntop mailing list
> Ntop@listgateway.unipi.it
> http://listgateway.unipi.it/mailman/listinfo/ntop
Re: limit mysql storage [ In reply to ]
On 12-Mar-19 10:47, Simone Mainardi wrote:
> It does the delete - you won't be able to find records older than the
> retention if you query the db - but deleting old records doesn't
> automatically translate into mysql recycling unused space.
I'm sorry, but that's not true.
Before I started deleting manually, on March 9, I had data from January
15th. That's the day I started using MySQl for storing flows. I don't
think any record was actually deleted.

I manually deleted data older than February 1st. Today is March 12:

select count(*) from flowsv4 where FIRST_SWITCHED < 1548979200 (Feb 1st)
Result: 0

select count(*) from flowsv4 where FIRST_SWITCHED < 1549324800 (Feb 5th)
Result: 17161146

I have 1.7M flows between Feb 1st and Feb 4th. I wasnt't supposed to
have anything older than Feb 10, right?

Best regards,
Dan Craciun
_______________________________________________
Ntop mailing list
Ntop@listgateway.unipi.it
http://listgateway.unipi.it/mailman/listinfo/ntop
Re: limit mysql storage [ In reply to ]
Dan,

> On 12 Mar 2019, at 10:08, Dan Craciun <dany.craciun@gmail.com> wrote:
>
> On 12-Mar-19 10:47, Simone Mainardi wrote:
>> It does the delete - you won't be able to find records older than the
>> retention if you query the db - but deleting old records doesn't
>> automatically translate into mysql recycling unused space.
> I'm sorry, but that's not true.
> Before I started deleting manually, on March 9, I had data from January
> 15th. That's the day I started using MySQl for storing flows. I don't
> think any record was actually deleted.
>
> I manually deleted data older than February 1st. Today is March 12:
>
> select count(*) from flowsv4 where FIRST_SWITCHED < 1548979200 (Feb 1st)
> Result: 0
>
> select count(*) from flowsv4 where FIRST_SWITCHED < 1549324800 (Feb 5th)
> Result: 17161146
>
> I have 1.7M flows between Feb 1st and Feb 4th. I wasnt't supposed to
> have anything older than Feb 10, right?

ntopng is supposed to delete old data every day ad midnight local time. If you think it's a bug feel free to file an issue on GitHub.

>
> Best regards,
> Dan Craciun
> _______________________________________________
> Ntop mailing list
> Ntop@listgateway.unipi.it
> http://listgateway.unipi.it/mailman/listinfo/ntop

_______________________________________________
Ntop mailing list
Ntop@listgateway.unipi.it
http://listgateway.unipi.it/mailman/listinfo/ntop
Re: limit mysql storage [ In reply to ]
Dan,

> On 12 Mar 2019, at 10:08, Dan Craciun <dany.craciun@gmail.com> wrote:
>
> On 12-Mar-19 10:47, Simone Mainardi wrote:
>> It does the delete - you won't be able to find records older than the
>> retention if you query the db - but deleting old records doesn't
>> automatically translate into mysql recycling unused space.
> I'm sorry, but that's not true.
> Before I started deleting manually, on March 9, I had data from January
> 15th. That's the day I started using MySQl for storing flows. I don't
> think any record was actually deleted.
>
> I manually deleted data older than February 1st. Today is March 12:
>
> select count(*) from flowsv4 where FIRST_SWITCHED < 1548979200 (Feb 1st)
> Result: 0
>
> select count(*) from flowsv4 where FIRST_SWITCHED < 1549324800 (Feb 5th)
> Result: 17161146
>
> I have 1.7M flows between Feb 1st and Feb 4th. I wasnt't supposed to
> have anything older than Feb 10, right?

ntopng is supposed to delete old data every day ad midnight local time. If you think it's a bug feel free to file an issue on GitHub.

>
> Best regards,
> Dan Craciun
> _______________________________________________
> Ntop mailing list
> Ntop@listgateway.unipi.it
> http://listgateway.unipi.it/mailman/listinfo/ntop

_______________________________________________
Ntop mailing list
Ntop@listgateway.unipi.it
http://listgateway.unipi.it/mailman/listinfo/ntop