[mythtv-users] SSD disk for DB

[Ronald Frazier]

On Mon, Oct 8, 2012 at 3:53 PM, Yeechang Lee <ylee at pobox.com> wrote:
> Ronald Frazier <ron at ronfrazier.net> says:
>> The only thing I found to be causing significant wear were the mysql
>> temporary tables created by the myth scheduler running several times
>> an hour. I solved this my moving /tmp to memory, but other's said it
>> could just as well be solved by configuring mysql to put temp tables
>> in memory by altering some settings in my.cnf
>
> Be careful with this. I found that mysql would sometimes create
> temporary files of up to 3.5GB in size when running certain functions,
> such as if a SQL statement in Custom Edit is accidentally made too
> broad[1] or the "Time" search from Schedule Recordings. As my systems
> do not have more than 2GB of RAM, I had to move away from mounting
> /tmp in memory in /etc/fstab with tmpfs to back to disk.
>
> [1] An example is
>
>     program.title = ('Family Guy') OR program.title = ('The Simpsons')
>
> If the above is incorrectly written as
>
>     program.title = ('Family Guy' OR 'The Simpsons')
>
> mythbackend essentially writes its entire scheduling data to disk as
> it searches through it. mythfrontend itself has a 10,000-line limit on
> search returns (<URL:http://code.mythtv.org/trac/ticket/4626>), which
> is more than sufficient for any sane query but not in this case.

Good point. However, I wonder how mysql and myth would react to
running out of memory for the temp table? I'd assume mysql would just
generate an error (rather than crash), and that mythfrontend would
likewise detect the failed query. I'd say that it might be preferable
to have it error out on some monstrously huge query that I almost
surely didn't intend, rather than just going and going and going and
going and then getting me a ridiculously huge result set 5 minutes
later.

-- 
Ron Frazier