On 06/08/11 08:06, Enrico van Goor wrote:
> Hi All,
> 
> We have a setup with  bacula-dir/MySQL and bacula-sd (2 servers) and a
> SAN connected via fiber. We are running bacula with about 250 jobs a
> day. The table File is about 500M records with a size of about 100G. We
> use batch insert. All tables are MyISAM. Currently we are experiencing
> performance problems with the database.
> 
> For all jobs we do a disk-to-disk backup. The next day we use a copyjob
> to put the data on tape. During daytime we run the copy jobs and during
> the evening/night we run the disk-to-disk jobs. There is some overlap.
> When the the disk-to-disk jobs start, we see number of queries on the
> database drop to virtually 0.
> 
> There are a few options to solve this
> 
> - use innodb for the tables in MySQL
> - migrate to PostgreSQL
> 
> Can anyone advise me what to do, to increase the performance of our
> platform?

The very first thing I would do would be upgrade to MySQL 5.5.[current]
(5.5.13, right now) if you're not already using 5.5, making sure it's
properly configured (hint:  look at the new configuration directive
innodb_buffer_pool_instances), then throw as much RAM as possible at the
InnoDB buffer pool and convert all of the tables to InnoDB.  Then
download MySQltuner (http://mysqltuner.com/mysqltuner.pl) and look at
its recommendations for some basic tuning.

MyISAM, frankly, *SHOULD* be deprecated at this point.  There is still a
lot of FUD about InnoDB performance out there, most of it from people
who don't actually understand the performance implications of the
differences between MyISAM and InnoDB, but the truth is there is
virtually no use case on a conventional MySQL server[1] for which "What
primary storage engine should I be using?" has any answer other than
"InnoDB".  It's probably not too inaccurate to say that unless you
*NEED* either merge tables or full-text indices, you should be using InnoDB.



[1]  Which is to say, not NDB Cluster

-- 
  Phil Stracchino, CDK#2     DoD#299792458     ICBM: 43.5607, -71.355
  alaric AT caerllewys DOT net   alaric AT metrocast DOT net   phil AT 
co.ordinate DOT org
  Renaissance Man, Unix ronin, Perl hacker, SQL wrangler, Free Stater
                 It's not the years, it's the mileage.

------------------------------------------------------------------------------
EditLive Enterprise is the world's most technically advanced content
authoring tool. Experience the power of Track Changes, Inline Image
Editing and ensure content is compliant with Accessibility Checking.
http://p.sf.net/sfu/ephox-dev2dev
_______________________________________________
Bacula-users mailing list
Bacula-users AT lists.sourceforge DOT net
https://lists.sourceforge.net/lists/listinfo/bacula-users