On 07/06/11 08:04, Adrian Reyer wrote:
> Hi,
>
> I am using bacula for a bit more than a month now and the database gets
> slower and slower both for selecting stuff and for running backups as
> such.
> I am using a MySQL database, still myisam tables and I am considering
> switching to InnoDB tables or postgresql.
Just for the record:
Unless you are using merge tables (which, since the advent of table
partitioning, you shouldn't be) or full-text indexes, there is NO USE
CASE for MySQL for which the correct answer to "What storage engine
should I use for my tables?" is MyISAM.[1] At this point, wherever
possible, EVERYONE should be using InnoDB.
(Also, preferably everyone should be using MySQL 5.5. However, RHEL -
for example - isn't even shipping MySQL 5.1 yet, let alone 5.5. They'll
probably start shipping MySQL 5.5 along about the time MySQL hits 6.5.)
There are many reasons for this, including performance, crash recovery,
and referential integrity (InnoDB offers full ACID guarantees, MyISAM
does not). MyISAM was designed to run acceptably well on servers with
32MB or less RAM, and it not only *does not*, it CANNOT make effective
use of more than a small fraction of the memory available on modern-day
commodity hardware. MyISAM cannot re-apply an interrupted transaction,
cannot roll back a failed transaction, and it is not robust in the face
of events like disk full conditions or unexpected power outages.
You will (still) hear a lot of FUD from people who frankly don't
understand the issues, about how InnoDB locks are slower than MyISAM
locks. This is, *technically*, true. However, it completely fails to
take into account that not only are InnoDB locks row level while MyISAM
locks are page level - meaning that many *write* transactions can
execute simultaneously on the same InnoDB table as long as they update
different rows, while NOTHING can execute simultaneously to any write
transaction on a MyISAM table - but, thanks to multi-view consistency,
InnoDB can execute most queries without needing to lock anything at all.
The real performance situation is this: With an identical transaction
load and identical data on identical hardware, on a 100% read query
load, which is the *best possible* performance case for MyISAM, InnoDB
still outperforms MyISAM by 60% or more. On a query load that is 75%
reads, 25% writes, InnoDB outperforms MySQL by over 400%.
So, yes. Convert all of your tables to InnoDB. Also, if you can,
update to MySQL 5.5 if you're not already using it. (Properly
configured, InnoDB in MySQL 5.5 on Linux has a 150% performance increase
over InnoDB 5.1, and on Windows, 5.5 InnoDB performs 1500% better than
5.1 InnoDB, according to Oracle's benchmarks.) Throw as much memory at
the InnoDB buffer pool as you can spare, pare down MyISAM buffers that
you're not using, and if you're using 5.5, look at the new
innodb_buffer_pool_instances variable. You can get a basic check of
your MySQL configuration using MySQLtuner (free download from
http://mysqltuner.com/mysqltuner.pl; requires Perl, DBI.pm, and DBD::mysql.)
[1] At this time, MySQL *itself* still requires MyISAM for the grant
tables. Word from inside Oracle says that fixing this and enabling the
grant tables to also be stored in InnoDB is work in progress, and that
once this is accomplished, the entire MyISAM storage engine will
probably be deprecated.
--
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.
------------------------------------------------------------------------------
All of the data generated in your IT infrastructure is seriously valuable.
Why? It contains a definitive record of application performance, security
threats, fraudulent activity, and more. Splunk takes this data and makes
sense of it. IT sense. And common sense.
http://p.sf.net/sfu/splunk-d2d-c2
_______________________________________________
Bacula-users mailing list
Bacula-users AT lists.sourceforge DOT net
https://lists.sourceforge.net/lists/listinfo/bacula-users
|
