On Thu, Nov 18, 2010 at 10:27 PM, Alan Brown <a...@mssl.ucl.ac.uk> wrote:
> On 13/11/10 04:46, Gary R. Schmidt wrote:
>> You mean "looks increasingly *unlikely*" don't you? As InnoDB is the
>> default in MySQL 5.5...
>
> Yes it is, but take a look at what Oracle's been doing to the other
> opensource projects it inherited.
>
> It says a lot when core mysql developers fork a new project.
>
> It says a lot more when this happens across a number of projects
> including the entire Open Office developer team.
That's quite an exaggeration, although I am definitely not feeling
good about the amount of bad will Oracle managed to inspire in me in
this short amount of time.
Frankly, the only thing going for them in my book right now IS MySQL 5.5.
> > I suspect there to be at least one person involved in this discussion
>> who has *religion* in relation to database engines...
>
> Nothing to do with religion - and FWIW, stating that postgresql requires
> a DBA is a clear case of FUD.
> My point of view comes from running both engines on the same hardware
> and observing the loads involved. Personally I'd prefer to be running
> mysql but it was clear postgres ran faster and had lower memory
> foorprints for our use than innodb. Others have reported the same thing
> over the years.
The FUD stops here, this is pointless in the case of (where this
discussion started) restore performance on a MySQL back-end. The SQL
queries are not at all written with performance for MySQL in mind. And
frankly, the file selection process shouldn't even pull the entire
file list from the database at once, it should be done kindof like the
.Bvfs API is built, with the Hierarchy in mind. Pulling 5 million
files out in one flat list is equally stupid to (or, rather in this
case, a simplification) storing 5 million files in an unhashed
directory structure.
As soon as you see subqueries like these run against a MySQL server it
is obvious it was not designed for MySQL and/or performance.
Frankly I don't know at this point how to make it better without
restructuring the database and actually avoiding pulling out millions
of millions of records at once.
Hierarchies are definitely the way to go in this case, as it was a
question of restore selection. Remember, this was about file
selection, you don't actually need the full list of million(s) if
you're only going to choose a subset. But you do need the ability to
traverse.
Here's the query which is the foundation of this entire thread, tidied
up a bit. Anyone who's ever dealt with MySQL can see that this is not
going to look good in EXPLAIN:
SELECT
Path.Path, Filename.Name, Temp.FileIndex, Temp.JobId, LStat, MD5
FROM
(
SELECT
FileId, Job.JobId AS JobId, FileIndex, File.PathId AS
PathId, File.FilenameId AS FilenameId, LStat, MD5
FROM
Job,
File,
(
SELECT
MAX(JobTDate) AS JobTDate, PathId, FilenameId
FROM
(
SELECT JobTDate, PathId, FilenameId
FROM File
JOIN Job
USING (JobId)
WHERE File.JobId IN (38,39)
UNION ALL
SELECT JobTDate, PathId, FilenameId
FROM BaseFiles
JOIN File USING (FileId)
JOIN Job ON (BaseJobId = Job.JobId)
WHERE BaseFiles.JobId IN (38,39)
) AS tmp
GROUP BY
PathId, FilenameId
) AS T1
WHERE
(Job.JobId IN
(
SELECT DISTINCT BaseJobId
FROM BaseFiles
WHERE JobId IN (38,39)
)
OR
Job.JobId IN (38,39)
)
AND T1.JobTDate = Job.JobTDate
AND Job.JobId = File.JobId
AND T1.PathId = File.PathId
AND T1.FilenameId = File.FilenameId
) AS Temp
JOIN Filename ON (Filename.FilenameId = Temp.FilenameId)
JOIN Path ON (Path.PathId = Temp.PathId)
WHERE FileIndex > 0 ORDER BY Temp.JobId, FileIndex ASC;
--
Mikael
------------------------------------------------------------------------------
Beautiful is writing same markup. Internet Explorer 9 supports
standards for HTML5, CSS3, SVG 1.1, ECMAScript5, and DOM L2 & L3.
Spend less time writing and rewriting code and more time creating great
experiences on the web. Be a part of the beta today
http://p.sf.net/sfu/msIE9-sfdev2dev
_______________________________________________
Bacula-users mailing list
Bacula-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users
