The biggest problem with Piwik is that is not scalable and the cost ($) with servers to store analytics data. I saw many cases that Piwik not supported the big volume of data.
Piwik is scalable up to at least 1 billion actions per month. Piwik is scalable! But it is not cheap, as one needs powerful database servers with a lot of RAM and fast SSD disks. It can be costly, but Piwik scales!
It can scale pretty far before it becomes an issue. Years ago I ran it on our main DB for sites that got over 1 million visits a month with no noticeable overhead. If I had it on its own server with its own DB it could have handled far more traffic.
That's a difficult thing to answer. However the more important problem is loss of data whenever your DB isn't available due to downtime, upgrade etc. It depends how important data loss is for your user case. I'm a data completist but I'm in therapy for it ;)
It's definitely worth playing with, and trivially easy to spin up. Other self-hosted options aren't anything like as simple to get up and running.
Well, but the point was MySQL + Piwik "does not scale" and that it's "expensive" besides, which doesn't comport with my experience and sounds like received wisdom.