[mythtv-users] ramfs for mysql

[Raymond Wagner]

On 1/5/2012 09:13, Simon Hobson wrote:
> Raymond Wagner wrote:
>
>> Then you have bad information, that shouldn't exist at all, or should
>> exist as a side note as "this is wrong" or "never do this".  Running
>> MySQL in a tmpfs is of the last type.  It has been documented on the
>> wiki several times in the past, and it has been rolled back or the page
>> deleted each time.
> The fact that a page has appeared several times means that there
> **ARE** people who are thinking of this. As others have said, isn't
> it better to leave it there and put a BIG disclaimer at the top
> saying why it's bad ? At least then, people could find it, see the
> disclaimer and either : know that it's bad and forget about it, or
> make their own mind up as to whether it is a suitable option for them.
> Instead, you seem to prefer hiding such information so people will
> guess in the dark, potentially oblivious to the risks and ways to
> mitigate them.

The fact that a page has appeared several times just shows that people 
who don't know what they're doing are fiddling around with MySQL.  Try 
to search for anything related to running MySQL on a memory disk, and 
you'll have a tough time finding anything.  You'll find a ton of stuff 
about putting the temporary tables in a tmpfs, but next to nothing about 
putting the whole database inside it.  Go through enough pages and you 
might come across this guy who actually benchmarked it, and found the 
only advantage to doing so is if you disable your disk cache.

http://aggregator.foolab.org/node/56840

Now his tests were only doing reads, and there would be significant 
advantages for writes, but then MythTV doesn't write to the database all 
that much.  Each simultaneous recording might account for a couple 
writes per second, but the only heavy database insertion occurs during 
the brief scheduler runs.

The real reason you don't see anyone wanting to run MyISAM from a tmpfs 
is because MySQL has the MEMORY engine for that purpose.  If you want to 
keep a database in memory, that's the correct way to do it.  Someone 
actually wanting to do a memory database would start with converting the 
handful of TEXT and BLOB fields in the schema over to VARCHARs, since 
the MEMORY engine doesn't support either.

 From there, you would want to have the master backend flush the 
database, and read new data in from the latest dump.  That would require 
modifying the master backend selection, since you wouldn't be able to 
determine that from the database.  Add a routine in the housekeeper to 
make periodic dumps, say every 15 minutes, as well as one on shutdown.  
Rotate these dumps, and automatically load the latest when the frontend 
starts.  You would still have the issue that tables in the MEMORY engine 
cannot grow past the max_heap_table_size, which defaults to 16MB.  You 
can increase that, but I don't know what the repercussions of doing so 
would be, and the program and recordedseek tables can grow to the 
several hundred MB range each.

http://dev.mysql.com/doc/refman/5.0/en/memory-storage-engine.html