0

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;

2 Answers 2

0

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?

6
  • SUM_CREATED_TMP_TABLES counts all the tables per query, so multiple temp tables should be covered. What do you mean by cumulative counters? I thought all those counters were sum of events, except in the case of the time columns. I divide counters by the number of queries to have the effect of not all but a percentage of queries will need temp table or sort buffer memalloc, etc. There may be spikes, but this is simplified. Do you think there is anything else I may have missed? Oct 10, 2022 at 18:37
  • On my server, SUM_CREATED_TMP_TABLES is 340. That is how many tmp tables my queries build ever since the latest restart. Compare that to a mere 8195 for SUM(COUNT_STAR) queries, it averages far less than 1 tmp table per query. So, if fails to help with "max" memory used.
    – Rick James
    Oct 10, 2022 at 19:24
  • I get quite a few rows from this... SELECT * FROM performance_schema.events_statements_summary_by_digest WHERE SUM_CREATED_TMP_TABLES > 0 AND SUM_CREATED_TMP_TABLES > COUNT_STAR LIMIT 100 Oct 10, 2022 at 19:36
  • @ShahidThaika - Give this a try: SELECT SUM_CREATED_TMP_TABLES, COUNT_STAR, DIGEST_TEXT FROM performance_schema.events_statements_summary_by_digest WHERE SUM_CREATED_TMP_TABLES > COUNT_STAR AND COUNT_STAR > 3 ORDER BY LENGTH(digest_text) ASC LIMIT 5;
    – Rick James
    Oct 10, 2022 at 21:21
  • @ShahidThaika - In the output note that SUM_CREATED_TMP_TABLES is a multiple of COUNT_STAR. That is SUM_CREATED_TMP_TABLES / COUNT_STAR is the number of tmp tables for that query. What you need is more like MAX(SUM_CREATED_TMP_TABLES / COUNT_STAR)
    – Rick James
    Oct 10, 2022 at 21:22
1

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.

3
  • I agree this is still not an accurate estimate. We do reach our max_connections value pretty often though, and see high swap usage if we set buffer pool size to more than 60% of RAM. This is more to see where can we reliably reduce memory consumption. Need a reasonable starting point to see where we are using the most RAM and see if we can adjust anything there. Oct 11, 2022 at 11:17
  • @ShahidThaika, You should never be using swap on a database server. In my experience, if the database server starts using swap memory, it kills performance and causes the application to fail its performance target (SLA). We even disabled swap on the database servers. I don't know how much RAM your server has. You may need to increase it to handle the number of client connections. Oct 11, 2022 at 15:58
  • @ShahidThaika, Regarding max_connections, I don't know how many that is on your server. The default of 151? Or did you increase it? Anyway, it's common to fill max_connections with idle clients. Most of the RAM usage happens when they run queries. So you want to watch the Threads_running status variable, not the Threads_connected status variable. The former is the number of clients running queries at that moment, the latter includes idle clients. Oct 11, 2022 at 16:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.