On 11/13/2013 09:01 AM, Phil Stracchino wrote:
> On 11/13/13 10:59, Charles Douglass wrote:
>> Thanks for the reply.
>>
>> I know nothing about MySQL optimization.  I downloaded the
>> "mysqltuner.pl" script and I will run it after the next full backup to
>> see if it has any recommendations.
> The mysqltuner script is a pretty decent basic tool.  The thing you need
> to keep in mind regarding the "mysql-large", "mysql-huge" sample
> configurations that are STILL widely distributed in MySQL packages (and
> still widely used by the unsuspecting) is that most of those sample
> configurations were originally written back when a "large" server was
> one that might have as much as a whole 32MB of RAM.  These days, that is
> less than the compiled-in default size of some individual MySQL
> *buffers*.  So the unwary install the "suggested" configurations, and
> can't understand why MySQL is performing like a geriatric tortoise.
>
>
I ran mysqltuner before the backup and increased a couple of buffer
sizes as well as taking this suggestion:

innodb_flush_log_at_trx_commit=0


Then I ran a full backup and see this:

18-Nov 12:45 maple-sd JobId 46: Sending spooled attrs to the Director. 
Despooling 153,470,245 bytes ...
18-Nov 21:41 maple-dir JobId 46: Bacula maple-dir 5.2.6 (21Feb12):
  Build OS:               x86_64-pc-linux-gnu ubuntu 13.04

So spooling/despooling took just about 9 hours.

And running mysqltuner after shows:

-------- Performance Metrics
-------------------------------------------------
[--] Up for: 2d 0h 15m 14s (765K q [4.407 qps], 203 conn, TX: 16M, RX: 158M)
[--] Reads / Writes: 1% / 99%
[--] Total buffers: 560.0M global + 2.7M per thread (100 max threads)
[OK] Maximum possible memory usage: 828.8M (13% of installed RAM)
[OK] Slow queries: 0% (3/765K)
[OK] Highest usage of available connections: 4% (4/100)
[OK] Key buffer size / total MyISAM indexes: 16.0M/6.0M
[OK] Key buffer hit rate: 100.0% (5M cached / 3 reads)
[OK] Query cache efficiency: 47.9% (11K cached / 23K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 32 sorts)
[OK] Temporary tables created on disk: 17% (175 on disk / 976 total)
[OK] Thread cache hit rate: 98% (4 created / 203 connections)
[OK] Table cache hit rate: 24% (443 open / 1K opened)
[OK] Open file limit used: 23% (261/1K)
[OK] Table locks acquired immediately: 100% (742K immediate / 742K locks)
[OK] InnoDB data size / buffer pool: 246.3M/256.0M

-------- Recommendations
-----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance

I believe this only refers to other tables, since I just previously
deleted and recreated the bacula database.

There was one other suggestion to make the spool file on a different
file system, but does this apply to me since I'm writing to tape?

Thanks for any further help.

Chas Douglass


------------------------------------------------------------------------------
Shape the Mobile Experience: Free Subscription
Software experts and developers: Be at the forefront of tech innovation.
Intel(R) Software Adrenaline delivers strategic insight and game-changing 
conversations that shape the rapidly evolving mobile landscape. Sign up now. 
http://pubads.g.doubleclick.net/gampad/clk?id=63431311&iu=/4140/ostg.clktrk
_______________________________________________
Bacula-users mailing list
Bacula-users AT lists.sourceforge DOT net
https://lists.sourceforge.net/lists/listinfo/bacula-users