[solved] MYSQL hammers HDD on backend startup

Have a MythTV related problem? Ask for help from other MythTV users here.

Moderator: Forum Moderators

moorsey
Newcomer
Posts: 10
Joined: Sat Feb 08, 2014 11:35 am
Great Britain

[solved] MYSQL hammers HDD on backend startup

Post by moorsey » Mon Feb 10, 2014 12:37 pm

Just wanted to check if this behaviour was normal or not.

When my backend bots up, it is basically unusable for the first 5 minutes (this time can vary to longer or shorter), due to mysql doing 100% read on my drive.

iotop shows two mysql processes, both at 100%, or near enough. While this is going on, the frontend (this is a combined machine) sticks on just it's background image, until mysql finishes whatever it is doing, when it crashes. Loading the frontend after that and all is OK.

Is this sort of initial mysql loading normal? I did check through the error logs for mysql, but there was nothing obvious.

I have also noticed my DB backups getting slowly larger, which I assume is also normal, backup today was 350MB, compared to a month ago it was 240MB, not sure how this size compares to others.

Cheers!

Image

User avatar
stuarta
Developer
Posts: 198
Joined: Wed Feb 05, 2014 5:13 pm
Great Britain

Re: MYSQL hammers HDD on backend startup

Post by stuarta » Mon Feb 10, 2014 1:25 pm

When mysqld is running at 100% like this, can you please run the following mysql query and share the output

mysql> show full processlist\G

moorsey
Newcomer
Posts: 10
Joined: Sat Feb 08, 2014 11:35 am
Great Britain

Re: MYSQL hammers HDD on backend startup

Post by moorsey » Mon Feb 10, 2014 1:40 pm

handy command!

result as follows:

Code: Select all

mysql> show full processlist;
+-----+--------+-----------+-------------+---------+------+----------------------+--------------------------------------------------------------------------------------------+
| Id  | User   | Host      | db          | Command | Time | State                | Info                                                                                       |
+-----+--------+-----------+-------------+---------+------+----------------------+--------------------------------------------------------------------------------------------+
|  10 | root   | localhost | NULL        | Query   |    0 | NULL                 | show full processlist                                                                      |
| 145 | mythtv | localhost | mythconverg | Execute |   32 | Copying to tmp table | SELECT DISTINCT LEFT(programid, LOCATE('/', programid)) FROM program WHERE programid <> '' |
| 147 | mythtv | localhost | mythconverg | Execute |   18 | Copying to tmp table | SELECT DISTINCT category FROM program GROUP BY category                                    |
| 148 | mythtv | localhost | mythconverg | Sleep   |   23 |                      | NULL                                                                                       |
| 149 | mythtv | localhost | mythconverg | Sleep   |   18 |                      | NULL                                                                                       |
| 150 | mythtv | localhost | mythconverg | Sleep   |   18 |                      | NULL                                                                                       |
| 151 | mythtv | localhost | mythconverg | Sleep   |   18 |                      | NULL                                                                                       |
+-----+--------+-----------+-------------+---------+------+----------------------+--------------------------------------------------------------------------------------------+
7 rows in set (0.00 sec)

Gary Buhrmaster
Junior
Posts: 52
Joined: Mon Feb 10, 2014 8:19 pm
United States of America

Re: MYSQL hammers HDD on backend startup

Post by Gary Buhrmaster » Mon Feb 10, 2014 8:39 pm

moorsey wrote:Just wanted to check if this behaviour was normal or not.
It is certainly not normal for a BE to be "hammering" the disk for 5 minutes at startup, unless, perhaps, you are running it on a woefully underpowered system. What is your hardware, and software configuration? Have you performed any of the MySQL tuning steps suggested on the MythTV wiki?

Gary Buhrmaster
Junior
Posts: 52
Joined: Mon Feb 10, 2014 8:19 pm
United States of America

Re: MYSQL hammers HDD on backend startup

