[mythtv-users] my experience with myth on a solid state drive

[Steven Adeff]

On Fri, Jan 6, 2012 at 9:31 AM, Steven Adeff <adeffs.mythtv at gmail.com> wrote:
> On Thu, Jan 5, 2012 at 3:20 PM, Dan Wilga
> <mythtv-users2 at dwilga-linux1.amherst.edu> wrote:
>> While I don't necessarily disagree with you on the other points, I have
>> a suggestion regarding this one:
>>
>> On 1/5/12 11:20 AM, Ronald Frazier wrote:
>>> So what was doing all the writes to /tmp? Several times an hour, myth
>>> recalculates its schedule. When it does, it uses about 500MB of
>>> temporary mysql tables. That alone was responsible for over 90% of the
>>> writes on the SSD. So instead, I created /tmp as a tmpfs partition.
>>> I've got 2GB of ram, so that gives /tmp up to 1GB to use.
>>
>> You could also just use this in your my.cnf to achieve the same effect:
>>
>> tmpdir = /dev/shm
>>
>> However, in the case of MySQL, it's even faster if you prevent it from
>> ever using temporary files. You can do this by throwing RAM at it with
>> these two parameters in my.cnf:
>>
>> tmp_table_size
>> max_heap_table_size
>>
>> The default for the heap is a ridiculously small 16M. In practice, I've
>> found that MySQL will free up RAM it's not actively using, once it's
>> done with the temporary, in-RAM tables. For more info, see:
>>
>> http://dev.mysql.com/doc/refman/5.1/en/internal-temporary-tables.html
>
> ok, so looking into this further,
>
> tmp_table_size
> Default         system dependent
> max_heap_table_size
> Default         16777216 (regardless of 32/64bit)
>
> So what your suggesting is to raise the value of both to a size that
> will allow for these temporary tables to reside in memory, which as
> Ronald suggests, is just over 500MB? (the documentation says the
> smaller of the two values, in case anyone wants to run one at a higher
> value)
>
> Then I assume mysql frees this memory up once it's done with it?

I'd like to point out that doing this still, in my case, places 352K
worth of SQL files in /tmp
so, in and of itself this is not a solution to what Ronald is
suggesting by making your SQL tmp point to a ramdisk for the purpose
of running an SSD. It helps, but not a total solution.

-- 
Steve
http://www.mythtv.org/wiki/User:Steveadeff
Before you ask, read the FAQ!
http://www.mythtv.org/wiki/index.php/Frequently_Asked_Questions
then search the Wiki, and this list,
http://www.gossamer-threads.com/lists/mythtv/
Mailinglist etiquette -
http://www.mythtv.org/wiki/index.php/Mailing_List_etiquette