Changing memory for MySQL Performance - MySQLTuner

Question

I have recently been having issues with my system running out of connections to MySQL (technically MariaDB).

I've set the max_connections to 250 (up from 151). But I'm confused on how I need to allocate RAM. The machine has 32GB of RAM and if I'm reading the results from mysqltuner correctly... I'm only allowing up to 1GB total to be used. But at 250 connections * 2.8M/thread it should only ever reach 700M + the global of 328M?

It looks like we've peaked at 755M. But with all this extra memory left over should I open things up a bit to let MariaDB breathe?

Am I reading this correctly?

This machine doubles as an apache & db server. Even at full tilt I rarely see the machine use more than 3 or 4GB of total system RAM

I ran mysqltuner and here are the performance results:

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 34d 1h 32m 42s (74M q [25.213 qps], 26M conn, TX: 46G, RX: 42G)
[--] Reads / Writes: 98% / 2%
[--] Binary logging is disabled
[--] Physical Memory     : 31.5G
[--] Max MySQL memory    : 1.0G
[--] Other process memory: 647.4M
[--] Total buffers: 328.0M global + 2.8M per thread (250 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 755.5M (2.34% of installed RAM)
[OK] Maximum possible memory usage: 1.0G (3.20% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/74M)
[OK] Highest usage of available connections: 60% (152/250)
[OK] Aborted connections: 0.00%  (2/26423553)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[!!] Query cache may be disabled by default due to mutex contention.
[OK] Query cache efficiency: 45.7% (21M cached / 47M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 1% (541 temp sorts / 32K sorts)
[!!] Joins performed without indexes: 24537
[OK] Temporary tables created on disk: 1% (644 on disk / 51K total)
[OK] Thread cache hit rate: 98% (383K created / 26M connections)
[!!] Table cache hit rate: 0% (120 open / 36K opened)
[OK] Open file limit used: 0% (24/4K)
[OK] Table locks acquired immediately: 99% (5M immediate / 5M locks)

Answer 1

You have to understand that MySQLtuner's calculation of "Max MySQL Memory" is total bullshit.

It's based on the theoretical maximum — which is possible only if you max out max_connections, and then every connection runs a query at exactly the same moment, and every one of those queries uses the maximum possible sort buffers, read buffers, and join buffers. Realistically, this will never happen in a real server.

When I run MySQLTuner on most production database servers I have supported, the "Max MySQL Memory" is reported as hundreds of times larger than the actual physical RAM on the server. This is not a problem because it's a theoretical maximum that will never actually happen.

If you run SHOW PROCESSLIST on your database server, even if all 150 or 250 threads are connected, you'll see only 6-8 threads running a query, while most other threads are in a state of "Sleeping".

It's like if you are logged into a server with ssh and your terminal is sitting ready at a shell prompt. Are you running any command? No. Your shell is idle. But you're still connected.

The same is true of MySQL. Your application may be connected to the database, but your app isn't running a query yet. And even when it does run a query, it probably won't use the maximum resources allowed. And then it'll finish quickly and return the connection to an idle state again.

At any given instant, the number of threads connected may be high, even while those connections actually running a query is small. You can compare:

mysql> show global status like 'Threads%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_connected | 510   |
| Threads_running   | 13    |
+-------------------+-------+

The above are typical numbers from a busy production MySQL instance. In my experience, the ratio of threads connected to threads running ranges between 10:1 and 100:1.

But MySQLTuner calculations are not realistic — they assume the ratio of threads connected to threads running is 1:1.

So given that, the memory you allocate to MySQL has nothing to do with the max_connections you allow.

You may find that increasing some tuning options is helpful for performance, but it has more to do with the size of your data and the types of queries you run against that data.

I recommend reading https://www.percona.com/blog/2016/10/12/mysql-5-7-performance-tuning-immediately-after-installation/

Or if you want to get into deeper study, read: High Performance MySQL, 3rd Edition