Re: [Bacula-users] Performance with many files
On Fri, Jul 08, 2011 at 08:30:17AM +0200, Adrian Reyer wrote: Speed improved many many times. My incremental backup finished after just 10 minutes while it took 2h earlier. This had been the benefit of using InnoDB over MyISAM. However, at 12GB RAM and 8900 File entries (12GB file on disk) it became slow again and I took the step to convert to PostgreSQL. While I only gave 8GB of memory to PostgreSQL it is quite a bit faster so far. A full backup that took 1day 1 month ago with less entries in the database was up to 3 days on MySQL this month. With PostgreSQL it had been down to 1day again. The hardware is the same system with 16GB RAM it has been before, serving as an iSCSI-storage for enhancing the bacula-sd residing on some other box the same time. The import read the dump at a constant 2MB/s whicht I regarded somewhat slow, but I think the 'constant' is the important part here. I did the migration with Bacula manual and http://mtu.net/~jpschewe/blog/2010/06/migrating-bacula-from-mysql-to-postgresql/ Just to throw in some numbers that might help others. Regards, Adrian -- LiHAS - Adrian Reyer - Hessenwiesenstraße 10 - D-70565 Stuttgart Fon: +49 (7 11) 78 28 50 90 - Fax: +49 (7 11) 78 28 50 91 Mail: li...@lihas.de - Web: http://lihas.de Linux, Netzwerke, Consulting Support - USt-ID: DE 227 816 626 Stuttgart -- Get a FREE DOWNLOAD! and learn more about uberSVN rich system, user administration capabilities and model configuration. Take the hassle out of deploying and managing Subversion and the tools developers use with it. http://p.sf.net/sfu/wandisco-dev2dev ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Performance with many files
On Wed, Jul 06, 2011 at 11:08:44AM -0400, Phil Stracchino wrote:
for table in $(mysql -N --batch -e 'select
concat(table_schema,'.',table_name) from information_schema.tables where
engine='MyISAM' and table_schema not in
('information_schema','mysql')'); do mysql -N --batch -e alter table
$table engine=InnoDB ; done
actually the outer ' in the first mysql need to be replaced by or the
inner ' to be quoted.
However, for some reason mysql 5.1 with compiled in innodb calced a lot
on the tables but never actually changed them to innodb. So I just did a
classic mysqldump, changed the MyISAM for InnoDB and loaded that again.
Speed improved many many times. My incremental backup finished after
just 10 minutes while it took 2h earlier.
Regards,
Adrian
--
LiHAS - Adrian Reyer - Hessenwiesenstraße 10 - D-70565 Stuttgart
Fon: +49 (7 11) 78 28 50 90 - Fax: +49 (7 11) 78 28 50 91
Mail: li...@lihas.de - Web: http://lihas.de
Linux, Netzwerke, Consulting Support - USt-ID: DE 227 816 626 Stuttgart
--
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
Re: [Bacula-users] Performance with many files
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
Re: [Bacula-users] Performance with many files
On Wed, Jul 06, 2011 at 10:09:56AM -0400, Phil Stracchino wrote: should I use for my tables? is MyISAM.[1] At this point, wherever possible, EVERYONE should be using InnoDB. I will, if the current backup ever finishes. For a start on MySQL 5.1 though (Debian squeeze). I am aware InnoDB has a more stable performance according to the posts I have found in various bacula-mysql related posts. Your post gives me some hope I can get away with converting the table format instead of migrating to postgres. Simple for the fact I have nicer backup scripts for mysql than for postgres. your MySQL configuration using MySQLtuner (free download from http://mysqltuner.com/mysqltuner.pl; requires Perl, DBI.pm, and DBD::mysql.) I am using that one and tuning-primer.sh from http://www.day32.com/MySQL/ Regards, Adrian -- LiHAS - Adrian Reyer - Hessenwiesenstraße 10 - D-70565 Stuttgart Fon: +49 (7 11) 78 28 50 90 - Fax: +49 (7 11) 78 28 50 91 Mail: li...@lihas.de - Web: http://lihas.de Linux, Netzwerke, Consulting Support - USt-ID: DE 227 816 626 Stuttgart -- 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
Re: [Bacula-users] Performance with many files
On 07/06/11 10:41, Adrian Reyer wrote:
On Wed, Jul 06, 2011 at 10:09:56AM -0400, Phil Stracchino wrote:
should I use for my tables? is MyISAM.[1] At this point, wherever
possible, EVERYONE should be using InnoDB.
I will, if the current backup ever finishes. For a start on MySQL 5.1
though (Debian squeeze). I am aware InnoDB has a more stable performance
according to the posts I have found in various bacula-mysql related
posts. Your post gives me some hope I can get away with converting the
table format instead of migrating to postgres. Simple for the fact I
have nicer backup scripts for mysql than for postgres.
Oh, sure. It's dead simple.
for table in $(mysql -N --batch -e 'select
concat(table_schema,'.',table_name) from information_schema.tables where
engine='MyISAM' and table_schema not in
('information_schema','mysql')'); do mysql -N --batch -e alter table
$table engine=InnoDB ; done
Keep in mind that on MySQL 5.1, you should preferably be using the
InnoDB plugin rather than the built-in InnoDB engine. The plugin InnoDB
engine is newer and performs better.
--
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
