[mythtv-users] Sloooow database

[Mache Creeger]

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 
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://mythtv.org/pipermail/mythtv-users/attachments/20080609/630488c2/attachment.htm