Mysql slowly uses memory until it starts to use swap

Question

I am running a 1gb of RAM rackspace database server. For some reason in about 2 days the memory usage goes from using very little swap, to using 100mb. If I don't restart sql it will keep using more swap. (My my.cnf file is shown below and memory usage shown below)

Some background: I have about 50 active databases that have the same schema that use INNODB for there tables. I have a couple databases with little traffic that use MyISAM.

On the INNODB tables I do NOT use persistent connections. I also have a reporting function that creates a temporary table. (This can be resource intensive, but does NOT happen often)

I am using CENTOS 6.3 and mysql 5.5.28-log

Even though I am using swap, the performance is still pretty good. I am just afraid that if I don't restart every few days I will have a problem.

Here is my log of free -m for about 2 days: (The first record is right after a mysql restart)

12/26 2:08 PM EST
             total       used       free     shared    buffers     cached
Mem:           992        697        295          0         74        362
-/+ buffers/cache:        260        732
Swap:          976         15        961

12/26 4:10 PM EST
[root@php-pos-db ~]# free -m
             total       used       free     shared    buffers     cached
Mem:           992        791        201          0         97        405
-/+ buffers/cache:        287        705
Swap:          976         14        961

12/27 2:52 PM EST
[root@php-pos-db ~]# free -m
             total       used       free     shared    buffers     cached
Mem:           992        947         45          0         55        169
-/+ buffers/cache:        722        270
Swap:          976         34        942

12/28 1:41 PM EST
             total       used       free     shared    buffers     cached
Mem:           992        963         29          0         45        119
-/+ buffers/cache:        797        195
Swap:          976         48        927

12/28 7:24 PM EST
[root@php-pos-db ~]# free -m
             total       used       free     shared    buffers     cached
Mem:           992        957         35          0         41        141
-/+ buffers/cache:        774        218
Swap:          976         90        886

12/28 8:33 PM EST
[root@php-pos-db ~]# free -m
             total       used       free     shared    buffers     cached
Mem:           992        948         44          0         48        130
-/+ buffers/cache:        768        224
Swap:          976         96        880

my.cnf

# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
# 
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html
#
# Take care to only add/remove/change a setting if you are comfortable
# doing so! For Rackspace customers, if you have any questions or
# concerns, please contact the MySQL Database Services Team. Be aware
# that some work performed by this team can involve additional billable
# fees.
#
# This file generated for host php-pos-db please modify
# variables if the server is resized from 1016636kB

[mysqld]

### General
user                = mysql
port                = 3306
datadir                         = /var/lib/mysql
tmpdir                          = /tmp
socket                          = /var/lib/mysql/mysql.sock
skip-external-locking           = 1
log_error                       = /var/log/mysqld.log

## This prevents using host-based authentication. That means users must be
## created using an ip-address (ie 'myuser'@'192.168.100.1') or must make
## use of the % wildcard (ie 'myuser'@'%'). The benefit to not using
## host-based authentication is that DNS will not impact MySQL performance.
#skip-name-resolve

## If open-files-limit is set very low, MySQL may increase on its own. Either
## way, increase this if MySQL gives 'too many open files' errors. Setting
## this above 65535 could be unwise (MySQL may crash).
open-files-limit                = 20000

### Cache
thread-cache-size               = 16
table-open-cache                = 4096
table-definition-cache          = 512

## Generally, it is unwise to set the query cache to be larger than 64-128M 
## as the costs associated with maintaining the cache outweigh the performance
## gains. A far superior solution would be to implement memcached, though this
## required modifying the application, among other things.
query-cache-type                = 1
query-cache-size                = 32M
query-cache-limit               = 1M

### Per-thread Buffers
sort-buffer-size                = 1M
read-buffer-size                = 1M
read-rnd-buffer-size            = 2M
join-buffer-size                = 1M

### Temp Tables
tmp-table-size                  = 64M 
max-heap-table-size             = 64M

### Networking
back-log                        = 100
max-connections                 = 50
max-connect-errors              = 10000
max-allowed-packet              = 16M
interactive-timeout             = 600
wait-timeout                    = 180
net_read_timeout        = 30
net_write_timeout       = 30
# This value is the size of the listen queue for incoming TCP/IP connections.
back_log            = 128

