Specifically talking about the innodb_data_file_path configuration option, which controls the size of the ibdataX files, I can remember from the talk Peter mentioned that a max file size of 8GB should be plenty, and the slideshow (slide 36) lists: innodb_data_file_path=ibdata1:10M:autoextend:max:10G
Now, getting back to the other problem, I’ve got a few of our slaves with ibdata1 file sizes of 500MB-2GB (i.e no where near the 8 or 10GB limit), but then two other slaves which have both dropped offline with table full errors, until I increased the limit, or removed it completely. One slave seemed to have a long running query that we’d missed that was never going to finish (and would have had a large temporary table which may have filled the table space) yet the other server exhibited no such issue.
As a general rule of thumb, I’d like to keep this small, as we use innodb_file_per_table and means we don’t have to worry about one large central tablespace file which never shrinks, but can anyone think of a reason why this may have grown so much, and why different amounts on different servers despite being identical slaves?
What setting does anyone/everyone else use for the innodb_data_file_path configuration option?
Generally, mostly users keep it mysql datadir. The default behavior is single 10MB file auto-extended, it’s good to place an upper limit on tablespace size to prevent it from growing huge because it doesn’t shrink once it grow (unless you dump/restore to reclaim space).
innodb_file_per_table option lets you configure InnoDB to use separate tablespace for each table, this make it easier to reclaim disk space, you can use Xtrabackup Partial backups and many more.
As far as slaves having different size of tablespace is concerns, might be slaves has different settings for tablespace, long running transaction which is not committed, replicatoin filters might be one of the reason means every slave might not get same no. of write etc.
I usually do not worry about setting a MAX size for the ibdata1 file. With file per table enabled, the size of ibdata1 should normally not be an issue unless you are running a super lean system.
As to why your shared ibdata1 file is still growing even with file per table enabled, the culprit is usually the undo log (rollback segment). If you do large transactions, this can increase the size of your ibdata1 file as the system stores the necessary information to roll back a transaction if need be and for other transactions to view unmodified data depending on the transaction isolation level the server is using.
Scott, that probably answers the issue I’m having, I suspect. I’m going to implement some further checks to spot queries/transactions that may be causing this issue.