The goal of this issue is to research whether Mysql partitioning could be used in Piwik to provide better performance.
A few years ago this was not an option because they didn't support partitioning by datetime column.
Now I see that they added the feature in Mysql 5.6. It could be helpful for partitioning tracker
log_* tables by monthly partition. Sub-partitioning sounds also interesting, so maybe we could sub-partition log data by day.
Hope this information is useful;
Running Matomo with AWS RDS Serverless is for us a cost-effective way to handle our dev-requirements for Matomo. We did encounter an issue where partitioning the tables would have helped our usecase.
In some rare cases, our serverless DB ran out of locale storage: SQLSTATE[HY000]: General error: 3 Error writing file '/rdsdbdata/tmp/MYB7XJVs' (Errcode: 28 - No space left on device). This if of course hard to fix when it is serverless :)
AWS Support gave us two options to solve this:
We opted for option 1 for now, option 2 would be nice to have upstream, and we might implement it but we would rather work on a as-close-to-upstream config as possible.
I'm not sure how option 2 this would impact write or read performance, since it probably depends on the storage backend and database engine. If the DB could read from multiple partitions when doing large queries I would imagine that would help the overall performance, given the storage backend is supportive of it.