I'm undecided on this show for little ones. Our 5yo son didn't flip out, but he was rattled by the villain stuff in Fantasmic (which IMO got a bit excessive and unnecessary--don't they have more happy things to focus on at WDW? Special effects were great, but it was overkill) Overall, he was ok with everything else in the show. I mean, who can tell with kids? He was terrified by the Haunted Mansion and was sobbing by the end of that, yet rode Space Mountain no sweat. I think he's not as scared by mechanical thrills as by things involving the imagination, which, of course, is what Fantasmic is all about. So you need to assess what kinds of things frighten your kids.
DD2 was very scared by the Villain portion of Fantasmic and kept her eyes hidden behind my hands the whole time. (She loved the Princesses though). She also had nightmares that night--that was a real treat at 2 am when I had to crawl in bed with her and she wouldn't let me leave all night. The kid behind us (age 2-3??) at the show was screaming in fright THROUGHOUT Fantasmic.
In sum, I would say, at age 4-5, it's probably ok, but kids younger than that (caveat, everyone's different) might be overwhelmed.