3

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

Here is the output of: SHOW GLOBAL VARIABLES and SHOW GLOBAL STATUS and SHOW FULL PROCESSLIST

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!

5
  • 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, 2020 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, 2020 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, 2020 at 16:03
  • @WilsonHauck, I have fixed the link for SHOW GLOBAL STATUS, which variable should I update?
    – Jarne
    Apr 15, 2020 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, 2020 at 14:26

3 Answers 3

1
Analysis of GLOBAL STATUS and VARIABLES:
 

Observations:

  • Version: 10.1.44-MariaDB-0ubuntu0.18.04.1
  • 7.8 GB of RAM
  • Uptime = 1d 00:37:45
  • 71.7 QPS

The More Important Issues:

Version 10.1 is getting quite old; consider upgrading.

There were a lot of DELETEs during that day. Is that normal?

Suggested changes to settings:

key_buffer_size = 20M

If using SSD disk:

innodb_io_capacity = 1000
innodb_flush_neighbors = 0

Details and other observations:

( Key_blocks_used * 1024 / key_buffer_size ) = 8 * 1024 / 262144 = 3.1% -- Percent of key_buffer used. High-water-mark. -- Lower key_buffer_size (now 262144) to avoid unnecessary memory usage.

( innodb_buffer_pool_size / innodb_buffer_pool_instances ) = 6144M / 1 = 6144MB -- Size of each buffer_pool instance. -- An instance should be at least 1GB. In very large RAM, have 16 instances.

( innodb_lru_scan_depth ) = 1,024 -- innodb_lru_scan_depth is a very poorly named variable. A better name would be innodb_free_page_target_per_buffer_pool. It is a number of pages InnoDB tries to keep free in each buffer pool instance to speed up read and page creation operations. -- "InnoDB: page_cleaner: 1000ms intended loop took ..." may be fixed by lowering lru_scan_depth

( innodb_io_capacity ) = 200 -- When flushing, use this many IOPs. -- Reads could be slugghish or spiky. Use 2000 if using SSD drive.

( innodb_io_capacity_max / innodb_io_capacity ) = 2,000 / 200 = 10 -- Capacity: max/plain -- Recommend 2. Max should be about equal to the IOPs your I/O subsystem can handle. (If the drive type is unknown 2000/200 may be a reasonable pair.)

( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written ) = 88,665 / 60 * 48M / 3815175168 = 19.5 -- Minutes between InnoDB log rotations Beginning with 5.6.8, innodb_log_file_size can be changed dynamically; I don't know about MariaDB. Be sure to also change my.cnf -- (The recommendation of 60 minutes between rotations is somewhat arbitrary.) Adjust innodb_log_file_size (now 50331648). (Cannot change in AWS.)

( default_tmp_storage_engine ) = default_tmp_storage_engine =

( Innodb_row_lock_time_avg ) = 38,990 -- Avg time to lock a row (millisec) -- Possibly conflicting queries; possibly table scans.

( Innodb_row_lock_time_max ) = 51,002 -- Max time to lock a row (millisec) -- Possibly conflicting queries; possibly table scans.

( innodb_flush_neighbors ) = innodb_flush_neighbors = 1 -- A minor optimization when writing blocks to disk. -- Use 0 for SSD drives; 1 for HDD.

( innodb_io_capacity ) = 200 -- I/O ops per second capable on disk . 100 for slow drives; 200 for spinning drives; 1000-2000 for SSDs; multiply by RAID factor. Limits write IO requests per second (IOPS). -- For starters: HDD: 200; SSD: 2000.

( innodb_strict_mode ) = innodb_strict_mode = OFF -- When ON, this catches some subtle errors earlier. -- OFF leaves some warnings as warnings; ON makes them errors.

( innodb_adaptive_hash_index ) = innodb_adaptive_hash_index = ON -- Whether to use the adapative hash (AHI). -- ON for mostly readonly; OFF for DDL-heavy

( innodb_flush_log_at_trx_commit ) = 0 -- 1 = secure; 2 = faster -- (You decide) Use 1, along with sync_binlog (now 0)=1 for the greatest level of fault tolerance. 0 is best for speed. 2 is a compromise between 0 and 1.

