[TYPO3-dev] innoDB filesize nightmare

[Steffen Kamper]

Hi,

i was very astonished looking at the filesizes of file ibdata1 in mysql-dir.
I have a "fresh" Rootserver with 2 TYPO3-productive sites. After 5 days i 
had 7.5 GB innoDB-data.
MySQL stopped because the size was limited to 2GB. No i increased to 4GB. I 
really had to investigate whats going on.

99% of this size was from only 2 pages (x 2 DB's): cache_hash and 
cache_pages (and file ibdata1).
I set innodb_file_per_table = 1, but ibdata1 was still used from one DB. So 
i removed both tables and created again - no changes with ibdata1.

Looking to the filesize of the other DB, after truncate both tables 
increased in 1 minute to 10 and 12 MB - thats a horrible amount of size.

If i don't want to have a serverfarm with HDs for cash :-) i read in 
documentation [1] and the only solution i found is doing the procedure 
described in [2], may be as cronjob.

So this brings me to some questions:

1) What happens after a month - data doesn't decrease with truncate? MySQL 
stopps when reaching next size limit ?
2) What do you do to prevent this ?
3) What can be done not to write so much cache data. Though data is cached 
it must be perfomance killing getting data out of a 2GB table.

[1] http://dev.mysql.com/doc/refman/5.0/en/adding-and-removing.html
[2] http://www.saturn.in/gpl/mysql.html

vg  Steffen