mysql uses too much memory

Question

Current configuration:

+------------------------------------------+--------------------+
|                          key_buffer_size |          64.000 MB |
|                         query_cache_size |          32.000 MB |
|                  innodb_buffer_pool_size |       15360.000 MB |
|          innodb_additional_mem_pool_size |           8.000 MB |
|                   innodb_log_buffer_size |         128.000 MB |
+------------------------------------------+--------------------+
|                              BASE MEMORY |       15592.000 MB |
+------------------------------------------+--------------------+
|                         sort_buffer_size |           0.500 MB |
|                         read_buffer_size |           1.000 MB |
|                     read_rnd_buffer_size |           2.000 MB |
|                         join_buffer_size |           0.500 MB |
|                             thread_stack |           0.250 MB |
|                        binlog_cache_size |           0.031 MB |
|                           tmp_table_size |           8.000 MB |
+------------------------------------------+--------------------+
|                    MEMORY PER CONNECTION |          12.281 MB |
+------------------------------------------+--------------------+
|                     Max_used_connections |                275 |
|                          max_connections |                700 |
+------------------------------------------+--------------------+
|                              TOTAL (MIN) |       18969.344 MB |
|                              TOTAL (MAX) |       24188.875 MB |
+------------------------------------------+--------------------+

top:

  PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND                                                           
24876 mysql     20   0 57.206g 0.045t   5.4m S 279.8 90.2   2358:20 mysqld   

According to documentation memory should be ~25GB. But with those settings Mysql 5.6.24 actually uses 47GB memory. Any ideas why or how I can reduce requirements?

UPDATE, - more info:

Mysql has about 600K tables so we have a big table_open_cache(=400K) as this made a big improvement in performance.

The system writes to DB every 10-15 minutes using most of the available connections. wait_timeout is 600sec.

System memory is 52G

UPDATE ADDED: SHOW VARIABLES, & SHOW GLOBAL STATUS BELOW: http://pastebin.com/K8ZNY0pC

This is used by a closed-source commercial software (Tridium Niagara). I cannot change the schema or the way it writes. All tables are InnoDB. Reducing table open cache by half has negligible impact on memory.

Answer 1

(adding a second answer -- to address the VARIABLES and STATUS)

Observations:

Version: 5.6.24
52 GB of RAM
Uptime = 21d 02:26:20
You are not running on Windows.
Running 64-bit version
You appear to be running entirely (or mostly) InnoDB.

The More Important Issues

• table_open_cache = 200K, yet 156 tables opened per second, and all are misses/overflows from the cache. You must redesign you schema to have a civilized number of tables.

• Are there really 403 ROLLBACKs per second? If so, think about how to avoid some of them. That's probably more than 10% of the transactions.

• innodb_lock_wait_timeout = 300 -- Assuming you really need transactions to hang around waiting for 5 minutes, this could be consuming RAM.

• tmp_table_size = 8M, together with 51 tmp tables created per second, may be leading to quite a few GB of transient MEMORY tables. Look through the slowlog to find which queries need tmp tables and see if they can be rewritten.

• Decrease long_query_time to 2 (now 10), turn on the slowlog, wait a day, run pt-query-digest. Then study the worst few queries -- improving them will speed up the entire system and probably decrease RAM usage.

Details and other observations

( Innodb_buffer_pool_reads ) = 329,533,959 / 1823180 = 180 /sec -- InnoDB buffer_pool I/O read rate -- check innodb_buffer_pool_size

( Innodb_buffer_pool_pages_flushed ) = 589,768,119 / 1823180 = 323 /sec -- Writes (flushes) -- check innodb_buffer_pool_size

( innodb_buffer_pool_size / _ram ) = 16,106,127,360 / 53248M = 28.8% -- % of RAM used for InnoDB buffer_pool

( Opened_tables ) = 285,544,112 / 1823180 = 156 /sec -- Frequency of opening Tables -- increase table_open_cache

( table_open_cache ) = 200,000 -- Number of table descriptors to cache -- Several hundred is usually good.

( Table_open_cache_overflows ) = 285,343,673 / 1823180 = 156 /sec -- May need to increase table_open_cache

( Table_open_cache_misses ) = 285,544,112 / 1823180 = 156 /sec -- May need to increase table_open_cache

( (Innodb_buffer_pool_reads + Innodb_buffer_pool_pages_flushed) ) = ((329533959 + 589768119) ) / 1823180 = 504 /sec -- InnoDB I/O -- Increase innodb_buffer_pool_size?

( innodb_log_buffer_size ) = 128M -- Suggest 2MB-64MB, and at least as big as biggest blob set in transactions. -- Adjust innodb_log_buffer_size.

( Innodb_log_writes ) = 167,769,519 / 1823180 = 92 /sec

( Innodb_os_log_written / (Uptime / 3600) / innodb_log_files_in_group / innodb_log_file_size ) = 332,945,398,784 / (1823180 / 3600) / 2 / 100M = 3.13 -- Ratio

( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written ) = 1,823,180 / 60 * 100M / 332945398784 = 9.57 -- Minutes between InnoDB log rotations Beginning with 5.6.8, this can be changed dynamically; be sure to also change my.cnf. -- (The recommendation of 60 minutes between rotations is somewhat arbitrary.) Adjust innodb_log_file_size.

( Com_rollback ) = 735,486,482 / 1823180 = 403 /sec -- ROLLBACKs in InnoDB. -- An excessive frequency of rollbacks may indicate inefficient app logic.

( innodb_lock_wait_timeout ) = 300 -- Two battling InnoDB transactions, but not a deadlock -- one will wait this long (seconds) in hopes of getting the desired locks. -- Fix the cause of timeouts rather than increasing this value.

( Innodb_dblwr_writes ) = 14,574,293 / 1823180 = 8 /sec -- "Doublewrite buffer" writes to disk. "Doublewrites" are a reliability feature. Some newer versions / configurations don't need them. -- (Symptom of other issues)

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

( Questions ) = 7,836,091,851 / 1823180 = 4298 /sec -- Queries (outside SP) -- "qps" -- >2000 may be stressing server

( Queries ) = 8,262,090,685 / 1823180 = 4531 /sec -- Queries (including inside SP) -- >3000 may be stressing server

( Created_tmp_tables ) = 92,537,439 / 1823180 = 51 /sec -- Frequency of creating "temp" tables as part of complex SELECTs.

( Created_tmp_disk_tables ) = 6,158,440 / 1823180 = 3.4 /sec -- Frequency of creating disk "temp" tables as part of complex SELECTs -- increase tmp_table_size and max_heap_table_size. Check the rules for temp tables being able to use MEMORY instead of MyISAM. It may be possible to make a minor schema or query change to avoid MyISAM. Better indexes and reformulation of queries can

( Handler_read_rnd_next ) = 266,467,437,770 / 1823180 = 146155 /sec -- High if lots of table scans -- possibly inadequate keys

( Com_rollback / Com_commit ) = 735,486,482 / 741864016 = 99.1% -- Rollback : Commit ratio -- Rollbacks are costly; change app logic

( Com_show_variables ) = 6,666,028 / 1823180 = 3.7 /sec -- SHOW VARIABLES ... -- Why are you requesting the VARIABLES so often?

( Select_scan ) = 315,076,005 / 1823180 = 172 /sec -- full table scans -- Add indexes / optimize queries (unless they are tiny tables)

( Select_scan / Com_select ) = 315,076,005 / 3888296954 = 8.1% -- % of selects doing full table scan. (May be fooled by Stored Routines.) -- Add indexes / optimize queries

( Com_insert + Com_delete + Com_delete_multi + Com_replace + Com_update + Com_update_multi ) = (613230844 + 1238003 + 0 + 30479625 + 71282659 + 0) / 1823180 = 392 /sec -- writes/sec -- 50 writes/sec + log flushes will probably max out I/O write capacity of normal drives

( ( Com_stmt_prepare - Com_stmt_close ) / ( Com_stmt_prepare + Com_stmt_close ) ) = ( 23 - 22 ) / ( 23 + 22 ) = 2.2% -- Are you closing your prepared statements? -- Add Closes.

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

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

( Com_change_db ) = 31,947,189 / 1823180 = 18 /sec -- Probably comes from USE statements. -- Consider connecting with DB, using db.tbl syntax, eliminating spurious USE statements, etc.

( max_connect_errors ) = 10,000 -- A small protection against hackers. -- Perhaps no more than 200.

( Connections ) = 21,121,749 / 1823180 = 12 /sec -- Connections -- Increase wait_timeout; use pooling?

( Threads_running - 1 ) = 22 - 1 = 21 -- Active threads (concurrency when data collected) -- Optimize queries and/or schema

You have the Query Cache half-off. You should set both query_cache_type = OFF and query_cache_size = 0 . There is (according to a rumor) a 'bug' in the QC code that leaves some code on unless you turn off both of those settings.

39 issues flagged, out of 125 computed Variables/Status/Expressions. Settings not mentioned are mostly OK.

Answer 2

(I don't see where "47GB" comes from. The "total" includes the "base". Anyway...)

The "MEMORY PER CONNECTION" is seriously pessimistic. And yet it is actually an underestimate!

Here's the pessimistic: The thread_stack (0.25MB) is the only thing that is necessarily allocated for a connection; the rest may or may not be allocated, and they may be freed while the connection lives.

Here's the underestimate: A complex SELECT can allocate multiple tmp tables of up to 16MB each.

To protect yourself, decrease max_connections and/or increase wait_timeout. (The latter kills off connections that sit around idle too long.)

Find out why 248 clients are staying connected. One possible reason is MaxClients in Apache could be higher than is realistic.

After that, I would set innodb_buffer_pool_size to 70% of available RAM and not worry about the pessimistic formula. (How much RAM do you have?)

I have reviewed formulas similar to yours on literally hundreds of production machines. All had the scare of "too much memory"; none actually exhibited "too much memory".