Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Add support for TiDB #19655

Open
bx80 opened this issue Aug 21, 2022 · 4 comments
Open

Add support for TiDB #19655

bx80 opened this issue Aug 21, 2022 · 4 comments
Labels
Enhancement For new feature suggestions that enhance Matomo's capabilities or add a new report, new API etc.
Milestone
For Prioritization

Comments

@bx80
Copy link
Contributor

bx80 commented Aug 21, 2022

After the linked general database abstraction issues have been completed it should be possible to create a new PDO adapter and schema definition to trial running Matomo entirely on TiDB. an open source distributed MySQL compatible database.

This change will allow for long term suitability testing of TiDB as an alternative high-performance database backend for Matomo.

Suggested Approach

  • Create a new PDO adapter core/Db/Adapter/Pdo/Tidb based on core/Db/Adapter/Pdo/Mysql.php.

    • Fully implement all requirements of core/Db/AdapterInterface.
    • Implement getLock() using the approach in the lockalt branch.
    • supportsWindowFunctions() to return true.
    • preferSubqueries() to return true.
    • Create a new OmniFixture dump compatible with the TiDB schema and return it's filename with `getOmniFixtureFilename()'.
    • Check and set the global sql_mode to disable strict grouping operators.

  • Create a new schema class at core/Db/Schema/Tidb

    • Use AUTO_RANDOM Primary keys.
    • Set TiFlash replicas for all log_xxx tables, this may need config settings for clusters with multiple replicas.
    • Create TiDB specific indexes.
    • TEXT columns cannot have default values, remove the defaults.

Related general database abstraction issues:

#19643: Abstract the database get_lock function to PDO adapter
#19646: Support option for ranking queries to use window functions
#19647: Abstract database specific tests and fixture code
#19648: Abstract database specific helper methods to PDO adapter
#19649: Support database specific DDL in migrations
#19650: Use sequences instead of auto increment keys for user facing identifiers
#19651: Support optional sub-query substitution for COUNT(DISTINCT x) queries
@bx80 bx80 added the Enhancement For new feature suggestions that enhance Matomo's capabilities or add a new report, new API etc. label Aug 21, 2022
@sgiehl
Copy link
Member

sgiehl commented Aug 22, 2022

@bx80 I haven't yet read all the other issues in detail, but would something like #18371 be a good base for starting TiDB support as well?

@bx80
Copy link
Contributor Author

bx80 commented Aug 23, 2022

@sgiehl This would be covered by issue #19648. My suggested approach there is almost identical to your solution for #18371, the only difference is you've implemented the abstraction using a db schema class whereas I was thinking to have statement code in a db specific PDO adapter and just have schema changes in the schema class.

PDO adapter descendants get access to override some lower level functions such as query() which might be needed if we implement a multi-tenancy solution at some point and need to enforce query safe guards. Also some of the existing MySQL PDO adapter code is already too MySQL specific to work with TiDB, SingleStore, etc, which don't have InnoDB tables at all, different default ports and different version numbering.

We could allow database choice during installation and then set the existing config.ini.php adapter and schema settings to determine the schema and PDO adapter classes to use.

It looks like #18371 is already compatible with this approach. Maybe it could be finished at the same time #19648 is implemented? It'd be a good test of the abstraction structure.

@dankasak
Copy link

Hi there. Just a quick note on #19650 - as of TiDB v6.4.x, TiDB has a high-performance, monotonically-incrementing AUTO_INCREMENT implementation, which can be activated on a per-table basis with the AUTO_ID_CACHE 1 directive, eg CREATE TABLE t(a int AUTO_INCREMENT key) AUTO_ID_CACHE 1
See: https://docs.pingcap.com/tidb/dev/auto-increment#mysql-compatibility-mode

Therefore, #19650 may no longer be required for TiDB support. Note however that tables with AUTO_INCREMENT primary keys are still prone to write ( and potentially read ) hotspots. Please feel free to reach out if you want to discuss further.

@tsteur
Copy link
Member

tsteur commented May 16, 2023

This is great 🎉 Thanks @dankasak

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
Enhancement For new feature suggestions that enhance Matomo's capabilities or add a new report, new API etc.
Projects
None yet
Milestone
For Prioritization
Development

No branches or pull requests
5 participants
@tsteur @sgiehl @dankasak @bx80 @innocraft-automation