The MYSQL database (ibdata1) is of size 73 GB and is configured to run as a dedicated database server on Windows 2008 O/S for INNODB tables. We are running the backup using mysqldump mysqldump --skip-opt --quick --single-transaction --create-options --extended-insert --disable-keys --add-drop-table --complete-insert --set-charset -- compress --log-error=Proddb0635.err -u root -pjohndoe Proddb> \devNas\devNas\sqlbackup\LIVE\db\Proddb0635.sql
The backup file Proddb0635.sql is stored on a separate server from the database server. RAM is 12 GB. INNODB Buffer Pool size is 6 GB. Additional mem.pool is 32 MB. Query Cache size is 2 GB Net buffer length is 16 M Max. packet size 1 GB.
mysql version is 5.0.67.
When backup is not running users are happy with the performance.
When backup is running INNODB Buffer pool hit rate is high close to 100%. There are no pending reads or pending writes. innodb wait free is 0. CPU usage is not high min 9% to max 15 % Query cache hit rate is low about 40% with or without mysqlbackup running. Currently Windows Task Manager is displaying that 10GB of RAM is being used. Should I increase Query Cache with only 2GB of RAM available? mysqlld-nt is taking 9.2 GB of RAM and mysqldump is taking 5 MB of RAM. Alos, noted that the size of the dump file is same in presence or absence of --compress option.
SHould I decrease the iNNODB Buffer Pool size?