Pruning the mythtv database, possible?

[monkeypet]

Hi all, so my mythtv database has been running for many many many years and have grown over time, I am at 4.5G for my database alone. I've been running the optimize script on the db about once a month. However, I was wondering if there was a script or safe way to reduce the size of the database while still keeping my recordings, etc?

OK, so the reason why I am asking is because my system doesn't have a lot of memory (8GB + 27GB of swap) and sometimes the oom-killer launches and kills mythtvbackend and also the mariadb. I only have mythtv on this Linux Server running Fedora 37. I suspect that when the mythcommflag runs it pushes the memory usage up.

Code: Select all

[root@mythtv2 mysql]#  du -sh /var/lib/mysql/mythconverg
4.5G    /var/lib/mysql/mythconverg

[white_haired_uncle]

Just curious, is it recordedseek taking up the space? My db is only 900MB, but about 800MB of that is recordedseek. It's on my list of things to investigate, but not real high on the list.

FWIW, I have about 5000 saved recordings, with 4000 distinct recordings (and 17million rows) in recordedseek in a 19yr old db.

[monkeypet]

My largest tables are the following:

Code: Select all

+--------------------------------+------------+-------------+--------------+---------+
| TABLE_NAME                     | table_rows | data_length | index_length | MB Size |
+--------------------------------+------------+-------------+--------------+---------+
| programrating                  |    7651221 |   264795236 |    260339712 |  500.81 |
| programgenres                  |   11184329 |   293252236 |    265878528 |  533.23 |
| credits                        |   11776199 |   223747781 |    433906688 |  627.19 |
| program                        |    3190691 |   697221936 |    407111680 | 1053.17 |
| recordedseek                   |   45462635 |  1000224962 |    899936256 | 1812.13 |

Full output below.

Code: Select all

MariaDB [(none)]> use mythconverg;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [mythconverg]> SELECT TABLE_NAME, table_rows, data_length, index_length,
    -> round(((data_length + index_length) / 1024 / 1024),2) "MB Size"
    -> FROM information_schema.TABLES WHERE table_schema = "mythconverg"
    -> ORDER BY (data_length + index_length) ASC;
