[mythtv-users] Database problems(?)
John Johnson
johnatl at mac.com
Wed Jul 5 19:55:50 UTC 2006
My MySQL server seems to be having some odd things happening. They
may be okay, but I would like to know either way.
1. At times there are several long running processes in the
processlist. I've seen some running for 14 000s (~4h) or more. There
are entries in the slow_queries.log, but I don't know what they are
trying to do. I can tell you they are huge, having 9 JOINs, 28 ANDs
and 11 ORs.
2. mythconverg.recordedmarkup has over 1 500 000 rows. Most entries
have a 'type' of 6, which I suppose is a keyframe. It looks like for
a lot (all?) of the recordings, there is an entry for every second.
3. mythconverg.oldrecorded becomes corrupt sometimes. Specifically, /
var/lib/mysql/mythconverg/oldrecorded.MYI is the file MySQL complains
about. When I run mysqlcheck on mythconverg, that table is flagged as
crashed. A mysqlcheck -r has always repaired it.
Thanks in advance for any insight!
Regards,
JJ
---
"America goes not abroad in search of monsters to destroy." -- John
Quincy Adams
[root at dell ~]# cat /etc/redhat-release
Fedora Core release 3 (Heidelberg)
[root at dell ~]# uname -a
Linux dell.johnjohnson.info 2.6.12-1.1381_FC3smp #1 SMP Fri Oct 21
04:03:26 EDT 2005 i686 i686 i386 GNU/Linux
[root at dell ~]# free
total used free shared buffers
cached
Mem: 1034556 952120 82436 0 22256
549484
-/+ buffers/cache: 380380 654176
Swap: 1052248 368 1051880
[root at dell ~]# vmstat
procs -----------memory---------- ---swap-- -----io---- --system--
----cpu----
r b swpd free buff cache si so bi bo in cs us
sy id wa
0 0 368 82496 22324 549416 0 0 11 1 7 73 20
2 76 2
[root at dell ~]# mysql --version
mysql Ver 14.7 Distrib 4.1.19, for pc-linux-gnu (i686) using
readline 4.3
[root at dell ~]# mythbackend --version
Library API version: 0.19.20060331-1
Source code version: 9938
Options compiled in:
linux release using_v4l using_oss using_alsa using_arts using_ivtv
using_dbox2 using_hdhr using_lirc using_joystick_menu using_x11
using_xv using_xrandr using_frontend using_backend
[root at dell ~]# /usr/lib/qt-3.3/bin/lrelease -version
lrelease version 3.3.4
[root at dell ~]# ivtvctl -V
driver info:
version = 0x00000402 (0.4.2)
comment = (tagged release)
cardnr = 0
hw_flags = 8a
[root at dell ~]# lspci -v
...
00:0a.0 Multimedia video controller: Internext Compression Inc iTVC16
(CX23416) MPEG-2 Encoder (rev 01)
Subsystem: Hauppauge computer works Inc.: Unknown device 0009
Flags: bus master, medium devsel, latency 64, IRQ 201
Memory at f8000000 (32-bit, prefetchable) [size=64M]
Capabilities: [44] Power Management version 2
...
(Hauppauge PVR-250, in other words.)
dell ~ 12:38 john $ mysqladmin processlist
+-----+--------+-----------------------------+-------------+---------
+------+-------+------------------+
| Id | User | Host | db | Command
| Time | State | Info |
+-----+--------+-----------------------------+-------------+---------
+------+-------+------------------+
| 4 | mythtv | mythtv-fe:42708 | mythconverg | Sleep
| 39 | | |
| 135 | mythtv | dell.johnjohnson.info:60679 | mythconverg | Sleep
| 9 | | |
| 136 | mythtv | dell.johnjohnson.info:60680 | mythconverg | Sleep
| 214 | | |
| 137 | mythtv | dell.johnjohnson.info:60681 | mythconverg | Sleep
| 4662 | | |
| 139 | mythtv | dell.johnjohnson.info:60684 | mythconverg | Sleep
| 5565 | | |
| 161 | mythtv | dell.johnjohnson.info:45033 | | Query
| 0 | | show processlist |
+-----+--------+-----------------------------+-------------+---------
+------+-------+------------------+
[root at dell ~]# lsof | grep mysql | wc -l
4036
[root at dell ~]# mysql -e "show table status from mythconverg;" | cut "-
d " -f1,5 | sort "-t " -n -k 2 | tail -10
recordedcredits 5284
oldrecorded 10537
oldprogram 25057
programrating 32031
filter_matches 50401
program 57782
people 78931
programgenres 82724
credits 198020
recordedmarkup 1591859
[root at dell ~]# mysql -e "select count(*) from recordedmarkup where
type=6"
+----------+
| count(*) |
+----------+
| 1584489 |
+----------+
[root at dell ~]# mysql -e "select count(*) from recordedmarkup where
type<>6"
+----------+
| count(*) |
+----------+
| 7370 |
+----------+
[root at dell ~]# mysql -e "select * from recordedmarkup where
chanid=10409 and starttime='2006-06-02 11:59:00' and type=6" | head -10
chanid starttime mark offset type
10409 2006-06-02 11:59:00 1 0 6
10409 2006-06-02 11:59:00 2 124928 6
10409 2006-06-02 11:59:00 3 327680 6
10409 2006-06-02 11:59:00 4 534528 6
10409 2006-06-02 11:59:00 5 737280 6
10409 2006-06-02 11:59:00 6 954368 6
10409 2006-06-02 11:59:00 7 1165312 6
10409 2006-06-02 11:59:00 8 1378304 6
10409 2006-06-02 11:59:00 9 1619968 6
[root at dell ~]# mysql -e "select * from recordedmarkup where
chanid=10409 and starttime='2006-06-02 11:59:00' and type=6" | tail -10
10409 2006-06-02 11:59:00 7294 1444716544 6
10409 2006-06-02 11:59:00 7295 1444907008 6
10409 2006-06-02 11:59:00 7296 1445105664 6
10409 2006-06-02 11:59:00 7297 1445298176 6
10409 2006-06-02 11:59:00 7298 1445498880 6
10409 2006-06-02 11:59:00 7299 1445693440 6
10409 2006-06-02 11:59:00 7300 1445896192 6
10409 2006-06-02 11:59:00 7301 1446098944 6
10409 2006-06-02 11:59:00 7302 1446301696 6
10409 2006-06-02 11:59:00 7303 1446504448 6
[root at dell ~]# llt /video/recordings/*.nuv /video/recordings/*.mpg|wc -l
356
[root at dell ~]# tail -40 /var/lib/mysql/slow_queries.log | grep SELECT
| sed -r -e "s/((AND|OR|CASE|LEFT|INNER|OUTER|WHEN)|ELSE|END)/\n\1/g"
-e "s/[ ]+/ /g" -e "s/(AND|OR|WHEN|ELSE)/\t\1/g" -e "s/FROM/\nFROM/g"
SELECT DISTINCT channel.chanid, channel.sourceid, program.starttime,
program.endtime, program.title, program.subtitle,
program.description, channel.channum, channel.callsign, channel.name,
oldrecorded.endtime IS NOT NULL AS oldrecduplicate, program.category,
record.recpriority, record.dupin, recorded.endtime IS NOT NULL AS
recduplicate, oldfind.findid IS NOT NULL AS findduplicate,
record.type, record.recordid, program.starttime - INTERVAL
record.startoffset minute AS recstartts, program.endtime + INTERVAL
record.endoffset minute AS recendts, program.previouslyshown,
record.recgroup, record.dupmethod, channel.commfree,
capturecard.cardid, cardinput.cardinputid, UPPER(cardinput.shareable)
= 'Y' AS shareable, program.seriesid, program.programid,
program.category_type, program.airdate, program.stars,
program.originalairdate, record.inactive, record.parentid, (
CASE record.type
WHEN 6 THEN record.findid
WHEN 9 THEN to_days(date_sub(program.starttime, interval time_format
(record.findtime, '%H:%i') hour_minute))
WHEN 10 THEN floor((to_days(date_sub(program.starttime, interval
time_format(record.findtime, '%H:%i') hour_minute)) - record.findday)/
7) * 7 + record.findday
WHEN 7 THEN record.findid
ELSE 0
END) , record.playgroup, oldrecstatus.recstatus,
oldrecstatus.reactivate, channel.recpriority + cardinput.recpriority,
program.hdtv
FROM recordmatch
INNER JOIN record ON (recordmatch.recordid = record.recordid)
INNER JOIN program ON (recordmatch.chanid = program.chanid
AND recordmatch.starttime = program.starttime
AND recordmatch.manualid = program.manualid)
INNER JOIN channel ON (channel.chanid = program.chanid)
INNER JOIN cardinput ON (channel.sourceid = cardinput.sourceid)
INNER JOIN capturecard ON (capturecard.cardid = cardinput.cardid)
LEFT JOIN oldrecorded as oldrecstatus ON ( oldrecstatus.station =
channel.callsign
AND oldrecstatus.starttime = program.starttime
AND oldrecstatus.title = program.title )
LEFT JOIN oldrecorded ON ( record.dupmethod > 1
AND oldrecorded.duplicate <> 0
AND program.title = oldrecorded.title
AND ( (program.programid <> ''
AND program.generic = 0
AND program.programid = oldrecorded.programid)
OR (oldrecorded.findid <> 0
AND oldrecorded.findid = (
CASE record.type
WHEN 6 THEN record.findid
WHEN 9 THEN to_days(date_sub(program.starttime, interval time_format
(record.findtime, '%H:%i') hour_minute))
WHEN 10 THEN floor((to_days(date_sub(program.starttime, interval
time_format(record.findtime, '%H:%i') hour_minute)) - record.findday)/
7) * 7 + record.findday
WHEN 7 THEN record.findid
ELSE 0
END) )
OR ( program.generic = 0
AND (program.programid = ''
OR oldrecorded.programid = '')
AND (((record.dupmethod & 0x02) = 0)
OR (program.subtitle <> ''
AND program.subtitle = oldrecorded.subtitle))
AND (((record.dupmethod & 0x04) = 0)
OR (program.description <> ''
AND program.description = oldrecorded.description)) ) ) )
LEFT JOIN recorded ON ( record.dupmethod > 1
AND recorded.duplicate <> 0
AND program.title = recorded.title
AND recorded.recgroup <> 'LiveTV'
AND ( (program.programid <> ''
AND program.generic = 0
AND program.programid = recorded.programid)
OR (recorded.findid <> 0
AND recorded.findid = (
CASE record.type
WHEN 6 THEN record.findid
WHEN 9 THEN to_days(date_sub(program.starttime, interval time_format
(record.findtime, '%H:%i') hour_minute))
WHEN 10 THEN floor((to_days(date_sub(program.starttime, interval
time_format(record.findtime, '%H:%i') hour_minute)) - record.findday)/
7) * 7 + record.findday
WHEN 7 THEN record.findid
ELSE 0
END) )
OR ( program.generic = 0
AND (program.programid = ''
OR recorded.programid = '')
AND (((record.dupmethod & 0x02) = 0)
OR (program.subtitle <> ''
AND program.subtitle = recorded.subtitle))
AND (((record.dupmethod & 0x04) = 0)
OR (program.description <> ''
AND program.description = recorded.description)) ) ) )
LEFT JOIN oldfind ON (oldfind.recordid = recordmatch.recordid
AND oldfind.findid = (
CASE record.type
WHEN 6 THEN record.findid
WHEN 9 THEN to_days(date_sub(program.starttime, interval time_format
(record.findtime, '%H:%i') hour_minute))
WHEN 10 THEN floor((to_days(date_sub(program.starttime, interval
time_format(record.findtime, '%H:%i') hour_minute)) - record.findday)/
7) * 7 + record.findday
WHEN 7 THEN record.findid
ELSE 0
END) )
ORDER BY record.recordid DESC;
More information about the mythtv-users
mailing list