I think it depends on the kind of music to a certain degree.
I've seen some great arena rock shows. Rush, Iron Maiden, ZZ Top, and others, and for those "larger than life" acts, an arena is the perfect size. A stadium is just too big, but a decent sized arena works great for that sort of thing. One of the best shows I've ever seen was Janet Jackson at an arena. I went in not being terribly interested (record company tickets) and left understanding why the Jackson family is rich and famous.
Another great venue is the outdoor pavilion. I saw Jimmy Buffet and a few other shows at this outdoor pavilion in the Detroit area called Pine Knob. There's a similar venue here in Phoenix where I've seen Fleetwood Mac, Van Halen, KISS, and others. It's halfway between an arena and a festival, and adds a party element to the show.
Speaking of festivals, I've been to a few! We have this one in Arizona called Country Thunder, and as I worked in country radio I went there at least a dozen times. It's a party with 100,000 people where a band just happens to be playing on stage. It's more of an "event" than a concert, and the entertainment comes as much from the people-watching as it does with the multiple acts over several days. My view is a bit skewed because I was usually "working" while there, but it's a helluva thing to see. Especially from the stage.
At the other end of the spectrum, there was this bar that has long since closed where we hosted some up-and-coming country acts. It held maybe a thousand people. For me it's a toss-up for the best show I saw there. 20 years ago it was Keith Urban, who only had one solo album under his belt, so he filled in his time with a set of acoustic classic rock tunes. He's more well known for being Nicole Kidman's husband and from American Idol, but the man can play. A few years on there was this young guy named Blake Shelton, who showed up with an acoustic guitar - no band - and had the crowd eating out of his hand inside of five minutes. I was standing off stage watching and thinking "this guy is going to be a huge star."
One of my favorite venues is a small "in the round" theater which is unfortunately located in a neighborhood that's become less savory over the years. Their tagline was "no seat more than 75 feet from the stage," and I saw Peter Frampton there. It had an intimate feel which worked really well.