An object-storage bill that jumped, and the bytes were not the reason

The ticket said: the bucket behind one cluster’s S3 disk costs far more than it did a week ago, find out why, and tell us whether last month’s upgrade did it. The cluster was a non-production one, small, with a cold tier on object storage that only held old system-log data. Nothing about it should have produced a bill that anyone noticed.

The interesting part is the order of the guesses, because each of them was a reasonable thing to check and the right answer was none of them. This note walks through the attribution method, since it works whichever way the bill actually goes.

What ClickHouse keeps where

On an object-storage disk (S3 or compatible) a data part is two things:

• Blobs in the bucket: the actual column files (.bin), marks, and so on, stored under generated random names. A part is not a prefix you can browse; it is a set of unrelated-looking keys.
• Metadata on a local disk, one small file per original file, listing which blob keys make up that file and how big they are. The default metadata_type is local.

Everything ClickHouse knows about what is in the bucket is in that local metadata. That is why a bucket listing alone cannot tell you which table owns what, and why “bytes in the bucket” and “bytes ClickHouse believes it stores” can drift apart. There are only a few ways for them to drift:

1. Inactive parts still holding blobs. A merge or a mutation writes a new part and marks the sources inactive. The sources are removed after old_parts_lifetime (480 seconds by default on 26.4), and only then do their blobs go. Raise that setting, or stall the cleanup thread, and the bucket holds old and new generations at once.
2. Dropped tables that are not dropped yet. In an Atomic database DROP TABLE only queues the removal; the data stays for database_atomic_delay_before_drop_table_sec, 480 by default, unless you say SYNC.
3. Detached parts and backups that reference blobs. Detached parts are still data. A BACKUP written into the same bucket is, to the bill, just more bytes.
4. Orphans: blobs with no metadata pointing at them, left by a crash between writing blobs and committing metadata, by a replica that was deleted without cleanup, or by a manual delete of the local metadata directory.
5. Zero-copy replication. With allow_remote_fs_zero_copy_replication, replicas share one copy of each blob and coordinate through Keeper so the blob is deleted only when the last replica drops the part. It saves bytes and costs you an extra failure mode: a replica that disappears without releasing its locks can pin blobs forever. The setting’s own description in the 26.4 build reads “Don’t use this setting in production, because it is not ready”, so treat any bucket that uses it as a place where orphans are an expected finding, not a surprise.

A sixth way is not about bytes at all, and it is the one that mattered here: requests. Object stores bill every PUT, GET and LIST, and billing counts requests that fail. Keep that in mind for the end.

Attribution: bytes first

The ticket asked about cost, but start with bytes because it is cheap to rule in or out. Three system tables answer it: system.disks for the disk, system.parts for what ClickHouse thinks is live, and system.remote_data_paths for every blob it knows about.

I rebuilt the situation on clickhouse local with a local_blob_storage disk, which behaves like S3 at this level (blobs with random names, local metadata) and gives system.remote_data_paths real content. The config defines a policy tiered with the default disk as the hot volume and the blob disk as the cold one:

<clickhouse>
  <storage_configuration>
    <disks>
      <blob>
        <type>object_storage</type>
        <object_storage_type>local_blob_storage</object_storage_type>
        <metadata_type>local</metadata_type>
        <path>/var/lib/clickhouse-blobs/</path>
      </blob>
    </disks>
    <policies>
      <tiered><volumes>
        <hot><disk>default</disk></hot>
        <cold><disk>blob</disk></cold>
      </volumes></tiered>
    </policies>
  </storage_configuration>
</clickhouse>

SELECT name, type, object_storage_type, metadata_type, is_remote FROM system.disks ORDER BY name;

blob     ObjectStorage  Local  Local  1
default  Local          None   None   0

Two tables on the cold disk. big_table has had two inserts and a merge; raw_events was written once:

