Quotas

Quotas in ClickHouse CloudQuotas are supported in ClickHouse Cloud but must be created using the DDL syntax. The XML configuration approach documented below is not supported.

Quotas allow you to limit resource usage over a period of time or track the use of resources. Quotas are set up in the user config, which is usually ‘users.xml’. The system also has a feature for limiting the complexity of a single query. See the section Restrictions on query complexity. In contrast to query complexity restrictions, quotas:

Place restrictions on a set of queries that can be run over a period of time, instead of limiting a single query.
Account for resources spent on all remote servers for distributed query processing.

Let’s look at the section of the ‘users.xml’ file that defines quotas.

<!-- Quotas -->
<quotas>
    <!-- Quota name. -->
    <default>
        <!-- Restrictions for a time period. You can set many intervals with different restrictions. -->
        <interval>
            <!-- Length of the interval. -->
            <duration>3600</duration>

            <!-- Unlimited. Just collect data for the specified time interval. -->
            <queries>0</queries>
            <query_selects>0</query_selects>
            <query_inserts>0</query_inserts>
            <errors>0</errors>
            <result_rows>0</result_rows>
            <read_rows>0</read_rows>
            <execution_time>0</execution_time>
        </interval>
    </default>
</quotas>

By default, the quota tracks resource consumption for each hour, without limiting usage. The resource consumption calculated for each interval is output to the server log after each request.

<statbox>
    <!-- Restrictions for a time period. You can set many intervals with different restrictions. -->
    <interval>
        <!-- Length of the interval. -->
        <duration>3600</duration>

        <queries>1000</queries>
        <query_selects>100</query_selects>
        <query_inserts>100</query_inserts>
        <written_bytes>5000000</written_bytes>
        <written_rows>100000</written_rows>
        <errors>100</errors>
        <result_rows>1000000000</result_rows>
        <read_rows>100000000000</read_rows>
        <execution_time>900</execution_time>
        <failed_sequential_authentications>5</failed_sequential_authentications>
    </interval>

    <interval>
        <duration>86400</duration>

        <queries>10000</queries>
        <query_selects>10000</query_selects>
        <query_inserts>10000</query_inserts>
        <errors>1000</errors>
        <result_rows>5000000000</result_rows>
        <result_bytes>160000000000</result_bytes>
        <read_rows>500000000000</read_rows>
        <result_bytes>16000000000000</result_bytes>
        <execution_time>7200</execution_time>
    </interval>
</statbox>

For the ‘statbox’ quota, restrictions are set for every hour and for every 24 hours (86,400 seconds). The time interval is counted, starting from an implementation-defined fixed moment in time. In other words, the 24-hour interval does not necessarily begin at midnight. When the interval ends, all collected values are cleared. For the next hour, the quota calculation starts over. Here are the amounts that can be restricted: queries – The total number of requests. query_selects – The total number of select requests. query_inserts – The total number of insert requests. errors – The number of queries that threw an exception. result_rows – The total number of rows given as a result. result_bytes - The total size of rows given as a result. read_rows – The total number of source rows read from tables for running the query on all remote servers. read_bytes - The total size read from tables for running the query on all remote servers. written_bytes - The total size of a writing operation. written_rows - The total number of rows written by INSERT queries. execution_time – The total query execution time, in seconds (wall time). failed_sequential_authentications - The total number of sequential authentication errors. queries_per_normalized_hash – The maximum number of executions of any single normalized query. Normalized queries are queries with literals replaced by placeholders, so SELECT 1 and SELECT 2 are considered the same normalized query. This limit is tracked per distinct normalized query pattern independently. In addition to the predefined amounts above, a limit can be set on any profile event (see the system.events table) by using the event name as the tag, for example:

<interval>
    <duration>86400</duration>
    <!-- At most 100000 S3 GET requests and 50 failed queries per day. -->
    <S3GetObject>100000</S3GetObject>
    <FailedQuery>50</FailedQuery>
</interval>

The same can be expressed with the DDL syntax:

CREATE QUOTA s3_reads FOR INTERVAL 1 day MAX S3GetObject = 100000, FailedQuery = 50 TO my_user;

