We've got a problem - during a backup through virtual min, our memory usage spikes MASSIVELY. From ~3GB to ~10GB.
Today we changed the innodb_buffer_pool_size from 8MB to 8GB - and our reading indicates this is directly the problem.
Supposedly, using mysqldump -quick fixes this as well I'm reading.
Is there a way to modify the backup commands to use this fix? Additionally is there a fix that we're missing perhaps?
The performance gains with innodb_buffer_pool_size are large, and we don't want to lose this.
Any help would be greatly appreciated!
P.S. This server has 32GB so 10GB won't sink it, but still, it seems like 7GB is just "wasted".
Hmmmm.. Isn't it kinda expected that this happens? :) I mean, when you tell MySQL "you can use 8 GB of memory for caching", and then a backup needs to fetch every record in the database, it's no wonder the cache fills up? :)
Is that a problem really, if you have 32 GB of memory? That's what the cache is for I should think.
Howdy,
Yeah it does sound like it's working as intended at the moment, and the values you're using may also be speeding up the backup process.
If you aren't running out of RAM during the backups, I'd personally be inclined to leave things as-is.
However, if you want to run mysqldump with different parameters during the backup -- you could do that by creating mysqldump wrapper.
If you create a script called, say, mysqldump_quick, which accepts parameters and passes those along to mysqldump -- and adds the "--quick" parameter to that -- you could configure Webmin to use your new script instead of mysqldump by going into Webmin -> Servers -> MySQL -> Module Config -> System Configuration, and set "Path to mysqldump command" to point to your new script.
Just make sure your script passes all the parameters it's given into mysqldump.
However, as locutus mentioned above, it seems like things are working okay, I'm not sure that I would change anything.
-Eric
well our concern is, we will be allocating that memory soon... and losing 7-8GB on "nothing" is a bit of a problem, not to mention we want the cache to speed up the sites for visitors, not necessarily for speeding up backups :)
appreciate the tips!
I can see the second point, but not really the first. ;) For if you assign 8 GB on MySQL cache, you "lose" that anyway. If memory is going to be an issue later, you should not assign that much to the database cache in the first place.
About the second point: Well you're probably not going to be doing backups all the time, right? :) So the cache space would be "wasted" for backups only very shortly, and it'd be quickly replaced by site/visitor data again afterwards. Also, it's not a bad thing to speed up the backup, because the faster it's done, the smaller the impact on your regular site operation. :)