Better estimating "worst case memory usage" for MySQL

Question

Tools such as MySQLTuner add up globally allocated memory and add it to the product of max connections and per connection memory requirement. I formulated the following query to better estimate how much max memory would be required, if 100% of the max_connections are used.

Can someone verify whether it looks correct and whether any adjustments can be done?

I realise it excludes many other RAM requirements by MySQL and the server, but the idea is to estimate better.

SELECT
    sys.FORMAT_BYTES(
        @@key_buffer_size + @@query_cache_size + @@innodb_buffer_pool_size + @@innodb_log_buffer_size +
        (
            @@max_connections
             * (
                ((select_scans / queries) * @@read_buffer_size)
                + ((sort_operations / queries) * (@@read_rnd_buffer_size + @@sort_buffer_size))
                + ((join_operations / queries) * @@join_buffer_size)
                + @@binlog_cache_size + @@thread_stack
                + ((temp_tables / queries) * LEAST(@@tmp_table_size, @@max_heap_table_size))
                #need a better way to determine average packet size
                + (@@max_allowed_packet * 0.5) + @@net_buffer_length
            )
        )
    ) AS MAX_MEMORY
FROM (
    SELECT
        SUM(COUNT_STAR) AS queries,
        SUM(SUM_SELECT_SCAN) AS select_scans,
        SUM(
            GREATEST(
                (CASE WHEN UPPER(DIGEST_TEXT) LIKE '%ORDER BY%' THEN COUNT_STAR ELSE 0 END),
                (CASE WHEN SUM_SORT_MERGE_PASSES > 0 OR SUM_SORT_RANGE > 0 OR SUM_SORT_ROWS > 0 OR SUM_SORT_SCAN > 0 THEN COUNT_STAR ELSE 0 END)
            )
        ) AS sort_operations,
        SUM(CASE WHEN UPPER(DIGEST_TEXT) LIKE '%JOIN%' THEN COUNT_STAR ELSE 0 END) AS join_operations,
        SUM(SUM_CREATED_TMP_TABLES) AS temp_tables
    FROM performance_schema.events_statements_summary_by_digest
) t;

Answer 1

That's a good stab at a better formula. But I still believe that it is not possible to get a "correct" upper bound.

• max_connections, table_open_cache, max_allowed_packet, etc, are rarely hit. (That is, your formula will still be an over-estimate of the max.)
• Some of the per-query buffers are really per-SELECT, and that includes subqueries. You have attempted to account for such.
• When there are multiple JOINs in a single query, there may be multiple temp tables. (I don't think you allowed for that.)
• Some of those "count_stars" and "sums" are cumulative and not high-water-marks. (I don't know which, of the top of my head.) Although your formula tries to account for multiple selects in a single query, it does not avoid the "cumulative" counters.
• Dividing counters by the number of queries gives an average; you need a max. (On my system, I get 0.08 "select_scans" and 0.03 "sorts".) You need the "max" over the queries. In my case that might be 1 or 2 scans and sorts per query, but usually 0. (No I don't know how to compute such.)
• There is a binlog buffer and probably some other things.

Some settings:

• You should not be using MyISAM, so key_buffer_size should be "small" (except on MySQL 8.0, where it should be 0).
• The Query cache is not usable on clusters and has been removed from some versions; hence query_cache_size should, with few exceptions, be 0. (The variable is even gone in 8.0, thereby invalidating the query.)

It feels like some other tunables are missing, but it would take a while to dig through my notes. Do you wish to continue this discussion?

Answer 2

This is really not a useful figure to calculate even if you could get a more accurate estimate, because the theoretical "max memory usage" never happens.

In every production MySQL Server I've analyzed (I've visited dozens of companies as a consultant, and supported thousands of MySQL Server instances as a DBA), the MySQLTuner type calculation always gives a theoretical max memory usage that is many times the amount of physical RAM on the server. But you never see swap-memory usage equal to 10x physical RAM.

The estimate formula you show has some inaccuracies anyway:

• Some buffers are not allocated to their full size every time they are used (example: tmp_table_size, etc.)

• Some buffers might be allocated more than once in a given query (example: join_buffer_size and tmp_table_size)

• Some usage of RAM is not available in a configuration variable (example: optimizer uses a variable amount of RAM while estimating cost of multi-valued range queries)

Anyway, for a MySQL Server to reach max memory usage by this formula, you would have to have max_connections clients, all executing queries simultaneously, and every query would have to allocate all buffers at their maximum allowed size.

This simply never happens. Your database server would collapse long before it reached this level of concurrent usage.

I cringe when I see people rely on MySQLTuner advice. It frequently gives really bad and misleading advice. I don't think the author is very knowledgeable about MySQL internals.

So what should we do instead to estimate memory usage accurately?

Observe.

Get some metrics-collection service to graph the real memory size of the mysqld process over time, as it handles realistic traffic from your application on your server. This will be accurate, it will show you the true information, minimum and maximum, and pattern of increase and decrease.