Mailing List Archive: MythTV: Users

Possibly Handy tweaks

Index | Next | Previous | View Threaded

steve at diverseyachts

Oct 27, 2004, 4:20 AM

Post #1 of 8 (16720 views)
Permalink

Possibly Handy tweaks

I am a very inexperienced linux user who has been putting together a Mythtv
box for a while and have enjoyed learning about how LInux and even some
limited C++ programming.

I have had some performance issues which have been massively improved by
using the unichrome XvMC drivers and the latest CVS.

I was still having problems with stuttering and general sluggishnes whenever
the database was being accessed, so thought I would play around with a few
possible enhancements.

This may be bad practice but I now have a simple script that mounts a small
part of the memory as a ramdisk, copies the MYSQL data into it and accesses
the data from there. It has seriously improved the general responsive of my
system hugely and I have not had a single problem.

There is a simple backup script that runs to save the ramdisk contents to
the HDD whenever the DVB card updates the EPG and when the machine boots it
sets up the ramdisk and copies this backed up folder into the ramdisk.

====

The other very simple tweak that I have found very useful is in using the
DVB signal to sync the system clock using the dvbdate utility contained in
the dvb-utils package. It means that I can now run the machine without
network connectivity and I can place the box entirely standalone and still
EPG, accurate time etc.

=====

/usr/src/tv_grab_dvb-0.9/tv_grab_dvb|mythfilldatabase --no-delete --file 1 0
-
echo "Backing up database.."
rm -f /var/lib/mysql_real/mythconverg/*
cp -a /var/lib/mysql/mythconverg/* /var/lib/mysql_real/mythconverg/
echo "database backup complete"
echo "Setting system clock from DVB card.."
/usr/src/linuxtv-dvb-apps-1.1.0/util/dvbdate/dvbdate --set
echo "Complete..."

May not be useful to anyone but I know how much I like to get simple bits of
help.

jesper at datapartner

Oct 27, 2004, 5:17 AM

Post #2 of 8 (16275 views)
Permalink

Re: Possibly Handy tweaks [In reply to]

Steve Hayles (diverse) wrote:

> This may be bad practice but I now have a simple script that mounts a
> small part of the memory as a ramdisk, copies the MYSQL data into it
> and accesses the data from there. It has seriously improved the
> general responsive of my system hugely and I have not had a single
> problem.

You're right, I think that *is* bad practice - and a corrupt DB waiting
to happen... :-(

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

yanfali at best

Oct 27, 2004, 10:12 AM

Post #3 of 8 (16263 views)
Permalink

Re: Possibly Handy tweaks [In reply to]

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:

+-------------------------+---------+
| 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

steve at diverseyachts

Oct 27, 2004, 2:23 PM

Post #4 of 8 (16252 views)
Permalink

RE: Possibly Handy tweaks [In reply to]

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:

+-------------------------+---------+
| 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
______________________________________________________________________

_______________________________________________
mythtv-users mailing list
mythtv-users[at]mythtv.org
http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-users

drees at greenhydrant

Oct 28, 2004, 12:31 AM

Post #5 of 8 (16273 views)
Permalink

Re: Possibly Handy tweaks [In reply to]

> 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

Thanks for getting my butt in gear and tweaking my mysql config. I'm on
FC1 which had MySQL 3.23.58 installed. I upgraded to MySQL 4.0.21, and
then looked to tweaking /etc/my.conf. I'm running these parameters now:

# Don't need InnoDb for Myth
skip-innodb
key_buffer = 16M
table_cache = 128
sort_buffer_size = 2M
myisam_sort_buffer_size = 8M
query_cache_size = 16M

Using MySQL Administrator to monitor server stats, it seems like the
key_buffer and query_cache_size I've set are a bit overkill for my setup.

What is amazing is how fast things run in the Mythweb now. Used to be
that the pages would be a bit sluggish, now they are quite snappy on my
Duron 800. MySQL CPU utilization seems to be down as well.

Does setting a larger net_buffer_size improve things much? The docs I
saw mentioned that you shouldn't need to change it typically...

-Dave
_______________________________________________
mythtv-users mailing list
mythtv-users[at]mythtv.org
http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-users

gregcope at gmail

Oct 28, 2004, 12:49 AM

Post #6 of 8 (16318 views)
Permalink

Re: Possibly Handy tweaks [In reply to]

Hi All,

Another way to set the key_buffer size is to check the size of the
mysql index files add up the size of those - 48Mb strikes me as
overkill, 16M would seem more sensible.

Setting the query cache is a very good idea.

Giving MySQL a little memory will make it allot quicker, so if you can
spare it, do it.

Greg

yanfali at best

Oct 28, 2004, 1:06 AM

Post #7 of 8 (16261 views)
Permalink

Re: Possibly Handy tweaks [In reply to]

That's a very handy tip. Thanks. I set it that high out of habit :D
For example my dspam server has one index of 44MB so 48MB would be about
right.

Yan

Greg Cope wrote:
> Hi All,
>
> Another way to set the key_buffer size is to check the size of the
> mysql index files add up the size of those - 48Mb strikes me as
> overkill, 16M would seem more sensible.
_______________________________________________
mythtv-users mailing list
mythtv-users[at]mythtv.org
http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-users

yanfali at best

Oct 28, 2004, 1:14 AM

Post #8 of 8 (16288 views)
Permalink

Re: Possibly Handy tweaks [In reply to]

Web based applications tend to benefit from the query cache most since
they are do mostly reads and tend to do very little application layer
caching, so I'm not surprised it's helping.

I think you're right, that setting larger net_buffer_sizes won't help
that much unless you're doing large transactions like transferring clobs
back and forth from the database. I'm setting it since remote frontends
do a lot of stuff over network sockets. It certainly seems to not do my
setup any harm.

Actually a couple of other settings I've been meaning to try were
tweaking the tcp read and write socket memory buffers to max. This
helps with throughput, which may be useful for a video streaming setup
to frontends.

Yan

David Rees wrote:
>
> Using MySQL Administrator to monitor server stats, it seems like the
> key_buffer and query_cache_size I've set are a bit overkill for my setup.
>
> What is amazing is how fast things run in the Mythweb now. Used to be
> that the pages would be a bit sluggish, now they are quite snappy on my
> Duron 800. MySQL CPU utilization seems to be down as well.
>
> Does setting a larger net_buffer_size improve things much? The docs I
> saw mentioned that you shouldn't need to change it typically...
_______________________________________________
mythtv-users mailing list
mythtv-users[at]mythtv.org
http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-users

Index | Next | Previous | View Threaded

Interested in having your list archived? Contact lists@gossamer-threads.com