0

Versions in this post: MySQL 5.6.21 Community Server, Linode 2048, Debian 7.6 plain vanilla install from Linode

I used to run a 1024 Linode i.e. 1GB ram for my MySQL instance that serves a fairly busy website. When I recently, this weekend, made an upgrade to the website I took the opportunity to install MySQL on a 2048 Linode instead.

The major architectural change made is that I now use only InnoDb tables instead of MyISAM that we used before.

Also, the 1024 linode I used before had 8 CPUs and now there's only 2, as I'm sure you are all aware.

So, before - in the busy hours - I could see cpu spikes but there was never a memory problem. Ever.

Now, I just had a auto-notification mail saying how my Linode instance had started using the filesystem inordinately much and I logged on and saw that MySQL was using 86% of the memory (which I guess could be OK since InnoDb is configured to use 1400MB for the buffer pool). However, it's using max swap and the instance is really struggling to handle the load, which is not really near what peak hour will look like.

Any thoughts? Any help at all would be much appreciated.

my.cnf looks like so

[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock

[mysqld_safe]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

[mysqld]
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir        = /usr
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
explicit_defaults_for_timestamp

# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address    = my_machine_name

log-error       = /var/log/mysql/error.log

# MyISAM #
key-buffer-size                = 32M
myisam-recover                 = FORCE,BACKUP

# SAFETY #
max-allowed-packet             = 16M
max-connect-errors             = 1000000

# DATA STORAGE #
datadir                        = /var/lib/mysql/

# BINARY LOGGING #
log-bin                        = /var/lib/mysql/mysql-bin
expire-logs-days               = 14
sync-binlog                    = 1

# CACHES AND LIMITS #
tmp-table-size                 = 32M
max-heap-table-size            = 32M
query-cache-type               = 0
query-cache-size               = 0
max-connections                = 500
thread-cache-size              = 50
open-files-limit               = 65535
table-definition-cache         = 1024
table-open-cache               = 2048

# INNODB #
innodb-flush-method            = O_DIRECT
innodb-log-files-in-group      = 2
innodb-log-file-size           = 128M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 1456M

# LOGGING #
log-error                      = /var/lib/mysql/mysql-error.log
log-queries-not-using-indexes  = 0
slow-query-log                 = 2
slow-query-log-file            = /var/lib/mysql/mysql-slow.log

# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

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

Yes, it's a percona tool standard file. I really have very little knowledge of what does what in MySQL. The change to InnoDb was made to avaoid table locks and to actually be able to NOT have to upgrade the machine too soon.

Thanks for any help at all.

2
  • Is the server doing anything other than running MySQL? (i.e. running the web server, too?) – Michael - sqlbot Dec 1 '14 at 22:50
  • Nope. My contention is, after looking into this more closely, is that my Innodb buffer pool size need to be => the data size, which is about 1.9 GB and thus I needed a bigger server. I'm now running with innodb-buffer-pool-size set to 2.4GB and mysql has stopped eating memory at about 1.94GB of the pool so I take it it's something anyone who uses InnoDb should consider. – Adergaard Dec 2 '14 at 7:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.