Avatar of CCVOIP
CCVOIP
 asked on

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
MySQL ServerLinux

Avatar of undefined
Last Comment
gheist

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Kent Olsen

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
gheist

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

ASKER
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

CCVOIP

ASKER
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

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
CCVOIP

ASKER
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%
SOLUTION
gheist

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
CCVOIP

ASKER
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 .
SOLUTION
gheist

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
CCVOIP

ASKER
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??
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
gheist

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?
CCVOIP

ASKER
we use linux centos6
CCVOIP

ASKER
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.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
SOLUTION
gheist

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
CCVOIP

ASKER
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??
gheist

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.