[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