Am not a DBA, so far be it for me (a lowly Instructional Technologist) to disagree, however, I have observed first hand this much ... a MySQL server can be functioning just fine but it's NOT optimized. Before I ever ran tuner for the first time, there were no errors, just sometimes slow (hmmmmm). After running tuner and installing mtop (a MySQL top app), I've not seen any 'slow queries' (there are probably some still there, just haven't found them yet). But performance is acceptable and I've not had a MySQL daemon crash/crater, either (lucky me, huh?).
Uhhhh ... so your apache server is on RedHat and the backend dedicated DB box is on Windows? That's a strange mix and maybe why the DBA you asked concerning tuner (if you passed that along to them), choose to ignore. Tuner is Perl. Perl is not native to Windows. Adds to the complexity of something that is relatively easy/simple on Linux ... but that's my take, of course.
As far as those slow queries ... the new file system is strange ... files that are not linked in any course are eventually moved to the 'trashdir' and when 'empty trash' kicks in via cron those files are removed not only physically from the trashdir but also any references in the DB that point to them. Maybe when you had one of those crash episodes, files had been removed physically, but the DB never got updated or vice-versa.
Moodle DB determines when files are moved to the trashdir by a timestamp field - like the timecreated < '1367257651' you see in one of your examples.
From the looks of that query (the 40 building up) looks like all the files they refer to in the DB may not be found in filedir. The only way one can determine for sure is to x-reference them manually.
Here's one shown:
bdb1bc8e0f3766a2e531658d20c1b32688aab174
On your Apache server (which is Red Hat - thus Linux) as root user:
cd /data/moodledata/filedir/
ls -l bd/b1/bdb1bc8e0f3766a2e531658d20c1b32688aab174
is the file there?
If so there should be a corresponding entry in the DB:
select contenthash from mdl_files where contenthash="bdb1bc8e0f3766a2e531658d20c1b32688aab174";
The only way I know to correct that issue is manually checking for the existence of a file. If not there, remove references in the DB for them.
Also have to ask this about the system since there is a mix of platform ... is /data a mount point or is it a partition on the drive where the apache server resides?
As root user on the RedHat Apache Server:
cd /
ls -ld data
Will tell ya.
In an efficient way to check for the existence of all files contained in the DB, have you DBA, dump a query of mdl_files table for 'contenthash' into a text file.
Then, on the RedHat Linux server, upload that text file to /data/moodledata/
Using that contenthash.txt file one can write a simple script to read each line of the text file, and search ./filedir/*/* for it's existence.
Using the one example we have: bdb1bc8e0f3766a2e531658d20c1b32688aab174
from /data/moodledata/ issue this command:
find ./filedir/ -name bdb1bc8e0f3766a2e531658d20c1b32688aab174
That should show location of the file
Example:
[root@sos moodle23data]# find ./filedir/*/* -name a5b4ffa9e9a17113765a1153a5fb58b35b85f615
./filedir/a5/b4/a5b4ffa9e9a17113765a1153a5fb58b35b85f615
and to see what is is:
file -b 'locationoffileshownabove'
as per example:
./filedir/a5/b4/a5b4ffa9e9a17113765a1153a5fb58b35b85f615
And what type of file it is:
[root@sos moodle23data]# file -b ./filedir/a5/b4/a5b4ffa9e9a17113765a1153a5fb58b35b85f615
Zip archive data, at least v2.0 to extract
If you get that text file:
for f in $(cat contenthashdump.txt);
do
echo "Checking: "; echo "$f"
find ./filedir/*/* -name "$f"
file -b ./filedir/*/*/"$f";
done
Obviously, if the find doesn't find the file -b won't work. ;) And, the dump will contain references to '.' and some other strange stuff that won't be found.
Am sure your DBA could so something similar to this to get a fairly clean contenthashdump.txt file:
mysql -host XXXX -u root -p moodleDB -e 'select contenthash from `mdl_files` where (`mimetype` not like "docume
nt/unknown" and `filepath` not like "%/tinyicon/%" and `filepath` not like "%/thumb/%" and `filename` not like ".") INT
O OUTFILE "/data/moodledata/filedir/contenthashdump.txt"'
That would be issued from the RedHat Linux server (assuming it might have mysql installed even if using the Windows box).
Also, the query above should be on ONE line ... not wrapped as portrayed in this posting.
Run a little scirpt similar to above which checks for existence of backup files. Out looks like:
Checking:
./filedir/cb/98/cb98dbe42fc442de33ebca56aadce7897246c70d
Zip archive data, at least v2.0 to extract
if the file is found
and if not:
Checking:
./filedir/6f/5f/6f5f10e4ba53abcae2bd59239582d724f4f3c910
ERROR: cannot open `./filedir/6f/5f/6f5f10e4ba53abcae2bd59239582d724f4f3c910' (No such file or directory)
'spirit of sharing', Ken