Hi Philipp,
Le Lundi 22 Février 2010 09:34:02, Philipp Storz a écrit :
> Hello Eric,
>
> how can we go on to solve the problem with mysql and base jobs?
Good question, as it's quite hard to know where is the performance
problem (i'm not able to read and understand the explain output), it could
take some time if we add indexes everywhere without info...
> Like it behaves now, it is unusable. Unfortunately, I do not have much
> skills in database tuning, but there has to be a big problem for mysql
> with the mentinoned INSERT:
Did you start by using the mysql-bigmem configuration file ? You can also
check the mysql manual on the memory section, and depending on your
configuration (innodb/myisam), you can set variables to handle your situation
(for example, if you have 1 million files, you need few megabytes to do join
and sort operations on memory rather than on disk with only 8kb of ram)
> INSERT INTO BaseFiles (BaseJobId, JobId, FileId, FileIndex) SELECT B.JobId
> AS BaseJobId, 2 AS JobId, B.FileId, B.FileIndex FROM basefile2 AS A,
> new_basefile2 AS B WHERE A.Path = B.Path AND A.Name = B.Name ORDER BY
> B.FileId
Maybe MySQL can't handle very well this join if the new_basefile or basefile2
tables don't have proper indexes.
Can you do some tests with this two patches? (don't apply them at the same
time to start, only one should be required)
Thanks for your help!
> the problem can be easily reproduced.
>
> regards,
>
> Philipp
>
> Am Sonntag 21 Februar 2010 22:41:30 schrieb Eric Bollengier:
> > Hi,
> >
> > This index was giving this result on postgres and sqlite
> >
> > http://sourceforge.net/apps/wordpress/bacula/2009/09/28/performance-issue
> > -w ith-a-useless-index-on-postgresql/
> >
> > It should do the same with MySQL...
> >
> > Bye
> >
> > Le Dimanche 21 Février 2010 10:00:35, Philipp Storz a écrit :
> > > Update:
> > >
> > > I tried the suggestion posted by Dan Langille
> > >
> > > (CREATE INDEX FilenameId_2 ON File (FilenameId, PathId);)
> > > ,but unfortunately this did not help in my case.
> > >
> > > regards,
> > > Philipp
> >
> > -------------------------------------------------------------------------
> > -- --- Download Intel® Parallel Studio Eval
> > Try the new software tools for yourself. Speed compiling, find bugs
> > proactively, and fine-tune applications for parallel performance.
> > See why Intel Parallel Studio got high marks during beta.
> > http://p.sf.net/sfu/intel-sw-dev
> > _______________________________________________
> > Bacula-devel mailing list
> > Bacula-devel@lists.sourceforge.net
> > https://lists.sourceforge.net/lists/listinfo/bacula-devel
diff --git a/bacula/src/cats/sql_create.c b/bacula/src/cats/sql_create.c
index a106fe8..e9b251a 100644
--- a/bacula/src/cats/sql_create.c
+++ b/bacula/src/cats/sql_create.c
@@ -1237,6 +1237,10 @@ bool db_create_base_file_list(JCR *jcr, B_DB *mdb, char *jobids)
if (!db_sql_query(mdb, mdb->cmd, NULL, NULL)) {
goto bail_out;
}
+ Mmsg(mdb->cmd, "CREATE INDEX basefile%lld_idx ON basefile%lld (PathId, FilenameId)", (uint64_t) jcr->JobId, (uint64_t) jcr->JobId);
+ if (!db_sql_query(mdb, mdb->cmd, NULL, NULL)) {
+ goto bail_out;
+ }
Mmsg(buf, select_recent_version[db_type], jobids, jobids);
Mmsg(mdb->cmd,
"CREATE TEMPORARY TABLE new_basefile%lld AS "
diff --git a/bacula/src/cats/sql_create.c b/bacula/src/cats/sql_create.c
index a106fe8..b1f63cc 100644
--- a/bacula/src/cats/sql_create.c
+++ b/bacula/src/cats/sql_create.c
@@ -1251,6 +1251,10 @@ bool db_create_base_file_list(JCR *jcr, B_DB *mdb, char *jobids)
(uint64_t)jcr->JobId, buf.c_str());
ret = db_sql_query(mdb, mdb->cmd, NULL, NULL);
+ Mmsg(mdb->cmd, "CREATE INDEX new_basefile%lld_idx ON new_basefile%lld (PathId, FilenameId)", (uint64_t) jcr->JobId, (uint64_t) jcr->JobId);
+ if (!db_sql_query(mdb, mdb->cmd, NULL, NULL)) {
+ goto bail_out;
+ }
bail_out:
db_unlock(mdb);
return ret;
------------------------------------------------------------------------------
Download Intel® Parallel Studio Eval
Try the new software tools for yourself. Speed compiling, find bugs
proactively, and fine-tune applications for parallel performance.
See why Intel Parallel Studio got high marks during beta.
http://p.sf.net/sfu/intel-sw-dev
_______________________________________________
Bacula-devel mailing list
Bacula-devel@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-devel
