MySQL 8 and MySQL 5.7 Memory Consumption on Small Devices

MySQL 8 and MySQL 5.7 Memory ConsumptionWhile we often run MySQL on larger scale systems in Production for Test and Dev, sometimes we want to run MySQL on the tiniest cloud instances possible or just run it on our laptops. In these cases, MySQL 8 and MySQL 5.7 memory consumption is quite important.

In comparing MySQL 8 vs MySQL 5.7, you should know that MySQL 8 uses more memory. Basic tests on a 1GB VM with MySQL 8 and MySQL 5.7 (actually they’re Percona Server versions) running the same light workload, I see the following vmstat output:

MySQL 5.7 vmstat output

MySQL 8.0 vmstat output

As you can see, MySQL 8 uses some 200MB more swap and also uses less OS cache, signaling more memory being allocated and at least “committed.” If we look at the “top” output we see:

MySQL 5.7

mysql 5.7

MySQL 8.0

MySQL 8.0

This also shows more Resident memory and virtual memory used by MySQL8.   Virtual Memory, in particular, is “scary” as it is well in excess of the 1GB of physical memory available on these VMs.  Of course, Virtual Memory usage (VSZ) is a poor indicator of actual memory needs for modern applications, but it does corroborate the higher memory needs story.

In reality, though, as we know from the “vmstat” output, neither MySQL 8 nor MySQL 5.7 is swapping with this light load, even though there isn’t much “room” left. If you have more than a handful of connections or wish to run some applications on the same VM, you would get swapping (or OOM killer if you have not enabled swap).

It would be an interesting project to see how low I can drive MySQL 5.7 and MySQL 8 memory consumption, but I will leave it to another project. Here are the settings I used for this test:

Summary:  When moving to MySQL 8 in a development environment, keep in mind it will require more memory than MySQL 5.7 with the same settings.

Share this post

