On 2015-12-15 04:50 PM, Lewis, Dave wrote: >> -----Original Message----- >> From: Uwe Schuerkamp [mailto:uwe.schuerk...@nionex.net] >> Sent: Tuesday, December 15, 2015 4:47 AM >> To: Lewis, Dave >> Cc: bacula-users@lists.sourceforge.net >> Subject: Re: [Bacula-users] Bacula backup speed >> > ... >> Hi Dave, >> >> how large is your catalog database? How many entries do you have in >> your File table, for instance? Attribute despooling should be much >> faster than what you're seeing even on SATA disks. > Hi Uwe, > > I don't know much about databases, but I'm learning. > > We have 659,172 entries in the File table. > > >> I guess your mysql setup could do with some optimization w/r to buffer >> pool size (I hope you're using InnoDB as the backend db engine) and >> lock / write strategies. > The command > SHOW TABLE STATUS; > shows that we're using InnoDB. > > >> As your DB runs on the director machine, I'd assign at last 50% of the >> available RAM if your catalog has a similar size. >> >> A quick google search came up with the following query to determine >> your catalog db size: >> >> SELECT table_schema "DB Name", >> Round(Sum(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB" >> FROM information_schema.tables GROUP BY table_schema; >> >> All the best, Uwe > The above command gave > +--------------------+---------------+ > | DB Name | DB Size in MB | > +--------------------+---------------+ > | bacula | 216.6 | > | information_schema | 0.0 | > +--------------------+---------------+ > > To assign 50% of RAM (we have 16 GB total) I suppose I should add the line > innodb_buffer_pool_size = 8G > in /etc/mysql/my.cnf, then I assume restart MySQL. But maybe we don't need it > that big at this time, since the database is much smaller. > > Our my.cnf doesn't currently have a line for innodb_buffer_pool_size; I don't > know what it uses by default. > > Thanks, > Dave > MySQL is clever enough that it won't assign memory until it actually needs it. Since your DB is only 216MB it shouldn't end up taking the full 8GB no matter what happens. However during attribute despooling the buffer pool isn't really coming in to play that much; you aren't doing any SELECT queries during an insert so cached data isn't going to help. On restore however the buffer pool will be handy.
One other thing you may wish to look at is adding this line to your my.cnf: innodb_flush_log_at_trx_commit=2 By default MySQL is trying to write each INSERT to disk one at a time which isn't really necessary with something like bacula. It makes more sense in the case of financial transactions or something like that. The above command will have MySQL flush out writes in batches once per second rather than trying to hit the disk (and wait) for each one. This can make a significant improvement in attribute spooling. A single 7200 RPM disk can't handle more than about 90 transactions per second, so the fewer individual disk transactions that are occurring the better. Bryn ------------------------------------------------------------------------------ _______________________________________________ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
