[mythtv-users] Sloooow database

[Tom Dexter]

On Wed, Jun 11, 2008 at 2:09 PM, Curtis Stanford
<curtis at stanfordcomputing.com> wrote:
>
> On 11-Jun-08, at 10:25 AM, Tom Dexter wrote:
>
> I'm not trying to be evangelical about starving your machine for ram
> or anything :D...just pointing out that I've had no need to upgrade
> from 526MB.
>
> My program table has about 28000 records at any one time, but I have
> to tell you 10000 rows in a mysql table is nothing.  That shouldn't be
> a big issue even if something was doing a fair amount of non-indexed
> table scans.  I occasionally use an old IBM thinkpad for web
> development with apache and mysql etc installed under Gentoo.  Believe
> it or not, that machine is a lowly 700 Mhz P-III with 256MB of ram and
> it handled one project I was working on with a 3 million row table
> without much trouble at all.
>
> I have very little compiled into my Gentoo kernels that I don't
> need...maybe that's a help, though I can't believe it's a big factor.
> A modular kernel with only the modules you need loaded should be about
> as good I'd think.
>
> I don't know if it was discussed anywhere in this thread before, but
> is the file system that your database files reside on possibly getting
> close to full or anything?
>
>
> Actually it's over 100,000 rows. Still, mysql should handle it OK. My file
> system has plenty of space. After looking some more at it, I think it may be
> swapping a little bit. What my.cnf settings do you use? I think mine are a
> little high for 512M. I'm using Brad's setting right now:
>
> key_buffer = 32M
> table_cache = 256
> sort_buffer_size = 4M
> myisam_sort_buffer_size = 16M
> query_cache_size = 64M
> query_cache_limit = 8M
> query_cache_type  = 1
> Curtis
>
> _______________________________________________
> mythtv-users mailing list
> mythtv-users at mythtv.org
> http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-users
>
>

Actually, I'm just using the default settings for all of those from
the Gentoo ebuild, which I'd think is probably the same defaults you'd
get from the mysql source.  Some of those aren't even in my cnf file,
so they're using built-in defaults, but here are all of those settings
from the running database:

show variables where variable_name in ('key_buffer_size',
'table_cache', 'sort_buffer_size', 'myisam_sort_buffer_size',
'query_cache_size', 'query_cache_limit', 'query_cache_type')
--------------

+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| key_buffer_size         | 16777216 |
| myisam_sort_buffer_size | 8388608  |
| query_cache_limit       | 1048576  |
| query_cache_size        | 0        |
| query_cache_type        | ON       |
| sort_buffer_size        | 524288   |
| table_cache             | 64       |
+-------------------------+----------+
7 rows in set (0.00 sec)

Tom