MySQL high memory usage problem

S

szk

Member
May 30, 2012
8
0
51
cPanel Access Level
Website Owner
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

Code: 
[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
 
S

szk

Member
May 30, 2012
8
0
51
cPanel Access Level
Website Owner
Here are the results from mysqltuner
Thank You

Code: 
-------- 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)
 
T

thinkbot

Well-Known Member
Oct 30, 2012
326
1
16
cPanel Access Level
Root Administrator
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
 
S

szk

Member
May 30, 2012
8
0
51
cPanel Access Level
Website Owner
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

Code: 
-------- 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)
 
T

thinkbot

Well-Known Member
Oct 30, 2012
326
1
16
cPanel Access Level
Root Administrator
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
 
You must log in or register to reply here.
Thread starter Similar threads Forum Replies Date
R Mysqld - high CPU usage Workarounds and Optimization 4
T High MySQL usage - how to troubleshoot? Workarounds and Optimization 2
L mysql using high memory Workarounds and Optimization 7
C mysqld CPU and memory Usage too high- 867% Workarounds and Optimization 4
N High CPU usage in CPU/Memory/MySQL Usage by [php5] Workarounds and Optimization 1
Similar threads
Mysqld - high CPU usage
High MySQL usage - how to troubleshoot?
mysql using high memory
mysqld CPU and memory Usage too high- 867%
High CPU usage in CPU/Memory/MySQL Usage by [php5]
Top Bottom