Mysql 5.6 using high memory where cpu is low

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
CCVOIPAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kent OlsenDBACommented:
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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gheistCommented:
what does mysqltuner (from epel-release repo) say about your database?
0
CCVOIPAuthor 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

0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

CCVOIPAuthor 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

0
CCVOIPAuthor 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%
0
gheistCommented:
# 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
0
CCVOIPAuthor 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 .
0
gheistCommented:
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.
0
CCVOIPAuthor 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??
0
gheistCommented:
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?
0
CCVOIPAuthor Commented:
we use linux centos6
0
CCVOIPAuthor 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.
0
gheistCommented:
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...
0
CCVOIPAuthor 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??
0
gheistCommented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.