Post by Gary Buhrmaster » Mon Feb 10, 2014 9:29 pm

moorsey wrote:I have also noticed my DB backups getting slowly larger, which I assume is also normal, backup today was 350MB, compared to a month ago it was 240MB, not sure how this size compares to others.
Since the DB contains things like recorded program information, and the seektable, it is common to see it get larger over time (until you reach some "equilibrium" of new recordings and deleting the old). 350MB suggests you have a fair amount of content. And that size also suggests that some tuning of MySQL is likely going to be needed to minimize disk I/O.

moorsey
Newcomer
Posts: 10
Joined: Sat Feb 08, 2014 11:35 am
Great Britain

Re: MYSQL hammers HDD on backend startup

Post by moorsey » Mon Feb 10, 2014 11:58 pm

Gary Buhrmaster wrote:Since the DB contains things like recorded program information, and the seektable, it is common to see it get larger over time (until you reach some "equilibrium" of new recordings and deleting the old). 350MB suggests you have a fair amount of content. And that size also suggests that some tuning of MySQL is likely going to be needed to minimize disk I/O.
I didn't think I was a heavy myth user, checking mythweb, I have 287 recorded items and 549 in deleted, which should be pretty steady, as my recordings drive runs at 99%, just removing old stuff as needed. Mythvideo has around 2,500 files in it, which seems like a lot, but I guess things soon add up. Also files in music (2,000 ish) and pictures (approx 100,000).

I have set mysql up as per http://www.mythtv.org/wiki/Tune_MySQL, but those changes don't seem to make much difference. I am also investigating mysqltuner, will run tomorrow when the machine has been up for a while and see what that says.

Hardware wise, I am running this on an Intel Dual Core E6300 2.6GHz and 4GB RAM. Has always coped with myth, but is 4/5 years old now, so an upgrade might be on the cards, but that may not fix things if there is an underlying software issue.

Cheers

Martin

moorsey
Newcomer
Posts: 10
Joined: Sat Feb 08, 2014 11:35 am
Great Britain

Re: MYSQL hammers HDD on backend startup

Post by moorsey » Tue Feb 11, 2014 12:04 am

also ran the mysql process list command when I rebooted the machine again and got this mess as output, not sure why it isn't a nice table this time, but some different stuff going on it seems:

Code: Select all

