Mysql 5.6 using high memory where cpu is low

what does mysqltuner (from epel-release repo) say about your database?

Mysqltuner:

-------- Performance Metrics -------------------------------------------------
[--] Up for: 63d 14h 49m 31s (1B q [193.199 qps], 801K conn, TX: 13696B, RX: 1038B)
[--] Reads / Writes: 49% / 51%
[--] Binary logging is enabled (GTID MODE: ON)
[--] Total buffers: 293.6G global + 32.9M per thread (10000 max threads)
[OK] Maximum reached memory usage: 351.1G (69.57% of installed RAM)
[!!] Maximum possible memory usage: 614.6G (121.79% of installed RAM)
[OK] Slow queries: 0% (729/1B)
[OK] Highest usage of available connections: 17% (1792/10000)
[OK] Aborted connections: 2.26%  (18129/801691)
[!!] Query cache efficiency: 13.9% (53M cached / 387M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 3M sorts)
[!!] Temporary tables created on disk: 37% (66K on disk / 179K total)
[OK] Thread cache hit rate: 99% (1K created / 801K connections)
[!!] Table cache hit rate: 1% (101 open / 6K opened)
[OK] Open file limit used: 0% (51/65K)
[OK] Table locks acquired immediately: 100% (2B immediate / 2B locks)
[OK] Binlog cache memory access: 100.00% ( 342684253 Memory / 342684253 Total)
 
-------- MyISAM Metrics -----------------------------------------------------
[!!] Key buffer used: 18.2% (6M used / 33M cache)
[OK] Key buffer size / total MyISAM indexes: 32.0M/97.0K
[!!] Read Key buffer hit rate: 74.0% (5K cached / 1K reads)
[!!] Write Key buffer hit rate: 37.5% (8 cached / 5 writes)
 
-------- InnoDB Metrics -----------------------------------------------------
[--] InnoDB is enabled.
[OK] InnoDB buffer pool / data size: 293.0G/143.0G
[!!] InnoDB buffer pool instances: 8
[OK] InnoDB Used buffer: 99.96% (19191800 used/ 19199992 total)
[OK] InnoDB Read buffer efficiency: 99.94% (147479420791 hits/ 147570407764 total)
[!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)
[OK] InnoDB log waits: 0.00% (0 waits / 1720111156 writes)
 
-------- AriaDB Metrics -----------------------------------------------------
[--] AriaDB is disabled.
 
-------- Replication Metrics -------------------------------------------------
[--] No replication slave(s) for this server.
[OK] This replication slave is running with the read_only option enabled.
[OK] This replication slave is up to date with master.
 
-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Set up a Secure Password for user@host ( SET PASSWORD FOR 'user'@'SpecificDNSorIp' = PASSWORD('secure_password'); )
    Restrict Host for user@% to user@SpecificDNSorIp
    Reduce your overall MySQL memory footprint for system stability
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries which have no LIMIT clause
    Increase table_open_cache gradually to avoid file descriptor limits
    Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C
    Beware that open_files_limit (65535) variable
    should be greater than table_open_cache ( 5000)
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    query_cache_limit (> 1G, or use smaller result sets)
    tmp_table_size (> 56M)
    max_heap_table_size (> 56M)
    table_open_cache (> 5000)
    innodb_buffer_pool_instances(=64)
               
               

Select all Open in new window

My.cnf:

### INNODB settings
expire_logs_days=6
innodb_buffer_pool_size=300000M         
innodb_file_per_table=1
innodb_log_file_size=20M
innodb_log_buffer_size=256M
innodb_flush_log_at_trx_commit=1
innodb_thread_concurrency=8
default_storage_engine=InnoDB
innodb_log_files_in_group=2
innodb_use_native_aio=0
innodb_flush_method = O_DIRECT          
 
#### MyISAM
key_buffer_size=32M
myisam_recover=FORCE,BACKUP
 
#### Connections
connect_timeout=60
max_connections=10000                   
max_connect_errors=5000
wait_timeout=3600
interactive_timeout=3600
net_read_timeout=600
net_write_timeout=600
 
#### Caches and buffers
table_open_cache=5000                   
thread_cache_size=5000                 
query_cache_type=1
query_cache_limit=1512M                 
query_cache_size=256M
join_buffer_size=32M
tmp_table_size=56M
max_heap_table_size=56M
 
#### Limits
open_files_limit=65535
 
#### Misc
# enforce usage of new password hashing method
old_passwords=0
symbolic-links=0
sql_mode=STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY
tmpdir=/var/tmp

Select all Open in new window

as a side information, this high Mysql Memory usage happening only on the slaves host ( 1 Master => 3 slave(open for read only requests)) with GTID replication
this keeps increasing ==> today is at 83.5%

why table_open_cache=10000

and how to set open file ulimit??

would decreasing the max_connection reduce high memory consumption?

also :

Temporary tables created on disk: 37% (68K on disk / 184K total)

=> is having  

tmpdir=/var/tmp/

instead of

/tmp

automatically causing the temp tables to be created in on disk ?

I noticed that

ibdata1

is huge up to

31G

on the slaves comparing to the master(

13G

)==> why this difference??

Thank you .

I was asking about " why I have to setup table_open_cache=10000" ( not the max_connections => I know how the maximum usage is calculated) !!

for max_connetions I thought that mysql allocates memory only for the connected processes (which is in my case around 200) and releases it when ever the number goes down?? right??

Not right. It accumulates. For 30gb data it is waaaay too much ram
65535 files limit is enough , tell at lest you use  bsd with capdb or linux or solaris?

we use linux centos6

Referring my above questions:  to any suggestion about the tmpdir path( /var/tmp  VS /tmp ) and ibdata1 being huge on the slave comparing to the master.

My database data has 230G for this I setup innodb_pool_size=300G
but ibdata1 file is huge up to   31G on the slaves comparing to the master (13G) ==> why this difference??

Are we still talking same database?
[OK] InnoDB buffer pool / data size: 293.0G/143.0G

You need to issue optimize table on master and it is sent to slaves. On old versions it locks tables and that takes time and timeouts.