Huge performance drop #4285

rickytato · 2024-06-27T14:42:11Z

rickytato
Jun 27, 2024

Hi, we tried to upgrade from v2.0.0 to v.2.1.1 (v2.1.0 -> v2.1.1) but we had to revert (restoring from backup) because is unusable.
Clickhouse use all 16core and 32GB of RAM (we also tried to increase to 64GB).
We are on our hardware, with AMD EPYC and NVMe , so we have no "cloud" problems.
With v2.0.0 all work perfectly but with v2.1.1 we are unable to see stats greather than one month, Clickhouse explode; with v2.0.0 dashboard show data from last year in near realtime.

Query id: e71f5113-d735-4b35-a27d-e3417e094ac5

   ┌─database──┬─table───────────┬─compressed─┬─uncompressed─┬─compr_rate─┬────────rows─┬─part_count─┐
1. │ plausible │ sessions_v2     │ 546.17 GiB │ 1.54 TiB     │       2.88 │  6578742026 │         93 │
2. │ plausible │ events_v2       │ 402.35 GiB │ 1.89 TiB     │       4.81 │ 10919460252 │         99 │
3. │ plausible │ ingest_counters │ 10.15 MiB  │ 41.84 MiB    │       4.12 │     1906951 │          7 │
   └───────────┴─────────────────┴────────────┴──────────────┴────────────┴─────────────┴────────────┘

3 rows in set. Elapsed: 0.003 sec.

ruslandoga · 2024-06-27T14:45:49Z

ruslandoga
Jun 27, 2024
Collaborator

👋 @rickytato

There haven't been any major changes in how Plausible uses ClickHouse from v2.0.0 to v2.1.1, so I can't think of anything that could cause this off the top of my head. You might be able to use system.query_log to find the problematic queries in v2.1.1 and compare them with v2.0.0 counterparts, you might be able to filter by http_user_agent column.

3 replies

rickytato Jun 27, 2024
Author

I see many changes on tables:

OLD

statement: CREATE TABLE plausible.sessions_v2
(
    `session_id` UInt64,
    `sign` Int8,
    `site_id` UInt64,
    `user_id` UInt64,
    `hostname` String,
    `timestamp` DateTime CODEC(DoubleDelta, LZ4),
    `start` DateTime CODEC(DoubleDelta, LZ4),
    `is_bounce` UInt8,
    `entry_page` String,
    `exit_page` String,
    `pageviews` Int32,
    `events` Int32,
    `duration` UInt32,
    `referrer` String,
    `referrer_source` String,
    `country_code` LowCardinality(FixedString(2)),
    `screen_size` LowCardinality(String),
    `operating_system` LowCardinality(String),
    `browser` LowCardinality(String),
    `utm_medium` String,
    `utm_source` String,
    `utm_campaign` String,
    `browser_version` LowCardinality(String),
    `operating_system_version` LowCardinality(String),
    `subdivision1_code` LowCardinality(String),
    `subdivision2_code` LowCardinality(String),
    `city_geoname_id` UInt32,
    `utm_content` String,
    `utm_term` String,
    `transferred_from` String,
    `entry_meta.key` Array(String),
    `entry_meta.value` Array(String)
)
ENGINE = CollapsingMergeTree(sign)
PARTITION BY toYYYYMM(start)
PRIMARY KEY (site_id, toDate(start), user_id, session_id)
ORDER BY (site_id, toDate(start), user_id, session_id)
SAMPLE BY user_id
SETTINGS index_granularity = 8192

NEW

