> Hello James, > > It looks like you have found the most important things: turn on > attribute spooling,
This actually made it worse. Instead of distributing the tiny little writes throughout the backup job, it saved them all to the end when nothing else was running. It would be nice if the next job could start while attributes were still spooling... > and switch to InnoDB. I switch to InnoDB last time this happened. > Before switching to PostgreSQL, you might try running > the MySQL tunning program mysqltune (I forgot the exact name). It tells > you > items you should tune. Often users do not give it enough memory, or even > do the opposite which can cause problems, that is give it too much. > > There are several scripts that switch from MySQL to PostgreSQL that work > fine. They also allow you to keep your MySQL running until you get a good > config for PostgreSQL. Tunning PostgreSQL is much more complicated, but > it gives *far* better results for big jobs. > When loading the data into postgresql absolutely crawled along (~50kb/second disk write speed with 100% iowait) I knew I had a problem. Something, somewhere has changed in my system that absolutely kills tiny sync writes. Or alternatively, something has changed in my system that makes mysql do tiny sync writes. iostat showed ~50kb/second with 100% iowait while loading the catalogs, and nothing I did changed this. The following dd also behaved appallingly: # dd if=/dev/zero of=test.bin bs=512 count=1024 oflag=sync 1024+0 records in 1024+0 records out 524288 bytes (524 kB) copied, 56.2482 s, 9.3 kB/s While on an old system (~10 years old) with a single ATA/100 harddisk: # dd if=/dev/zero of=test.bin bs=512 count=1024 oflag=dsync 1024+0 records in 1024+0 records out 524288 bytes (524 kB) copied, 1.10022 seconds, 477 kB/s I'm working on trying to track down wtf is going on there, but in the meantime I have set innodb_flush_log_at_trx_commit=0 which means it won't run an fsync after each tiny little write but will instead wait for around a second then flush everything. This means I stand to lose 1 second of database commit in the event of a crash, but I also probably lose the whole backup job anyway so I don't see it as a loss. Performance is now back to normal and I can take my time figuring out why this happened. Thanks James ------------------------------------------------------------------------------ Try New Relic Now & We'll Send You this Cool Shirt New Relic is the only SaaS-based application performance monitoring service that delivers powerful full stack analytics. Optimize and monitor your browser, app, & servers with just a few lines of code. Try New Relic and get this awesome Nerd Life shirt! http://p.sf.net/sfu/newrelic_d2d_apr _______________________________________________ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
