[mythtv-users] MySQL Database Overflow

[Yeechang Lee]

Nathan Lanchbury <mythtv at lanchbury.id.au> says:
> I have seen this problem once before and it turned out my database
> was corrupt (when exported over 500MBs)

It's not your database's size per se that's causing the issue; mine
has been over 500MB for ages.

(If I ever pull the trigger on connecting an Infiniband/eSATA-based
external enclosure with a bunch of the new 1.5TB drives it should hit
2GB+.)

> so I was able to strip out what was needed and import it back.

Vhat iss dis "strip out"?

I take it 'perl optimize_mythdb.pl' doesn't work?

> So everything shows to me the same thing has happened. BUT stripping
> the data doesn't work. The last night backup doesn't help along with
> the week backup, as is or stripped to just the recorded and
> scheduled recordings.

Your last sentence doesn't parse.

If restoring your database
(<URL:http://www.mythtv.org/docs/mythtv-HOWTO-23.html#ss23.5>), even
going back a few days, doesn't help, that'd indicate a non MythDB
database-related problem.

I'm no database guru--I have to look up any SQL queries beyond 'select
* from _____'--but here's the /etc/my.cnf for my 2GB RAM backend;
maybe your database has exceeded some MySQL memory limit? As you
mention ATRPMS I presume you're also using a Fedora- or CentOS-based
system. There are settings for both InnoDB and MyISAM engine but I use
MyISAM.

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql
# 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

skip-locking
key_buffer = 432M # Ideally enough for all .MYI files
max_allowed_packet = 16M
table_cache = 256
net_buffer_length = 8M
sort_buffer_size = 6M
read_buffer_size = 6M
join_buffer_size = 6M
read_rnd_buffer_size = 6M
myisam_sort_buffer_size = 512M
thread_cache = 16
query_cache_limit = 4M
query_cache_size  = 128M
query_cache_type  = 1

innodb_data_home_dir = /var/lib/mysql/
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /var/lib/mysql/
innodb_log_arch_dir = /var/lib/mysql/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 432M
innodb_additional_mem_pool_size = 32M
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 96M
innodb_log_buffer_size = 4M
innodb_flush_log_at_trx_commit = 2
innodb_lock_wait_timeout = 50 
innodb_flush_method=O_DIRECT
innodb_thread_concurrency=8

# skip-innodb
set-variable=thread_stack=256k

expire_logs_days = 365

# utf8
# init-connect='SET NAMES utf8'
# character-set-server=utf8
# collation-server=utf8_general_ci
# skip-character-set-client-handshake

# log-bin = mysqld-binary-log
sync_binlog = 1

[mysql.server]
user=mysql
basedir=/var/lib

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

-- 
Frontend:		P4 3.0GHz, 1.5TB software RAID 5 array
Backend:		Quad-core Xeon 1.6GHz, 6.6TB sw RAID 6
Video inputs:		Four high-definition over FireWire/OTA
Accessories:		47" 1080p LCD, 5.1 digital, and MX-600