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@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users
