Just to be clear, /tmp isn't hitting I/O or swap.
free
total used free shared buffers
cached
Mem: 16279548 16227476 52072 0 14436
11822828
-/+ buffers/cache: 4390212 11889336
Swap: 4194296 124 4194172
IE the box has about 12GB of free ram.
-Jason
On Mon, 2009-06-22 at 01:58 -0400, Jason A. Kates wrote:
> In my /etc/my.cnf I have this set:
> tmpdir = /tmp
>
> /tmp is a memory file system to avoid I/O contention.
>
> When looking you can see that temp tables are create in /tmp
> thus /var/tmp should be out of the loop.
>
> The db resides in /export/mysql which is part of a 6 disk 15K rpm
> hardware raid 10 disk set.
>
> df -Th
> Filesystem Type Size Used Avail Use% Mounted on
> /dev/cciss/c0d0p1
> ext3 996M 489M 456M 52% /
> /dev/mapper/Volume00-vartest
> ext3 2.0G 368M 1.5G 20% /var
> /dev/mapper/Volume00-export
> ext3 186G 47G 130G 27% /export
> /dev/mapper/Volume00-usr
> ext3 3.9G 1.8G 2.0G 47% /usr
> none tmpfs 8.0G 8.0K 8.0G 1% /tmp
> /dev/cciss/c1d0p1
> ext3 943G 316G 579G 36% /var/spool/bacula
>
>
> We are running Redhat ES 5 with Kernel 2.6.18-128.el5.
> If you would like to test a mount option I would be glad to give it a
> test.
>
> -Jason
>
> On Mon, 2009-06-22 at 07:38 +0200, Bruno Friedmann wrote:
> > 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 <[email protected]>
> > >>>>>>> To: Tom Sommer <[email protected]>
> > >>>>>>>
> > >>>>>>> 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.
> > >>>>>>
> >
> >
>
> --
> ----------------------------------------------------------------------------
> Jason A. Kates ([email protected])
> Fax: 208-975-1514
> Phone: 212-400-1670 x2
> ============================================================================
>
>
>
> ------------------------------------------------------------------------------
> 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
> [email protected]
> https://lists.sourceforge.net/lists/listinfo/bacula-devel
--
----------------------------------------------------------------------------
Jason A. Kates ([email protected])
Fax: 208-975-1514
Phone: 212-400-1670 x2
============================================================================
------------------------------------------------------------------------------
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
[email protected]
https://lists.sourceforge.net/lists/listinfo/bacula-devel
