[mythtv-users] HowTo tune MySQL for schedule time and in general?

[Michael T. Dean]

On 05/09/2013 01:48 PM, Thomas Börkel wrote:
> HI!
>
> On 09.05.2013 16:39, David Engel wrote:
>
>>> mythfilldatabase is way faster, too.
>>>
>>> Before, Marking episode first/last showings too over 3 minutes each. Now
>>> it's 15 seconds!
>> It only takes 3 seconds for me with nothing special done to MySQL.
>> Admittedly, my backed is fairly powerful.  Perhaps you had a really
> My main backend is an Ivy Bridge 2.9 GHz Core i5. But the disk is a
> pretty old 320 GB IDE. This system was slow before tuning.
>
> My test/desktop system is a Sandy Bridge 3.3 GHz Core i5 with SATA disk
> and always was pretty fast regarding scheduling and mythfilldatabase.
> But it has less channels.
>
> Now that I raised the temp table space on my main backend, it is as fast
> as my test system.

You did not raise the temp table space.  You changed the value for the 
amount of space an in-memory temp table is allowed to take before MySQL 
decides to write it to disk.

http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_tmp_table_size
http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_max_heap_table_size
and
http://dev.mysql.com/doc/refman/5.0/en/internal-temporary-tables.html

By raising this value, you've simply bypassed the disk.  Ergo, problem = 
disk (or, really, disk configuration).  Again, I suspect you're using 
barriers on the tmp file system, which means that your system is 
blocking on temporary (i.e. 
ephemeral/not-valid-after-crash-and-or-reboot) data writes to ensure 
data integrity of that temporary data in the event of a crash.

> Probably this is the problem, if you have not enough temp table space
> configured:
>
> http://code.mythtv.org/trac/ticket/11282
>
>> badly tuned MySQL to begin with.  What distribution are you using?
> All my systems use Gentoo, not far away from each other regarding packages.
>

Post the output of:

cat /proc/mounts

and you'll likely see that barriers are enabled on your /tmp file system.

Mike