mysql> show full processlist\g
+-----+--------+-----------+-------------+---------+------+--------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Id  | User   | Host      | db          | Command | Time | State        | Info                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |
+-----+--------+-----------+-------------+---------+------+--------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 144 | mythtv | localhost | mythconverg | Sleep   |   47 |              | NULL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |
| 146 | mythtv | localhost | mythconverg | Sleep   |    0 |              | NULL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |
| 148 | mythtv | localhost | mythconverg | Sleep   |  187 |              | NULL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |
| 151 | mythtv | localhost | mythconverg | Execute |    2 | Sending data | REPLACE INTO recordmatch (recordid, chanid, starttime, manualid,                           oldrecduplicate, findid) SELECT record.recordid, program.chanid, program.starttime,  IF(search = 5, record.recordid, 0), (CASE   WHEN record.type IN (1, 7, 8) THEN  0   WHEN record.type IN (6, 2, 5) THEN  -1   ELSE (program.generic - 1)  END) , (CASE record.type   WHEN 6    THEN record.findid   WHEN 2    THEN to_days(date_sub(convert_tz(program.starttime, 'UTC', 'SYSTEM'),             interval time_format(record.findtime, '%H:%i') hour_minute))   WHEN 5    THEN floor((to_days(date_sub(convert_tz(program.starttime, 'UTC',             'SYSTEM'), interval time_format(record.findtime, '%H:%i')             hour_minute)) - record.findday)/7) * 7 + record.findday   WHEN 7    THEN record.findid   ELSE 0  END) FROM (record, program INNER JOIN channel       ON channel.chanid = program.chanid)  WHERE record.type <> 11 AND record.search = 0 AND program.manualid = 0 AND program.seriesid <> '' AND program.seriesid = record.seriesid  AND channel.visible = 1  AND program.endtime > (NOW() - INTERVAL 480 MINUTE) AND (((record.filter & 1) = 0) OR (program.previouslyshown = 0)) AND (((record.filter & 2) = 0) OR (program.generic = 0)) AND (((record.filter & 4) = 0) OR (program.first > 0)) AND (((record.filter & 8) = 0) OR (HOUR(CONVERT_TZ(program.starttime, 'UTC', 'SYSTEM')) >= 19 AND HOUR(CONVERT_TZ(program.starttime, 'UTC', 'SYSTEM')) < 22)) AND (((record.filter & 16) = 0) OR (channel.commmethod = -2)) AND (((record.filter & 32) = 0) OR (program.hdtv > 0)) AND (((record.filter & 64) = 0) OR ((record.programid <> '' AND program.programid = record.programid) OR (record.programid = '' AND program.subtitle = record.subtitle AND program.description = record.description))) AND (((record.filter & 128) = 0) OR ((record.seriesid <> '' AND program.seriesid = record.seriesid))) AND (((record.filter & 256) = 0) OR (ABS(TIMESTAMPDIFF(MINUTE, CONVERT_TZ(  ADDTIME(record.startdate, record.starttime), 'Etc/UTC', 'SYSTEM'),   CONVERT_TZ(program.starttime, 'Etc/UTC', 'SYSTEM'))) MOD 1440 <= 10)) AND (((record.filter & 512) = 0) OR (ABS(TIMESTAMPDIFF(MINUTE, CONVERT_TZ(  ADDTIME(record.startdate, record.starttime), 'Etc/UTC', 'SYSTEM'),   CONVERT_TZ(program.starttime, 'Etc/UTC', 'SYSTEM'))) MOD 10080 <= 10)) AND (((record.filter & 1024) = 0) OR (channel.callsign = record.station)) AND ( (record.type = 4   OR record.type = 6   OR record.type = 2   OR record.type = 5)  OR   ((record.type = 1    OR record.type = 7    OR record.type = 8)   AND    ADDTIME(record.startdate, record.starttime) = program.starttime    AND    record.station = channel.callsign) ) |
| 152 | mythtv | localhost | mythconverg | Sleep   |   37 |              | NULL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |
| 153 | mythtv | localhost | mythconverg | Sleep   |   45 |              | NULL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |
| 154 | mythtv | localhost | mythconverg | Sleep   |  105 |              | NULL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |
| 155 | mythtv | localhost | mythconverg | Sleep   |  104 |              | NULL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |
| 156 | mythtv | localhost | mythconverg | Sleep   |   26 |              | NULL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |
| 158 | root   | localhost | NULL        | Query   |    0 | NULL         | show full processlist                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
+-----+--------+-----------+-------------+---------+------+--------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
10 rows in set (0.00 sec)

User avatar
rwagner
Developer
Posts: 217
Joined: Thu Feb 06, 2014 11:37 pm
United States of America

Re: MYSQL hammers HDD on backend startup

Post by rwagner » Tue Feb 11, 2014 1:20 am

The scheduler is running a very large query. Scheduler runs will produce very high database server loads for several seconds to tens of seconds, depending on the amount of data that needs to be processed.

User avatar
stuarta
Developer
Posts: 198
Joined: Wed Feb 05, 2014 5:13 pm
Great Britain

Re: MYSQL hammers HDD on backend startup

Post by stuarta » Tue Feb 11, 2014 1:31 pm

Code: Select all

mysql> show full processlist\g
the output is messy because the format modifier for mysql is '\G' not '\g'

moorsey
Newcomer
Posts: 10
Joined: Sat Feb 08, 2014 11:35 am
Great Britain

Re: MYSQL hammers HDD on backend startup

Post by moorsey » Tue Feb 11, 2014 4:34 pm

OK, so that huge query.....

