[mythtv-users] database size

Wed Apr 2 11:09:48 UTC 2008

On Wed, Apr 2, 2008 at 12:52 AM, Frank Lynch <frank.lynch at gmail.com> wrote:

>
>
> On Wed, Apr 2, 2008 at 12:31 AM, Frank Lynch <frank.lynch at gmail.com>
> wrote:
>
> >
> >
> > On Tue, Apr 1, 2008 at 10:36 PM, Michael T. Dean <
> > mtdean at thirdcontact.com> wrote:
> >
> > > On 04/01/2008 10:14 PM, Frank Lynch wrote:
> > > > On Tue, Apr 1, 2008 at 10:05 PM, Michael T. Dean wrote:
> > > >> On 04/01/2008 09:53 PM, Frank Lynch wrote:
> > > >>
> > > >>> I've been running mythtv happily for a couple of years, tonight I
> > > >>> decided to upgrade to 0.21 & migrate from Fedora Core 4 to ubuntu
> > > >>> gutsy. All appears to be going well at the moment.
> > > >>> However during the process I backedup my db & noticed that its a
> > > >>> whopping 2.2GB!
> > > >>>
> > > >> By what measure?  Looking at the binary files?  Looking at the size
> > > of
> > > >> the backup?  Basically, it sounds large, but I want to verify that
> > > >> you're using some appropriate means of measuring the size.
> > > >>
> > > >
> > > >
> > > > the 2.2GB file is the output from mysqldump for mythconverg before I
> > > gzip'd
> > > > it. It compresses down to ~200 mb.
> > >
> > > Better would be a long listing of the MySQL data directory.  I'd
> > > actually be interested in seeing one (perhaps today--while large, and
> > > tomorrow--which could be much smaller).  Something like:
> > >
> > > ls -lS /var/lib/mysql/mythconverg
> > >
> > > or
> > >
> > > ls -lS /srv/mysql/mythconverg
> > >
> > > depending on where your distro puts the DB data.  Since there are so
> > > many tables, feel free to bzip2 the output (or, if it's just a few
> > > tables at the top, you can chop it with " | head -n 30" or something).
> > >
> > > The only table that should be very large is recordedseek, as it
> > > contains
> > > a couple of records per second of recorded video (and, if using
> > > mythcommflag --video, per MythVideo video).  So, extrapolating from my
> > > 65MB data/60MB index of recordedseek for 348 hours of recorded TV
> > > taking
> > > up >1.8TiB of storage (and considering all other data would be
> > > negligible), to really have a multi-gigabyte DB, you'd need to have A
> > > Whole Lot (sorry about the technical terms) of recorded video.
> > >
> > > Mike
> > >
> >
> > I've got a reasonable amount of tv/video but not that much. When I view
> > the db using your method its:
> > -rw-rw---- 1 mysql mysql  24M 2008-04-01 23:56 recordedseek.MYI
> > -rw-rw---- 1 mysql mysql  21M 2008-04-01 23:30 recordedseek.MYD
> > -rw-rw---- 1 mysql mysql 4.2M 2008-04-01 22:55 music_songs.MYD
> > -rw-rw---- 1 mysql mysql 2.7M 2008-04-01 22:55 music_songs.MYI
> > -rw-rw---- 1 mysql mysql 220K 2008-04-01 17:21 music_albums.MYD
> > -rw-rw---- 1 mysql mysql 163K 2008-04-01 22:53 music_directories.MYD
> > -rw-rw---- 1 mysql mysql 144K 2008-04-01 17:21 music_albums.MYI
> > -rw-rw---- 1 mysql mysql 108K 2008-04-01 17:21 music_playlists.MYD
> > -rw-rw---- 1 mysql mysql 100K 2008-04-01 17:21 music_artists.MYI
> > -rw-rw---- 1 mysql mysql  76K 2008-04-01 17:21 music_artists.MYD
> > -rw-rw---- 1 mysql mysql  35K 2008-04-01 23:56 music_directories.MYI
> > -rw-rw---- 1 mysql mysql  35K 2008-04-01 21:39 nestitle.MYD
> > -rw-rw---- 1 mysql mysql  21K 2008-04-01 21:39 nestitle.MYI
> > -rw-rw---- 1 mysql mysql  15K 2008-04-01 23:56 displayprofiles.MYI
> > -rw-rw---- 1 mysql mysql  11K 2008-04-02 00:03 record.frm
> > -rw-rw---- 1 mysql mysql  10K 2008-04-02 00:03 record_tmp.frm
> >
> > with my .21 gutsy based install.
> > My older FC4 .20.2 install has the following for mythconverg:
> > -rw-rw---- 1 27 sudo  95M 2008-04-01 20:44 recordedseek.MYD
> > -rw-rw---- 1 27 sudo  80M 2008-04-01 20:46 recordedseek.MYI
> > -rw-rw---- 1 27 sudo 5.4M 2008-04-01 18:41 music_songs.MYD
> > -rw-rw---- 1 27 sudo 1.4M 2008-04-01 20:53 music_songs.MYI
> > -rw-rw---- 1 27 sudo 220K 2007-10-18 18:19 music_albums.MYD
> > -rw-rw---- 1 27 sudo 158K 2007-10-19 04:05 music_albums.MYI
> > -rw-rw---- 1 27 sudo 108K 2007-09-06 22:33 music_playlists.MYD
> > -rw-rw---- 1 27 sudo 106K 2007-10-15 08:08 music_artists.MYI
> > -rw-rw---- 1 27 sudo  76K 2007-10-14 22:23 music_artists.MYD
> > -rw-rw---- 1 27 sudo  35K 2007-09-06 22:39 nestitle.MYD
> > -rw-rw---- 1 27 sudo  19K 2007-09-06 22:39 nestitle.MYI
> > -rw-rw---- 1 27 sudo  11K 2007-09-06 22:40 record.frm
> > -rw-rw---- 1 27 sudo  10K 2007-09-06 22:40 record_tmp.frm
> >
> > So its certainly quite a bit smaller already!
> > cheers,
> > --Frank
> >
>
> Sorry cut n' paste error there. The first listing where recordedseek.MYI
> is 24mb is from the new 0.21 / gutsy where as this file was 95mb with .20.2
> / FC4 setup.
>
> I'm seeing some interesting behavior now on with my 0.21 installation. I
> rebooted after getting lirc setup, the frontend & backend were working
> together nicely prior to the reboot.
> However since I rebooted mysql is chewing up a lot of cputime & my backend
> isn't coming up i.e. its not listening on its tcp port(s). The backend log
> has the following:
>
> 2008-04-02 00:38:49.298 New DB connection, total: 2
> 2008-04-02 00:38:49.300 Connected to database 'mythconverg' at host:
> localhost
> 2008-04-02 00:38:49.309 Current Schema Version: 1214
> Starting up as the master server.
>  2008-04-01 23:34:53.428 DB Error (DelLogEntry#2):
> Query was:
> DELETE FROM mythlog WHERE logid= 1236033 ;
> Driver error was [2/1205]:
> QMYSQL3: Unable to execute query
> Database error was:
> Lock wait timeout exceeded; try restarting transaction
>
> I should probably mention that I was reviewing all my mythtv settings
> after doing the upgrade & noticed that I was logging a lot of events to the
> db, so I put some more sane limits in place. I'm guessing that mysql is now
> very busy clearing out old log entries?
> I'm thinking that I'll let it work away over night & take another look in
> the morning.
>

Sure enough all is looking well this morning. I'm starting to quite like
0.21 already, many thanks to all involved. I am seriously missing the
blootube-wide theme tho. :-(

cheers,
--Frank
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://mythtv.org/pipermail/mythtv-users/attachments/20080402/5955e41b/attachment-0001.htm 