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 ala...@caerllewys.net ala...@metrocast.net p...@co.ordinate.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@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
