[mythtv-users] Possibly Handy tweaks
Yan-Fa Li
yanfali at best.com
Wed Oct 27 17:12:16 UTC 2004
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-)
More information about the mythtv-users
mailing list