It's not "hype", but whether or not it's useful to you depends on your site and how people are using it. If you have few static assets (everything's dynamic) and few users who visit infrequently, then you might even notice a slight slowdown since user-agent caches are faster than server caches for retrieval and there's an extra step between the user and the site. (It's fast, but it's still an extra step.) If your site sees even modest usage, then moving your static assets (images, stylesheets, javascript, etc.) to a distributed CDN is going to speed things up considerably -- every user is essentially generating a cache for those assets for every other subsequent user, so your server can concentrate on serving only new or generated content. (This is a particular benefit if you're using SASS/LESS on the server to generate stylesheets.)
But that's just the day-to-day thing. There's also the DoS protection to think about. It's not just about organised black hat activity or botnets aimed at you, there's also the slashdot/reddit/HN effect to think about. All it takes is one user to think that something on your site is interesting (or worse, a hilarious error) and your site can be down for the count -- you can run over CPU or DB usage limits, run out of bandwidth (depending on your hosting plan) etc., and getting your site back online (if it's at all important to you) can put you out of pocket considerably. A service like Cloudflare can handle the viral for you (assuming you're not using header values that prevent it from doing so) and it can actively shut down anything that looks organised (which would include inadvertent DoS attacks as well, like CURL or WGET calls stuck in a loop).
But again, if you have next to nothing that sits still, few or no new users, and all of your major static assets are already being served by a CDN, then all you get is the DoS protection. It's up to you to decide whether or not the very slight performance penalty is worth it.