statement: CREATE TABLE plausible.sessions_v2
(
    `session_id` UInt64,
    `sign` Int8,
    `site_id` UInt64,
    `user_id` UInt64,
    `hostname` String CODEC(ZSTD(3)),
    `timestamp` DateTime CODEC(Delta(4), LZ4),
    `start` DateTime CODEC(Delta(4), LZ4),
    `is_bounce` UInt8,
    `entry_page` String CODEC(ZSTD(3)),
    `exit_page` String CODEC(ZSTD(3)),
    `pageviews` Int32,
    `events` Int32,
    `duration` UInt32,
    `referrer` String CODEC(ZSTD(3)),
    `referrer_source` String CODEC(ZSTD(3)),
    `country_code` LowCardinality(FixedString(2)),
    `screen_size` LowCardinality(String),
    `operating_system` LowCardinality(String),
    `browser` LowCardinality(String),
    `utm_medium` String CODEC(ZSTD(3)),
    `utm_source` String CODEC(ZSTD(3)),
    `utm_campaign` String CODEC(ZSTD(3)),
    `browser_version` LowCardinality(String),
    `operating_system_version` LowCardinality(String),
    `subdivision1_code` LowCardinality(String),
    `subdivision2_code` LowCardinality(String),
    `city_geoname_id` UInt32,
    `utm_content` String CODEC(ZSTD(3)),
    `utm_term` String CODEC(ZSTD(3)),
    `transferred_from` String,
    `entry_meta.key` Array(String) CODEC(ZSTD(3)),
    `entry_meta.value` Array(String) CODEC(ZSTD(3)),
    `exit_page_hostname` String CODEC(ZSTD(3)),
    `city` UInt32 ALIAS city_geoname_id,
    `country` LowCardinality(FixedString(2)) ALIAS country_code,
    `device` LowCardinality(String) ALIAS screen_size,
    `entry_page_hostname` String ALIAS hostname,
    `os` LowCardinality(String) ALIAS operating_system,
    `os_version` LowCardinality(String) ALIAS operating_system_version,
    `region` LowCardinality(String) ALIAS subdivision1_code,
    `screen` LowCardinality(String) ALIAS screen_size,
    `source` String ALIAS referrer_source,
    INDEX minmax_timestamp timestamp TYPE minmax GRANULARITY 1
)
ENGINE = VersionedCollapsingMergeTree(sign, events)
PARTITION BY toYYYYMM(start)
PRIMARY KEY (site_id, toDate(start), user_id, session_id)
ORDER BY (site_id, toDate(start), user_id, session_id)
SAMPLE BY user_id
SETTINGS index_granularity = 8192

For example, with new installation, this query is slow:

SELECT
    e0.pathname,
    toUInt64(round(uniq(e0.user_id) * any(_sample_factor))) AS visitors
FROM events_v2 AS e0
WHERE ((e0.site_id = _CAST(7, 'Int64')) AND (e0.timestamp >= _CAST(1716741115, 'DateTime'))) AND (e0.timestamp < _CAST(1719419477, 'DateTime'))
GROUP BY e0.pathname
ORDER BY
    uniq(e0.user_id) DESC,
    e0.pathname ASC
LIMIT _CAST(0, 'Int64'), _CAST(9, 'Int64')

9 rows in set. Elapsed: 7.580 sec. Processed 404.53 million rows, 37.84 GB (53.37 million rows/s., 4.99 GB/s.)
Peak memory usage: 7.11 GiB.

ruslandoga Jun 27, 2024
Collaborator

Would you be able to post EXPLAIN indexes=1 plans with for this and other slow queries and compare them with v2.0.0 versions? Finding and posting their entries from query_log would also be helpful :) Please also check your system.mutations for any ongoing mutations resulting from the migration. You might also need to run OPTIMIZE on both events_v2 and sessions_v2 tables if you haven't to get the columns fully compressed.

rickytato Jun 27, 2024
Author

For example. session_v2 also have many different changes:
OLD

