[mythtv-users] mythfilldatabase takes long, mysql at 100% CPU, mythfrontend hangs

[Thomas Börkel]

HI!

Recently, I upgraded from 0.23.1 to 0.26 and I also got lots of new
channels.

Since then, mythfilldatabase needs about 15 minutes to load the data
(from external grabber xml file) into the database.

During most of the time, mysql needs 100% CPU and locks parts of the
database. As result, mythfrontend hangs in the menu until
mythfilldatabase is finished.

I already optimized the db and also recently, I did backup and restore
of the db.

The XML file to load is 55 MB. It provides guide data for 21 days.

show processlist shows this SQL fragment:

UPDATE program JOIN (SELECT MIN(starttime) AS starttime, title,
subtitle,           LEFT(description

MythTV 0.26 fixes from February.
MySQL 5.1.67.
AMD Athlon 64 X2, 2 GHz.
Gentoo 32 bit, Kernel 3.5.7, 4 GB RAM.

mythfilldatabase output with timestamps:

2013-04-06 07:55:18.534780 I  Updated programs: 3 Unchanged programs: 22982
2013-04-06 07:55:18.987035 I  Adjusting program database end times.
2013-04-06 07:55:19.474670 I      0 replacements made
2013-04-06 07:55:19.474682 I  Marking generic episodes.
2013-04-06 07:55:19.957061 I      Found 0
2013-04-06 07:55:19.957129 I  Extending non-unique programids with
multiple parts.
2013-04-06 07:55:20.119926 I      Found 0
2013-04-06 07:55:20.119961 I  Marking repeats.
2013-04-06 07:55:20.468188 I      Found 0
2013-04-06 07:55:20.468202 I  Unmarking new episode rebroadcast repeats.
2013-04-06 07:55:20.468589 I      Found 0
2013-04-06 07:55:22.949184 I  Marking episode first showings.
2013-04-06 08:02:53.033445 I      Found 34820
2013-04-06 08:02:53.033460 I  Marking episode last showings.
2013-04-06 08:10:33.809246 I      Found 34701
2013-04-06 08:10:33.820626 I
===============================================================
| Attempting to contact the master backend for rescheduling.  |
| If the master is not running, rescheduling will happen when |
| the master backend is restarted.                            |
===============================================================
2013-04-06 08:10:33.826033 I  MythCoreContext: Connecting to backend
server: 192.168.0.7:6543 (try 1 of 1)
2013-04-06 08:10:33.827619 I  Using protocol version 75
2013-04-06 08:10:33.831708 I  Received remote 'Clear Cache' request
2013-04-06 08:10:33.834092 N  mythfilldatabase run complete.


Anything I can do about this?

Thanks!

Thomas