Re: problems with growing database

what you are talking about is truncating your table space
PostgreSQL uses a "FULL VACUUM" of the of the database to do this
I did a quick search of the MySQL site and it seems as though there is
no method to do this for innodb
here is the only thing i could find on the subject

http://www.mysql.com/news-and-events/newsletter/2003-05/a0000000170.html


it basically agrees with what you are doing with one exception dump
your database first then stop it delete the file restart the database
and restore the dump.



On Jan 31, 2008 12:36 PM, Paulo Ferreira <[email protected]> wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Dear all,
> I have an issue regarding disk space occupied by the prelude database.
>
> When prelude manager is receiving alerts from several sensors, the file
> regarding innodb data (ibdata1) grows very fast. Because of this growing
> innodb file, my disk is almost full, and when I try to get back such
> space, I must delete the database and the innodb file and start over again.
>
> I already tried to use preludedb-admin application and on the first time
> I used it, the alerts that I wanted to delete were deleted, but the
> innodb file instead of decrease, the file simply grew again.
>
> Is it possible to get back such disk space by deleting alerts, or by
> using other procedure, without lose all Alerts??
>
> I used the following syntax to delete my alerts:
>
> preludedb-admin delete alert "type=mysql name=XXXXX user=XXXXX
> pass=XXXXX" --criteria "alert.create_time < 2008-01-31T13:00:00"
>
> 20597 'delete' events processed in 287.225360 seconds (0.013945
> seconds/events - 71.710242 delete/sec average).
> 20597 events processed in 287.225360 seconds (0.013945 seconds/events -
> 71.710242 events/sec average).
>
> As you can see, the innodb file before and after I have run the
> prelude-admin.
>
> Before using preludedb-admin:
> total 797M
> - -rw-rw---- 1 mysql mysql 786M Jan 31 13:39 ibdata1
> - -rw-rw---- 1 mysql mysql 5.0M Jan 31 13:39 ib_logfile0
> - -rw-rw---- 1 mysql mysql 5.0M Jan 31 13:36 ib_logfile1
>
>
> After using preludedb-admin:
> total 869M
> - -rw-rw---- 1 mysql mysql 858M Jan 31 13:54 ibdata1
> - -rw-rw---- 1 mysql mysql 5.0M Jan 31 13:54 ib_logfile0
> - -rw-rw---- 1 mysql mysql 5.0M Jan 31 13:54 ib_logfile1
>
> I also noticed that in very very large databases (not this one), the
> usage of preludedb-admin as I presented above, is not easy, or in other
> words, almost impossible.
>
> Thanks in advance, very best regards,
> Paulo Ferreira
>
> - --
> - -------------------------------------------
> Paulo Ferreira
> FCCN
> Av. do Brasil, n.º 101
> 1700-066 Lisboa
> Portugal
> Tel.: +351 21 8440100
> Fax.: +351 21 8472167
> http://www.fccn.pt/
>
> Aviso de Confidencialidade
> Esta mensagem é exclusivamente destinada ao seu destinatário, podendo
> conter informação CONFIDENCIAL, cuja divulgação está expressamente
> vedada nos termos da lei. Caso tenha recepcionado indevidamente esta
> mensagem,solicitamos-lhe que nos comunique esse mesmo facto por esta via
> ou para o telefone +351 218440100 devendo apagar o seu conteúdo de
> imediato.
>
> Disclaimer
> This message is intended exclusively for its addressee. It may contain
> CONFIDENTIAL information protected by law. If this message has been
> received by error, please notify us via e-mail or by telephone +351
> 218440100 and delete it immediately.
>
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.5 (MingW32)
> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
>
> iD8DBQFHogcMzlEr5GDkYkwRApIkAJ4laWBH91Zq9vJI0Dk4+BUNyapzZgCgycI3
> xpN/upkhx2ZUATYCkuSXvcY=
> =gXPe
> -----END PGP SIGNATURE-----
> _______________________________________________
> Prelude-user site list
> [email protected]
> http://www.prelude-ids.org/mailman/listinfo/prelude-user
>
_______________________________________________
Prelude-user site list
[email protected]
http://www.prelude-ids.org/mailman/listinfo/prelude-user