MySQL Bugs: #68287: High Memory Usage with MySQL 5.6.12 GA in "Development Machine" mode

Bug #68287	High Memory Usage with MySQL 5.6.12 GA in "Development Machine" mode
Submitted:	6 Feb 2013 6:37	Modified:	7 Jun 2013 1:03
Reporter:	MELERIX ...	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL for Windows: Installer	Severity:	S3 (Non-critical)
Version:	5.6.12 GA	OS:	Windows (XP SP3, W7 SP1)
Assigned to:	Reggie Burnett	CPU Architecture:	Any
Tags:	5.6.12, Development Machine, GA, High Memory Usage, mysql installer

Description:
MySQL 5.6.10 is using big amounts of memory, between 200-400mb when is idle.

How to repeat:
1 - download: mysql-installer-community-5.6.10.0.msi
2 - install "Server Only".
3 - configure it as "Developer".
4 - finish installation and look in windows task manager (process tab).

you will notice "mysqld.exe" is using around 200+ or 400+ mb of memory, but mysql is not being used at all, is just idle.

with MySQL 5.6.9 memory usage is ok.

Suggested fix:
reverting to MySQL 5.6.9 memory usage is ok.

Hi, please upload your my.ini file in an attachment here?
Add in attachment, output of:

SHOW FULL PROCESSLIST;
SHOW GLOBAL VARIABLES;
SHOW GLOBAL STATUS;
SHOW ENGINE INNODB STATUS;

Also notice that XP is not a supported platform for 5.6....

my.ini file

Attachment: my.ini (application/octet-stream, text), 13.98 KiB.

SHOW FULL PROCESSLIST, SHOW GLOBAL VARIABLES, SHOW GLOBAL STATUS, SHOW ENGINE INNODB STATUS

Attachment: tests.zip (application/x-zip-compressed, text), 8.23 KiB.

Shane Bester, you have said: "Also notice that XP is not a supported platform for 5.6..."

but during mysql installer "requirements check" it doesn't says that XP is incompatible or something.

also as I said above, with MySQL 5.6.9 the memory usage is ok in XP.

So I used your my.ini and compared.  This is on a setup with no users, no data.
I see no problems.

D:\>tlist mysqld.exe
20856 mysqld.exe
   CWD:     D:\mysql-5.6.9-rc-winx64\data\
   CmdLine: mysqld.exe  --defaults-file=e:/my.ini --console
   VirtualSize:   535316 KB   PeakVirtualSize:   550276 KB
   WorkingSetSize:425716 KB   PeakWorkingSetSize:425716 KB
   NumberOfThreads: 23

----

D:\>tlist mysqld.exe
12292 mysqld.exe
   CWD:     D:\mysql-5.6.10-winx64\data\
   CmdLine: mysqld.exe  --defaults-file=e:/my.ini --console
   VirtualSize:   535340 KB   PeakVirtualSize:   550300 KB
   WorkingSetSize:425652 KB   PeakWorkingSetSize:425652 KB
   NumberOfThreads: 23

You could even use something like sysinternals vmmap to check what memory is allocated where.

VMMap export

Attachment: mysqld.mmp (application/octet-stream, text), 144.51 KiB.

You should be 100% sure you have identical my.ini. Installer might do strange things with my.ini.  This is why I used non-installer versions to compare here
and run mysqld from the command line.

Please, send the output of:

show variables like '%size%';

from mysql command line client connected to your new 5.6.10 instance. I assume memory use is related to new default values for some global buffers.

show variables like '%size%';

