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.