> 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.
------------------------------------------------------------------------------
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
