MySQL 5.7, Ubuntu 14.04 mystery

mysql.md

Install

I have mysql 5.7.11-log installed on Ubuntu 14.04, installed from MySQL official deb repository deb http://repo.mysql.com/apt/ubuntu/ trusty mysql-5.7.

Configuration

Basically everything is stock configuration.

• Innodb buffer pool is set to only 250M to ensure MySQL doesn't allocate too much to the buffer pool. There's only one innodb pool.
• Perf schema memory counting is enabled - As per the mysql memory usage docs, performance-schema-instrument='memory/%=COUNTED' is enabled.
• 80+% memory usage - After about 30 minutes, MySQL memory usage is roughly 80% of ram, so with other stock system items (nothing else at all is installed), ram usage is at about 90%.
• This is a fresh server with a fresh install of MySQL, nothing else installed

Some investigation

I originally had the innodb buffer pool set at 750M, but turned it down to 250M. Here's what I see from the last query in the above linked docs:

mysql> SELECT substring_index(`x$memory_global_by_current_bytes`.`event_name`,'/',2) AS `code_area`, 
       sys.`format_bytes`(sum(`x$memory_global_by_current_bytes`.`current_alloc`)) AS `current_alloc` 
FROM `sys`.`x$memory_global_by_current_bytes`
GROUP BY substring_index(`x$memory_global_by_current_bytes`.`event_name`,'/',2) 
ORDER BY sum(`x$memory_global_by_current_bytes`.`current_alloc`) DESC;

+---------------------------+---------------+
| code_area                 | current_alloc |
+---------------------------+---------------+
| memory/innodb             | 981.47 MiB    |  -- Why so high?
| memory/mysys              | 128.22 MiB    |
| memory/performance_schema | 123.49 MiB    |
| memory/sql                | 1.62 MiB      |
| memory/memory             | 220.81 KiB    |
| memory/myisam             | 108.45 KiB    |
| memory/keyring            | 4.14 KiB      |
| memory/blackhole          | 512 bytes     |
| memory/csv                | 512 bytes     |
| memory/vio                | 8 bytes       |
+---------------------------+---------------+
10 rows in set (0.01 sec)

mysql> SELECT * FROM performance_schema.memory_summary_global_by_event_name WHERE EVENT_NAME LIKE 'memory/innodb/buf_buf_pool'\G
*************************** 1. row ***************************
                  EVENT_NAME: memory/innodb/buf_buf_pool
                 COUNT_ALLOC: 2
                  COUNT_FREE: 0
   SUM_NUMBER_OF_BYTES_ALLOC: 274857984  -- Roughly the 250M set
       SUM_NUMBER_OF_BYTES_FREE: 0
              LOW_COUNT_USED: 0
          CURRENT_COUNT_USED: 2
             HIGH_COUNT_USED: 2
    LOW_NUMBER_OF_BYTES_USED: 0
CURRENT_NUMBER_OF_BYTES_USED: 274857984
   HIGH_NUMBER_OF_BYTES_USED: 274857984
1 row in set (0.01 sec)

mysql> SELECT @@innodb_buffer_pool_instances;
+--------------------------------+
| @@innodb_buffer_pool_instances |
+--------------------------------+
|                              1 |
+--------------------------------+
1 row in set (0.00 sec)

When I had innodb buffer at 750M, memory/innodb was 1.5GB. Now it's down to 981.47M. This seems to suggest a roughly 500mb "discrepency" in memory usage (by "discrepency" I mean, "things going on that I am ignorant about").

I'm not sure if that's RAM, as in one case it was above the amount of RAM available (1.5 GB on a 1GB server) and in the other, it's almost all of it (981MB on a server with 1GB, reported as 994MB).

Htop and similar tools report that swap wasn't used and that this clearly doesn't reflect RAM actually used. Somewhere, that summation is off a bit in terms of memory used. Given the complexities involved in any database, that's not overly surprising, I'm sure the answer is just in something I don't know about.

Disabling Performance Schema does not seem to help either. Overall memory usage certainly goes down, but it still grows over time to use most of the available memory (MySQL uses 76% instead of 83%)

However I'm totally out of ideas on where to look for why MySQL is using so much memory (increasing over time) on a stock install.

MySQL typically works well out of the box in low-resource environments, this is a bit of a surprise!

Could it be specific to Ubuntu Trusty?

Following this bug for possible resolution.