statement: CREATE TABLE plausible.events_v2
(
    `timestamp` DateTime CODEC(Delta(4), LZ4),
    `name` LowCardinality(String),
    `site_id` UInt64,
    `user_id` UInt64,
    `session_id` UInt64,
    `hostname` String,
    `pathname` String CODEC(ZSTD(3)),
    `referrer` String,
    `referrer_source` String,
    `country_code` FixedString(2),
    `screen_size` LowCardinality(String),
    `operating_system` LowCardinality(String),
    `browser` LowCardinality(String),
    `utm_medium` String,
    `utm_source` String,
    `utm_campaign` String,
    `meta.key` Array(String),
    `meta.value` Array(String),
    `browser_version` LowCardinality(String),
    `operating_system_version` LowCardinality(String),
    `subdivision1_code` LowCardinality(String),
    `subdivision2_code` LowCardinality(String),
    `city_geoname_id` UInt32,
    `utm_content` String,
    `utm_term` String,
    `transferred_from` String
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(timestamp)
PRIMARY KEY (site_id, toDate(timestamp), name, user_id)
ORDER BY (site_id, toDate(timestamp), name, user_id, timestamp)
SAMPLE BY user_id
SETTINGS index_granularity = 8192

NEW

statement: CREATE TABLE plausible.events_v2
(
    `timestamp` DateTime CODEC(Delta(4), LZ4),
    `name` LowCardinality(String),
    `site_id` UInt64,
    `user_id` UInt64,
    `session_id` UInt64,
    `hostname` String CODEC(ZSTD(3)),
    `pathname` String CODEC(ZSTD(3)),
    `referrer` String CODEC(ZSTD(3)),
    `referrer_source` String CODEC(ZSTD(3)),
    `country_code` FixedString(2),
    `screen_size` LowCardinality(String),
    `operating_system` LowCardinality(String),
    `browser` LowCardinality(String),
    `utm_medium` String CODEC(ZSTD(3)),
    `utm_source` String CODEC(ZSTD(3)),
    `utm_campaign` String CODEC(ZSTD(3)),
    `meta.key` Array(String) CODEC(ZSTD(3)),
    `meta.value` Array(String) CODEC(ZSTD(3)),
    `browser_version` LowCardinality(String),
    `operating_system_version` LowCardinality(String),
    `subdivision1_code` LowCardinality(String),
    `subdivision2_code` LowCardinality(String),
    `city_geoname_id` UInt32,
    `utm_content` String CODEC(ZSTD(3)),
    `utm_term` String CODEC(ZSTD(3)),
    `revenue_reporting_amount` Nullable(Decimal(18, 3)),
    `revenue_reporting_currency` FixedString(3),
    `revenue_source_amount` Nullable(Decimal(18, 3)),
    `revenue_source_currency` FixedString(3),
    `city` UInt32 ALIAS city_geoname_id,
    `country` LowCardinality(FixedString(2)) ALIAS country_code,
    `device` LowCardinality(String) ALIAS screen_size,
    `os` LowCardinality(String) ALIAS operating_system,
    `os_version` LowCardinality(String) ALIAS operating_system_version,
    `region` LowCardinality(String) ALIAS subdivision1_code,
    `screen` LowCardinality(String) ALIAS screen_size,
    `source` String ALIAS referrer_source
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(timestamp)
PRIMARY KEY (site_id, toDate(timestamp), name, user_id)
ORDER BY (site_id, toDate(timestamp), name, user_id, timestamp)
SAMPLE BY user_id
SETTINGS index_granularity = 8192

rickytato · 2024-06-27T15:08:56Z

rickytato
Jun 27, 2024
Author

@ruslandoga actually, we revert the installation, we are on production, so I can't see slow query log for the new installation.

10 replies

ruslandoga Jun 27, 2024
Collaborator

This comes from time_on_page query.

You can either follow the suggestion from the error message and set allow_deprecated_error_prone_window_functions or you can switch to the window function implantation with a feature flag, :window_time_on_page

$ docker compose exec plausible bin/plausible remote

iex> FunWithFlags.enable(:window_time_on_page)

Note that this won't be necessary in the next release as the "neighbor" time on page implementation has already been removed in #4206

madanyang Jul 1, 2024

@ruslandoga when will that next release be? Should we wait another 6-8 months so it eventually arrives, or maybe better to have a bug fix release to address this and if any other issues that are there.

ruslandoga Jul 1, 2024
Collaborator

👋 @madanyang

Should we wait another 6-8 months so it eventually arrives

It will be sooner than that. I'm working on automatic TLS right now ruslandoga#302 and once it's done I'll publish v2.1.2.

madanyang Jul 1, 2024

@ruslandoga thanks for the info

ruslandoga Sep 25, 2024
Collaborator

v2.1.2 has been released: #4618

rickytato · 2024-10-30T17:17:17Z

rickytato
Oct 30, 2024
Author

I just upgraded to ghcr.io/plausible/community-edition:v2.1.4... my DB is quite big I think:

SELECT
    database,
    `table`,
    formatReadableSize(sum(data_compressed_bytes) AS size) AS compressed,
    formatReadableSize(sum(data_uncompressed_bytes) AS usize) AS uncompressed,
    round(usize / size, 2) AS compr_rate,
    sum(rows) AS rows,
    count() AS part_count
FROM system.parts
WHERE (active = 1) AND (database LIKE 'plausible') AND (`table` LIKE '%')
GROUP BY
    database,
    `table`
ORDER BY size DESC

Query id: cf485d8e-1b22-4578-90e6-60c4b832a6de

   ┌─database──┬─table─────────────────────┬─compressed─┬─uncompressed─┬─compr_rate─┬────────rows─┬─part_count─┐
1. │ plausible │ sessions_v2_tmp_versioned │ 639.86 GiB │ 1.81 TiB     │       2.89 │  7584903501 │        107 │
2. │ plausible │ sessions_v2               │ 634.08 GiB │ 1.81 TiB     │       2.92 │  7591712923 │        109 │
3. │ plausible │ events_v2                 │ 464.44 GiB │ 2.25 TiB     │       4.95 │ 12709306465 │        119 │
4. │ plausible │ location_data             │ 61.50 MiB  │ 106.47 MiB   │       1.73 │     5368570 │          1 │
5. │ plausible │ ingest_counters           │ 13.99 MiB  │ 57.71 MiB    │       4.12 │     2630150 │          7 │
   └───────────┴───────────────────────────┴────────────┴──────────────┴────────────┴─────────────┴────────────┘

5 rows in set. Elapsed: 0.041 sec.

Now it is impossible to show statistics by year; Clickhouse server (24.9. 2.42) (20 cores / 50 GB RAM) uses all cores but fails to finish queries:

With the old version plausible/analytics:v2.0.0 everything works fine; I see that data sampling is used:

1 reply

ruslandoga Oct 31, 2024
Collaborator

👋 @rickytato

I see that data sampling is used:

Yes, this seems to be the reason that v2.0.0 was faster in processing these queries. Please see #4360 (reply in thread)

rickytato · 2024-10-30T17:39:49Z

rickytato
Oct 30, 2024
Author

I found, for example, this query for the last week:

SELECT toUInt64(round(uniq(s0.user_id) * any(_sample_factor))) AS visitors, s0.country AS dim0, (SELECT toUInt64(round(uniq(se0.user_id) * any(_sample_factor))) AS result FROM events_v2 AS se0 INNER JOIN (SELECT sss0.session_id AS session_id FROM sessions_v2 AS sss0 WHERE (((((sss0.site_id = _CAST(7, 'Int64')) AND (sss0.start >= _CAST(1727042400, 'DateTime'))) AND (sss0.timestamp >= _CAST(1727647200, 'DateTime'))) AND (sss0.start <= _CAST(1730329199, 'DateTime'))) AND (NOT (sss0.country IN ('\0\0', 'ZZ')))) AND (sss0.sign = 1) GROUP BY sss0.session_id) AS ss1 ON se0.session_id = ss1.session_id WHERE (((se0.site_id = _CAST(7, 'Int64')) AND (se0.timestamp >= _CAST(1727647200, 'DateTime'))) AND (se0.timestamp <= _CAST(1730329199, 'DateTime'))) AND _CAST('true', 'Bool')) AS total_visitors, if(total_visitors > 0, round((visitors / total_visitors) * 100, 1), NULL) AS percentage FROM sessions_v2 AS s0 WHERE ((((s0.site_id = _CAST(7, 'Int64')) AND (s0.start >= _CAST(1727042400, 'DateTime'))) AND (s0.timestamp >= _CAST(1727647200, 'DateTime'))) AND (s0.start <= _CAST(1730329199, 'DateTime'))) AND (NOT (s0.country IN ('\0\0', 'ZZ'))) GROUP BY dim0 ORDER BY visitors DESC, dim0 ASC LIMIT _CAST(0, 'Int64'), _CAST(9, 'Int64');

Query id: 435104f0-bd6e-4aff-9721-3e246b104d32

   ┌──visitors─┬─dim0─┬─total_visitors─┬─percentage─┐
1. │ 163067639 │ IT   │      166770437 │       97.8 │
2. │    616788 │ CH   │      166770437 │        0.4 │
3. │    495687 │ DE   │      166770437 │        0.3 │
4. │    430508 │ US   │      166770437 │        0.3 │
5. │    276697 │ ES   │      166770437 │        0.2 │
6. │    275448 │ FR   │      166770437 │        0.2 │
7. │    249646 │ GB   │      166770437 │        0.1 │
8. │    110688 │ NL   │      166770437 │        0.1 │
9. │     97450 │ BE   │      166770437 │        0.1 │
   └───────────┴──────┴────────────────┴────────────┘

9 rows in set. Elapsed: 31.341 sec. Processed 1.09 billion rows, 27.92 GB (34.83 million rows/s., 890.90 MB/s.)
Peak memory usage: 24.89 GiB.

0 replies

rickytato · 2024-10-30T18:55:14Z

rickytato
Oct 30, 2024
Author

explain SELECT toUInt64(round(uniq(s0.user_id) * any(_sample_factor))) AS visitors, s0.country AS dim0, (SELECT toUInt64(round(uniq(se0.user_id) * any(_sample_factor))) AS result FROM events_v2 AS se0 INNER JOIN (SELECT sss0.session_id AS session_id FROM sessions_v2 AS sss0 WHERE (((((sss0.site_id = _CAST(7, 'Int64')) AND (sss0.start >= _CAST(1698188400, 'DateTime'))) AND (sss0.timestamp >= _CAST(1698793200, 'DateTime'))) AND (sss0.start <= _CAST(1730415599, 'DateTime'))) AND (NOT (sss0.country IN ('\0\0', 'ZZ')))) AND (sss0.sign = 1) GROUP BY sss0.session_id) AS ss1 ON se0.session_id = ss1.session_id WHERE (((se0.site_id = _CAST(7, 'Int64')) AND (se0.timestamp >= _CAST(1698793200, 'DateTime'))) AND (se0.timestamp <= _CAST(1730415599, 'DateTime'))) AND _CAST('true', 'Bool')) AS total_visitors, if(total_visitors > 0, round((visitors / total_visitors) * 100, 1), NULL) AS percentage FROM sessions_v2 AS s0 WHERE ((((s0.site_id = _CAST(7, 'Int64')) AND (s0.start >= _CAST(1698188400, 'DateTime'))) AND (s0.timestamp >= _CAST(1698793200, 'DateTime'))) AND (s0.start <= _CAST(1730415599, 'DateTime'))) AND (NOT (s0.country IN ('\0\0', 'ZZ'))) GROUP BY dim0 ORDER BY visitors DESC, dim0 ASC LIMIT _CAST(0, 'Int64'), _CAST(9, 'Int64');

Code: 241. DB::Exception: Received from localhost:9000. DB::Exception: Memory limit (for query) exceeded: would use 32.60 GiB (attempt to allocate chunk of 16777216 bytes), current RSS 33.50 GiB, maximum: 32.60 GiB.: While executing AggregatingTransform. (MEMORY_LIMIT_EXCEEDED)

If I use 1 month:

explain SELECT toUInt64(round(uniq(s0.user_id) * any(_sample_factor))) AS visitors, s0.country AS dim0, (SELECT toUInt64(round(uniq(se0.user_id) * any(_sample_factor))) AS result FROM events_v2 AS se0 INNER JOIN (SELECT sss0.session_id AS session_id FROM sessions_v2 AS sss0 WHERE (((((sss0.site_id = _CAST(7, 'Int64')) AND (sss0.start >= _CAST(1698188400, 'DateTime'))) AND (sss0.timestamp >= _CAST(1727808990, 'DateTime'))) AND (sss0.start <= _CAST(1730415599, 'DateTime'))) AND (NOT (sss0.country IN ('\0\0', 'ZZ')))) AND (sss0.sign = 1) GROUP BY sss0.session_id) AS ss1 ON se0.session_id = ss1.session_id WHERE (((se0.site_id = _CAST(7, 'Int64')) AND (se0.timestamp >= _CAST(1727808990, 'DateTime'))) AND (se0.timestamp <= _CAST(1730415599, 'DateTime'))) AND _CAST('true', 'Bool')) AS total_visitors, if(total_visitors > 0, round((visitors / total_visitors) * 100, 1), NULL) AS percentage FROM sessions_v2 AS s0 WHERE ((((s0.site_id = _CAST(7, 'Int64')) AND (s0.start >= _CAST(1727808990, 'DateTime'))) AND (s0.timestamp >= _CAST(1727808990, 'DateTime'))) AND (s0.start <= _CAST(1730415599, 'DateTime'))) AND (NOT (s0.country IN ('\0\0', 'ZZ'))) GROUP BY dim0 ORDER BY visitors DESC, dim0 ASC LIMIT _CAST(0, 'Int64'), _CAST(9, 'Int64');


Query id: 9da71183-d4da-4f27-bfe6-82b199079cdc

   ┌─explain────────────────────────────────────────────────────────────────────────┐
1. │ Expression ((Project names + (Before ORDER BY + Projection) [lifted up part])) │
2. │   Limit (preliminary LIMIT (without OFFSET))                                   │
3. │     Sorting (Sorting for ORDER BY)                                             │
4. │       Expression ((Before ORDER BY + Projection))                              │
5. │         Aggregating                                                            │
6. │           Expression (Before GROUP BY)                                         │
7. │             Expression                                                         │
8. │               ReadFromMergeTree (plausible.sessions_v2)                        │
   └────────────────────────────────────────────────────────────────────────────────┘

8 rows in set. Elapsed: 48.523 sec. Processed 3.36 billion rows, 61.42 GB (69.17 million rows/s., 1.27 GB/s.)
Peak memory usage: 25.14 GiB.

0 replies

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Huge performance drop #4285

{{title}}

{{editor}}'s edit

{{editor}}'s edit

Replies: 5 comments 14 replies

{{title}}

{{editor}}'s edit

{{editor}}'s edit

{{title}}

{{title}}

{{editor}}'s edit

{{editor}}'s edit

{{title}}

{{title}}

{{title}}

{{editor}}'s edit

{{editor}}'s edit

{{title}}

{{title}}

{{editor}}'s edit

{{editor}}'s edit

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{editor}}'s edit

{{editor}}'s edit

Select a reply

Huge performance drop #4285

rickytato Jun 27, 2024

Replies: 5 comments · 14 replies

ruslandoga Jun 27, 2024 Collaborator

rickytato Jun 27, 2024 Author

ruslandoga Jun 27, 2024 Collaborator

rickytato Jun 27, 2024 Author

rickytato Jun 27, 2024 Author

ruslandoga Jun 27, 2024 Collaborator

madanyang Jul 1, 2024

ruslandoga Jul 1, 2024 Collaborator

madanyang Jul 1, 2024

ruslandoga Sep 25, 2024 Collaborator

rickytato Oct 30, 2024 Author

ruslandoga Oct 31, 2024 Collaborator

rickytato Oct 30, 2024 Author

rickytato Oct 30, 2024 Author

rickytato
Jun 27, 2024

Replies: 5 comments 14 replies

ruslandoga
Jun 27, 2024
Collaborator

rickytato Jun 27, 2024
Author

ruslandoga Jun 27, 2024
Collaborator

rickytato Jun 27, 2024
Author

rickytato
Jun 27, 2024
Author

ruslandoga Jun 27, 2024
Collaborator

ruslandoga Jul 1, 2024
Collaborator

ruslandoga Sep 25, 2024
Collaborator

rickytato
Oct 30, 2024
Author

ruslandoga Oct 31, 2024
Collaborator

rickytato
Oct 30, 2024
Author

rickytato
Oct 30, 2024
Author