[mythtv-users] Sloooow database

[Roger Heflin]

Mache Creeger wrote:
> At 09:28 AM 6/9/2008, Curtis Stanford wrote:
> 
>> On 9-Jun-08, at 10:04 AM, Mache Creeger wrote:
>>
>>> At 03:48 PM 6/7/2008, Curtis Stanford wrote:
>>>
>>>> On 6-Jun-08, at 10:26 AM, Brad DerManouelian wrote:
>>>>
>>>> > On Jun 6, 2008, at 9:10 AM, Curtis Stanford wrote:
>>>> >
>>>> >> Hey everyone, here's something I've put up with for a long time and
>>>> >> thought I'd try to resolve it.
>>>>
>>>> > Is your backend swapping? Have you optimized mysql in your my.cnf 
>>>> file
>>>> > at all?
>>>> > I have 2GB in my backend, so I set /etc/my.cnf with this:
>>>> >
>>>> > 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
>>>> >
>>>> > It's much faster than the default settings. I could probably give it
>>>> > more memory, but I do lots of transcoding and other stuff on my
>>>> > backend so I didn't want to push it too much.
>>>>
>>>>
>>>> I'll try your settings and see what happens. I only have 512M but I
>>>> don't think it's swapping much.
>>>>
>>>> Curtis
>>>
>>> Also,
>>>
>>> Set up a daily cron job to optimize the database.  Here is a copy of 
>>> what I do:
>>>
>>> #!/bin/sh
>>> # Optimize the database
>>> OPT_MYTHDB='/usr/share/doc/mythtv-docs-0.21/contrib/optimize_mythdb.pl'
>>> LOG='/var/log/mythtv/optimize_mythdb.log'
>>> echo "Started ${OPT_MYTHDB} on `date`" >> ${LOG}
>>> ${OPT_MYTHDB} >> ${LOG}
>>> echo "Finished ${OPT_MYTHDB} on `date`" >> ${LOG}
>>
>> Yes, I already have a daily cron to do that and I've tried Brad's 
>> settings as well. No difference. I don't think my backend is 
>> underpowered. I remember back in the .12 days that it was lightning 
>> quick. I might try a database rebuild from scratch as my database has 
>> been around since Moses.
>>
>> Thanks for all the tips.
>>
>> Curtis
> 
> How about generating a backup and reloading it from the backup. That may 
> clean up some of the cruft.
> 
> Here is my backup script as well.
> 
> #!/bin/sh
> #Dumps the mythconverg database - daily backup
> DUMPFILE="mythdb_backup.bz2"
> /usr/bin/mysqldump -u mythtv -pmythtv mythconverg -c | /usr/bin/bzip2 
> -cq9 > /opt/mythdb/$DUMPFILE
> exit 0
> 
> -- Mache


Recently on my 1GB system (I moved things from a 2.8GB to a 1GB system) I 
improved the database response by setting this setting with sysctl:

sysctl -w vm.vfs_cache_pressure = 500

This is supposed to reduce the amount of memory used for file cache and has 
appeared to improve my database response quite a lot.  I did not see the issue 
at all on the 2.8GB system, but it was often a problem on the 1GB system, using 
this setting has appeared to correct the slow response I was having, I am using 
a settop device and it actually was timing out about 1/2 of the time when 
stopping a recording at the end (it was not having an issue on the 2.8GB system, 
and so far after changing the setting it has not timed out).

Given that the IO patterns of transcode/viewing recordings/writing recordings is 
such that the data won't likely be reused from the cache soon enough, it makes 
sense to not push out application pages for the vfs cache.

                         Roger