Performance & Scalability improvements in Piwik: ideas to research

[mattab]

From #386, some ideas we gathered that could potentially improve Piwik performance and scalability:

• Investigate mysql based, column Database engines, such as Infinidb (see existing discussion in trac or infobright or TokuDB
• Investigate Nosql DBs (eg. Mongodb) (see our Gsoc proposal) - See NoSQL support #2592
• in case of Column based DB engine, or Nosql / mongodb / hadoop, we should consider using a hybrid solution where the log_ tables and possibly archive_ tables are using the new db engine, and other tables are still using Mysql. This way we dont have to rewrite all the SQL queries and only focus the rewrite on the analytics workload.
• Investigate Innodb performance VS Myisam => Winner = innodb Make Innodb the new default type for all new Piwik installations (VS Myisam) #4611

Algorithms

• store reports in json encoded VS serialized? Test space + CPU (see link) - Update: happening in Store blob archives more efficient #7375
• Datatable: store column names index in the datatable VS in each row as done currently.

Other performance related tickets are: #409, #5, #134, #766, #1077, #3126

[mattab]

I tried to use json as a replacement, but forgot that we serialize objects... so it doesn't work

Recoverable Error: Argument 1 passed to Piwik_DataTable::addRow() must be an instance of Piwik_DataTable_Row, instance of stdClass given, called in trunk\core\DataTable.php on line 996 and defined in trunk\core\DataTable.php on line 497 

For the record I attach the patch.

So to use JSON, we should create the DataTable when loading the blob. This is doable, just a bit more work. Definitely worth testing more.

[mattab]

• Could we get rid of the idlink_va primary key in the log_link_visit_action table? it is useless as it is, appart from the practicality that it auto increments. We could put up the primary key as compound index.

[anonymous-matomo-user]

The MongoDB way would definitely be interesting. Is it still an active topic ?

[mattab]

It is still an active topic of discussion and brainstorming, but we haven't scheduled any work, besides issues and pull requests linked below.

If you have knowledge and are a developer, feel free to experiment or work with us, it would be great!

[mattab]

Marking this meta issue closed as topics are now covered in separate issues and there are quite a few issues & good ideas tagged with Performance label.