@mattab opened this Issue on November 23rd 2014 Member

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.

@toredash commented on January 21st 2019

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:

  • Increase the number of ACUs when expecting queries that consumes a lot of local storage
  • Partition your tables into smaller sizes so they do not consume all the local storage

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.

@tsteur commented on January 21st 2019 Member

You could try to partition based on log_visit.visit_last_action_time and log_link_visit_action.server_time for example but we haven't run any tests yet. Maybe you could ask in the forum or check if other people already talked about it: https://forum.matomo.org

Powered by GitHub Issue Mirror