<html>
  <head>
    <meta content="text/html; charset=windows-1252"
      http-equiv="Content-Type">
  </head>
  <body bgcolor="#FFFFFF" text="#000000">
    I only see recent timestamps in the table corresponding to the mpeg2
    recordings I have.   <br>
    <br>
    mysql> select count(*) from recordedseek;<br>
    <br>
    +----------+<br>
    | count(*) |<br>
    +----------+<br>
    |    33110 |<br>
    +----------+<br>
    <br>
    I used to have a bunch of scripts that audited the database and
    clean things up like this, but most have been retired over the years
    as they got fixed in the code.  <br>
    <br>
    Do you have any recent recordings that were deleted and still show
    up in the table? By the age of your system,  I'm guessing this might
    be an old bug and the data never got cleaned up.  <br>
    <br>
    <br>
    <div class="moz-cite-prefix">On 04/20/2015 07:09 PM, Steve E wrote:<br>
    </div>
    <blockquote cite="mid:55359527.5020205@sbcglobal.net" type="cite">
      <meta http-equiv="content-type" content="text/html;
        charset=windows-1252">
      <br>
      <div class="moz-forward-container">
        <meta http-equiv="content-type" content="text/html;
          charset=windows-1252">
        I was looking through the MythTV mysql database to try to<br>
        figure out where the current position in a recording is stored.<br>
        Based on the name, I thought <b>recordedseek</b> might be the
        spot.<br>
        <br>
        <blockquote><tt>mysql> <b>describe recordedseek;</b></tt><br>
          <tt>+-----------+-----------------------+------+-----+---------------------+-------+</tt><br>
          <tt>| Field     | Type                  | Null | Key |
            Default             | Extra |</tt><br>
          <tt>+-----------+-----------------------+------+-----+---------------------+-------+</tt><br>
          <tt>| chanid    | int(10) unsigned      | NO   | PRI |
            0                   |       |</tt><br>
          <tt>| starttime | datetime              | NO   | PRI |
            0000-00-00 00:00:00 |       |</tt><br>
          <tt>| mark      | mediumint(8) unsigned | NO   | PRI |
            0                   |       |</tt><br>
          <tt>| <font color="#ff0000">offset    | bigint(20) unsigned</font>  


            | NO   |     | NULL                |       |</tt><br>
          <tt>| type      | tinyint(4)            | NO   | PRI |
            0                   |       |</tt><br>
          <tt>+-----------+-----------------------+------+-----+---------------------+-------+</tt><br>
          <tt>5 rows in set (0.00 sec)</tt><br>
          <br>
          <br>
          <tt>mysql> <b>select * from recordedseek;</b></tt><br>
          <tt>+--------+---------------------+--------+-------------+------+</tt><br>
          <tt>| chanid | starttime           | mark   | offset      |
            type |</tt><br>
          <tt>+--------+---------------------+--------+-------------+------+</tt><br>
          <tt>|   1111 | 2010-04-04 05:00:00 |  44794 |  2181271880 |   
            9 |</tt><br>
          <tt>|   1111 | 2010-04-04 05:00:00 |  44781 |  2180506720 |   
            9 |</tt><br>
          <tt>|   1111 | 2010-04-04 05:00:00 |  44766 |  2179733476 |   
            9 |</tt><br>
        </blockquote>
        ...<br>
        <br>
        This went on forever, until I typed Ctrl-C to terminate the
        operation.<br>
        <br>
        <blockquote><tt>|   1091 | 2009-01-17 21:00:00 |  49644 | 
            3156051316 |    9 |</tt><br>
          <tt>|   1091 | 2009-01-17 21:00:00 |  49644 |  3156051316 |   
            9 |</tt><br>
          <tt>Ctrl-C -- sending "KILL QUERY 52407" to server ...</tt><br>
          <tt>^CCtrl-C -- query aborted.</tt><br>
          <br>
          <tt>+--------+---------------------+--------+-------------+------+</tt><br>
          <tt><b><font color="#ff0000">2280198</font></b> rows in set
            (3.00 sec)</tt><br>
        </blockquote>
        <br>
        This message rather surprised me. I've got 2.28 million <b>recordedseek</b>
        rows<br>
        in my database? Granted, I currently have a large number of
        recorded shows (234),<br>
        but that's averaging 9744 recordedseek instances per recording,
        which is ridiculous.<br>
        <br>
        It seems likely that a large number of <b>recordedseek</b>
        instances are not being<br>
        deleted from the database when the corresponding recording is
        deleted. Some<br>
        of the dates listed above are from 2009, so I've been
        accumulating them for at<br>
        least 6 years.<br>
        <br>
        Do other developers / users have really large numbers of these
        rows in<br>
        their databases?<br>
        <br>
        You can retrieve an approximate count using the following
        syntax:<br>
        <br>
        <blockquote><tt>mysql> <b>show table status like
              'recordedseek';</b></tt><br>
          <tt>+--------------+--------+---------+------------+---------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------+</tt><br>
          <tt>| Name         | Engine | Version | Row_format | Rows    |
            Avg_row_length | Data_length | Max_data_length  |
            Index_length | Data_free | Auto_increment |
            Create_time         | Update_time         |
            Check_time          | Collation       | Checksum |
            Create_options | Comment |</tt><br>
          <tt>+--------------+--------+---------+------------+---------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------+</tt><br>
          <tt>| recordedseek | MyISAM |      10 | Fixed      | <b><font
                color="#ff0000">2280198</font></b> |             25 |   
            61642050 | 7036874417766399 |     67974144 |   4637100
            |           NULL | 2014-12-06 17:48:53 | 2015-04-19 23:49:30
            | 2015-04-18 07:42:10 | utf8_general_ci |     NULL
            |                |         |</tt><br>
          <tt>+--------------+--------+---------+------------+---------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------+</tt><br>
          <tt>1 row in set (0.00 sec)</tt><br>
          <br>
          <tt>mysql></tt> <br>
        </blockquote>
        Any theories on why these keep accumulating?<br>
        <br>
        The size of this database table worries me. I wonder if it's
        slowing down<br>
        my system performance. Lately, my recording playback has been<br>
        pausing for 2 or 3 seconds every time I hit the top of an hour,
        even<br>
        when there is no recording currently going on. Would the
        insertion<br>
        of another instance of <b>recordedseek</b> every hour cause
        such a delay<br>
        due to the size of the table?<br>
        <br>
             - Steve<br>
        <br>
        <br>
        <br>
      </div>
      <br>
      <br>
      <fieldset class="mimeAttachmentHeader"></fieldset>
      <br>
      <pre wrap="">_______________________________________________
mythtv-dev mailing list
<a class="moz-txt-link-abbreviated" href="mailto:mythtv-dev@mythtv.org">mythtv-dev@mythtv.org</a>
<a class="moz-txt-link-freetext" href="http://lists.mythtv.org/mailman/listinfo/mythtv-dev">http://lists.mythtv.org/mailman/listinfo/mythtv-dev</a>
<a class="moz-txt-link-freetext" href="http://wiki.mythtv.org/Mailing_List_etiquette">http://wiki.mythtv.org/Mailing_List_etiquette</a>
MythTV Forums: <a class="moz-txt-link-freetext" href="https://forum.mythtv.org">https://forum.mythtv.org</a></pre>
    </blockquote>
    <br>
  </body>
</html>