[mythtv-users] Performance MySQL on RAM

[Dan Wilga]

On 10/24/12 6:06 AM, Simon Hobson wrote:
> jedi wrote:
>
>>  > >I have some questions abot MySQL performance, I have a dedicatd 
>> server
>>>  >for the backend with 2GB of memory. I am thinking on buy 2GB more and
>>>  >get 4GB of RAM.
>>>  >
>>>  >Is possible to use RAM memory to optimize MySQL?
>>
>>    I think you will see better performance by just
>> putting your database (or just the whole OS boot disk)
>> on an SSD. My own master backend boots off an SSD and
>> it has made a very visible difference.
>
> Really, you think moving the DB to SSD (and to avoid confusion, I mean 
> the current usage of the term meaning flash based storage) will give 
> better performance than allowing the DB to keep the entire database in 
> RAM ?
> Yes it will be faster than in spinning rust, but holding the DB in RAM 
> will be faster (at least for the type of usage that MythTV does) - and 
> generally a lot cheaper.
>
Exactly. Furthermore, the slowest thing MySQL does is not writing 
regular database tables to disk, it's writing temporary tables: to disk 
is very expensive, but even to a RAMdisk is pretty bad. In my 6+ years 
of experience with MySQL performance tuning, I've found that these are 
the best plans of action, in order of bang-for-the buck:

1. Try to prevent the creation of temporary tables in the first place. 
With MythTV, this is in the hands of the devs and the way they construct 
the database schema and queries.
2. Tune MySQL so that it has more heap memory (tmp_table_size and 
max_heap_table_size) and therefore does not tend to write temporary tables.
3. Tune MySQL so that tables are kept open longer (table_open_cache and 
open_files_limit).
4. Examine and try to correct any other obvious problems, as reported by 
a tool like phpMyAdmin's Status feature.
5. Only then should you consider improving the speed of the physical 
storage, since it will have minimal impact compared to the above.

-- 
Dan Wilga                                                        "Ook."