| Bug #68980 | MySQL 5.6 memory usage too high | ||
|---|---|---|---|
| Submitted: | 17 Apr 2013 6:15 | Modified: | 22 Apr 2013 6:41 |
| Reporter: | wei liu | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: Options | Severity: | S3 (Non-critical) |
| Version: | 5.6.10 | OS: | Solaris (solaris10 (x86_64)) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | 5.6, high, Memory | ||
[17 Apr 2013 6:15]
wei liu
[17 Apr 2013 6:23]
wei liu
mysql> show variables like '%size%'; +--------------------------------------------------------+----------------------+ | Variable_name | Value | +--------------------------------------------------------+----------------------+ | binlog_cache_size | 32768 | | binlog_stmt_cache_size | 32768 | | bulk_insert_buffer_size | 8388608 | | delayed_queue_size | 1000 | | host_cache_size | 636 | | innodb_additional_mem_pool_size | 8388608 | | innodb_buffer_pool_size | 134217728 | | 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 | 8388608 | | 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 | 268435456 | | key_cache_block_size | 1024 | | large_page_size | 0 | | max_binlog_cache_size | 18446744073709547520 | | max_binlog_size | 52428800 | | max_binlog_stmt_cache_size | 18446744073709547520 | | max_heap_table_size | 268435456 | | max_join_size | 18446744073709551615 | | max_relay_log_size | 0 | | metadata_locks_cache_size | 1024 | | myisam_data_pointer_size | 6 | | myisam_max_sort_file_size | 9223372036853727232 | | myisam_mmap_size | 18446744073709551615 | | myisam_sort_buffer_size | 8388608 | | 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 | 268435456 | | query_prealloc_size | 8192 | | range_alloc_block_size | 4096 | | read_buffer_size | 2097152 | | read_rnd_buffer_size | 4194304 | | slave_pending_jobs_size_max | 16777216 | | sort_buffer_size | 1048576 | | thread_cache_size | 8 | | tmp_table_size | 268435456 | | transaction_alloc_block_size | 8192 | | transaction_prealloc_size | 4096 | +--------------------------------------------------------+----------------------+ 61 rows in set (0.00 sec)
[17 Apr 2013 14:24]
MySQL Verification Team
Thank you for the bug report. Please check with bug: http://bugs.mysql.com/bug.php?id=68287 read about table_definition_cache=1400 . Thanks.
[19 Apr 2013 2:37]
wei liu
I have add 'table_definition_cache=400' in my.cnf. mysql> show variables like 'table_definition_cache'; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | table_definition_cache | 400 | +------------------------+-------+ 1 row in set (0.00 sec) But the memory is still high: PID USERNAME LWP PRI NICE SIZE RES STATE TIME CPU COMMAND 2253 mysql 28 59 0 10G 7312M sleep 0:16 0.55% mysqld
[20 Apr 2013 17:21]
MySQL Verification Team
can you check how much memory performance_schema is using? SHOW ENGINE PERFORMANCE_SCHEMA STATUS; I'm also quiet sure you have a typo in my.cnf: table_open_cache = 2M
[22 Apr 2013 2:24]
wei liu
As I said before, "The memory usage of 5.5 with same my.cnf setting is less than 2G."
[22 Apr 2013 2:27]
wei liu
performance_schema_status
Attachment: performance_schema_status.txt (text/plain), 15.60 KiB.
[22 Apr 2013 5:33]
MySQL Verification Team
Hi! Memory usage here is expected. In your case, you must use in my.cnf performance_schema=0 in 5.6.11 Furthermore, please use 5.6.11 instead of 5.6.10 so that you can see the correct memory usage of performance_schema via the SHOW ENGINE PERFORMANCE_SCHEMA STATUS command. In 5.6.10, this wraps at 4GB. In 5.6.11 It shows true amount. Your table cache is simply incorrect. You really do not want or need 2097152 table cache with performance_schema monitoring enabled for that size. I encourage you to read thoroughly, this post also: http://marcalff.blogspot.com/2013/04/on-configuring-performance-schema.html
[22 Apr 2013 6:40]
wei liu
Yep, I know the table_open_cache need to be adjusted, but mysql will adjust it automatically every time when start like '[Warning] option 'table_open_cache': unsigned value 2097152 adjusted to 524288'. So I haven't changed it so far, but I will. And the memory become normal after I set performance_schema=0, but it means the performance schema has been disabled too. Can I say that how luck we are that we haven't use this version officially so widely yet?
[22 Apr 2013 6:41]
wei liu
Meanwhile, thanks a lot for your help.
