Re: [Bacula-users] Performance with MySQL queries since 3.0.0 (Dir inserting attributes hang)
Tom Sommer wrote: Mike Holden wrote: Jari Fredriksson wrote: INSERT INTO Filename( Name ) SELECT a.Name FROM ( SELECT DISTINCT Name FROM batch ) AS a WHERE NOT EXISTS ( SELECT Name FROM Filename AS f WHERE f.Name = a.Name ) That looks silly. I would write it shorter as INSERT INTO Filename(Name) SELECT DISTINCT Name FROM batch AS a WHERE NOT EXISTS ( SELECT Name FROM Filename AS f WHERE f.Name = a.Name ) You may also want to consider using a JOIN rather than a subquery with a NOT EXISTS, something like (untested and unvalidated!): INSERT INTO filename(name) SELECT DISTINCT name FROM batch AS a LEFT JOIN filename AS f USING (name) WHERE f.name IS NULL I know from experience as an Oracle DBA (my day job) that this can often produce far more efficient results. Of course, all options need testing for both speed of execution and resource usage, bearing in mind that data varies from one installation to another, and one size may not fit all! Good suggestions, sounds like there might be an overall performance problem with the current query for batch-inserts with lots of data. I'm a bit unsure if I dare test these queries on my current installation. I'll CC Kern on the thread, perhaps he has some insights. So this morning I had to kill the above query because it's been running for 24+ hours, preventing the new daily jobs from running. I think I'm going to try and disable batch-inserts, the current situation is simply not good enough, it's become a major headache to run backups suddenly. When I hit the end of this month and ALL servers have to run FULL backups, I'm gonna be in a world of trouble I think - I just don't understand what has changed, because it's all been running great up until now. // Tom -- Are you an open source citizen? Join us for the Open Source Bridge conference! Portland, OR, June 17-19. Two days of sessions, one day of unconference: $250. Need another reason to go? 24-hour hacker lounge. Register today! http://ad.doubleclick.net/clk;215844324;13503038;v?http://opensourcebridge.org ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Performance with MySQL queries since 3.0.0 (Dir inserting attributes hang)
On Saturday 20 June 2009 08:51:53 Tom Sommer wrote: Tom Sommer wrote: Mike Holden wrote: Jari Fredriksson wrote: INSERT INTO Filename( Name ) SELECT a.Name FROM ( SELECT DISTINCT Name FROM batch ) AS a WHERE NOT EXISTS ( SELECT Name FROM Filename AS f WHERE f.Name = a.Name ) That looks silly. I would write it shorter as INSERT INTO Filename(Name) SELECT DISTINCT Name FROM batch AS a WHERE NOT EXISTS ( SELECT Name FROM Filename AS f WHERE f.Name = a.Name ) You may also want to consider using a JOIN rather than a subquery with a NOT EXISTS, something like (untested and unvalidated!): INSERT INTO filename(name) SELECT DISTINCT name FROM batch AS a LEFT JOIN filename AS f USING (name) WHERE f.name IS NULL I know from experience as an Oracle DBA (my day job) that this can often produce far more efficient results. Of course, all options need testing for both speed of execution and resource usage, bearing in mind that data varies from one installation to another, and one size may not fit all! Good suggestions, sounds like there might be an overall performance problem with the current query for batch-inserts with lots of data. I'm a bit unsure if I dare test these queries on my current installation. I'll CC Kern on the thread, perhaps he has some insights. So this morning I had to kill the above query because it's been running for 24+ hours, preventing the new daily jobs from running. I think I'm going to try and disable batch-inserts, the current situation is simply not good enough, it's become a major headache to run backups suddenly. When I hit the end of this month and ALL servers have to run FULL backups, I'm gonna be in a world of trouble I think - I just don't understand what has changed, because it's all been running great up until now. // Tom Hello, We certainly can use help optimizing SQL since we are not DBAs, and we will look into the SQL optimization suggestions given above, keeping in mind that there are often rather radical differences in timing of particular SQL queries depending on the database engine used. To the best of my knowledge nothing has changed in terms of the Batch insert queries since when it was implemented, and it is *very* unlikely (though I haven't checked the code) that something changed from 2.4.4 to 3.0.x. More likely, your workload or MySQL has changed in some way -- e.g. more Jobs, more machines backed up, Director machine with less memory or other jobs that use memory, a new version of MySQL, ... - I would suggest that you ensure that your database has all the recommended indexes (see the make_mysql_tables file), and that you are running with the large memory /etc/my.cnf file. - Another thing to do is to compact your database. One way to do it is to write it to an ASCII file and then re-insert it. - If you are running certain programs that create and delete lots of temporary files with different names, you Filename table may need cleaning. - I would strongly recommend not starting *lots* of Full backups at the same time or on the same day. By lots, I mean more than say 10 or 20 (depends on the size of your system). It is generally far better to stage 1/4 of the backup every week for a full backup so that the peak workload is spread out over the month. - If the bottleneck is in MySQL, you might consider moving it to another machine that has more memory and faster disks. - If you really have a huge number of backups (say 50 or more) that all run at the same time, it might be advisable to consider using PostgreSQL, but in that case, you will probably need an onsite DBA to properly tune and maintain it. - Regardless of what hardware you have, there are certain limitations on how many simultaneous jobs you can run (this kicks in on many systems around 50). Once a certain number is exceeded, the total throughput can rather radically decrease so careful monitoring is necessary. Bweb can help a lot in these situations. - If you are really in a world of trouble and it is a performance issue, there is not much we (the Bacula project) can do for you other than the above tips. However, ($$) Bacula Systems has tools that can help more precisely identify bottlenecks and help balance loads. Regards, Kern -- Are you an open source citizen? Join us for the Open Source Bridge conference! Portland, OR, June 17-19. Two days of sessions, one day of unconference: $250. Need another reason to go? 24-hour hacker lounge. Register today! http://ad.doubleclick.net/clk;215844324;13503038;v?http://opensourcebridge.org ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] The tipically error: Cannot find any appendable volumes.
Reynier Pérez Mira wrote:
John Drescher wrote:
I think John or us, need also the
@/etc/bacula/clients/salvasprod_f06_polo_bioinformatica-fd.conf
if this is the fd which has trouble and in which the incriminated
pool was defined
Sorry for both of you. Here is the file needed:
#Description: Backup the files include in FS for salvasprod_f02_imac-fd
#-
#Job Definition
Job {
Name = SP_F06_POLO_BIOINFORMATICA-FD
JobDefs = DefinicionPorDefectoParaCopias
Level = Incremental
Client = salvasprod_f06_polo_bioinformatica-fd
FileSet = SP_F06_POLO_BIOINFORMATICA-FS
Schedule = ScheduleFull
Write Bootstrap = /data/BoostrapFiles/SP_F06_POLO_BIOINFORMATICA-FD.bsr
Priority = 5
Pool = SP_F06_POLO_BIOINFORMATICA_Pool
}
#Client Definition
Client {
Name = salvasprod_f06_polo_bioinformatica-fd
Address = 10.7.19.203
FDPort = 9102
Catalog = MyCatalog
Password = jdl5.!jhdg45dfg1
File Retention = 1 years
Job Retention = 1 years
}
#Pool Definition
Pool {
Name = SP_F06_POLO_BIOINFORMATICA_Pool
Pool Type = Backup
Recycle = yes
AutoPrune = yes
Volume Retention = 1 year
Recycle Oldest Volume = yes
Maximum Volume Jobs = 1
Label Format = SP_F06_POLO_BIOINFORMATICA_Pool-
Maximum Volumes = 2
}
#FileSet Definition
FileSet {
Name = SP_F06_POLO_BIOINFORMATICA-FS
Include {
Options {
signature = SHA1
compression = GZIP
}
File = /etc
File = /data/svn
}
}
Regards and thanks for all your reply
Ok for the pool you have a maximum of two volumes and a recycle delay at 1 year.
Even if you are making incremental backup, you have the limit of Maximum Volume
Jobs = 1
Which allow you to save only two jobs on this pool.
Did you really save this fd only once a year ? That's sound strange to me ...
For the svn you save, I would dump the svn structure, and save the dump with
svnadmin dump command
--
Bruno Friedmann
--
Are you an open source citizen? Join us for the Open Source Bridge conference!
Portland, OR, June 17-19. Two days of sessions, one day of unconference: $250.
Need another reason to go? 24-hour hacker lounge. Register today!
http://ad.doubleclick.net/clk;215844324;13503038;v?http://opensourcebridge.org
___
Bacula-users mailing list
Bacula-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] The tipically error: Cannot find any appendable volumes.
Bruno Friedmann wrote: Ok for the pool you have a maximum of two volumes and a recycle delay at 1 year. Even if you are making incremental backup, you have the limit of Maximum Volume Jobs = 1 Which allow you to save only two jobs on this pool. Did you really save this fd only once a year ? That's sound strange to me ... No my politics is: - Full 4th sunday (the last of the month) - Differential (every sunday except the last one) - Incremental (diary except sundays) Wich will be the correct setup for this politics? For the svn you save, I would dump the svn structure, and save the dump with svnadmin dump command I don't know how to do this. Where I can find relative information? Cheer -- Ing. Reynier Pérez Mira -- Are you an open source citizen? Join us for the Open Source Bridge conference! Portland, OR, June 17-19. Two days of sessions, one day of unconference: $250. Need another reason to go? 24-hour hacker lounge. Register today! http://ad.doubleclick.net/clk;215844324;13503038;v?http://opensourcebridge.org ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
[Bacula-users] waiting for a mount request
My external hard drive wasn't plugged in when backups started last night, so I plugged it in and according to the status the jobs seem to be waiting (as opposed to failed), so while the hard drive is now plugged in, I still see is waiting for a mount request so it doesn't seem to recognize that the volume is now available. What do I need to do to jump start the jobs that are waiting? -- Are you an open source citizen? Join us for the Open Source Bridge conference! Portland, OR, June 17-19. Two days of sessions, one day of unconference: $250. Need another reason to go? 24-hour hacker lounge. Register today! http://ad.doubleclick.net/clk;215844324;13503038;v?http://opensourcebridge.org___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
[Bacula-users] Heads up -- no Bacula encryption on FreeBSD Release 7
Hello Dan, This is to warn you that Bacula will probably not be able to be compiled and run with encryption on Release 7 of FreeBSD. This is because the version of pthreads in that release has pthread_t defined as a structure, which is incompatible with OpenSSL. The OpenSSL API mandates doing comparison of thread ids without the use of pthread_equal(). In otherwords, Bacula must be able to return a thread id to OpenSSL when requested. It currently does so with: return ((unsigned long)pthread_self()); which is far from being elegant, but does currently work on all platforms including FreeBSD 7 (with the possible exception of Win32). This will not work with the new FreeBSD implementation of pthreads. Perhaps the FreeBSD pthreads developers have a FreeBSD specific solution for this problem. If so, could you please let me know. If not, Bacula encryption will unfortunately not be available on the new version of FreeBSD. Note, as far as I know this means that any program that uses pthreads and OpenSSL will have the same problem. Best regards, Kern -- Are you an open source citizen? Join us for the Open Source Bridge conference! Portland, OR, June 17-19. Two days of sessions, one day of unconference: $250. Need another reason to go? 24-hour hacker lounge. Register today! http://ad.doubleclick.net/clk;215844324;13503038;v?http://opensourcebridge.org ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
[Bacula-users] Fwd: waiting for a mount request
2009/6/20 Jeff Shanholtz jeffs...@shanholtz.com My external hard drive wasn’t plugged in when backups started last night, so I plugged it in and according to the status the jobs seem to be waiting (as opposed to failed), so while the hard drive is now plugged in, I still see is waiting for a mount request so it doesn’t seem to recognize that the volume is now available. What do I need to do to jump start the jobs that are waiting? use the mount command in bconsole John -- John M. Drescher -- Are you an open source citizen? Join us for the Open Source Bridge conference! Portland, OR, June 17-19. Two days of sessions, one day of unconference: $250. Need another reason to go? 24-hour hacker lounge. Register today! http://ad.doubleclick.net/clk;215844324;13503038;v?http://opensourcebridge.org ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
[Bacula-users] Reproducable bug with VirtualFull backups
Hello Bacula-users, I have been testing the VirtualFull backup because I'm still having problem. I have created a minimal configuration on a test machine which demonstrates the problem. The configuration has two different backup devices, both are hard disks. Each is in a separate pool, and each is a different type. The system seems to get the external pool confused with the internal pool You can download the entire configuration from the following URL: http://www.bayertechnologygroup.com//bacula.tar.gz You will have to label two volumes, one for the external drive (put it into the external pool), and one for the internal drive (put it in the internal pool) This is using a standard 3.0.1 release. While this config uses MySql I have experienced the same problem with a PostgreSql database. The following sequence of commands will activate the bug: run job=Client1 yes run job=BackupCatalog yes run job=Client1 level=VirtualFull yes run job=BackupCatalog level=VirtualFull yes run job=Client1 yes run job=Client1 level=VirtualFull yes When you run these commands one at a time and wait for it to finish, each finishes successfully. However, the problem occurs when there are multiple jobs waiting. To demonstrate, simply do a copy/paste of all the commands into bconsole in a terminal window, and some of the VirtualFull backups will fail. The following is one of the error messages I get: From: (Bacula) r...@localhost Subject: Bacula: Intervention needed for Client1.2009-06-20_19.46.45_11 To: r...@localhost Date: Sat, 20 Jun 2009 19:46:49 -0400 (EDT) 20-Jun 19:46 userver9-sd JobId 72: Please mount Volume D1 for: Job: Client1.2009-06-20_19.46.45_11 Storage: InternalFile (/var/bacula/working/archive) Pool: Internal Media type: ExternalDrive JBB -- Enhancing your business through Technology Bayer Technology Group http://www.BayerTechnologyGroup.com Jonathan Bayer, CEOmailto:jba...@bayertechnologygroup.com Work: (609) 632-1200 Mobile: (609) 658-9408 PO Box 276 East Windsor, NJ 08520 -- Are you an open source citizen? Join us for the Open Source Bridge conference! Portland, OR, June 17-19. Two days of sessions, one day of unconference: $250. Need another reason to go? 24-hour hacker lounge. Register today! http://ad.doubleclick.net/clk;215844324;13503038;v?http://opensourcebridge.org ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
[Bacula-users] Fwd: waiting for a mount request
Thanks. Strangely, one of the queued jobs ran fine at that point, but the next says “cannot find any appendable volumes” even though all jobs use the same volume. I don’t have the first or second problem if the drive is plugged in when a job starts (i.e. backups have been working fine until last night when it wasn’t plugged in). -- Are you an open source citizen? Join us for the Open Source Bridge conference! Portland, OR, June 17-19. Two days of sessions, one day of unconference: $250. Need another reason to go? 24-hour hacker lounge. Register today! http://ad.doubleclick.net/clk;215844324;13503038;v?http://opensourcebridge.org ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
