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.

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

Clickhouse V2 #18318

Closed
RoyBellingan opened this issue Nov 15, 2021 · 4 comments
Closed

Clickhouse V2 #18318

RoyBellingan opened this issue Nov 15, 2021 · 4 comments
Labels
duplicate For issues that already existed in our issue tracker and were reported previously. Enhancement For new feature suggestions that enhance Matomo's capabilities or add a new report, new API etc. not-in-changelog For issues or pull requests that should not be included in our release changelog on matomo.org.

Comments

@RoyBellingan
Copy link

RoyBellingan commented Nov 15, 2021 •
edited

So I saw clickhouse is beeing discussed several time already in

#2592
#17697

I think you are missing an interesting option, keep using mysql for ALL except the SLOW query...
I am working at the moment on speeding up the following query, that in my use case are the one that takes the majority of time which are like

https://gist.github.com/RoyBellingan/4391287fa5b88e97f7b79062c1b92274

So the table log_visit can ONLY be updated if visit_last_action_time is not older than SESSION_TIME (default of 30 minutes)
and of course log_action is append only (PLEASE correct me if I am wrong, but I logged the sql in mysql and I have never seen been done in other cases (also looking at the index, really looks like the case))

I just have to replicate those 2... for log_action is just a matter max(id), the other one is more involved, I already connected clickhouse to mysql https://clickhouse.com/docs/en/sql-reference/table-functions/mysql/ and I am already insert into mysql from clickhouse in other project.

Of course this is "pro user" thing only, and only if you are having massive amount of traffic...

I will update once I have a few more number and example ready to be analyzed.
@RoyBellingan RoyBellingan added the Enhancement For new feature suggestions that enhance Matomo's capabilities or add a new report, new API etc. label Nov 15, 2021
@tsteur
Copy link
Member

tsteur commented Nov 15, 2021

@RoyBellingan thanks for this. Can we maybe continue this in #17697 ? Or you just want to share how your experience with clickhouse goes?

@RoyBellingan
Copy link
Author

RoyBellingan commented Nov 15, 2021 •
edited

@tsteur up to you, I think the other one was already moving on a "let's move all to CH" (please do not even try), except a note in the second comment, but I have no preferences on the matter.

@RoyBellingan
Copy link
Author

@tsteur BTW did you check about ColumnStore ? The new engine in mariadb ?
I think I will still go on the Clickhouse route, but I see is not commented in this repo at all as an option.

@tsteur
Copy link
Member

tsteur commented Nov 15, 2021

@RoyBellingan thanks for this. As there are already a few issues around this I'll prefer to close this one and I'll comment in the other issue. This way we have all the comments in one place. We have generally made no decision what to support additionally to MySQL and there are no such plans yet. This could change though next year or some time. I remember looking at ColumnStore 1 or 2 years ago. I don't remember exactly the details but there were some downsides overall. Whether it was performance compared to others, or updates/deletes, or joins or something else I don't remember unfortunately.

@tsteur tsteur closed this as completed Nov 15, 2021
@tsteur tsteur added duplicate For issues that already existed in our issue tracker and were reported previously. not-in-changelog For issues or pull requests that should not be included in our release changelog on matomo.org. labels Nov 15, 2021
@tsteur tsteur mentioned this issue Nov 15, 2021
Open
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
duplicate For issues that already existed in our issue tracker and were reported previously. Enhancement For new feature suggestions that enhance Matomo's capabilities or add a new report, new API etc. not-in-changelog For issues or pull requests that should not be included in our release changelog on matomo.org.
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@tsteur @RoyBellingan