Code: Select all

*************************** 3. row ***************************
     Id: 147
   User: mythtv
   Host: localhost
     db: mythconverg
Command: Execute
   Time: 81
  State: Sending data
   Info: REPLACE INTO recordmatch (recordid, chanid, starttime, manualid,                                                              oldrecduplicate, findid) SELECT record.recordid, program.cha                                   nid, program.starttime,  IF(search = 5, record.recordid, 0), (CASE   WHEN record                                   .type IN (1, 7, 8) THEN  0   WHEN record.type IN (6, 2, 5) THEN  -1   ELSE (prog                                   ram.generic - 1)  END) , (CASE record.type   WHEN 6    THEN record.findid   WHEN                                    2    THEN to_days(date_sub(convert_tz(program.starttime, 'UTC', 'SYSTEM'),                                                interval time_format(record.findtime, '%H:%i') hour_minute))   WHEN 5                                       THEN floor((to_days(date_sub(convert_tz(program.starttime, 'UTC',             '                                   SYSTEM'), interval time_format(record.findtime, '%H:%i')             hour_minute                                   )) - record.findday)/7) * 7 + record.findday   WHEN 7    THEN record.findid   EL                                   SE 0  END) FROM (record, program INNER JOIN channel       ON channel.chanid = pr                                   ogram.chanid)  WHERE record.type <> 11 AND record.search = 0 AND program.manuali                                   d = 0 AND program.title = record.title  AND channel.visible = 1  AND program.end                                   time > (NOW() - INTERVAL 480 MINUTE) AND (((record.filter & 1) = 0) OR (program.                                   previouslyshown = 0)) AND (((record.filter & 2) = 0) OR (program.generic = 0)) A                                   ND (((record.filter & 4) = 0) OR (program.first > 0)) AND (((record.filter & 8)                                    = 0) OR (HOUR(CONVERT_TZ(program.starttime, 'UTC', 'SYSTEM')) >= 19 AND HOUR(CON                                   VERT_TZ(program.starttime, 'UTC', 'SYSTEM')) < 22)) AND (((record.filter & 16) =                                    0) OR (channel.commmethod = -2)) AND (((record.filter & 32) = 0) OR (program.hd                                   tv > 0)) AND (((record.filter & 64) = 0) OR ((record.programid <> '' AND program                                   .programid = record.programid) OR (record.programid = '' AND program.subtitle =                                    record.subtitle AND program.description = record.description))) AND (((record.fi                                   lter & 128) = 0) OR ((record.seriesid <> '' AND program.seriesid = record.series                                   id))) AND (((record.filter & 256) = 0) OR (ABS(TIMESTAMPDIFF(MINUTE, CONVERT_TZ(                                     ADDTIME(record.startdate, record.starttime), 'Etc/UTC', 'SYSTEM'),   CONVERT_T                                   Z(program.starttime, 'Etc/UTC', 'SYSTEM'))) MOD 1440 <= 10)) AND (((record.filte                                   r & 512) = 0) OR (ABS(TIMESTAMPDIFF(MINUTE, CONVERT_TZ(  ADDTIME(record.startdat                                   e, record.starttime), 'Etc/UTC', 'SYSTEM'),   CONVERT_TZ(program.starttime, 'Etc                                   /UTC', 'SYSTEM'))) MOD 10080 <= 10)) AND (((record.filter & 1024) = 0) OR (chann                                   el.callsign = record.station)) AND ( (record.type = 4   OR record.type = 6   OR                                    record.type = 2   OR record.type = 5)  OR   ((record.type = 1    OR record.type                                    = 7    OR record.type = 8)   AND    ADDTIME(record.startdate, record.starttime)                                    = program.starttime    AND    record.station = channel.callsign) )
..... is normal and like Raymond said, would understandably take a little while to run?

