MySQL high memory usage problem
Hello,
I would appreciate any help for optimization
I use VPS server with next configuration
Total processors: 10
Intel(R) Xeon(R) CPU E5620 @ 2.40GHz
Total memory: 2GB
mysql 5.1.56
with daily visitors round 8000.
Mysql works stable except I can see that it uses lot of memory
and sometimes causes memory used peeks up to 56%
Does anybody see in this my.cnf something that could cause issue or solve it
Thank You
This is my.cnf
[mysqld]
max_connections=200
max_user_connections=50
key_buffer_size=256M
myisam_sort_buffer_size=64M
join_buffer_size=2M
read_buffer_size=1M
sort_buffer_size=1M
table_open_cache=2500
thread_cache_size=128
interactive_timeout=45
wait_timeout=20
connect_timeout=8
max_allowed_packet=16M
max_connect_errors=10
query_cache_limit=1M
query_cache_size=64M
query_cache_type=1
#flush
#flush_time=60
table_definition_cache = 512
max_heap_table_size = 32M
tmp_table_size = 32M
[mysqld_safe]
open_files_limit=8192
[mysqldump]
max_allowed_packet=16M
[myisamchk]
key_buffer_size=64M
sort_buffer_size=64M
read_buffer_size=16M
write_buffer_size=16M
[mysqlhotcopy]
interactive-timeout
-
run mysqltuner.pl and post results 0 -
The following thread is a good place to start for how to best provide the information required to assist you: mysqlmymonlite.sh server stats gathering tool for cPanel Server Thank you. 0 -
Here are the results from mysqltuner Thank You -------- General Statistics ------------------------------------------------- - [--] Skipped version check for MySQLTuner script [OK] Currently running supported MySQL version 5.1.56 [OK] Operating on 32-bit architecture with less than 2GB RAM -------- Storage Engine Statistics ------------------------------------------ - [--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster [--] Data in MyISAM tables: 1G (Tables: 106) [!!] InnoDB is enabled but isn't being used [OK] Total fragmented tables: 0 -------- Performance Metrics ------------------------------------------------ [--] Up for: 1d 8h 43m 32s (472M q [4K qps], 116M conn, TX: 385B, RX: 80B) [--] Reads / Writes: 99% / 1% [--] Total buffers: 362.0M global + 4.4M per thread (200 max threads) [OK] Maximum possible memory usage: 1.2G (61% of installed RAM) [OK] Slow queries: 0% (6/472M) [OK] Highest usage of available connections: 25% (50/200) [OK] Key buffer size / total MyISAM indexes: 256.0M/281.6M [OK] Key buffer hit rate: 99.9% (437M cached / 512K reads) [OK] Query cache efficiency: 93.3% (113M cached / 121M selects) [!!] Query cache prunes per day: 1522159 [OK] Sorts requiring temporary tables: 0% (607 temp sorts / 1M sorts) [!!] Joins performed without indexes: 447757 [!!] Temporary tables created on disk: 37% (641K on disk / 1M total) [OK] Thread cache hit rate: 99% (54 created / 116M connections) [OK] Table cache hit rate: 83% (1K open / 2K opened) [OK] Open file limit used: 24% (1K/8K) [OK] Table locks acquired immediately: 99% (20M immediate / 20M locks) -------- Recommendations ---------------------------------------------------- General recommendations: Add skip-innodb to MySQL configuration to disable InnoDB Enable the slow query log to troubleshoot bad queries Adjust your join queries to always utilize indexes When making adjustments, make tmp_table_size/max_heap_table_size equal Reduce your SELECT DISTINCT queries without LIMIT clauses Variables to adjust: query_cache_size (> 64M) join_buffer_size (> 2.0M, or always use indexes with joins) tmp_table_size (> 32M) max_heap_table_size (> 32M)0 -
here [mysqld] max_connections=200 max_user_connections=50 key_buffer_size=325M myisam_sort_buffer_size=64M join_buffer_size=512K sort_buffer_size=256K table_open_cache=500 thread_cache_size=16 interactive_timeout=45 wait_timeout=20 connect_timeout=8 max_allowed_packet=16M max_connect_errors=10 query_cache_limit=1M query_cache_size=50M query_cache_type=1 max_heap_table_size = 32M tmp_table_size = 32M innodb_buffer_pool_size = 10M [mysqld_safe] open_files_limit=8192 [mysqldump] max_allowed_packet=16M [myisamchk] key_buffer_size=64M sort_buffer_size=64M read_buffer_size=16M write_buffer_size=16M [mysqlhotcopy] interactive-timeout what sites do you have there ? if something on CMSes, like wordpress, you can add caching plugin 0 -
Thank You, Type of site is CMS but fully custom, it is not open source As I implemented params and restarted mysql service, memory usage on server dropped from 32% to 22%. Here are new results from mysqltuner -------- General Statistics ------------------------------------------------- - [--] Skipped version check for MySQLTuner script [OK] Currently running supported MySQL version 5.1.56 [OK] Operating on 32-bit architecture with less than 2GB RAM -------- Storage Engine Statistics ------------------------------------------ - [--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster [--] Data in MyISAM tables: 1G (Tables: 106) [!!] InnoDB is enabled but isn't being used [!!] Total fragmented tables: 1 -------- Performance Metrics ------------------------------------------------ [--] Up for: 6m 51s (1M q [4K qps], 479K conn, TX: 1B, RX: 325M) [--] Reads / Writes: 98% / 2% [--] Total buffers: 419.0M global + 1.3M per thread (200 max threads) [OK] Maximum possible memory usage: 681.5M (33% of installed RAM) [OK] Slow queries: 0% (0/1M) [OK] Highest usage of available connections: 10% (20/200) [OK] Key buffer size / total MyISAM indexes: 325.0M/282.0M [OK] Key buffer hit rate: 99.7% (1M cached / 5K reads) [OK] Query cache efficiency: 93.2% (457K cached / 490K selects) [OK] Query cache prunes per day: 0 [OK] Sorts requiring temporary tables: 0% (26 temp sorts / 7K sorts) [!!] Joins performed without indexes: 1527 [!!] Temporary tables created on disk: 37% (2K on disk / 6K total) [OK] Thread cache hit rate: 99% (27 created / 479K connections) [OK] Table cache hit rate: 94% (131 open / 138 opened) [OK] Open file limit used: 2% (203/8K) [OK] Table locks acquired immediately: 100% (82K immediate / 82K locks) -------- Recommendations ---------------------------------------------------- - General recommendations: Add skip-innodb to MySQL configuration to disable InnoDB Run OPTIMIZE TABLE to defragment tables for better performance MySQL started within last 24 hours - recommendations may be inaccurate Enable the slow query log to troubleshoot bad queries Adjust your join queries to always utilize indexes When making adjustments, make tmp_table_size/max_heap_table_size equal Reduce your SELECT DISTINCT queries without LIMIT clauses Variables to adjust: join_buffer_size (> 512.0K, or always use indexes with joins) tmp_table_size (> 32M) max_heap_table_size (> 32M)0 -
Yeah, and max memory usage based on mysqltuner dropped from [OK] Maximum possible memory usage: 1.2G (61% of installed RAM) to [OK] Maximum possible memory usage: 681.5M (33% of installed RAM) The only thing you can do now, since this is custom CMS, [!!] Temporary tables created on disk: 37% (2K on disk / 6K total) you can review slow queries and optimize them 0 -
Thanks for great help and for next suggestions Greetings 0
Please sign in to leave a comment.
Comments
7 comments