Comments (11)

  • Morgan Tocker (@morgo) Reply

    This looks like a good opportunity to use performance_schema 🙂

    The bottom of this manual page has an example: https://dev.mysql.com/doc/refman/8.0/en/memory-use.html

    I loaded your sample config, and just made one change to 5.7 since it doesn’t instrument memory by default (performance-schema-instrument=’memory/%=COUNTED’).

    With Percona Server 8.0.15 vs Percona Server 5.7.26:

    mysql [localhost:8015] {msandbox} ((none)) > SELECT SUBSTRING_INDEX(event_name,’/’,2) AS
    -> code_area, sys.format_bytes(SUM(current_alloc))
    -> AS current_alloc
    -> FROM sys.x$memory_global_by_current_bytes
    -> GROUP BY SUBSTRING_INDEX(event_name,’/’,2)
    -> ORDER BY SUM(current_alloc) DESC;
    +—————————+—————+
    | code_area | current_alloc |
    +—————————+—————+
    | memory/innodb | 328.51 MiB |
    | memory/performance_schema | 270.73 MiB |
    | memory/mysys | 8.57 MiB |
    | memory/sql | 4.46 MiB |
    | memory/temptable | 1.00 MiB |
    | memory/mysqld_openssl | 208.01 KiB |
    | memory/mysqlx | 3.62 KiB |
    | memory/myisam | 696 bytes |
    | memory/vio | 632 bytes |
    | memory/csv | 88 bytes |
    | memory/blackhole | 88 bytes |
    +—————————+—————+
    11 rows in set (0.02 sec)

    mysql [localhost:5726] {msandbox} ((none)) > SELECT SUBSTRING_INDEX(event_name,’/’,2) AS code_area, sys.format_bytes(SUM(current_alloc)) AS current_alloc FROM sys.x$memory_global_by_current_bytes GROUP BY SUBSTRING_INDEX(event_name,’/’,2) ORDER BY SUM(current_alloc) DESC;
    +—————————+—————+
    | code_area | current_alloc |
    +—————————+—————+
    | memory/innodb | 320.68 MiB |
    | memory/performance_schema | 137.16 MiB |
    | memory/mysys | 8.22 MiB |
    | memory/sql | 3.40 MiB |
    | memory/memory | 174.18 KiB |
    | memory/myisam | 106.14 KiB |
    | memory/vio | 512 bytes |
    | memory/blackhole | 512 bytes |
    | memory/csv | 512 bytes |
    +—————————+—————+
    9 rows in set (0.01 sec)

    So the extra memory is mainly performance_schema. But with that you get memory instrumentation, mdl, etc.

    August 14, 2019 at 10:32 am
    • LawTrollsDotCom (@LawTrollsDotCom) Reply

      Those test vms are horribly underpowered judging by your idle/io/steal stats. I the real world, nobody will care about the slight overhead 🙂

      August 14, 2019 at 12:17 pm
  • Morgan Tocker (@morgo) Reply

    As a followup comment, the MySQL manual also says this:

    “MySQL allocates buffers and caches to improve performance of database operations. The default configuration is designed to permit a MySQL server to start on a virtual machine that has approximately 512MB of RAM. You can improve MySQL performance by increasing the values of certain cache and buffer-related system variables. You can also modify the default configuration to run MySQL on systems with limited memory.”

    .. would you agree that it probably makes sense to change to 1GB of RAM for MySQL 8.0?

    August 14, 2019 at 10:38 am
  • Peter Zaitsev Reply

    Hi Morgan,

    You just stole idea for my followup blog post 🙂 Default allocation for Performance Memory is indeed huge and it is a pity MySQL8 does not scale it down with small devices – I would imagine I should be still able to instrument 10 connections and 10 tables on the small system but not 10000 concurrent connections.

    I also agree based on this data default should be raised to 1GB for MySQL8 I also wonder if it was ever tested running MySQL8 on 512MB system as part of release process to validate this manual claim 🙂

    August 14, 2019 at 10:46 am
    • Morgan Tocker (@morgo) Reply

      > I also agree based on this data default should be raised to 1GB for MySQL8 I also wonder if it was ever tested running MySQL8 on 512MB system as part of release process to validate this manual claim 🙂

      It does only say approximately 🙂 This text was just imported from MySQL 5.7’s manual, where I did validate it. If you switch to the 5.6 manual, you can see this claim is not there (by default with pfs enabled, it won’t start on a 512MB VM).

      The origin is this bug report https://bugs.mysql.com/bug.php?id=80737

      August 14, 2019 at 11:09 am
  • lefred Reply

    Peter thank you for starting this thread. And thank you Morgan for the accurate comment. I’ve added some information in this post: https://lefred.be/content/mysql-8-0-memory-consumption-on-small-devices/

    August 15, 2019 at 5:02 am
  • Romber Li Reply

    I wonder if there is anyway to know the usage of each part of mysql.
    as you can see blow, operation system tells me that mysql uses 46.5GB of RAM,
    but performance_schema tells me that mysql uses 37.7GB of RAM,
    it is nearly 10GB gap!!!
    here is several questions:
    1. Is it caused by innodb_flush_log_at_trx_commit=2 and sync_binlog=0?
    2. if yes, why these memory is not freed even in the light workload?
    3. is it caused by connection thread?
    4. if yes, why when i killed all connection, the memory usage does not get down?

    welcome for any help,
    thanks in advance.

    free -m
    total used free shared buff/cache available
    Mem: 64145 48544 271 3227 15328 11925
    Swap: 0 0 0

    mysql> show variables like ‘innodb_buffer_pool_size’;
    +————————-+————-+
    | Variable_name | Value |
    +————————-+————-+
    | innodb_buffer_pool_size | 37580963840 |
    +————————-+————-+
    1 row in set (0.00 sec)

    mysql> show variables like ‘innodb_flush_log_at_trx_commit’;
    +——————————–+——-+
    | Variable_name | Value |
    +——————————–+——-+
    | innodb_flush_log_at_trx_commit | 2 |
    +——————————–+——-+
    1 row in set (0.00 sec)

    mysql> show variables like ‘sync_binlog’;
    +—————+——-+
    | Variable_name | Value |
    +—————+——-+
    | sync_binlog | 0 |
    +—————+——-+
    1 row in set (0.00 sec)

    mysql> SELECT sum(current_number_of_bytes_used)/1024/1024/1024 FROM performance_schema.memory_summary_global_by_event_name;
    +————————————————–+
    | sum(current_number_of_bytes_used)/1024/1024/1024 |
    +————————————————–+
    | 37.710148520768 |
    +————————————————–+
    1 row in set (0.01 sec)

    mysql> exit
    Bye
    [root@10-42-106-194 ~]# pidstat -r -p 2932 1 1
    Linux 3.10.0-229.14.1.el7.x86_64 (10-42-106-194) 08/15/2019 _x86_64_ (32 CPU)

    11:57:36 PM UID PID minflt/s majflt/s VSZ RSS %MEM Command
    11:57:37 PM 700 2932 224.00 0.00 57718944 48764400 74.24 mysqld
    Average: 700 2932 224.00 0.00 57718944 48764400 74.24 mysqld

    August 15, 2019 at 12:08 pm
  • Peter Zaitsev Reply

    Hi Romber,

    Couple of things here. First there are some areas which are NOT covered by Memory instrumentation in Performance Schema, for example MySQL code also is kept in memory but not allocation which can be seen in Performance Schema

    More important through is what Performance Schema tracks how much space was allocated not how much it takes in memory, due to memory fragmentation/memory allocation library overhead.

    August 15, 2019 at 12:53 pm
    • Romber Li Reply

      Hi Peter,
      Thanks for your reply.
      I think MySQL code will not use much RAM, and also for other areas(if I was wrong, please kindly point out),
      and I want to know where the 10GB GAP come from,
      so is there anyway to find out?

      August 15, 2019 at 10:27 pm

Leave a Reply