> If you either send me an attachment with your "comments"
> or point me to an exact link to the thread, we'll at
> least take a quick look at it for anything that might
> stand out. Sorry, I no longer have the time to search
> for such things :-( I think Eric might be interested in
> this too since batch insert is mostly his baby :-)
>
--------- sql_create.c -------------------------------------------------
bool my_batch_start(JCR *jcr, B_DB *mdb)
{
bool ok;
db_lock(mdb);
ok = db_sql_query(mdb,
"CREATE TEMPORARY TABLE batch ("
"FileIndex integer,"
"JobId integer,"
"Path blob,"
"Name blob,"
"LStat tinyblob,"
"MD5 tinyblob)",NULL, NULL);
db_unlock(mdb);
return ok;
}
------------------------------------------------------------------------
Path and Name are blobs. I'm not very good with MySQL (that means, there are
younger guys who have tested everything and are great in what they are doing),
but from my experience from mostly Oracle world is that a BLOB is not good at
searching and indexing. Sometimes they are implemented as external files to the
database, and are used to just store *stuff*.
Still, bacula generates the following SQL:
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 suspicious to me.
------------------------------------------------------------------------
Another suspiciously looking thing is in the same SQL query:
SELECT a.Name FROM (SELECT DISTINCT Name FROM batch) AS a
Is actually (to me) same as
SELECT DISTINCT a.Name FROM batch AS a
------------------------------------------------------------------------
Those things look suspicious to me. I did not try EXPLAIN PLAN for the queries,
but there are 2 facts.
1) My system hangs on a 100% processor time by MySQL, and the query is as shown
up there
2) My commmon sense with 20 years of SQL and C/C++ says says that the sql
clause is suspicious. Double full table query for 1 exact thing.
Regards,
jarif
-------------------------------------------------------------------------
This SF.net email is sponsored by: Microsoft
Defy all challenges. Microsoft(R) Visual Studio 2008.
http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/
_______________________________________________
Bacula-users mailing list
Bacula-users AT lists.sourceforge DOT net
https://lists.sourceforge.net/lists/listinfo/bacula-users
|
