I manage a large 8 slave ~200GB Percona database and am always looking at ways to tweak and improve performance. I saw Peter Zaitsev’s talk (from Percona Live London December 2012 available at http://www.percona.com/live/london-2012/sessions/optimizing-mysql-configuration) about MySQL/Percona configuration.
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:
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?