[mythtv-users] database size

[Frank Lynch]

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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://mythtv.org/pipermail/mythtv-users/attachments/20080402/3818d7eb/attachment.htm