#### Storage Engines
## Set this to force MySQL to use a particular engine / table-type
## for new tables. This setting can still be overridden by specifying
## the engine explicitly in the CREATE TABLE statement.
default-storage-engine         = InnoDB

## Makes sure MySQL does not start if InnoDB fails to start. This helps
## prevent ugly silent failures.
innodb                          = FORCE

### MyISAM
## Not sure what to set this to?
## Try running a 'du -sch /var/lib/mysql/*/*.MYI'
## This will give you a good estimate on the size of all the MyISAM indexes.
## (The buffer may not need to set that high, however)
key-buffer-size                 = 2M
## This setting controls the size of the buffer that is allocated when 
## sorting MyISAM indexes during a REPAIR TABLE or when creating indexes 
## with CREATE INDEX or ALTER TABLE.
myisam-sort-buffer-size         = 2M

### InnoDB
## Note: While most settings in MySQL can be set at run-time, many InnoDB
## variables cannot be set at runtime as require restarting MySQL
###
## These settings control how much RAM InnoDB will use. Generally, when using
## mostly InnoDB tables, the innodb-buffer-pool-size should be as large as
## is possible without swapping or starving other processes of RAM. The other 
## two settings usually do not need to be changed, but can help for very large 
## datasets.
innodb-buffer-pool-size         = 285M
innodb-log-buffer-size          = 8M

## Be careful when changing these as they require re-generating the 
## ib-logfile* files, which must be done carefully. Do not change this unless 
## you are familiar with the procedure.
innodb-log-file-size           = 128M
innodb-log-files-in-group      = 2

## This will cause each table to create its own .ibd file
innodb-file-per-table           = 1

## Setting this to 2 will decrease disk I/O but can cause up to a second of
## queries to be lost during a hard outage (i.e. power failures)
# innodb-flush-log-at-trx-commit = 2

### Replication
## Set this to the Server's instance ID in replication environments
server-id                       = 1

#log-bin                        = /var/lib/mysql/bin-log
#relay-log                      = /var/lib/mysql/relay-log
#relay-log-space-limit          = 4G
#expire-logs-days               = 5

## This should be enabled on conventional MySQL slaves
#read-only                      = 1

## This will cause replicated statements on a slave to be written to the slave's binlog
## Enable this on the middle slave of M->S->S configs
#log-slave-updates              = 1

#binlog-format                  = STATEMENT

### Logging
## This option determines the destination for general query log and slow query log output.
## The option value can be given as one or more of the words TABLE, FILE, or NONE.
## NOTE: Table logging takes away 50% of performance and thus is not recommended
##       http://bugs.mysql.com/bug.php?id=30414
## In addition, you cannot backup the contents of these tables properly
## (mysqldump skips these tables by default since they cannot be locked)
#log-output                     = FILE
slow-query-log                 = 1
slow-query-log-file            = /var/lib/mysql/slow-log
long-query-time                = 2
log-queries-not-using-indexes  = 1

[mysqld-safe]
log-error                       = /var/log/mysqld.log

[mysqldump]
max-allowed-packet      = 16M

# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/sysconfig/mysqld-config/

Answer 1

MySQL does have the nasty habit being swap-happy. Jeremy Cole has best addressed this in his blog : http://blog.jcole.us/2012/04/16/a-brief-update-on-numa-and-mysql/. From that blog, you learn that there is something you can do: Add numactl --interleave=all inside /etc/init.d/mysql.

SUGGESTIONS

If the Server is dedicated to doing only MySQL, please change the following in /etc/my.cnf:

[mysqld]
innodb_open_files=1000
innodb_flush_method=O_DIRECT
innodb_buffer_pool_size=768M
innodb_log_file_size=192M

If the server is at least dual-core, add these

innodb_buffer_pool_instances=2
innodb_read_io_threads=16
innodb_write_io_threads=16
innodb_io_capacity=2000

Next, login to mysql run SET GLOBAL innodb_fast_shutdown = 0;

Next, run the following in the OS