SELECT t.database, t.name, b.blob_count,
       formatReadableSize(b.blob_bytes) AS in_bucket,
       formatReadableSize(a.live_bytes) AS live,
       round(b.blob_bytes / a.live_bytes, 2) AS ratio
FROM (SELECT toUUID(splitByChar('/', local_path)[3]) AS uuid, count() AS blob_count, sum(size) AS blob_bytes
      FROM system.remote_data_paths WHERE disk_name = 'blob' GROUP BY uuid) AS b
JOIN (SELECT database, table, sum(bytes_on_disk) AS live_bytes FROM system.parts
      WHERE active AND disk_name = 'blob' GROUP BY database, table) AS a ON 1 = 1
JOIN system.tables AS t ON t.uuid = b.uuid AND t.database = a.database AND t.name = a.table
ORDER BY b.blob_bytes DESC;

d  raw_events  14  19.54 MiB  19.54 MiB  1
d  big_table   48   2.73 MiB   1.07 MiB  2.55

local_path in system.remote_data_paths is store/<uuid prefix>/<table uuid>/<part name>/<file>, so splitting on / attributes every blob to a table and, with the fourth element, to a part. A ratio of 1 is a table with nothing extra. A ratio above 1 is bytes in the bucket that the active parts do not account for. Here big_table is at 2.55 because its two pre-merge parts are still there. Per part:

SELECT p.name AS part, p.active, count() AS blobs, formatReadableSize(sum(r.size)) AS bytes
FROM system.remote_data_paths r
JOIN system.parts p ON p.disk_name = r.disk_name AND splitByChar('/', r.local_path)[4] = p.name
WHERE p.table = 'big_table'
  AND splitByChar('/', r.local_path)[3] = toString((SELECT uuid FROM system.tables WHERE database = 'd' AND name = 'big_table'))
GROUP BY part, p.active ORDER BY part;

all_1_1_0  0  16  847.63 KiB
all_1_2_1  1  16    1.07 MiB
all_2_2_0  0  16  847.63 KiB

Two inactive parts, 16 blobs each, waiting out old_parts_lifetime. Filter on the table UUID too: part names such as all_1_1_0 repeat across tables, and my first version of this join, without the UUID filter, multiplied the counts. That ratio is transient and fine. The pattern that is not fine is a ratio that stays above 1 for hours, which means something is holding old parts: a long old_parts_lifetime, a stuck cleanup, a refcount that never drops.

Finally, the orphan check. Compare the bucket listing to what system.remote_data_paths knows. On a real bucket you get the listing from the provider’s inventory report, not from a LIST loop, because LIST is itself a billed request. Locally, file() plays that role. I planted one 5 MB file in the blob directory:

SELECT 'in bucket listing', count(), formatReadableSize(sum(sz))
FROM (SELECT _size AS sz FROM file('/var/lib/clickhouse-blobs/**', RawBLOB));
SELECT 'known to ClickHouse', count(), formatReadableSize(sum(size)) FROM system.remote_data_paths;
SELECT 'orphans', _path, _size FROM file('/var/lib/clickhouse-blobs/**', RawBLOB)
WHERE _path NOT IN (SELECT remote_path FROM system.remote_data_paths);

in bucket listing    15  24.31 MiB
known to ClickHouse  14  19.54 MiB
orphans  /var/lib/clickhouse-blobs/zzz/orphanedblobexample  5000000

One more behaviour worth knowing, because it is the common false alarm: after a plain DROP TABLE in an Atomic database the blob count did not move (14 before, 14 after), and system.dropped_tables listed the table as waiting. That is the 480-second delay doing its job, not a leak. Look there before calling anything an orphan.

What the investigation found

On the real cluster the bytes view was boring. The cold tier held a little over one TiB of active parts spread over the nodes, the ratio per table was about 1, and the bucket-side usage graph had not moved in the week the bill had. No orphan pile, no zero-copy, no backup in that bucket. The upgrade, which was the first suspect because it was the last thing anyone had touched, had not changed the byte count either.

What had moved was the request count, and the place to see it is not a storage table but the counters:

SELECT event, sum(value) AS n
FROM system.events
WHERE event IN ('DiskS3PutObject', 'S3PutObject', 'DiskS3GetObject', 'S3WriteRequestsErrors')
GROUP BY event;

SELECT name, code, value, last_error_time, substring(last_error_message, 1, 160) AS msg
FROM system.errors
WHERE name = 'S3_ERROR'
ORDER BY value DESC;

Both parse with clickhouse format; they need a server that has actually talked to S3, which clickhouse local here had not, so there is no output to show. On the cluster, DiskS3PutObject had climbed by a few hundred million in about a week, and system.errors carried an S3_ERROR whose message was an AccessDenied on s3:PutObject for the disk’s key prefix. Not a throttle, not a network error: an explicit denial from the identity and access layer, on every write, from every node.

The failing identity was the surprise. The credential the disk was using belonged to a different environment’s bucket user. That is the mechanism: a deploy had applied another environment’s configuration values onto this cluster’s release, because the deploy tool was pointed at the wrong cluster context. Most of the release rejected the change (an immutable field in the workload spec made the upgrade error), but the configuration objects were applied before the failure, and ClickHouse hot-reloads the storage config. The disk kept its name and its endpoint shape and silently changed who it was.

Why would a denied write cost anything? Three facts line up:

• Object stores bill requests, and here the provider billed the denied ones as well; check your provider’s rules for failed requests, but do not assume they are free.
• A TTL move to the cold volume that fails is retried by the background move task, and it kept retrying for as long as the denial lasted.
• Tables with a TTL … TO DISK rule and a large backlog, here the system log tables, have a lot to move, so each retry round is a lot of PUTs.

So the pattern is: stored bytes flat, request count up by orders of magnitude, a single error repeated. Reading is affected too: data already on the cold tier also became unreadable (GET denied), which would have been the loud symptom if anyone had queried old logs that week.

The fix

The fix was a redeploy with the cluster’s own values from the right context. The config hot-reloaded, the next move attempts succeeded, the error counter stopped growing within minutes, and the request graph fell back to its old shape with the next billing granularity. No restart was needed, and the failed-move backlog drained on its own.

The more durable fixes were in the deployment path, not in ClickHouse:

• Pin the cluster context in the deploy tooling instead of trusting whatever the shell currently points at.
• Use an atomic upgrade mode so a rejected change rolls back what it already applied, rather than leaving half a release.
• Give each environment’s disk credential permission on only its own bucket, and make sure the bucket policy denies the others. That does not stop the retry storm, but it makes the error immediate and unambiguous instead of a mystery.

What to take from it

1. Split a storage bill into bytes and requests before you chase either. system.remote_data_paths and system.parts answer “where are the bytes”. DiskS3* counters, ProfileEvents in system.query_log and system.metric_log, and system.errors answer “who is calling the bucket”. Both queries take seconds, and they would have ruled the upgrade in or out before anyone argued about it.
2. A flat byte graph with a rising cost line is a request problem. Do not start the orphan hunt.
3. Alert on S3_ERROR growth and on the PUT rate of every object-storage disk, including on clusters nobody cares about. A cold tier that fails silently still bills.
4. Treat a disk’s credentials as part of the cluster’s identity. The configuration can change them without a restart, which is convenient and also how this happened.
5. When bytes really are the problem, the checklist is the one above: inactive parts older than old_parts_lifetime, dropped tables in system.dropped_tables, detached parts, backups in the same bucket, and a diff of the bucket inventory against system.remote_data_paths for true orphans. Zero-copy deployments get the orphan check on a schedule.

For the surrounding mechanics see the storage and disk runbook, the walkthrough of tiered storage with multiple volumes, and ClickHouse and S3. The disk and policy options are documented in Using multiple block devices for data storage. The blob-disk queries above ran on clickhouse local 26.4; the DiskS3* and system.errors queries need a server that has talked to S3 and were only syntax-checked.