Mysql innodb ibdata1 filesize

[ii silver ii]

For some time I've had a database that's been expanding slowly, it makes use of primary keys, foreign keys etc so it's vital to use Innodb table type.

However the ibdata1 size is forever increasing and it's got to a size (1.4gb~) that starts to 'make bells ring'.

I've seen a few 'solutions' floating around but not too sure the best, at the moment the current filesize is a manageable size and I've seen that you can't reduce the ibdata1 size without basically recreating the database tables? but I've seen you can limit the size of ibdata1?

If you applied a my.conf limit to the ibdata1 size and it's reached then does the database just change the innards of ibdata1 instead of adding to it or is this a bad solution to the issue? or maybe I'm missing some of the problem?

Thanks!

 

[mattseh]

are you removing any data from the database? If not, it should be reasonably space efficient. The keys could be taking up a lot of the space.

 

[ii silver ii]

Nop no data is being removed, but there's no currently set for the filesize so I'm looking for the best way to prevent it from becoming huge?

 

[mattseh]

I assume you meant to say "there is no limit currently set", you can try adjusting column types if you are using wasteful types, eg a string for a small int, but ultimately the more data you add, the more space you have to use.

Maybe Save disk space by compressing MySQL tables | TechRepublic ?