Mysql 5.6 using high memory where cpu is low

CCVOIP
CCVOIP used Ask the Experts™
on
Hi,
I have a mysql server running on centos6. RAM=500G where innodb_buffer_pool=300G.
top command shows 82.7% memory usage by mysqld process

I need some help in troubleshooting the memory usage by mysqd.

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Hi CCVOIP,

82% of 500 is 410, so mysqld is running in 410G with a 300G buffer pool.  That seems a bit high, but not hugely.

With a 300G buffer pool we need to identify about 110G.  About 30G is normal for other data structures, perhaps 40G if you're using small (4K or 8K) pages.  The buffer pool typically has up to 7% overhead, so that's another 20G.  

We're now trying to identify 50-60G, not 100G.  

Can you post your configuration file?  It might give some clues.

Kent
Top Expert 2015

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

Author

Commented:
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)
               
               

Open in new window

Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
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

Open in new window

Author

Commented:
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%
Top Expert 2015
Commented:
# Critical - fit mysql in RAM!!!
# max_connections=10000
max_connections=5000

# Good to have (measure for a week, run tuner again)
thread_cache_size=1000 #it does not use that much
table_open_cache=10000 # set open file ulimit to at least this + 2x max_connections
innodb_buffer_pool_instances=64 #it parallelizes better with moo instances

Author

Commented:
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 .
Top Expert 2015
Commented:
Total buffers: 293.6G global + 32.9M per thread (10000 max threads)
Is it so hard to add two numbers?


temp tables on disk:
tmp_table_size (> 56M)
max_heap_table_size (> 56M)
Rise these two until happy.

Author

Commented:
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??
Top Expert 2015

Commented:
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?

Author

Commented:
we use linux centos6

Author

Commented:
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.
Top Expert 2015
Commented:
slave tables are never optimized

/var/tmp - /tmp - /dev/shm
temp tables on disk:
tmp_table_size (> 56M)
max_heap_table_size (> 56M)
Rise these two until happy.

Maybe reduce those 500GB RAM for 30GB database, something casual like 4GB should be sufficient...

Author

Commented:
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??
Top Expert 2015

Commented:
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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial