Hi Jason, can you tell us if the place mysql works typically /var/lib/mysql
are on the same partition of /tmp or /var/tmp ?
I've found (in another project) with mysql myisam table you can kill disk io
if the two are on same disk. It's worst when you insert lot of data from the
same.
In a perfect world after tunning carefully the /etc/my.cnf ( huge isn't
suffisant in many big case )
you should have one full io for mysql temp files, one full io for indexes one
full io for tables
AND one full io for import data ( completely separate /tmp for example ).
In many installation, /tmp and /var are on the same device if you add the fact
of lvm or raid 1 configuration
you get some io trouble.
I wouldn't explain all fine tuning of mysql here, there's a lot and each off
them should be review & tested.
Next there's also ext3 tuning for those who use it as many distro are very
conservative.
(Newer kernel >2.6.30 would reduce the impact)
atime,diratime,data=ordered etc ... could change life :-)
First time bacula came with batch-insert enabled, I've test it, and have really
poor performances.
So I disable it. ( lvm on top of soft raid 1 ) my mistake to not mention it on
user/devel list.
In some future here we decide to migrate all possible application to postgresql.
For debugging and making some sort of test case, it would be nice to have a
"joke" dump database
containing lot of dir & files.
so we can test large queries ....
Perhaps someone with very large records could do it using the md5() on Filename
and directory ?
Jason A. Kates wrote:
> Based on following the thread I recompile bacula with the following
> options:
> ./configure --enable-smartalloc --with-mysql
> --with-tcp-wrappers=/usr/lib/libwrap.so --enable-static-fd
> --enable-static-dir --enable-static-sd --disable-batch-insert
> And my 18+ hour backup ran in 9 hours 22 mins 27 secs last night. It
> seems to be back to the pre 3.0 time.
>
> As for my tables I have an Admin job that optimizes my bacula tables
> within mysql.
> Job {
> Name = optimize_table_JobMedia
> JobDefs = "DefaultJob"
> Type = Admin
> Client = bacula-local-fd
> RunBeforeJob = "/etc/bacula/optimize_table_JobMedia.sh"
> Priority = 40
> Cancel Queued Duplicates=yes
> Maximum Concurrent Jobs = 1
> Run Before Job = "/etc/bacula/check-duplicate-queued-job.sh '%n'
> '%i'"
> }
>
> Please let me know what else I can do to provide useful data for the
> improvement of bacula (Worlds best backup software!).
> Thanks -Jason
>
> On Sun, 2009-06-21 at 08:11 +0200, Marc Cousin wrote:
>> First thing to know is which of the queries takes time (there are the
>> multiple
>> inserts into the batch table, then insert missing filenames/path, and the
>> final insert). Or is it slow with all the queries ? Which storage engine ?
>>
>> There are two possibilities :
>>
>> - either you have a bad plan for one of the queries (but I don't see how,
>> mysql having a very simple optimizer).
>> - or you have a tuning problem with mysql (there I can't really help you :
>> not
>> enough memory ? fragmented indexes ? I don't really know how mysql works)
>>
>> Kern, I have not been following closely the 3.0 development : do the xattr
>> go
>> into the lstat field ?
>>
>> On Sunday 21 June 2009 01:52:34 Jason A. Kates wrote:
>>> I have a backup that runs for 3 hours then spends another 15 hours in
>>> status "Dir inserting Attributes".
>>>
>>> The backup has 9,442,578 files and 239,718,461,832 bytes. It was much
>>> faster with bacula before and I thought that that the time change was
>>> due to additional data with xattrsupport that didn't exist before 3.0.
>>> The job a spool job with spooled attributes too.
>>>
>>>
>>>
>>>
>>> We are using mysql-server-5.0.45-7.el5. I would be glad to collect and
>>> share data. Please let me know what you sould like to me set or do to
>>> get the appropriate data.
>>>
>>> Thanks -Jason
>>>
>>> On Sat, 2009-06-20 at 17:48 +0200, Marc Cousin wrote:
>>>> There may be something. But first we have to know why mysql behaves that
>>>> slowly for Tom Summers. The problem being that I don't have a big mysql
>>>> database to test on ... So as long as we don't have the query plan or an
>>>> explanation ...
>>>>
>>>> On Saturday 20 June 2009 14:36:44 Kern Sibbald wrote:
>>>>> Hello Marc,
>>>>>
>>>>> Thanks for looking at this and for your responses. If I understand you
>>>>> correctly, there is nothing to be done, which does not surprise me :-)
>>>>>
>>>>> Best regards,
>>>>>
>>>>> Kern
>>>>>
>>>>> On Saturday 20 June 2009 13:16:44 Marc Cousin wrote:
>>>>>>> Subject: Re: [Bacula-users] Performance with MySQL queries since
>>>>>>> 3.0.0 (Dir inserting attributes hang)
>>>>>>> Date: Saturday 20 June 2009
>>>>>>> From: Kern Sibbald <k...@sibbald.com>
>>>>>>> To: Tom Sommer <m...@tomsommer.dk>
>>>>>>>
>>>>>>> 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
>>>>>>>
>>>>>>> -------------------------------------------------------
>>>>>> Sorry Kern, I wasn't monitoring this thread (mysql in the title :) )
>>>>>>
>>>>>> I wrote these queries, so maybe I should explain (not why I'm right,
>>>>>> I'm not sure of that, but why we've done it this way).
>>>>>>
>>>>>> A few forewords :
>>>>>> - batch insert was created at the begining for postgresql. I don't
>>>>>> have any experience with mysql, except that knowing at the moment we
>>>>>> programmed batch insert it didn't really do anything but nested
>>>>>> loops... - The main reason for batch inserts with postgresql was to
>>>>>> use COPY instead of INSERT, which is much faster (we also had good
>>>>>> results with stored procedures, but batch insert was even faster)
>>>>>> - our current file table is nearly 200GB (1 billion rows), with a
>>>>>> 250GB database, and we insert around 50 million records a day (rough
>>>>>> estimate)
>>>>>>
>>>>>> * First question I've seen in the thread:
>>>>>>
>>>>>> Why
>>>>>>
>>>>>> 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
>>>>>> )
>>>>>>
>>>>>> instead of the much simpler
>>>>>>
>>>>>> 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
>>>>>> )
>>>>>>
>>>>>>
>>>>>> => Because the first one removes the duplicate rows from batch table
>>>>>> before checking they exist in the filename table, while the second
>>>>>> one checks after. Performance was better in benchmarks with the first
>>>>>> one (with PostgreSQL, with our data).
>>>>>>
>>>>>>
>>>>>> * Second question :
>>>>>> Why not :
>>>>>>
>>>>>> INSERT INTO filename(name)
>>>>>> SELECT DISTINCT name
>>>>>> FROM batch AS a
>>>>>> LEFT JOIN filename AS f USING (name)
>>>>>> WHERE f.name IS NULL
>>>>>>
>>>>>> In fact, if the engine choses the right execution path, the execution
>>>>>> plan will be the roughly the same (nested loop) 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
>>>>>> )
>>>>>>
>>>>>> Except if batch is close in size to filename (for instance on my
>>>>>> bacula database, it would be a 50 million files backup). Anyway, If i
>>>>>> recall correctly about mysql (and it hasn't changed recently), mysql
>>>>>> will do a nested loop for both, as it doesn't know how to merge or
>>>>>> hash join ...
>>>>>>
>>>>>>
>>>>>> But I agree that this second syntax theorically gives more lattitude
>>>>>> to the engine. Except that recent oracle and postgresql versions will
>>>>>> anyway be able to convert between the exists/not exists and the join
>>>>>> (or IN) in this case, as they are semantically equivalent.
>>>>>>
>>>>>>
>>>>>> Now, I don't know how mysql would react to all these queries. An
>>>>>> explain plan would be great to understand... but I would have thought
>>>>>> that the NOT EXIST query would have been great for it's optimizer, as
>>>>>> it tells mysql exactly what to do :)
>>>>>>
>>>>>>
>>>>>> A last note, for postgreSQL users on this mailing list :
>>>>>> You can try to get better performance with batch inserts by lowering
>>>>>> seq_page_cost and random_page_cost, so that the engine favours nested
>>>>>> loop instead of hash join for the INSERT INTO File query : most of
>>>>>> the time, most of filename and path will be in the database or
>>>>>> filesystem cache, and the plan will be much faster (and save you big
>>>>>> sorts)
>>>>>>
>>>>>>
>>>>>> Please tell me if you need more information.
>>>>>>
--
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-devel mailing list
Bacula-devel@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-devel
