0

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)

and

[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/
  • 1
    Enable the slow query log (if some tuner script suggested any changes to the join_buffer_size then probably you have some nonindexed joins and other problems), add(pastebin) results of show global status; and show global variables;. Are the tables MyISAM or InnoDB? If InnoDB then you need to set some 40-60% of RAM to innodb_buffer_pool_size (usually is 70-80% of available memory - after deducing what has to be used for Apache and other services). – jkavalik Jan 21 '16 at 7:58
  • 1
    should really assume that with a properly tuned server, the queries will be performant - never assume that, there are lot of "widely used" systems (cough wordpress cough) which stop being usable when you put nontrivial amount of data in them. When you have some worst queries, you can look at them and decide, often just the right index can do miracles, but sometimes not. You may even ask for suggestions about specific queries on this site. – jkavalik Jan 21 '16 at 10:59
  • 1
    "Old server: MySQL 5.4."? Probably not. Please check again what version of mysql the old server runs. – ypercubeᵀᴹ Jan 21 '16 at 11:01
  • 1
    If you can find a particular query that runs a bunch slower, let's stare at that. – Rick James Jan 22 '16 at 23:26
  • 1
    Thanks for the humor ('assume ... performant'), I needed a chuckle today. – Rick James Jan 22 '16 at 23:28

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.