Can someone help me optimize my MSQL configuration.

I run Zabbix and Grafana on a Ubuntu 18.4. MySQL Tuner shows me that my memory usage is dangerously high, but when I use the free command it shows that I have more than enough memory.

Here is the full mysqltuner rapport: MySQLTuner


And the output of: SHOW ENGINE INNODB STATUS since Zabbix uses excursively innodb tables.

The output of the top command And the htop command I also have the output of innotop: Google Drive

And the output of ulimit -a

core file size          (blocks, -c) 0
data seg size           (kbytes, -d) unlimited
scheduling priority             (-e) 0
file size               (blocks, -f) unlimited
pending signals                 (-i) 31652
max locked memory       (kbytes, -l) 16384
max memory size         (kbytes, -m) unlimited
open files                      (-n) 1024
pipe size            (512 bytes, -p) 8
POSIX message queues     (bytes, -q) 819200
real-time priority              (-r) 0
stack size              (kbytes, -s) 8192
cpu time               (seconds, -t) unlimited
max user processes              (-u) 31652
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited

and free -h

              total        used        free      shared  buff/cache   available
Mem:           7,8G        7,0G        146M         37M        689M        554M
Swap:          2,0G        417M        1,6G

How can I increase the mysql memory usage? How can I optimize my MySQL configuration, so that I have maximum CPU and Memory usage?

Thank you for any help!

  • Additional information request. # cores, any SSD or NVME devices on MySQL Host server? Post on pastebin.com and share the links. From your SSH login root, Text results of: B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; D) SHOW FULL PROCESSLIST; E) complete MySQLTuner report AND Optional very helpful information, if available includes - htop OR top for most active apps, ulimit -a for a Linux/Unix list of limits, iostat -xm 5 3 for IOPS by device and core/cpu count, for server workload tuning analysis to provide suggestions. Apr 10 '20 at 0:24
  • 1
    Hello, I have updated the question with the nessary information. My mysql is now 25 hours up since the latest restart yesterday.
    – Jarne
    Apr 10 '20 at 13:18
  • 1
    What was labeled SHOW GLOBAL VARIABLES link is actually SHOW GLOBAL STATUS; output. Please post TEXT results to pastebin.com and share the link for results of SHOW GLOBAL VARIABLES; for analysis to begin. Thank you for the other data. Apr 10 '20 at 16:03
  • @WilsonHauck, I have fixed the link for SHOW GLOBAL STATUS, which variable should I update?
    – Jarne
    Apr 15 '20 at 12:57
  • Thanks for the SHOW GLOBAL VARIABLES; Analysis has started. We will try to have G V suggestions posted in an Answer within 24 hours. Apr 15 '20 at 14:26

Rate Per Second = RPS

Suggestions to consider for your my.cnf [mysqld] section

read_rnd_buffer_size=128K  # from 256K to reduce handler_read_rnd_next RPS of 917
innodb_change_buffer_max_size=15  # from 25 (percent) set aside for maintenance
innodb_flush_neighbors=2  # from 1 to clear in current extent to reduce innodb_buffer_pool_pages_dirty count faster
innodb_flushing_avg_loops=5  # from 30 to reduce flushing delay
innodb_lru_scan_depth=100  # from 1024 to reduce 90% of CPU cycles used for function every second
innodb_thread_concurrency=6  # from 10 to expedite query completion by your 4 cores

These changes will reduce RAM required and improve response time. For additional suggestions and free downloadable Utility Scripts, view my profile, Network profile for contact information.

Observations: Your 8GB server will continue to struggle to support the 26GB of innodb data. 48GB would be a reasonable step up for your workload. com_begin had 34 more counted than com_commit activities. Did someone forget to COMMIT when done? Incidentally it appears you had 34 rollbacks as well during the period covered 88,665 seconds (just over 1 day).

  • @Jarne I take it you already see the performance difference. Any chance we could Skype TALK? Thank you, Wilson my Skype ID is wlhauck@aol.com Apr 16 '20 at 12:22

RAM consumption for mysql/mariadb (and probably for other engines) can be represented this way:

| OS | Other services | DB common caching |      DB clients buffers     |
|    |                |                   +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
|    |                |                   | | | | | | | | | | | | | | | |

Common DB caching is a sum of key_buffer and InnoDB_buffer_pool values.
Buffer size per each client is a sum of read_buffer, read_rnd_buffer, join_buffer and sort_buffer. This amount is multiplied by max_connections value. Both values together shouldn't be bigger than reasonable part of the whole RAM. Some RAM is required by host's OS itself and some amount of RAM should be leaved for 3d-party services on the host.

Maximum possible memory usage: 9.0G (116.34% of installed RAM) means that you have dedicated to the mysql service even more RAM than your host physically has installed. This doesn't mean some problems until you'll get too many simultaneous connections to the DB. And even then you'll meet a slowdown first because of swapping.

If you want to tune your mysql perfectly you should know how many connections can be established at the same time. This value is shown here:

Highest usage of available connections: 40% (61/151)

Then you have to decide how much RAM each client needs. This value depend on your DB data and queries. If your DB isn't very critical you can start from some reasonable values and then decrease them until the overall performance goes down significantly. Then increase them 2x. But in general 1-2MB for each client's buffer and 4-8MB in total is good enough.
Now multiply total client's buffers by max_connections value. By default it is 151 but you can change it to the 1.2-1.5x of the real peak number of connections.

Say, for max 61 actual connections I'll reserve max_connections = 80 with 8MB buffers per connection. That mean that I reserved 80x8=640MB of RAM.

Now I leave 1GB for OS itself and 512MB for other services. The remainder is:

8GB - 1GB - 512MB - 640MB = 6016MB

Let's round it to the 6GB. This RAM you can split between MyISAM key_buffer and innodb_buffer_pool. If you have no MyISAM tables in the DB you can set key_buffer_size = 256k because mariadb 10.1.x is still used MyISAM/ARIA for internals. And all the rest could be dedicated to the innodb caching: innodb_buffer_pool_size = 6G

Now you'll get something like that:

Total buffers: 6.G global + 8M per thread (80 max threads)
Maximum possible memory usage: 6.7G (84% of installed RAM)
  • 1
    Please note, that there is Apache, Zabbix server and Java running some remote access on that server, so I'm afraid 512M is insufficient for other services, not to say about network buffers.
    – Jouriy
    Apr 8 '20 at 14:02
  • @Jouriy Well, values should be adjusted accordingly to the apache/java requirements. AFAIR lot of apache's mods can be disabled for smaller RAM footprint.
    – Kondybas
    Apr 8 '20 at 14:39
  • 1
    Hello, Thank you for your answer. I have set this parameters accordingly. If I disable the performance_schema and information_schema in my mysql does that also saves memory? And how does myslq logging affect RAM usage?
    – Jarne
    Apr 9 '20 at 9:19
  • @Jarne Not sure how you will run MySQL with information_schema disabled. :) Your MySQLTuner report posted is missing the end of the report. Typical goals are to reduce CPU and memory usage to allow breathing room for unexpected work load capacity while supporting frequently accessed data in RAM. Apr 10 '20 at 0:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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