I just ran "mysqlcheck" on "--all-databases", which is possibly what the mythtv optimise script does anyway. Anyway, that seems to have helped a bit. I am planning on blasting my channels and doing a re-scan etc this week, still some channels left over from a currently bust tuner, which could be causing some problems.

Cheers all

moorsey
Newcomer
Posts: 10
Joined: Sat Feb 08, 2014 11:35 am
Great Britain

Re: MYSQL hammers HDD on backend startup

Post by moorsey » Thu Feb 13, 2014 7:46 pm

Just a little update to this.

Myth now running on SSD and Haswell i5, which of course alleviates the slowness issues. Hopefully nothing is bust with my install / DB and it was just my old hardware and large DB causing the issues.

I was however looking through mythconverg in phpmyadmin and noticed some tables with a lot of rows. Well seemed like a lot, anyone else have figures to compare?

credits - 9,324,497
recordedseek - 6,370,480
program - 3,832,911

Are the top 3, drops down to 10s of thousands after that.

Credits seemed somewhat unusual to be so big??

Cheers

User avatar
rwagner
Developer
Posts: 217
Joined: Thu Feb 06, 2014 11:37 pm
United States of America

Re: MYSQL hammers HDD on backend startup

Post by rwagner » Thu Feb 13, 2014 8:20 pm

Recordedseek holds all your seek data, so expect around 2 entries per second of recording. Right now, my system is around 5.75M entries. The credits and programs are outliers. Credits holds cross references between cast members and the recordings and guide data they are associated with. In that regards, the proportion between credits and programs entries is reasonable. Programs holds all your guide data. With 135 channels and two weeks of data (actually three since it keeps a week of history), I only have around 100k entries.

moorsey
Newcomer
Posts: 10
Joined: Sat Feb 08, 2014 11:35 am
Great Britain

Re: MYSQL hammers HDD on backend startup

Post by moorsey » Tue Mar 04, 2014 11:57 am

Sorry for taking a while to reply to this. Have been waiting for a good time to re-scan, removing and re-adding all tuners, to see if it was a load of junk making the DB size huge.

But, after this, the DB backup is still as big, now up to 420MB:

Table sizes:

credits - 10 million rows
recordedseek - 6.5 million rows
program 4 million rows

Going to rwagners comparison, recordedseek is most likely normal. But looking at program for example, rwagner has 100k for 135 channels, 3 weeks of program data. I have 4 million rows for 116 channels and 3 weeks of data!! Which I guess is why the credits is so big, because of the relationship between the 2.

Anything I can do to see what is going wrong here? Can I cull the program table and re-run mythfilldatabase? I did run "mythfilldatabase --refresh-all", but that hasn't changed things.

Cheers!

User avatar
dekarl
Developer
Posts: 228
Joined: Thu Feb 06, 2014 11:01 pm
Germany

Re: MYSQL hammers HDD on backend startup

Post by dekarl » Tue Mar 04, 2014 4:27 pm

moorsey wrote:Table sizes:
program 4 million rows

Going to rwagners comparison, recordedseek is most likely normal. But looking at program for example, rwagner has 100k for 135 channels, 3 weeks of program data. I have 4 million rows for 116 channels and 3 weeks of data!! Which I guess is why the credits is so big, because of the relationship between the 2.
Do you have lots of guide data in the past? (older then two weeks) If so you might want to look at the following values.
What are CleanOldRecorded and NewEpisodeWindow set to? (In the settings table)
When did DBCleanup last run? (In the housekeeping table)

Regards,
Karl

moorsey
Newcomer
Posts: 10
Joined: Sat Feb 08, 2014 11:35 am
Great Britain

Re: MYSQL hammers HDD on backend startup

Post by moorsey » Tue Mar 04, 2014 4:37 pm

Thanks Karl,

Looking in MythWeb, only the usual 1 week back and 2 weeks forward showing

In the settings table, I do not have either of those entries at all.

In housekeeping, DBCleanup ran yesterday (hostname null). DailyCleanup has not ran since September however, if related

Thanks

Martin

Post Reply