binlog_cache_size	32768
binlog_stmt_cache_size	32768
bulk_insert_buffer_size	8388608
delayed_queue_size	1000
host_cache_size	228
innodb_additional_mem_pool_size	2097152
innodb_buffer_pool_size	11534336
innodb_change_buffer_max_size	25
innodb_ft_cache_size	8000000
innodb_ft_max_token_size	84
innodb_ft_min_token_size	3
innodb_log_buffer_size	1048576
innodb_log_file_size	50331648
innodb_online_alter_log_max_size	134217728
innodb_page_size	16384
innodb_purge_batch_size	300
innodb_sort_buffer_size	1048576
innodb_sync_array_size	1
join_buffer_size	262144
key_buffer_size	8388608
key_cache_block_size	1024
large_page_size	0
max_binlog_cache_size	18446744073709547520
max_binlog_size	1073741824
max_binlog_stmt_cache_size	18446744073709547520
max_heap_table_size	16777216
max_join_size	18446744073709551615
max_relay_log_size	0
metadata_locks_cache_size	1024
myisam_data_pointer_size	6
myisam_max_sort_file_size	107374182400
myisam_mmap_size	4294967295
myisam_sort_buffer_size	13631488
optimizer_trace_max_mem_size	16384
performance_schema_accounts_size	100
performance_schema_digests_size	10000
performance_schema_events_stages_history_long_size	10000
performance_schema_events_stages_history_size	10
performance_schema_events_statements_history_long_size	10000
performance_schema_events_statements_history_size	10
performance_schema_events_waits_history_long_size	10000
performance_schema_events_waits_history_size	10
performance_schema_hosts_size	100
performance_schema_session_connect_attrs_size	512
performance_schema_setup_actors_size	100
performance_schema_setup_objects_size	100
performance_schema_users_size	100
preload_buffer_size	32768
profiling_history_size	15
query_alloc_block_size	8192
query_cache_size	1048576
query_prealloc_size	8192
range_alloc_block_size	4096
read_buffer_size	24576
read_rnd_buffer_size	262144
slave_pending_jobs_size_max	16777216
sort_buffer_size	262144
thread_cache_size	9
tmp_table_size	8388608
transaction_alloc_block_size	8192
transaction_prealloc_size	4096

All this info is useless, unless you can confirm with 100% certainty you're running 5.6.9 and 5.6.10 with identical my.ini.

my.ini is generated by installer.

wait several minutes more and I'll check in a compare editor if there is differences in my.ini from 5.6.9 RC vs my.ini from 5.6.10 GA.

Differences in my.in (5.6.9 vs 5.6.10) both generated by mysql installers.

5.6.9:
------
query_cache_size=0
table_open_cache=256
tmp_table_size=9M
thread_cache_size=8
myisam_sort_buffer_size=17M
read_buffer_size=36K
innodb_buffer_pool_size=36M
innodb_log_file_size=19M

5.6.10:
-------
query_cache_size=1M
table_open_cache=2000
tmp_table_size=8M
thread_cache_size=9
myisam_sort_buffer_size=13M
read_buffer_size=26K
innodb_buffer_pool_size=11M
innodb_log_file_size=48M

also this present in 5.6.10 only:
---------------------------------
# The increment size (in MB) for extending the size of an auto-extend InnoDB system tablespace file when it becomes full.
innodb_autoextend_increment=64M

# The number of regions that the InnoDB buffer pool is divided into.
# For systems with buffer pools in the multi-gigabyte range, dividing the buffer pool into separate instances can improve concurrency,
# by reducing contention as different threads read and write to cached pages.
innodb_buffer_pool_instances=8

# Determines the number of threads that can enter InnoDB concurrently.
innodb_concurrency_tickets=5000

# Specifies how long in milliseconds (ms) a block inserted into the old sublist must stay there after its first access before
# it can be moved to the new sublist.
innodb_old_blocks_time=1000

# It specifies the maximum number of .ibd files that MySQL can keep open at one time. The minimum value is 10.
innodb_open_files=300

# When this variable is enabled, InnoDB updates statistics during metadata statements.
innodb_stats_on_metadata=0

# When innodb_file_per_table is enabled (the default in 5.6.6 and higher), InnoDB stores the data and indexes for each newly created table
# in a separate .ibd file, rather than in the system tablespace.
innodb_file_per_table=1

# Use the following list of values: 0 for crc32, 1 for strict_crc32, 2 for innodb, 3 for strict_innodb, 4 for none, 5 for strict_none.
innodb_checksum_algorithm=0