( sync_binlog ) = 0 -- Use 1 for added security, at some cost of I/O =1 may lead to lots of "query end"; =0 may lead to "binlog at impossible position" and lose transactions in a crash, but is faster. 0 is OK for Galera.

( innodb_adaptive_hash_index ) = innodb_adaptive_hash_index = ON -- Usually should be ON. -- There are cases where OFF is better. See also innodb_adaptive_hash_index_parts (after 5.7.9) and innodb_adaptive_hash_index_partitions (now 1) (MariaDB and Percona). ON has been implicated in rare crashes (bug 73890). 10.5.0 decided to default OFF.

( innodb_print_all_deadlocks ) = innodb_print_all_deadlocks = OFF -- Whether to log all Deadlocks. -- If you are plagued with Deadlocks, turn this on. Caution: If you have lots of deadlocks, this may write a lot to disk.

( innodb_purge_threads ) = 1 -- Number of threads to clean up history list. -- If you have a lot of writes, recommend 4 in versions 5.6 and 10.0 or later.

( max_connections ) = 80 -- Maximum number of connections (threads). Impacts various allocations. -- If max_connections (now 80) is too high and various memory settings are high, you could run out of RAM.

( innodb_ft_result_cache_limit ) = 2,000,000,000 / 8375186227.2 = 23.9% -- Byte limit on FULLTEXT resultset. (It grows as needed.) -- Lower the setting.

( local_infile ) = local_infile = ON -- local_infile (now ON) = ON is a potential security issue

( (Com_insert + Com_update + Com_delete + Com_replace) / Com_commit ) = (760824 + 1003342 + 2842 + 0) / 2014065 = 0.877 -- Statements per Commit (assuming all InnoDB) -- Low: Might help to group queries together in transactions; High: long transactions strain various things.

( Com__biggest ) = Com__biggest = Com_begin -- Which of the "Com_" metrics is biggest. -- Normally it is Com_select (now 499597).

( binlog_format ) = binlog_format = STATEMENT -- STATEMENT/ROW/MIXED. -- ROW is preferred by 5.7 (10.3)

( slow_query_log ) = slow_query_log = OFF -- Whether to log slow queries. (5.1.12)

( long_query_time ) = 10 -- Cutoff (Seconds) for defining a "slow" query. -- Suggest 2

( log_slow_slave_statements ) = log_slow_slave_statements = OFF -- (5.6.11, 5.7.1) By default, replicated statements won't show up in the slowlog; this causes them to show. -- It can be helpful in the slowlog to see writes that could be interfering with Replica reads.

( back_log ) = 66 -- (Autosized as of 5.6.6; based on max_connections) -- Raising to min(150, max_connections (now 80)) may help when doing lots of connections.

Abnormally small:

(Com_select) / (Com_insert + Com_update + Com_delete + Com_replace) = 0.283
Acl_proxy_users = 0
Key_blocks_unused = 194
host_cache_size = 208

Abnormally large:

Acl_table_grants = 18
Com_begin = 23 /sec
Com_show_master_status = 2.1 /HR
Com_show_slave_hosts = 0.24 /HR
Com_show_status = 0.2 /sec
Handler_delete = 99 /sec
Innodb_buffer_pool_pages_flushed / max(Questions, Queries) = 0.651
Innodb_buffer_pool_pages_misc * 16384 / innodb_buffer_pool_size = 11.5%
Innodb_ibuf_merged_delete_marks = 48 /sec
Innodb_num_index_pages_written = 46 /sec
Innodb_rows_deleted = 99 /sec
Tc_log_page_size = 4,096

Abnormal strings:

binlog_checksum = NONE
innodb_default_row_format = compact
innodb_fast_shutdown = 1
innodb_log_compressed_pages = OFF
myisam_stats_method = NULLS_UNEQUAL
sql_slave_skip_counter = 0
1
  • Thank you for your answer, but I no longer maintain this project. I have changed jobs and this server is no longer maintained. In fact this question can be removed/archived.
    – Jarne
    Aug 28, 2022 at 10:02
2

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)
4
  • 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, 2020 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, 2020 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, 2020 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, 2020 at 0:31
1

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).

1
  • @Jarne I take it you already see the performance difference. Any chance we could Skype TALK? Thank you, Wilson my Skype ID is [email protected] Apr 16, 2020 at 12:22

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.