Unlike the predefined amounts, profile events are accounted once per query, at the moment the query finishes (successfully or with an error): a query that crosses the limit is still allowed to finish, and subsequent queries are rejected until the interval ends. The consumption is reported in the profile_events column of the system.quotas_usage table. Profile events accumulated outside of queries (for example, by background merges) are not charged to quotas. For a distributed query, the profile events that the remote servers report back to the initiator are charged to the quota of the initiating user together with the local ones. The remote servers always send these reports for a distributed query, regardless of the send_profile_events setting in the query, in its SETTINGS clause, or in the settings constraints of the user on the remote server, so a user cannot turn them off. This includes a synchronous INSERT into a Distributed table or the remote table function, for which a remote server reports its profile events once it has received all the data; the background sends of Distributed tables with distributed_foreground_insert = 0 are not part of any user query and are not charged. The deferred flush of asynchronous inserts is charged to the quota of the user who submitted the inserts, in the same bucket as the inserts were checked against (for a quota keyed by ip_address or forwarded_ip_address, the bucket of the client address; for a quota keyed by normalized_query_hash, the bucket of the submitted INSERT), and not to the query that forces the flush, such as SYSTEM FLUSH ASYNC INSERT QUEUE. If a remote server receiving a distributed INSERT queues it as an asynchronous insert, the deferred flush on that server is charged there, to the quota of the user the initiator connects as, and is not reported back to the initiator. Nothing is counted twice: the rows that the initiator of a distributed INSERT sends to a remote server are charged once in InsertedRows and InsertedBytes, while what the remote server writes beyond them, for example into the target tables of materialized views, is charged in addition. If the limit is exceeded for at least one time interval, an exception is thrown with a text about which restriction was exceeded, for which interval, and when the new interval begins (when queries can be sent again).

Queries excluded from quota accountingTo allow quota configuration and usage to be inspected even when a quota is exhausted, SELECT queries that exclusively read from one of the following system tables are not charged to quotas and are not subject to query-complexity limits:

system.quotas
system.quota_limits
system.quota_usage
system.quotas_usage
system.one

Quotas can use the “quota key” feature to report on resources for multiple keys independently. Here is an example of this:

<!-- For the global reports designer. -->
<web_global>
    <!-- keyed – The quota_key "key" is passed in the query parameter,
            and the quota is tracked separately for each key value.
        For example, you can pass a username as the key,
            so the quota will be counted separately for each username.
        Using keys makes sense only if quota_key is transmitted by the program, not by a user.

        You can also write <keyed_by_ip />, so the IP address is used as the quota key.
        (But keep in mind that users can change the IPv6 address fairly easily.)

        Instead of <keyed_by_ip /> you can use <keyed_by_forwarded_ip />, so the address
        from the X-Forwarded-For header is used as the quota key.

        For both <keyed_by_ip /> and <keyed_by_forwarded_ip /> you can additionally specify
        <ipv4_prefix_bits> and <ipv6_prefix_bits> to group clients by subnet instead of by a
        single address: the IP address is masked to the given prefix length before being used
        as the quota key. For example, <ipv4_prefix_bits>24</ipv4_prefix_bits> shares one bucket
        across a /24 IPv4 subnet, and <ipv6_prefix_bits>64</ipv6_prefix_bits> across a /64 IPv6
        subnet. These elements can only be used together with <keyed_by_ip /> or
        <keyed_by_forwarded_ip />.
    -->
    <keyed />
</web_global>

You can also key quotas by normalized query hash, so that each distinct query pattern gets its own independent quota bucket. In the XML configuration this is written as <keyed_by_normalized_query_hash />:

<my_quota>
    <keyed_by_normalized_query_hash />
    <interval>
        <duration>3600</duration>
        <queries>100</queries>
    </interval>
</my_quota>

The same can be expressed with the DDL syntax:

CREATE QUOTA my_quota KEYED BY normalized_query_hash FOR INTERVAL 1 hour MAX queries = 100 TO my_user;

In this example, the user can execute up to 100 instances of each distinct normalized query per hour. SELECT number FROM numbers(1) and SELECT number FROM numbers(2) share the same bucket (because they have the same normalized form), but SELECT number, number FROM numbers(1) uses a separate bucket. The quota is assigned to users in the ‘users’ section of the config. See the section “Access rights”. For distributed query processing, the accumulated amounts are stored on the requestor server. So if the user goes to another server, the quota there will “start over”. When the server is restarted, quotas are reset.

Blog: Building single page applications with ClickHouse

Fundamentals

ClickHouse concepts

Best practices

​Related Content

Related Content