Multiple database and storage engine support #2593

This ticket narrows the scope of changes contemplated in #1368:

• Zend_Db supported adapters
  • e.g., Postgresql and Mssql
  • NoSQL is out of scope for Piwik 1.x
• MySQL storage engines
  • e.g., InnoDB
  • possibility of subclassing would allow a Sharding schema
• Use Zend_Db for the tracker
  • eliminates some redundancy, eg core/Tracker/Config and core/Tracker/Db
  • increases cohesiveness of core/Db and lowers the learning curve for new devs
  • eliminate more require_once in favor of the auto loader everywhere

ToDo:

• Installation
  • dropdown for schemas
• Piwik.php:
  • move MySQL-specific bulk loader to Schema/Myisam.php; leave a wrapper behind
• better support for users with the resources to run db clusters:
  • add Db::factory($dbConfigName); see read vs write, tracker vs reports
  • always use Zend_Registry::get($dbConfigName)->methodName() and remove the plugin helper functions: Piwik_Exec, Piwik_FetchAll, Piwik_FetchRow, Piwik_FetchOne, and Piwik_Query
• (vipsoft: further scope analysis of current code in trunk)

Attachment: Sqlrelay - Tracker adapter
tracker_Sqlrelay.php

How does MSSQL support currently look like?

dk: there's a stub adapter; it's not functional yet.

Proposed dbConfigNames:

• 'log-master' will primarily be used by the tracker
• 'log' and 'archive-master' will be used by archive processing
• 'archive' will be used by reports
• 'piwik' and 'piwik-master' are Piwik's other tables (eg option, session, sites, users, etc)

Alternate naming scheme: log_r, log_w (or log_rw)

This compat-buster may break some third-party plugins. To mitigate:

• the existing "database" config is copied to the other configs
• 'db' could be an alias for 'piwik-master'
• cursory inspection of the known 3rd party plugins

Note: For role-based deployment, we need a config file setting that tells the server what role(s) it serves. We won't be carving up the codebase by role (eg "oh, these files are only used by the tracker, etc")

Moving to feature requests, but of course we can keep working on it, I just don't think it will be ready for 2.0... As long as there is no team member keen to build & support another sql driver, then it is unfortunately/ realistically not going to happen, so if you are interested (and have a lot of time :), please let us know here!

Dropping the helper functions would definitely have to be post 1.x (for BC).

Another naming convention I came across is -bp for batch processing (e.g., long running reports).

How are chances that this feature will be ready in 1.x? Any updates?

We will probably not work on this in 1.x - we are however planning to work on nosql support and do some experiments, maybe with mongoDB...

This is certainly something I'd like to work on but depends on how long the 1.x development cycle is and of course, my availability.

hi vipsoft

in your opinion, is there any chance that i could use piwik with mssql for a customer next year?

dk: there's a chance, but not highly probable on the current horizon; how urgent is this? are you able to deploy with mysql initially (e.g., for evaluation, and migrate to mssql later)?

Patch to load plugin-based db adapter, e.g.,

Index: Tracker.php
===================================================================
--- Tracker.php (revision 5686)
+++ Tracker.php (working copy)
@@ -207,6 +207,17 @@
            case 'MYSQLI':
                require_once PIWIK_INCLUDE_PATH .'/core/Tracker/Db/Mysqli.php';
                return new Piwik_Tracker_Db_Mysqli($configDb);
+
+           default:
+               $name = ucwords(str_replace('_', ' ', $configDb['adapter']));
+               $pluginName = str_replace(' ', '', $name);
+               $pathName = PIWIK_INCLUDE_PATH . '/plugins/' . $pluginName . '/Tracker/Db/' . str_replace(' ', '/', $name) . '.php';
+               $className = 'Piwik_' . $pluginName . '_Tracker_Db_' . str_replace(' ', '_', $name);
+               if (file_exists($pathName))
+               {
+                   require_once $pathName;
+                   return new $className($configDb);
+               }
        }

        throw new Exception('Unsupported database adapter '.$configDb['adapter']);

thank you vipsoft. we will try that one out.
it's not that urgent, but still important.
we setup piwik with mysql now but still need to move to mssql.
how about performance? any experiences with that? any issues?

A few questions regarding multi database support :

• Would a project like Doctrine DAL (http://www.doctrine-project.org/projects/dbal.html) be a credible option to implement this ?
• If someone is volonteering for this bounty would it be possible to implement this on 1.x, 2.0 or would it be postponed to further releases ?

Doctrine wouldn't be the preferred solution.

It would be implemented in 2.X or 3.0 possibly, if someone volunteers or if we find enough sponsors (get in touch with us if you are interested -- hello@piwik)

See also #3914 Sqlite support and #500 Postgresql support

any updates on PostgreSQL support? It appears that #500 doesn't exist anymore.

Same with Issue #3914, SQLite support, the issue no longer exists. I'm having huge problems with Piwik because of the large traffic it recreates over http/s, and the memory requirements of MySQL. This is not at all feasible for smaller sites.

FYI the issues are back (it was a temporary github glitch)

I'm having huge problems with Piwik because of the large traffic it recreates over http/s, and the memory requirements of MySQL. This is not at all feasible for smaller sites.

Maybe you could consider Cloud-hosted Piwik? https://piwik.org/hosting/

Cloud-hosted is against our privacy and security requirements. We are migrating to a custom log analysis tool which will fix all these problems with Piwik (since we won't be using Piwik).

Sounds OK, and btw, Piwik MySQL requirements are not big or special, you just need enough RAM and CPU to run MySQL which is easy and cheap these days (especially for a small site)