+--------------------------------+------------+-------------+--------------+---------+
| TABLE_NAME                     | table_rows | data_length | index_length | MB Size |
+--------------------------------+------------+-------------+--------------+---------+
| videometadatacountry           |          0 |           0 |         1024 |    0.00 |
| callsignnetworkmap             |          0 |           0 |         1024 |    0.00 |
| gallery_files                  |          0 |           0 |         1024 |    0.00 |
| scannerpath                    |          0 |           0 |         1024 |    0.00 |
| videogenre                     |          0 |           0 |         1024 |    0.00 |
| diseqc_tree                    |          0 |           0 |         1024 |    0.00 |
| eit_cache                      |          0 |           0 |         1024 |    0.00 |
| mythlog                        |          0 |           0 |         1024 |    0.00 |
| videocategory                  |          0 |           0 |         1024 |    0.00 |
| videopart                      |          0 |           0 |         1024 |    0.00 |
| cardinput                      |          0 |           0 |         1024 |    0.00 |
| dvdbookmark                    |          0 |           0 |         1024 |    0.00 |
| videometadatacast              |          0 |           0 |         1024 |    0.00 |
| bdbookmark                     |          0 |           0 |         1024 |    0.00 |
| gallery_directories            |          0 |           0 |         1024 |    0.00 |
| internetcontentarticles        |          0 |           0 |         1024 |    0.00 |
| networkiconmap                 |          0 |           0 |         1024 |    0.00 |
| powerpriority                  |          0 |           0 |         1024 |    0.00 |
| scannerfile                    |          0 |           0 |         1024 |    0.00 |
| tvosdmenu                      |          0 |           0 |         1024 |    0.00 |
| videocountry                   |          0 |           0 |         1024 |    0.00 |
| diseqc_config                  |          0 |           0 |         1024 |    0.00 |
| recgrouppassword               |          0 |           0 |         1024 |    0.00 |
| videocast                      |          0 |           0 |         1024 |    0.00 |
| videometadatagenre             |          0 |           0 |         1024 |    0.00 |
| iptv_channel                   |          0 |           0 |         1024 |    0.00 |
| user_permissions               |          0 |           0 |         1024 |    0.00 |
| internetcontent                |          0 |           0 |         1024 |    0.00 |
| videopathinfo                  |          0 |           0 |         1024 |    0.00 |
| inputgroup                     |          3 |          96 |         1024 |    0.00 |
| logging                        |          0 |           0 |         2048 |    0.00 |
| videometadata                  |          0 |           0 |         2048 |    0.00 |
| oldfind                        |          1 |           9 |         2048 |    0.00 |
| channelgroupnames              |          2 |          40 |         2048 |    0.00 |
| playgroup                      |          2 |          48 |         2048 |    0.00 |
| tvchain                        |          1 |          92 |         2048 |    0.00 |
| channelscan                    |          5 |          95 |         2048 |    0.00 |
| customexample                  |          1 |         140 |         2048 |    0.00 |
| dvdinput                       |          8 |         288 |         2048 |    0.00 |
| capturecard                    |          3 |         332 |         2048 |    0.00 |
| channelgroup                   |         28 |         364 |         2048 |    0.00 |
| dtv_privatetypes               |         50 |        1560 |         1024 |    0.00 |
| dvdtranscode                   |         11 |         600 |         2048 |    0.00 |
| videotypes                     |         30 |         708 |         2048 |    0.00 |
| recgroups                      |          5 |         100 |         3072 |    0.00 |
| recordfilter                   |         13 |        1304 |         2048 |    0.00 |
| sportscleanup                  |         31 |        1692 |         2048 |    0.00 |
| dtv_multiplex                  |         20 |        1840 |         2048 |    0.00 |
| upnpmedia                      |          0 |           0 |         4096 |    0.00 |
| filemarkup                     |          0 |           0 |         4096 |    0.00 |
| videocollection                |          0 |           0 |         4096 |    0.00 |
| keyword                        |          9 |         180 |         4096 |    0.00 |
| housekeeping                   |         10 |         364 |         4096 |    0.00 |
| recordingprofiles              |         77 |        2060 |         3072 |    0.00 |
| users                          |          1 |          52 |         5120 |    0.00 |
| videosource                    |          1 |          56 |         5120 |    0.00 |
| user_sessions                  |          2 |         184 |         5120 |    0.01 |
| codecparams                    |         53 |        1348 |         4096 |    0.01 |
| sportslisting                  |         95 |        4656 |         2048 |    0.01 |
| livestream                     |         14 |        5404 |         2048 |    0.01 |
| profilegroups                  |         18 |         780 |         9216 |    0.01 |
| storagegroup                   |         17 |         816 |        10240 |    0.01 |
| inuseprograms                  |         80 |        5660 |         7168 |    0.01 |
| channelscan_dtv_multiplex      |        105 |        8820 |         4096 |    0.01 |
| weathersourcesettings          |          0 |       16384 |            0 |    0.02 |
| newssites                      |          0 |       16384 |            0 |    0.02 |
| music_playlists                |          0 |       16384 |            0 |    0.02 |
| weatherscreens                 |          0 |       16384 |            0 |    0.02 |
| websites                       |          0 |       16384 |            0 |    0.02 |
| gallerymetadata                |          0 |       16384 |            0 |    0.02 |
| music_directories              |          0 |       16384 |            0 |    0.02 |
| music_stats                    |          0 |       16384 |            0 |    0.02 |
| displayprofilegroups           |        141 |        5436 |        16384 |    0.02 |
| sportsapi                      |        278 |       16252 |        12288 |    0.03 |
| pidcache                       |       1408 |       15488 |        14336 |    0.03 |
| music_albumart                 |          0 |       16384 |        16384 |    0.03 |
| music_smartplaylist_items      |          8 |       16384 |        16384 |    0.03 |
| music_artists                  |          0 |       16384 |        16384 |    0.03 |
| music_smartplaylist_categories |          3 |       16384 |        16384 |    0.03 |
| music_genres                   |          0 |       16384 |        16384 |    0.03 |
| archiveitems                   |          0 |       16384 |        16384 |    0.03 |
| mythweb_sessions               |          9 |       16384 |        16384 |    0.03 |
| gameplayers                    |          0 |       16384 |        16384 |    0.03 |
| channel                        |        124 |       17248 |        23552 |    0.04 |
| jumppoints                     |        472 |       21484 |        19456 |    0.04 |
| channelscan_channel            |        650 |       47448 |         1024 |    0.05 |
| music_albums                   |          0 |       16384 |        32768 |    0.05 |
| music_smartplaylists           |          8 |       16384 |        32768 |    0.05 |
| weatherdatalayout              |          0 |       16384 |        32768 |    0.05 |
| music_radios                   |          0 |       16384 |        32768 |    0.05 |
| record                         |         81 |       27800 |        23552 |    0.05 |
| recordmatch                    |        512 |       14848 |        37888 |    0.05 |
| recordedartwork                |        894 |       49768 |        16384 |    0.06 |
| music_streams                  |          0 |       16384 |        65536 |    0.08 |
| gamemetadata                   |          0 |       16384 |        81920 |    0.09 |
| music_songs                    |          0 |       16384 |        98304 |    0.11 |
| romdb                          |          0 |       16384 |        98304 |    0.11 |
| displayprofiles                |       2302 |       75708 |        98304 |    0.17 |
| settings                       |       2871 |      123124 |        71680 |    0.19 |
| jobqueue                       |       2081 |      154736 |        78848 |    0.22 |
| keybindings                    |       5360 |      357280 |       124928 |    0.46 |
| recordedfile                   |       5061 |      446236 |       202752 |    0.62 |
| oldprogram                     |      10322 |      341188 |       313344 |    0.62 |
| recordedrating                 |      10621 |      347752 |       520192 |    0.83 |
| roles                          |      41769 |      973800 |      1311744 |    2.18 |
| recorded                       |       5061 |     1886712 |       499712 |    2.28 |
| recordedmarkup                 |      89562 |     1612314 |      1777664 |    3.23 |
| recordedprogram                |      11074 |     2612684 |       796672 |    3.25 |
| recordedcredits                |      79574 |     1511906 |      2905088 |    4.21 |
| oldrecorded                    |      40751 |     9103704 |      8038400 |   16.35 |
| people                         |     342280 |     7947008 |     10403840 |   17.50 |
| programrating                  |    7651221 |   264795236 |    260339712 |  500.81 |
| programgenres                  |   11184329 |   293252236 |    265878528 |  533.23 |
| credits                        |   11776199 |   223747781 |    433906688 |  627.19 |
| program                        |    3190691 |   697221936 |    407111680 | 1053.17 |
| recordedseek                   |   45462635 |  1000224962 |    899936256 | 1812.13 |
+--------------------------------+------------+-------------+--------------+---------+
116 rows in set (0.004 sec)

[white_haired_uncle]

Interesting. My programrating, programgenres, and credits are MUCH smaller than yours, and only contain records for the last few days. Probably because I didn't have metadata collection working/enabled until just then. They also look to me like they would seriously benefit from normalization. Personally, I have basically no use for the data in them. Looks like I need to make sure metadata collection s shut off for all recording rules.

Your program table is serious larger than mine (3M rows vs 72K, 1GB vs 33MB), which is curious. Based on the number of rows in recorded/oldrecorded. I would think ours would be roughly the same size.