cd /var/lib/mysql
service mysql stop
mv ib_logfile0 ib_logfile0.bak
mv ib_logfile1 ib_logfile1.bak
service mysql start

Give it a Try !!!

UPDATE 2012-12-31 08:30 EDT

From your last comment

It stopped climbing around 1 gb. I removed unused databases and it just seems mysql 5.5 stores a lot of data in memory as this didn't happen in 5.0. Did mysql change a lot?

Yes, MySQL has changed a lot. In fact, there are many cases where upgrading from MySQL 5.0 to MySQL 5.5 and resulted in performance degradation. InnoDB 5.5 is now equipped to do hyperthreading and multicore engagement.

Percona actually tested this out awhile ago.

Please read me past posts on this subject

• Jul 16, 2012 : decreased performance of stored procedure when migrated from mysql server 5.0 to 5.5
• Nov 24, 2011 : Why mysql 5.5 slower than 5.1 (linux,using mysqlslap)
• Oct 05, 2011 : Query runs a long time in some newer MySQL versions
• Sep 20, 2011 : Multi cores and MySQL Performance
• Jun 19, 2011 : How do I properly perform a MySQL bake-off?

I also wrote about this in ServerFault and StackOverflow

• Feb 22, 2012 : https://serverfault.com/questions/362388/mysql-5-1-vs-mysql-5-5-5-1-twice-as-fast/362706#362706
• Feb 08, 2012 : https://stackoverflow.com/questions/9202825/mysql-5-5-perfomance/9202860#9202860

Answer 2

Apart of the very good advice given by Rolando, you can, on the system side, activate a swap-less setting using sysctl. I usually set vm.swappiness=10 on MySQL machine in /etc/sysctl.conf. It gives restricted access to the swap, but permits it if required.

The default value of vm.swappiness is 60, which is very permissive.

Answer 3

Note: I posted this answer to a related question on stackoverflow. This solution is Linux and Systemd-specific, but in fact, can be adapted to any system that properly supports memlock calls and provides the capability to do so for processes that don't stay root.

Update: This solution might not, in fact, work that well. See note at end.

There exist a class of applications in which you never want them to swap. One such class is a database. Databases will use memory as caches and buffers for their disk areas, and it makes absolutely no sense that these are ever put to swap. The particular memory may hold some relevant data that is not needed for a week until one day when a client asks for it. Without the caching/swapping, the database would simply find the relevant record on disk, which would be quite fast; but with swapping, your service might suddenly be taking a long time to respond.

mysqld includes code to use the OS / system call memlock. On Linux, since at least 2.6.9, this system call will work for non-root processes that have the CAP_IPC_LOCK capability[1]. When using memlock(), the process must still work within the bounds of the LimitMEMLOCK limit. [2]. One of the (few) good things about systemd is that you can grant the mysqld process these capabilities, without requiring a special program. If can also set the rlimits as you'd expect with ulimit. Here is an override file for mysqld that does the requisite steps, including a few others that you might need for a process such as a database:

[Service]
# Prevent mysql from swapping
CapabilityBoundingSet=CAP_IPC_LOCK

# Let mysqld lock all memory to core (don't swap)
LimitMEMLOCK=-1 

# do not kills this process if low on memory
OOMScoreAdjust=-900 

# Use higher io scheduling
IOSchedulingClass=realtime    

Type=simple    
ExecStart=
ExecStart=/usr/sbin/mysqld --memlock $MYSQLD_OPTS

Note The standard community mysql currently ships with Type=forking and adds --daemonize in the option to the service on the ExecStart line. This is inherently less stable than the above method.

About Override files in systemd: You create a directory in /etc/systemd/system/ named mysqld.service.d and place the new file (with the above contents) into it.

UPDATE I am not 100% happy with this solution. After several days of runtime, I noticed the process still had enormous amounts of swap! Examining /proc/XXXX/smaps, I note the following:

• The largest contributor of swap is from a stack segment! At first this seemed not so bad, but after several days, it stood at 437 MB and fluctuating. This presents obvious performance issues. It also indicates stack-based memory leak.
• There are zero Locked pages. This indicates the memlock option in MySQL (or Linux) is broken. In this case, it wouldn't matter much because MySQL can't memlock stack.