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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
what does mysqltuner (from epel-release repo) say about your database?
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)
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
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%
this keeps increasing ==> today is at 83.5%
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
why table_open_cache=10000
and how to set open file ulimit??
would decreasing the max_connection reduce high memory consumption?
also :
I noticed that
Thank you .
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
/tmpautomatically causing the temp tables to be created in on disk ?
I noticed that
ibdata1is huge up to
31Gon the slaves comparing to the master(
13G)==> why this difference??
Thank you .
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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??
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?
65535 files limit is enough , tell at lest you use bsd with capdb or linux or solaris?
ASKER
we use linux centos6
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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??
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.
[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.