[mythtv-users] Is 4GB RAM overkill for mythtv?

[Osma Ahvenlampi]

On ma, 2008-02-25 at 13:03 +0000, Matthew McClement wrote:
> However, I suspect the biggest problem with MySQL, especially when using
> MyISAM, is that Myth's disk activities will result in the MySQL, not
> currently being used, datafiles being flushed from the pagecache. This
> is normally a good thing however whenever you try to access the flushed
> out data you incur a disk read, which is expensive and slow.

Like Matthew mentioned in the end of his email, InnoDB is a better
option -- for this reason as well as concurrency, but also because
InnoDB is far more difficult to corrupt by for example losing power to
the server at an inopportune moment.

For InnoDB, the optimization parameters change entirely, since InnoDB is
configured separately from MyISAM. The most impactful setting will be
innodb_buffer_pool_size, which controls how much memory MySQL will be
using for its own in-process buffers (independent of OS page cache).

You'll also want to turn off the use of page cache for the tables
entirely, because a) that'll waste memory by buffering the same data
twice and b) it won't help anyway because MythTV will blow away the page
cache with the recordings. That's done with innodb_flush_method=O_DIRECT
-- and before someone comments with the "but Linus doesn't like
O_DIRECT!" bit, yes, that's true, but for entirely different reasons,
and it *is helpful* for MySQL/InnoDB (I do this for a living).

Now, the MyISAM parameters will still have some effect -- in particular
sort buffers will make a difference in all queries which cause MySQL to
use temporary tables (which are always either heap or MyISAM, and which
MythTV's queries often resort to).

This article is directed more towards administrators of big dedicated DB
machines, but it's still helpful -- just don't spend more than, say,
minimum 50M, max 25% of your total RAM on innodb_buffer_pool for use
with MythTV if the same machine also runs your main backend.

http://www.mysqlperformanceblog.com/2007/11/01/innodb-performance-optimization-basics/

Incidentally, the page cache pollution could be greatly alleviated in
MythTV with judicious use of posix_fadvise(2) when accessing (either for
read or write) the recordings. That might be simple exercise to try
out... Hmm.