Cannon balls i imagine would be traveling around 300 ish mph. However as you said about the CGI show its not really dramatizing, cannonballs on their own are fairly small and do not do large quantities of damage, a lucky shot may hit the mast and send splinters flying, however if you have a broadside with 60 guns, you're really going to do some damage and it will be very devastating.
Another idea i would like to debunk is the fact that some people believe that if you hit the magazine of 17th century ships, they will explode which is highly unlikely, most nearing the Napoleonic era had magazines en lined with copper, and even if a cannonball went right through one side of a ship, through the magazine and out the other side, unless there was an open flame nearby the most that would happen is a mess of gunpowder all over the floor...