@RoyBellingan opened this Issue on November 15th 2021

So I saw clickhouse is beeing discussed several time already in

https://github.com/matomo-org/matomo/issues/2592
https://github.com/matomo-org/matomo/issues/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.

@tsteur commented on November 15th 2021 Member

@RoyBellingan thanks for this. Can we maybe continue this in https://github.com/matomo-org/matomo/issues/17697 ? Or you just want to share how your experience with clickhouse goes?

@RoyBellingan commented on November 15th 2021

@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 commented on November 15th 2021

@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 commented on November 15th 2021 Member

@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.

This Issue was closed on November 15th 2021
Powered by GitHub Issue Mirror