[mythtv-users] Possibly Handy tweaks

[Yan-Fa Li]

Well I wouldn't completely agree with that *fast* thing.  :)  Relational 
Databases are about data integrity which is in opposition to speed.  If 
you want fast, then there is actually a MySQL database mode that is 
purely in memory.  Here are some common useful tweaks to make MySQL more 
responsive, these go in the /etc/my.cnf under the mysqld section:

key_buffer = 48M
max_allowed_packet = 8M
table_cache = 128
sort_buffer_size = 48M
net_buffer_length = 8M
thread_cache_size = 4
query_cache_type = 1
query_cache_size = 4M

Adjust them for how much memory you have on your system.  In general 
giving MySQL more memory will make it more responsive.  The important 
ones to try in your environment would probably be:

query_cache	this caches repeated SQL queries.
key_buffer	is used for caching primary key indexes.
table_cache	tells mysql how many table files handles to
		keep open simultaneously.
thread_cache_size this tells mysql to keep worker threads around which 	
		should are expensive to start up, but cheap to maintain
		which makes mysql more responsive.
sort_buffer_size this value is used during queries to hold 
results in 				memory otherwise it creates temporary result tables
		on disk
net_buffer_length should help on larger network based queries to improve
		throughput

I usually tweak these values when I setup a mysql database.  I use mysql 
at work and at home and as long as you check it after a system crash 
it's pretty reliable.  Here's the advantage of using a query cache from 
my mythbackend:

mysql> show status like "%cache%";
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| Qcache_queries_in_cache | 1954    |
| Qcache_inserts          | 43964   |
| Qcache_hits             | 1576954 |
| Qcache_lowmem_prunes    | 0       |
| Qcache_not_cached       | 197591  |
| Qcache_free_memory      | 2066256 |
| Qcache_free_blocks      | 70      |
| Qcache_total_blocks     | 4005    |
| Threads_cached          | 3       |
+-------------------------+---------+
9 rows in set (0.02 sec)

As you can see.  It's cached 1954 queries and I've used them over 1.5 
million times in 15 days of uptime.  This comes straight out of memory 
and does not trigger an SQL query so is much faster than not turning the 
cache on.  I'm also only using 2MB of my 4MB cache so it's pretty 
obvious that you don't need to allocate very much memory for mythtv for 
it to be effective.

Yan

Jesper Sörensen wrote:
> If you haven't done so already you should probably take a look at the 
> MySQL manual. There's an entire chapter about optimization and there are 
> tons of different switches and settings you can play with. (You probably 
> only need to tell it to use a bit more memory or something.)
> 
> http://dev.mysql.com/doc/mysql/en/MySQL_Optimization.html
> 
> Serving data *fast* is what database servers are all about and you'd be 
> very hard pressed to come up with a better caching mechanism than what 
> is already built in. If you find some good settings for Myth, feel free 
> to share it with the list though! I'm sure we all want Myth to be as 
> fast as possible. 8-)