# The number of outstanding connection requests MySQL can have.
# This option is useful when the main MySQL thread gets many connection requests in a very short time.
# It then takes some time (although very little) for the main thread to check the connection and start a new thread.
# The back_log value indicates how many requests can be stacked during this short time before MySQL momentarily
# stops answering new requests.
# You need to increase this only if you expect a large number of connections in a short period of time.
back_log=70

# If this is set to a nonzero value, all tables are closed every flush_time seconds to free up resources and
# synchronize unflushed data to disk.
# This option is best used only on systems with minimal resources.
flush_time=0

# The minimum size of the buffer that is used for plain index scans, range index scans, and joins that do not use
# indexes and thus perform full table scans.
join_buffer_size=256K

# The maximum size of one packet or any generated or intermediate string, or any parameter sent by the
# mysql_stmt_send_long_data() C API function.
max_allowed_packet=4M

# If more than this many successive connection requests from a host are interrupted without a successful connection,
# the server blocks that host from performing further connections.
max_connect_errors=100

# Changes the number of file descriptors available to mysqld.
# You should try increasing the value of this option if mysqld gives you the error "Too many open files".
open_files_limit=4110

# Set the query cache type. 0 for OFF, 1 for ON and 2 for DEMAND.
query_cache_type=1

# If you see many sort_merge_passes per second in SHOW GLOBAL STATUS output, you can consider increasing the
# sort_buffer_size value to speed up ORDER BY or GROUP BY operations that cannot be improved with query optimization
# or improved indexing.
sort_buffer_size=256K

# The number of table definitions (from .frm files) that can be stored in the definition cache.
# If you use a large number of tables, you can create a large table definition cache to speed up opening of tables.
# The table definition cache takes less space and does not use file descriptors, unlike the normal table cache.
# The minimum and default values are both 400.
table_definition_cache=1400

# Specify the maximum size of a row-based binary log event, in bytes.
# Rows are grouped into events smaller than this size if possible. The value should be a multiple of 256.
binlog_row_event_max_size=8K

# If the value of this variable is greater than 0, a replication slave synchronizes its master.info file to disk.
# (using fdatasync()) after every sync_master_info events.
sync_master_info=10000

# If the value of this variable is greater than 0, the MySQL server synchronizes its relay log to disk.
# (using fdatasync()) after every sync_relay_log writes to the relay log.
sync_relay_log=10000

# If the value of this variable is greater than 0, a replication slave synchronizes its relay-log.info file to disk.
# (using fdatasync()) after every sync_relay_log_info transactions.
sync_relay_log_info=10000

ini files from both versions

Attachment: ini files.zip (application/x-zip-compressed, text), 9.30 KiB.

Memory usage with my.ini created by 5.6.10 installer

Attachment: TaskManager1.png (image/png, text), 24.62 KiB.

Memory usage with my.ini created by 5.6.9 installer (copied from older install)

Attachment: TaskManager2.png (image/png, text), 28.16 KiB.

I attached 2 pictures the first one TaskManager1 shows the memory usage using the my.ini file created by the MySQL Installer for Windows. The second picture shows the memory usage starting the server 5.6.10 with a my.ini file created with the MySQL Windows Installer 5.6.9. So something in the new my.ini file increases the memory usage.

yes, that is the bug, thanks for upload the pictures.

seems W7 is affected too, by this bug.

I forgot to mention the 5.6.10 was installed on fresh Windows 7 64-bit OS (it was the first MySQL server installed).

Could you please comment the below variable on your my.ini (stop/restart the server).

from:
table_definition_cache=1400
to:
#table_definition_cache=1400

then check the memory usage. Thanks.

changed from:
table_definition_cache=1400
to:
#table_definition_cache=1400

your workaround is valid, now memory usage decreased from 420mb to 65mb, so seems to be ok ;)

this will be fixed in 5.6.11 ?

btw I also noticed a warning inside .err file, it says:

2013-02-06 22:01:25 5716 [Warning] option 'innodb-autoextend-increment': unsigned value 67108864 adjusted to 1000

category changed to: "MySQL installer".

