I have a MySQL performance issue with a brand new server, and am somewhat of a beginner as a database administrator (I'm mostly a general-purpose Linux sysadmin). I'm looking for pointers where to start troubleshooting and how to pinpoint the problem. So far, I found some scripts that seem to identify common performance issue, specifically the tuning-primer.sh script described here: http://www.howtogeek.com/howto/linux/using-a-mysql-performance-tuning-analyzer-script/
The application is a widely used third-party application (Moodle), so the problem is likely related to tuning rather than to optimizing queries.
I migrated a system from an older server to a new server. The older server was generally running well and smoothly, but aging. Ever since the migration, we are having performance issues.
The problems appear to be centered around write operations - reads seem to perform well after some tuning I already did. I suspect that the affected writes are to joined tables.
Details: both servers have roughly the same hardware specs, 8 GB of RAM, decent CPU (quad core, not sure which exact one). We did upgrade the operating system, as well as all the software versions.
Old server: FreeBSD 9.3, Apache 2.2, Moodle 2.8, MySQL 5.4 (correction: 5.5.41). New server: CentOS 7, Apache 2.4, Moodle 2.9, MySQL 5.6.
To do the migration, I used mysqldump, then restored it using mysql. Moodle made some changes to the database schema from the 2.8 to the 2.9 version.
On the new server, I wrapped both the application and the database into docker containers (using the official MySQL 5.6 container image as the base).
Interestingly, the old server ran with no my.cnf at all - whoever set it up originally seems to not paid any attention to tuning, yet it performed well right out of the box.
The new server has a minimal my.cnf - actually, three parts: the my.cnf from the official MySQL image, and two configuration files that I added.
My own configuration files:
[mysqld] character-set-server=utf8 collation-server=utf8_unicode_ci innodb_file_format=Barracuda
(the Barracuda file format and the character set settings are requirements for Moodle)
[mysqld] join_buffer_size = 6M
(based on a tuning-primer.sh suggestion. Performance did improve, but some issues remain).
The official MySQL image my.cnf:
# Copyright (c) 2015, Oracle and/or its affiliates. All rights reserved. # # This program is free software; you can redistribute it and/or modify # it under the terms of the GNU General Public License as published by # the Free Software Foundation; version 2 of the License. # # This program is distributed in the hope that it will be useful, # but WITHOUT ANY WARRANTY; without even the implied warranty of # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the # GNU General Public License for more details. # # You should have received a copy of the GNU General Public License # along with this program; if not, write to the Free Software # Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA # # The MySQL Community Server configuration file. # # For explanations see # http://dev.mysql.com/doc/mysql/en/server-system-variables.html [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] skip-host-cache skip-name-resolve user = mysql pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port = 3306 basedir = /usr datadir = /var/lib/mysql 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 = 127.0.0.1 #log-error = /var/log/mysql/error.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/