My friend and I calculated a good size to use for CDXL movies a few years ago. It works best with movies that are around 2.35:1 aspect ratio, like Star Wars, Lord of the Rings and other very widescreen films. The CDXL video itself is 320x64, and the CDXL player uses the Copper to double the height of the video to 320x128, so it fits the full width of the screen (without overscan anyway). This format runs on a 2x CD drive like the CD32, we made a couple of video clips in both HAM8 and 256 colours (256 colour CDXL can have a different palette per frame with 24bit depth so can sometimes give nicer and clearer results than the 4,096 colour locked palette of HAM6. Of course on an A500 you could not view a 256 colour CDXL, so the 4,096 colour format is fine. You can fit around 40-45 minutes of CDXL video in this size on a CD, so if you converted a few 1.5hour movies you could fit them on 2 CDs, just like Video CDs which usually came on 2 discs.
Of course if you were reading the files from a hard drive you could make them higher resolution because the Amiga can read the images faster, but then the files end up being much larger too. You could also get some cartoons and convert them to CDXL in 16 or 32 colours, which would keep the frame file size smaller and let you use a larger size.
Unfortunately there's no easy to use software to convert video files to CDXL, and it's a long process involving several steps and programs to extract the frames, sort and delete unneeded frames, calculate framerates and audio playback rate, batch processing the frames, resizing and remapping them, recombining them into an animation then merging the audio... it takes a while, and because of the effort involved it seems no one has really converted and uploaded CDXL movies for people to download and watch.
But using the CDXL format, the A500 is totally capable of playing movies if you have enough storage space to keep the videos.
Also, you can convert MP3s to 8SVX/IFF or WAV format and use a player on the Amiga to play them back. Some players have playlists and tapedeck style controls like WinAmp and other popular players, so it can sort of look and feel like you're listening to a collection of MP3s on your Amiga. The sound quality won't be as good as a modern PC, since the Amiga only has 8bit audio but if the music is resampled right when it's converted it can still sound pretty good on the Amiga.
I think you should look up the upcoming ACA520 accelerator. It will probably have a 28Mhz 68EC020 CPU, 8MB FastRAM, IDE and a CF slot, Clockport and maybe Ethernet too, so it would make an A500 internet capable, or at least expand it enough to run a decent Workbench system and give you the storage options to keep a collection of uncompressed videos and music.