this bug is caused due "MySQL Installer" create wrong default values in "my.ini" file, causing high memory usage.

Why do you think it's the 'wrong default value'?. That's OK for my laptop and if you have a very low resource machine then you can edit my.ini file and change for the minimum value which is 400.

I forgot to mention the laptop I referred has 4 GB which I considered the average RAM installed nowadays.

seems I'll need to explain it again and more detailed ?

ok, here we go...

the point is that in the "MySQL Installer" I've selected "Development Machine" mode during "configuration" step, which is designed to consume "low resources".

even you can read in the description of this mode, the following: "This is a development machine, and many others applications will be installed on it. A minimal amount of memory will be used by MySQL"

in all MySQL versions before 5.6.10 (5.0, 5.1, 5.5, 5.6.9) memory usage is ok with default values for "Development Machine" mode.

so as I said above "Development Machine" mode was always used to consume "low resources", and now this is using 400mb ram by default, do you think is ok ?

ofc is not ok, is a bug! and only present in 5.6.10.

also in the description for "table_definition_cache" inside my.ini you can read the following: "The minimum and default values are both 400."

and MySQL Installer still continue setting 1400 by default which is wrong.

I could consider normal if MySQL consume 400mb or more, when you select "Dedicated Mahine" mode during install, but not with "Development Machine" mode!

proof

Attachment: Delopment Machine mode.gif (image/gif, text), 70.29 KiB.

Alright thank you for the feedback. Actually the comment in the installer while selecting the developer option mention to use the minimum of resources. Workaround so is to edit the my.ini file for values below 1400.

okay, fair enough. now I wonder why the installer needs to put any options into my.ini at all (except directories).  the compiled-in defaults are perfect for small deployments.

MySQL 5.6.10.1 BUG

I can confirm that on WIndows Server 2012 Datacenter build 9200. Never experienced it before. New Server 2012 instance with all updates + MySql Community 5.6.10.1. After installing Mysqld process using 420,6 MB memory.

How to repeat:
1 - download: mysql-installer-community-5.6.10.1.msi
2 - install all features
3 - configure it as "Developer machine" (should use MINIMUM amount of memory).
4 - finish installation and look in windows task manager (process tab).

I'm seeing the same thing on:
Red Hat Enterprise Linux Server release 6.3
Linux 2.6.32-279.5.2.el6.x86_64 #1 SMP Tue Aug 14 11:36:39 EDT 2012 x86_64 x86_64 x86_64 GNU/Linux

I am also experiencing this. could you give an advice what to do. please

I just make fresh install yesterday from MySQl installer after I reformat my laptop, then when i try to look in task manager, the mysql using more 420 mb of memory.

OS: Win 7
mysql: 5.6.11 downloaded yesterday

I will appreciate for any help

Same problem with 5.6.11 CS:
* mysql-installer-community-5.6.11.0.msi
* Windows 7 (x64)
* "Developer" installation

I can confirm the solution works, commenting out the "table_definition_cache" reduces the private bytes from 420 MB to 87 MB, thanks!

now is even worse with 5.6.12, because now if you comment out the "table_definition_cache" it still continue using 400mb+ of memory in developer mode.

Fwiw, I've noticed a very similar issue w/ 5.5.30 (and now 5.5.32).

Windows 7, initially installed w/ the installer, and it is using 405,660K via task manager.  My other instances are all fine.  I've even lowered the setting to extremely minimal values, and there is 0 traffic.

I'll continue to investigate from my side, and post any useful info I find, if anything.

For me, I see the high usage in 5.5.32 and 5.7.1, but the work-around reduces it for 5.6.12.

table_definition_cache=400

That worked for 5.6.12.  

However, I still have ~400M of RAM usage for the 5.5 and 5.7 instances, even with table_definition_cache=400.  Again, all ram settings are very low and there is no traffic.

Please, check what 'show engine performance_schema status' says is the total memory used.  

Again, there is no server mystery here. If the installer set wrong my.ini values, we'll have to see why...  Personally I think they only values installer need to set are paths and innodb buffer/log options.  
The rest at defaults is probably better!

