I've got a server running a LAMP stack with 1GB RAM that is maxed out on RAM usage. What I'm observing is that directly after a reboot, it drops to 25% usage, and then slowly climbs over time so that by the end of the day, it's sitting at 100%. When it peaks out, mysql is using 940MB according to TOP.
I've tried lowering many of the settings to reduce caching and buffer sizes (not ideal I know, but just trying to get the memory usage under control first – then things can be redialed in)
Current /etc/my.cnf:
[mysqld]
# Basic settings
user = mysql
datadir = /var/lib/mysql
port = 3306
socket = /var/lib/mysql/mysql.sock
# Security settings
local-infile = 0
symbolic-links = 0
# Memory and cache settings
query_cache_type = 1
query_cache_size = 4M
thread_cache_size = 4
table_cache = 256
tmp_table_size = 8M
max_heap_table_size = 8M
join_buffer_size = 1M
key_buffer_size = 1M
max_connections = 10
wait_timeout = 300
# Innodb settings
innodb_buffer_pool_size = 16M
innodb_additional_mem_pool_size = 1M
innodb_log_buffer_size = 1M
innodb_thread_concurrency = 2
[mysqld_safe]
# Basic safe settings
log-error = /var/log/mysqld.log
pid-file = /var/run/mysqld/mysqld.pid
MySQLTuner.pl Output:
# ./mysqltuner.pl
>> MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.30
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 51K (Tables: 109)
[--] Data in InnoDB tables: 15M (Tables: 387)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 387
-------- Security Recommendations -------------------------------------------
[OK] All database users have passwords assigned
-------- Performance Metrics -------------------------------------------------
[--] Up for: 2d 15h 14m 29s (1M q [4.411 qps], 71K conn, TX: 2B, RX: 78M)
[--] Reads / Writes: 62% / 38%
[--] Total buffers: 31.0M global + 3.6M per thread (10 max threads)
[OK] Maximum possible memory usage: 67.2M (6% of installed RAM)
[OK] Slow queries: 0% (0/1M)
[OK] Highest usage of available connections: 80% (8/10)
[OK] Key buffer size / total MyISAM indexes: 1.0M/450.0K
[!!] Key buffer hit rate: 90.7% (935 cached / 87 reads)
[OK] Query cache efficiency: 82.7% (201K cached / 243K selects)
[!!] Query cache prunes per day: 11934
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 15K sorts)
[OK] Temporary tables created on disk: 9% (17K on disk / 194K total)
[OK] Thread cache hit rate: 99% (20 created / 71K connections)
[!!] Table cache hit rate: 6% (256 open / 3K opened)
[OK] Open file limit used: 9% (102/1K)
[OK] Table locks acquired immediately: 100% (81K immediate / 81K locks)
[OK] InnoDB data size / buffer pool: 15.7M/16.0M
-------- Recommendations -----------------------------------------------------
General recommendations:
> Run OPTIMIZE TABLE to defragment tables for better performance
> Enable the slow query log to troubleshoot bad queries
> Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
> query_cache_size (> 4M)
> table_cache (> 256)
What I don't understand is that MySQLTuner says "Maximum possible memory usage: 67.2M (6% of installed RAM)", yet as indicated earlier, MySQL is using 940MB of RAM.
Any ideas on how to resolve this?
** EDIT **
If I stop the mysql process, memory usage dips a little bit but is still pretty maxed out.
Before:
# free -m
total used free shared buffers cached
Mem: 1024 1019 4 0 0 3
-/+ buffers/cache: 1016 7
Swap: 1024 79 944
After:
# /etc/init.d/mysqld stop
# free -m
total used free shared buffers cached
Mem: 1024 998 25 0 0 4
-/+ buffers/cache: 993 30
Swap: 1024 53 970
** EDIT **
I tried stopping processes in order of what's taking up the most memory and it seems like something just isn't releasing. Here's what I did:
# cat /proc/meminfo
MemTotal: 1048576 kB
MemFree: 5164 kB
Cached: 14128 kB
Active: 37104 kB
Inactive: 53436 kB
Active(anon): 29876 kB
Inactive(anon): 46536 kB
Active(file): 7228 kB
Inactive(file): 6900 kB
Unevictable: 0 kB
Mlocked: 0 kB
SwapTotal: 1048576 kB
SwapFree: 968756 kB
Dirty: 12 kB
Writeback: 0 kB
AnonPages: 76412 kB
Shmem: 4776 kB
Slab: 952804 kB
SReclaimable: 941112 kB
SUnreclaim: 11692 kB
#top -a
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
1240 mysql 20 0 872m 33m 7476 S 0.7 3.3 22:59.35 mysqld
12662 root 20 0 234m 26m 3136 S 0.0 2.6 0:04.72 spamd
12663 popuser 20 0 234m 24m 964 S 0.0 2.4 0:00.02 spamd
15299 site1 20 0 285m 14m 8816 S 2.7 1.4 0:00.28 php-cgi
15302 site2 20 0 285m 12m 7800 S 0.0 1.3 0:00.10 php-cgi
782 root 20 0 436m 11m 10m S 0.0 1.1 0:10.66 httpd
15277 root 20 0 102m 4928 3828 S 0.0 0.5 0:00.05 sshd
14342 apache 20 0 436m 3924 2048 S 0.0 0.4 0:03.31 httpd
14272 apache 20 0 436m 3920 2048 S 0.0 0.4 0:03.79 httpd
793 nginx 20 0 46432 2568 1684 S 0.3 0.2 5:38.31 nginx
14830 postfix 20 0 51716 2332 1772 S 0.0 0.2 0:00.00 pickup
15281 root 20 0 105m 2064 1532 S 0.0 0.2 0:00.02 bash
754 postfix 20 0 51916 2028 1932 S 0.0 0.2 0:35.17 qmgr
3977 postfix 20 0 51716 1992 1896 S 0.0 0.2 0:00.13 tlsmgr
743 root 20 0 51656 1936 1836 S 0.0 0.2 0:43.71 master
589 sw-cp-se 20 0 61704 1784 1780 S 0.0 0.2 0:01.19 sw-cp-serverd
21335 apache 20 0 318m 1356 612 S 0.0 0.1 0:20.91 httpd
757 postfix 20 0 394m 1340 1236 S 0.0 0.1 0:05.20 psa-pc-remote
476 root 20 0 179m 1316 1056 S 0.0 0.1 0:00.38 rsyslogd
1 root 20 0 19232 1256 1148 S 0.0 0.1 0:00.03 init
15301 root 20 0 14896 1248 1008 R 0.0 0.1 0:00.01 top
837 root 20 0 11304 756 752 S 0.0 0.1 0:00.03 mysqld_safe
1408 root 20 0 114m 728 644 S 0.0 0.1 0:01.17 crond
608 root 20 0 22096 696 692 S 0.0 0.1 0:00.00 xinetd
600 root 20 0 64120 612 508 S 0.0 0.1 0:00.06 sshd
634 root 20 0 13972 560 556 S 0.0 0.1 0:00.00 couriertcpd
644 root 20 0 13972 560 556 S 0.0 0.1 0:00.00 couriertcpd
652 root 20 0 13972 560 556 S 0.0 0.1 0:00.12 couriertcpd
661 root 20 0 13972 560 556 S 0.0 0.1 0:00.00 couriertcpd
***killed mysql***
# cat /proc/meminfo
MemTotal: 1048576 kB
MemFree: 35296 kB
Cached: 13820 kB
Active: 24436 kB
Inactive: 36712 kB
Active(anon): 16984 kB
Inactive(anon): 30344 kB
Active(file): 7452 kB
Inactive(file): 6368 kB
Unevictable: 0 kB
Mlocked: 0 kB
SwapTotal: 1048576 kB
SwapFree: 1001512 kB
Dirty: 16 kB
Writeback: 0 kB
AnonPages: 47328 kB
Shmem: 4776 kB
Slab: 952068 kB
SReclaimable: 941180 kB
SUnreclaim: 10888 kB
#top -a
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
12662 root 20 0 234m 25m 3136 S 0.0 2.5 0:04.73 spamd
12663 popuser 20 0 234m 23m 964 S 0.0 2.3 0:00.02 spamd
15322 site1 20 0 285m 14m 8804 S 0.0 1.4 0:00.23 php-cgi
15324 site2 20 0 285m 12m 7800 S 0.0 1.3 0:00.10 php-cgi
782 root 20 0 436m 11m 10m S 0.0 1.1 0:10.66 httpd
15277 root 20 0 102m 4916 3828 S 0.0 0.5 0:00.05 sshd
14342 apache 20 0 436m 3924 2048 S 0.0 0.4 0:03.33 httpd
14272 apache 20 0 436m 3920 2048 S 0.0 0.4 0:03.82 httpd
793 nginx 20 0 46432 2568 1684 S 0.0 0.2 5:38.32 nginx
14830 postfix 20 0 51716 2332 1772 S 0.0 0.2 0:00.00 pickup
754 postfix 20 0 51916 2028 1932 S 0.0 0.2 0:35.19 qmgr
3977 postfix 20 0 51716 1992 1896 S 0.0 0.2 0:00.13 tlsmgr
15281 root 20 0 105m 1948 1552 S 0.0 0.2 0:00.02 bash
743 root 20 0 51656 1936 1836 S 0.0 0.2 0:43.72 master
589 sw-cp-se 20 0 61704 1784 1780 S 0.0 0.2 0:01.19 sw-cp-serverd
21335 apache 20 0 318m 1356 612 S 0.0 0.1 0:20.92 httpd
757 postfix 20 0 394m 1340 1236 S 0.0 0.1 0:05.20 psa-pc-remote
476 root 20 0 179m 1316 1056 S 0.0 0.1 0:00.38 rsyslogd
1 root 20 0 19232 1256 1148 S 0.0 0.1 0:00.03 init
15362 root 20 0 14896 1244 1008 R 0.0 0.1 0:00.00 top
1408 root 20 0 114m 728 644 S 0.0 0.1 0:01.17 crond
608 root 20 0 22096 696 692 S 0.0 0.1 0:00.00 xinetd
600 root 20 0 64120 612 508 S 0.0 0.1 0:00.06 sshd
634 root 20 0 13972 560 556 S 0.0 0.1 0:00.00 couriertcpd
644 root 20 0 13972 560 556 S 0.0 0.1 0:00.00 couriertcpd
652 root 20 0 13972 560 556 S 0.0 0.1 0:00.12 couriertcpd
661 root 20 0 13972 560 556 S 0.0 0.1 0:00.00 couriertcpd
***killed httpd ***
# cat /proc/meminfo
MemTotal: 1048576 kB
MemFree: 58476 kB
Cached: 13932 kB
Active: 11752 kB
Inactive: 30812 kB
Active(anon): 4180 kB
Inactive(anon): 24452 kB
Active(file): 7572 kB
Inactive(file): 6360 kB
Unevictable: 0 kB
Mlocked: 0 kB
SwapTotal: 1048576 kB
SwapFree: 1013752 kB
Dirty: 24 kB
Writeback: 0 kB
AnonPages: 28632 kB
Shmem: 2568 kB
Slab: 947400 kB
SReclaimable: 941172 kB
SUnreclaim: 6228 kB
#top -a
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
12662 root 20 0 234m 25m 3136 S 0.0 2.5 0:04.73 spamd
12663 popuser 20 0 234m 23m 964 S 0.0 2.3 0:00.02 spamd
15277 root 20 0 102m 4916 3828 S 0.0 0.5 0:00.06 sshd
793 nginx 20 0 46432 2580 1688 S 0.0 0.2 5:38.35 nginx
14830 postfix 20 0 51716 2332 1772 S 0.0 0.2 0:00.00 pickup
15281 root 20 0 105m 2060 1552 S 0.0 0.2 0:00.03 bash
754 postfix 20 0 51916 2028 1932 S 0.0 0.2 0:35.19 qmgr
3977 postfix 20 0 51716 1992 1896 S 0.0 0.2 0:00.13 tlsmgr
743 root 20 0 51656 1936 1836 S 0.0 0.2 0:43.72 master
589 sw-cp-se 20 0 61704 1784 1780 S 0.0 0.2 0:01.19 sw-cp-serverd
757 postfix 20 0 394m 1340 1236 S 0.0 0.1 0:05.20 psa-pc-remote
476 root 20 0 179m 1316 1056 S 0.0 0.1 0:00.38 rsyslogd
1 root 20 0 19232 1256 1148 S 0.0 0.1 0:00.03 init
15380 root 20 0 14892 1120 900 R 0.0 0.1 0:00.00 top
1408 root 20 0 114m 728 644 S 0.0 0.1 0:01.17 crond
608 root 20 0 22096 696 692 S 0.0 0.1 0:00.00 xinetd
600 root 20 0 64120 612 508 S 0.0 0.1 0:00.06 sshd
634 root 20 0 13972 560 556 S 0.0 0.1 0:00.00 couriertcpd
644 root 20 0 13972 560 556 S 0.0 0.1 0:00.00 couriertcpd
652 root 20 0 13972 560 556 S 0.0 0.1 0:00.12 couriertcpd
661 root 20 0 13972 560 556 S 0.0 0.1 0:00.00 couriertcpd
***killed spamd***
# cat /proc/meminfo
MemTotal: 1048576 kB
MemFree: 83548 kB
Cached: 13948 kB
Active: 10668 kB
Inactive: 7720 kB
Active(anon): 3068 kB
Inactive(anon): 1372 kB
Active(file): 7600 kB
Inactive(file): 6348 kB
Unevictable: 0 kB
Mlocked: 0 kB
SwapTotal: 1048576 kB
SwapFree: 1039244 kB
Dirty: 8 kB
Writeback: 0 kB
AnonPages: 4440 kB
Shmem: 2568 kB
Slab: 946580 kB
SReclaimable: 941176 kB
SUnreclaim: 5404 kB
#top -a
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
15277 root 20 0 102m 4916 3828 S 0.0 0.5 0:00.08 sshd
793 nginx 20 0 46432 2580 1688 S 0.0 0.2 5:38.36 nginx
14830 postfix 20 0 51716 2332 1772 S 0.0 0.2 0:00.00 pickup
15281 root 20 0 105m 2064 1552 S 0.0 0.2 0:00.04 bash
754 postfix 20 0 51916 2028 1932 S 0.0 0.2 0:35.19 qmgr
3977 postfix 20 0 51716 1992 1896 S 0.0 0.2 0:00.13 tlsmgr
743 root 20 0 51656 1936 1836 S 0.0 0.2 0:43.73 master
589 sw-cp-se 20 0 61704 1784 1780 S 0.0 0.2 0:01.19 sw-cp-serverd
757 postfix 20 0 394m 1340 1236 S 0.0 0.1 0:05.20 psa-pc-remote
476 root 20 0 179m 1316 1056 S 0.0 0.1 0:00.38 rsyslogd
1 root 20 0 19232 1256 1148 S 0.0 0.1 0:00.03 init
15383 root 20 0 14892 1120 900 R 0.0 0.1 0:00.00 top
1408 root 20 0 114m 728 644 S 0.0 0.1 0:01.17 crond
608 root 20 0 22096 696 692 S 0.0 0.1 0:00.00 xinetd
600 root 20 0 64120 612 508 S 0.0 0.1 0:00.06 sshd
634 root 20 0 13972 560 556 S 0.0 0.1 0:00.00 couriertcpd
644 root 20 0 13972 560 556 S 0.0 0.1 0:00.00 couriertcpd
652 root 20 0 13972 560 556 S 0.0 0.1 0:00.12 couriertcpd
661 root 20 0 13972 560 556 S 0.0 0.1 0:00.00 couriertcpd
***killed nginx***
# cat /proc/meminfo
MemTotal: 1048576 kB
MemFree: 84848 kB
Cached: 14000 kB
Active: 10100 kB
Inactive: 7396 kB
Active(anon): 2460 kB
Inactive(anon): 1036 kB
Active(file): 7640 kB
Inactive(file): 6360 kB
Unevictable: 0 kB
Mlocked: 0 kB
SwapTotal: 1048576 kB
SwapFree: 1041180 kB
Dirty: 4 kB
Writeback: 0 kB
AnonPages: 3496 kB
Shmem: 2564 kB
Slab: 946196 kB
SReclaimable: 941164 kB
SUnreclaim: 5032 kB
#top -a
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
15277 root 20 0 102m 4916 3828 S 0.0 0.5 0:00.09 sshd
14830 postfix 20 0 51716 2332 1772 S 0.0 0.2 0:00.00 pickup
15281 root 20 0 105m 2064 1552 S 0.0 0.2 0:00.04 bash
754 postfix 20 0 51916 2028 1932 S 0.0 0.2 0:35.20 qmgr
3977 postfix 20 0 51716 1992 1896 S 0.0 0.2 0:00.13 tlsmgr
743 root 20 0 51656 1936 1836 S 0.0 0.2 0:43.74 master
589 sw-cp-se 20 0 61704 1784 1780 S 0.0 0.2 0:01.19 sw-cp-serverd
757 postfix 20 0 394m 1340 1236 S 0.0 0.1 0:05.20 psa-pc-remote
476 root 20 0 179m 1316 1056 S 0.0 0.1 0:00.38 rsyslogd
1 root 20 0 19232 1256 1148 S 0.0 0.1 0:00.03 init
15392 root 20 0 14892 1120 900 R 0.0 0.1 0:00.00 top
1408 root 20 0 114m 728 644 S 0.0 0.1 0:01.17 crond
608 root 20 0 22096 696 692 S 0.0 0.1 0:00.00 xinetd
600 root 20 0 64120 612 508 S 0.0 0.1 0:00.06 sshd
634 root 20 0 13972 560 556 S 0.0 0.1 0:00.00 couriertcpd
644 root 20 0 13972 560 556 S 0.0 0.1 0:00.00 couriertcpd
652 root 20 0 13972 560 556 S 0.0 0.1 0:00.12 couriertcpd
***killed postfix***
# cat /proc/meminfo
MemTotal: 1048576 kB
MemFree: 86168 kB
Cached: 14524 kB
Active: 9448 kB
Inactive: 7624 kB
Active(anon): 1628 kB
Inactive(anon): 920 kB
Active(file): 7820 kB
Inactive(file): 6704 kB
Unevictable: 0 kB
Mlocked: 0 kB
SwapTotal: 1048576 kB
SwapFree: 1042612 kB
Dirty: 4 kB
Writeback: 0 kB
AnonPages: 2548 kB
Shmem: 2564 kB
Slab: 945276 kB
SReclaimable: 941152 kB
SUnreclaim: 4124 kB
#top -a
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
15277 root 20 0 102m 4916 3828 S 0.0 0.5 0:00.09 sshd
15281 root 20 0 105m 2064 1552 S 0.0 0.2 0:00.06 bash
589 sw-cp-se 20 0 61704 1784 1780 S 0.0 0.2 0:01.19 sw-cp-serverd
757 postfix 20 0 394m 1340 1236 S 0.0 0.1 0:05.20 psa-pc-remote
476 root 20 0 179m 1316 1056 S 0.0 0.1 0:00.38 rsyslogd
1 root 20 0 19232 1256 1148 S 0.0 0.1 0:00.03 init
15409 root 20 0 14892 1112 900 R 0.0 0.1 0:00.00 top
1408 root 20 0 114m 728 644 S 0.0 0.1 0:01.17 crond
608 root 20 0 22096 696 692 S 0.0 0.1 0:00.00 xinetd
600 root 20 0 64120 612 508 S 0.0 0.1 0:00.06 sshd
634 root 20 0 13972 560 556 S 0.0 0.1 0:00.00 couriertcpd
644 root 20 0 13972 560 556 S 0.0 0.1 0:00.00 couriertcpd
652 root 20 0 13972 560 556 S 0.0 0.1 0:00.12 couriertcpd
661 root 20 0 13972 560 556 S 0.0 0.1 0:00.00 couriertcpd
***killed courier***
# cat /proc/meminfo
MemTotal: 1048576 kB
MemFree: 87032 kB
Cached: 14504 kB
Active: 9456 kB
Inactive: 7564 kB
Active(anon): 1596 kB
Inactive(anon): 920 kB
Active(file): 7860 kB
Inactive(file): 6644 kB
Unevictable: 0 kB
Mlocked: 0 kB
SwapTotal: 1048576 kB
SwapFree: 1043556 kB
Dirty: 0 kB
Writeback: 0 kB
AnonPages: 2516 kB
Shmem: 2564 kB
Slab: 944472 kB
SReclaimable: 941128 kB
SUnreclaim: 3344 kB
#top -a
top - 10:06:59 up 4 days, 11 min, 1 user, load average: 0.00, 0.00, 0.00
Tasks: 17 total, 1 running, 16 sleeping, 0 stopped, 0 zombie
Cpu(s): 0.0%us, 0.0%sy, 0.0%ni,100.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 1048576k total, 961660k used, 86916k free, 0k buffers
Swap: 1048576k total, 5020k used, 1043556k free, 14488k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
15277 root 20 0 102m 4916 3828 S 0.0 0.5 0:00.10 sshd
15281 root 20 0 105m 2064 1552 S 0.0 0.2 0:00.06 bash
589 sw-cp-se 20 0 61704 1784 1780 S 0.0 0.2 0:01.19 sw-cp-serverd
757 postfix 20 0 394m 1340 1236 S 0.0 0.1 0:05.21 psa-pc-remote
476 root 20 0 179m 1316 1056 S 0.0 0.1 0:00.38 rsyslogd
1 root 20 0 19232 1256 1148 S 0.0 0.1 0:00.03 init
15436 root 20 0 14896 1220 1008 R 0.0 0.1 0:00.00 top
1408 root 20 0 114m 728 644 S 0.0 0.1 0:01.17 crond
608 root 20 0 22096 696 692 S 0.0 0.1 0:00.00 xinetd
600 root 20 0 64120 612 508 S 0.0 0.1 0:00.06 sshd
1462 root 20 0 4064 508 504 S 0.0 0.0 0:00.00 mingetty
1463 root 20 0 4064 508 504 S 0.0 0.0 0:00.00 mingetty
616 root 20 0 64308 312 308 S 0.0 0.0 0:00.00 saslauthd
134 root 16 -4 10640 296 292 S 0.0 0.0 0:00.00 udevd
617 root 20 0 64308 48 44 S 0.0 0.0 0:00.00 saslauthd
2 root 20 0 0 0 0 S 0.0 0.0 0:00.00 kthreadd/106004
3 root 20 0 0 0 0 S 0.0 0.0 0:00.00 khelper/106004
Best Answer
The formulas that tuning scripts like mysqltuner use to estimate potential memory usage are bunk. These posts explain why and give some examples of things that could be causing the memory usage pattern you're seeing:
How much memory can MySQL use in the worst case?
MySQL Server Memory Usage
This post talks about isolating the problem by running tests:
Troubleshooting MySQL Memory Usage
The simplest explanation is that there is a query with large memory overhead, which should be identifiable by running the query in isolation and observing its impact on memory usage.
As the post says, memory usage issues are one of the more annoying problems to have in MySQL, and the lack of instrumentation features that other RDBMS have really shows here and makes this type of problem difficult to diagnose.