As you pointed it out, one particular problem is the DB schema upgrade. I have a 160GB database and it took nearly and entire day to upgrade from 2.8.1 to 2.10.0.
Actually, it could be more acceptable if only tracking worked during that time.
Upgrading the core tables was "quite fast" whereas the archive tables took soooo long and we lost an entire day of data.

I know the piwik team is thinking about implementing alternate DBMS so it may not be good idea to focus on a mysql-specific solution here (cf. idea 3).

We are still several versions back because of this issue. Basically, what we have come down to is an annual Piwik upgrade cycle. We want to keep the old log tables, but they will never be upgraded. So, once a year, we make a backup of all of the log tables, remove all but the most recent data, and run an upgrade. Even if the process that removes data after X days archived the data instead of deleting it, that would be good enough for us to upgrade on a more regular basis.

@mattkolb You can setup Piwik to purge old log data (e.g. older than 180 days), see this FAQ.

A first step in the right direction for this issue would be to inform all Piwik users of the best database to use that would allow hot ALTER schema upgrades. For example Mariadb and Mysql 5.6 have good features.

feel free to share your findings so it helps other Piwik admins

I'm aware of the purging feature. Purging is not the same as archiving. With purging there really is nothing to go back to. By archiving (aka something like "INSERT INTO piwik_log_visit_archive SELECT * FROM piwik_log_visit WHERE idvisit < X" followed by a DELETE), it allows for a backup and makes update time go a lot faster. You only need to have Piwik down during the ALTER TABLE for piwik_log_visit and can bring it back up while the ALTER TABLE runs for the archive.

it allows for a backup and makes update time go a lot faster.

fyi: If we had such an archive log table, we would still need to Update this archive log table via ALTER and it would be time consuming. advantage is that such ALTER wouldn't "block" tracking in log_* tables, but still one would have to wait hours or days for the archive log tables to be altered. We recommend to use tokudb and mariadb for now, which provide the best performance. feel free to talk to us also as we can help with high traffic piwik servers.