Hi Shane, many thanks for the tip!  Just had a chance to check out the 'show engine performance_schema status' and in my case, for both the 5.5 and 5.7 instances, the increased memory usage was from the performance schema itself (by having it enabled).  Once I disabled it (performance_schema=0), the memory dropped to the normal level (I've later re-enabled it - just wanted to know where/why the memory went).  Thought I'd mention it here, since I suspect some of the others (and certainly some future users will) have encountered this for the same reason.

Useful guide:
http://marcalff.blogspot.com/2013/04/on-configuring-performance-schema.html

The amount of RAM used by Performance Schema if no PS settings are used is determined by the values set for these settings:

table_definition_cache 400
table_open_cache 2000
max_connections 151
open_files_limit 5000 

The expected values for low usage servers are the ones given at https://blogs.oracle.com/supportingmysql/entry/server_defaults_changes_in_mysql and copied above for convenience .

The change to calculated default values was implemented between 5.6.9 and 5.6.10 so it isn't a surprise that there would be a change in these settings and the related PS memory use between those versions. On *nix these calculations are done during mysqld startup, on Windows the installer writes a my.cnf with calculated values. It doesn't have to follow the ones used in *nix but should probably be close for low usage servers.

I doubt that it is a good idea to use higher values than those for a development server but the settings aren't higher.

It's up to the Windows team to decide what their definition for development server means but if the objective is low RAM use I suggest one of two possible approaches:

1. Reduce table_open_cache and table_definition_cache and max_connections so that at least one value is below the expected mysqld defaults. Maybe to 400 (5.5 default), 400 (5.5 default), 25 (5.5 default was 151 but I suppose a dev machine will be expected to have far fewer connections).

Now look at the PS autoconfiguration code in storage/perfschema/pfs_autosize.cc and observe:

  if ((param->m_hints.m_max_connections <= MAX_CONNECTIONS_DEFAULT) &&
      (param->m_hints.m_table_definition_cache <= TABLE_DEF_CACHE_DEFAULT) &&
      (param->m_hints.m_table_open_cache <= TABLE_OPEN_CACHE_DEFAULT))
  {
    /* The my.cnf used is either unchanged, or lower than factory defaults. */
    return & small_data;
  }

  if ((param->m_hints.m_max_connections <= MAX_CONNECTIONS_DEFAULT * 2) &&
      (param->m_hints.m_table_definition_cache <= TABLE_DEF_CACHE_DEFAULT * 2) &&
      (param->m_hints.m_table_open_cache <= TABLE_OPEN_CACHE_DEFAULT * 2))
  {
    /* Some defaults have been increased, to "moderate" values. */
    return & medium_data;
  }

  /* Looks like a server in production. */
  return & large_data;

The reduced settings should case the small_data settings to be used.

Later you'll see things like this where con is based on max_connections:

      + con * rwlock_per_connection

So the combination of reduced max_connections and other base settings should cause PS to ask for much less RAM.

2. Could take more direct control of the PS sizing settings. Whether this is better depends on whether you think dev boxes will have reason to use larger values for the base variables or not.

I think adjusting the base values is likely to be the easiest way to go.

Any user of MySQL can just try the settings I suggest in the start of option 1. Please let us know if those don't produce a nice improvement.

James Day, MySQL Senior Principal Support Engineer, Oracle

There seems to be a threshold for table_definition_cache or table_open_cache (at 800 and 4000 respectively) where the memory usage takes an unexpected hit.

** Test 1 **

my.cnf contains only:

> [mysqld]
> table_definition_cache = 800
> table_open_cache = 4000
> open_files_limit = 9999

=> memory usage as reported by TaskManager (Working Set) = 163 Mb

** Test 2 **

my.cnf contains only:

> [mysqld]
> table_definition_cache = 801
> table_open_cache = 4000
> open_files_limit = 9999

=> memory usage as reported by TaskManager (Working Set) = 484 MB

** Test 3 **

my.cnf contains only:

> [mysqld]
> table_definition_cache = 800
> table_open_cache = 4001
> open_files_limit = 9999

=> memory usage as reported by TaskManager (Working Set) = 484 MB

** Test 4 **

my.cnf contains only:

> [mysqld]
> table_definition_cache = 801
> table_open_cache = 4001
> open_files_limit = 9999

=> memory usage reported by TaskManager (Working Set) = 484 MB

(actual readings rounded to the MB)

Environment: Windows 7 32bit, MySQL 5.6.14 from mysql-5.6.14-win32.zip

We're at 5.6.14 GA and this bug is still Not fixed. Today I had to upgrade a 5.5.8 instance to the latest. Again, I had to lose time and research the internet for the solution found in this long thread.
Year ago I had to go through the same pain after I upgraded another instance.
This time I made a TXT file with upcased name where I referred the URL to this thread in case I need to troubleshoot another instance.
And this would be so easy to fix... An year later, still no fix for this easy to fix bug.

François,

Yes, there are thresholds based on table_open_cache and table_definition_cache and max_connections and crossing the thresholds produces a big increase in RAM used. The thresholds work by first deciding if the server size is small, medium or large.

Small: all three are same as or less than defaults (2000, 400, 151).
Large: any of the three is more than twice the default.
Medium: others.

The server size is then used to calculate a scaling factor that is used to calculate other values. The scaling factor depends on the PS setting.

Here is a linux example to show the effect of just changing table_definition_cache, with max_connections always 151, table_open_cache 2000 and open_files_limit 65000, all other server settings default and little data in any table:

400: small size: 52.6 megabytes
401: medium size: 90.7 megabytes
800: medium size: 98.4 megabytes
801: large size: 400 megabytes

Notice the big increase once large size rules are used.

James Day. MySQL Senior Principal Support Engineer, Oracle

also exists in mysql-5.6.*-win32.zip and mysql-5.6.*-winx64.zip

> It's up to the Windows team to decide what their definition for development server means (James Day, 23 Jul 2013 2:55)

Dear Windows Team, the Windows Installer describes the "Development Machine" configuration in these terms:

"A minimal amount of memory will be used by MySQL"

The installer offers three possible "Config types", I just tried each of them. The three "my.ini" files I got during my tests only differ on these values:

(option=Development/Server/Dedicated)

tmp_table_size= 23M / 23M / 24M
myisam_sort_buffer_size= 38M / 38M / 38M
read_buffer_size= 64K / 64K / 64K
innodb_buffer_pool_size= 111M / 112M / 113M

... all resulting in the same memory usage (about 420MB). Tested with mysql-installer-community-5.6.16.0.msi

It looks like the selected "Config Type" has no effect on these values. Only the amount of free memory at installation time seems to matter.

Suggested fixes:
- lower the table_definition_cache setting for the "Development Machine" configuration type (currently 1400, I suggest <800 or even <400, i.e. a value closer to the default 400). This single change brings the memory usage down to 113MB (=800) or 68MB (=400).
- compute the dynamic values based on total system memory (as opposed to free memory). Or better, replace the "Config type" selection with a question asking how much memory to allocate to MySQL (absolute value, or percentage).

Same under centos, Server version: 5.6.19 MySQL Community Server (GPL)

Reducing table_definition_cache to 400 helps reduce empty server memory usage.

I encountered this Bug today on a machine with MySQL 5.7.5-m15 on Linux and just a gigabyte of memory.

I lowered performance_schema_max_table_instances quite a bit and that helped. Then I lowered table_open_cache and table_definition_cache which helped even more. Finally I lowered performance_schema_max_prepared_statements_instances and then the memory usage was what I expected.

Hello, same 440MB memory issue here with Win XP.
from discussion here.. i fixed it by:
1) editing my.ini
2) after section [mysqld] -- add a line:
   performance_schema=off

my.ini file excerpt:
     
     # server_type=3
     [mysqld]
     performance_schema=off        <<<<-- line i added

     # The next three options are mutually exclusive to SERVER_PORT below.
     # skip-networking
     
It Works !
I left "table_definition_cache=1400" as it originally was.
virtual memory use is now  29M.