[mythtv-users] Possibly Handy tweaks

[Steve Hayles (diverse)]

Thanks very much,  I have used SQL server in the past but have no real idea
about performance tuning on MYSQL. 

I have 'tweaked' the Mysql setup to use more cached memory and it defintely
is more responsive than it was before running from the HDD.  I have removed
the ramdisk concept ( as a long term windows user I was more excited at the
ease with which things like ramdisks could be set-up on linux and although
it was not *correct* I am very happy ro be learning more about the Myth and
Linux in general)

Thanks again

 

-----Original Message-----
From: mythtv-users-bounces at mythtv.org
[mailto:mythtv-users-bounces at mythtv.org] On Behalf Of Yan-Fa Li
Sent: 27 October 2004 07:12 PM
To: Discussion about mythtv
Subject: Re: [mythtv-users] Possibly Handy tweaks

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-)
_______________________________________________
mythtv-users mailing list
mythtv-users at mythtv.org
http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-users

______________________________________________________________________
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email
______________________________________________________________________