View All Products
Compute
Storage
Networking
Databases
Services
Developer Tools
Industries
Pricing
Community
Engage With Us
Community ▶ Questions ▶ Trying to optimize MySQL server
Log in to Ask a Question

Trying to optimize MySQL server

performance

So long story short, my 8GB Plan Linode server has been having issues with the server crashing on weekly basis, and requiring a manual reboot. I made a ticket with support and went through the basic steps to identify the problem, and they recommended using mysqltuner to fine tune the database server.

I copied a few files that I noticed should help. Let me know if there's anything else I should look for.

Here's the output from mysqltuner, followed by an output of my /etc/mysql/my.cnf file, and then my query_cache parameters..

[OK] Logged in using credentials from debian maintenance account.
 >>  MySQLTuner 1.6.4 - Major Hayden <major@mhtx.net>>>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.73-0ubuntu0.10.04.1
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
[--] Data in MyISAM tables: 4G (Tables: 325)
[--] Data in InnoDB tables: 29M (Tables: 71)
[!!] Total fragmented tables: 117

-------- Security Recommendations  -------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[!!] User 'wp_admin@%' hasn't specific host restriction.
[!!] There is no basic password file list!

-------- CVE Security Recommendations  ---------------------------------------
[--] Skipped due to --cvefile option undefined

-------- Performance Metrics -------------------------------------------------
[--] Up for: 2d 12h 29m 6s (6M q [31.379 qps], 125K conn, TX: 443B, RX: 1B)
[--] Reads / Writes: 98% / 2%
[--] Binary logging is disabled
[--] Total buffers: 186.0M global + 96.2M per thread (35 max threads)
[OK] Maximum reached memory usage: 3.0G (38.35% of installed RAM)
[OK] Maximum possible memory usage: 3.5G (44.35% of installed RAM)
[OK] Slow queries: 0% (40/6M)
[OK] Highest usage of available connections: 85% (30/35)
[OK] Aborted connections: 0.00%  (4/125283)
[OK] Query cache efficiency: 69.9% (4M cached / 6M selects)
[!!] Query cache prunes per day: 89346
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 1M sorts)
[!!] Temporary tables created on disk: 49% (199K on disk / 400K total)
[OK] Thread cache hit rate: 99% (778 created / 125K connections)
[!!] Table cache hit rate: 0% (32 open / 20K opened)
[OK] Open file limit used: 4% (51/1K)
[OK] Table locks acquired immediately: 99% (2M immediate / 2M locks)

-------- MyISAM Metrics ------------------------------------------------------
[OK] Key buffer used: 100.0% (33M used / 33M cache)
[OK] Key buffer size / total MyISAM indexes: 32.0M/410.0M
[OK] Read Key buffer hit rate: 99.5% (1B cached / 7M reads)
[OK] Write Key buffer hit rate: 96.7% (781K cached / 26K writes)

-------- InnoDB Metrics ------------------------------------------------------
[--] InnoDB is enabled.
[!!] InnoDB buffer pool / data size: 8.0M/29.5M
[OK] InnoDB Used buffer: 100.00% (512 used/ 512 total)
[OK] InnoDB Read buffer efficiency: 96.06% (9790 hits/ 10192 total)
[!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)
[OK] InnoDB log waits: 0.00% (0 waits / 4 writes)

-------- ThreadPool Metrics --------------------------------------------------
[--] ThreadPool stat is disabled.

-------- AriaDB Metrics ------------------------------------------------------
[--] AriaDB is disabled.

-------- TokuDB Metrics ------------------------------------------------------
[--] TokuDB is disabled.

-------- Galera Metrics ------------------------------------------------------
[--] Galera is disabled.

-------- Replication Metrics -------------------------------------------------
[--] No replication slave(s) for this server.
[--] This is a standalone server..

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Restrict Host for user@% to user@SpecificDNSorIp
    Enable the slow query log to troubleshoot bad queries
    Increasing the query_cache size over 128M may reduce performance
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries which have no LIMIT clause
    Increase table_open_cache gradually to avoid file descriptor limits
    Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C
    Beware that open_files_limit (1024) variable
    should be greater than table_open_cache ( 32)
Variables to adjust:
    query_cache_size (> 128M) [see warning above]
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
    table_open_cache (> 32)
    innodb_buffer_pool_size (>= 29M) if possible.</major@mhtx.net>

So I know there are a bunch of issues, but what could possibly help right now in terms of the most impact?

I read the Tuning your mysql database article and I'm not sure which ones to adjust.

Here's my /etc/mysql/my.cnf file:

#
# 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/mysql/en/server-system-variables.html

# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.
[client]
port        = 3306
socket        = /var/run/mysqld/mysqld.sock

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket        = /var/run/mysqld/mysqld.sock
nice        = 0

[mysqld]

key_buffer = 720M
sort_buffer_size = 32M
read_buffer_size = 32M
read_rnd_buffer_size = 32M
myisam_sort_buffer_size = 160M
query_cache_size = 128M

#
# * Basic Settings
#

#
# * IMPORTANT
#   If you make changes to these settings and your system uses apparmor, you may
#   also need to also adjust /etc/apparmor.d/usr.sbin.mysqld.
#

user        = mysql
socket        = /var/run/mysqld/mysqld.sock
port        = 3306
basedir        = /usr
datadir        = /var/lib/mysql
tmpdir        = /tmp
skip-external-locking
#
# 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
#
# * Fine Tuning
#
key_buffer        = 32M
max_allowed_packet    = 1M
thread_stack        = 128K
thread_cache_size       = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover         = BACKUP
max_connections        = 35
table_cache            = 32
#thread_concurrency     = 10
#
# * Query Cache Configuration
#
query_cache_limit    = 1M
#query_cache_size        = 16M
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1

log_error                = /var/log/mysql/error.log

# Here you can see queries with especially long duration
#log_slow_queries    = /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
#server-id        = 1
#log_bin            = /var/log/mysql/mysql-bin.log
expire_logs_days    = 10
max_binlog_size         = 100M
#binlog_do_db        = include_database_name
#binlog_ignore_db    = include_database_name
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem

[mysqldump]
quick
quote-names
max_allowed_packet    = 16M

[mysql]
#no-auto-rehash    # faster start of mysql but no tab completition

[isamchk]
key_buffer        = 16M

#
# * 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/

Also, is my querycachesize too big?

mysql> show variables like '%query_cache%';
+------------------------------+-----------+
| Variable_name                | Value     |
+------------------------------+-----------+
| have_query_cache             | YES       |
| query_cache_limit            | 1048576   |
| query_cache_min_res_unit     | 4096      |
| query_cache_size             | 134217728 |
| query_cache_type             | ON        |
| query_cache_wlock_invalidate | OFF       |
+------------------------------+-----------+
6 rows in set (0.00 sec)

I'm fairly inexperienced at this kind of stuff, so any recommendations would be greatly appreciated.

10 Replies

In a typical LAMP setup, MySQL is often an innocent victim of Apache+PHP gobbling up all of your RAM. This makes people waste a lot of time trying to tune MySQL when actually Apache+PHP is the culprit. You should check whether this is the case, even if only to eliminate a possibly confounding factor.

What does your Apache configuration look like, especially the "MaxClients" and/or "MaxRequestWorkers" setting under the "mpm_prefork" section? If it's anything higher than 25 (just an off-the-top-of-my-head number based on the size of your Linode and mysqltuner results), reduce it to 25 and see how it goes.

Thanks for the response hybinet, I checked my /etc/apache2/apache2.conf file and I'm not sure if this is the right place but this is what I found under the mpm_prefork section.

So should I try reducing MaxClients to 25? What about MaxRequestsPerChild?

prefork MPM

StartServers: number of server processes to start

MinSpareServers: minimum number of server processes which are kept spare

MaxSpareServers: maximum number of server processes which are kept spare

MaxClients: maximum number of server processes allowed to start

MaxRequestsPerChild: maximum number of requests a server process serves

StartServers 2

MinSpareServers 6

MaxSpareServers 12

MaxClients 30

MaxRequestsPerChild 3000

My suggestion was just an off-the-top-of-my-head figure, so the difference between 25 and 30 might not matter. MaxRequestsPerChild is irrelevant.

So far, we know that:

  • MySQL uses a maximum of 3.5GB of RAM, based on your mysqltuner result.

  • That leaves 4.5GB of RAM for all the other things that run on your server.

  • 30 Apache/PHP processes can use up to 4GB of RAM, assuming PHP's default memorylimit of 128MB per process. If you set your memorylimit higher, it may use even more RAM.

  • That's pretty close to the total amount of RAM you have, so if you have other things running on your server as well (such as a control panel, mail server, spam and virus filter, etc.) you might run out of RAM at some point.

Other (not RAM related) possibilities:

  • What is the KeepAlive setting in /etc/apache2/apache2.conf? It should be either off or a single-digit number, in order to kick out people who just connect and do nothing. Otherwise, if a few people open 30 connections and occupy all of your Apache processes, nobody else will be able to connect to your server until they leave.

  • Let's look more closely at the mysqltuner results. You have 4GB of data in MyISAM tables, but the key buffer is too small and the per-thread buffer (sort buffer + join buffer + read buffer) is too big. If you have a lot of simultaneous visitors, heavy I/O and locking might make your site unresponsive even if there's plenty of RAM to go around. Also consider using more InnoDB.

Okay, FYI I don't have cPanel or any other control panel running. No mail server either.

I've got KeepAlive set to off.

KeepAlive: Whether or not to allow persistent connections (more than

one request per connection). Set to "Off" to deactivate.

#

KeepAlive Off

What would you recommend setting the key buffer and per-thread buffers to?

Server's been crashing on a fairly regular basis for the past few days. It just recently crashed (I was logged in through Putty and the server said it was getting shut down and I was getting kicked out), I checked my mysql error log and it said it was hitting the MaxClients limit.

[Tue Mar 01 09:31:52 2016] [notice] caught SIGTERM, shutting down

[Tue Mar 01 09:32:14 2016] [notice] Apache/2.2.14 (Ubuntu) PHP/5.4.39-1+deb.sury.org~lucid+2 configured – resuming normal operations

[Tue Mar 01 09:32:20 2016] [error] server reached MaxClients setting, consider raising the MaxClients setting

[Tue Mar 01 09:33:19 2016] [notice] caught SIGTERM, shutting down

[Tue Mar 01 09:33:40 2016] [notice] Apache/2.2.14 (Ubuntu) PHP/5.4.39-1+deb.sury.org~lucid+2 configured – resuming normal operations

[Tue Mar 01 09:33:46 2016] [error] server reached MaxClients setting, consider raising the MaxClients setting

[Tue Mar 01 09:35:34 2016] [notice] caught SIGTERM, shutting down

[Tue Mar 01 09:35:54 2016] [notice] Apache/2.2.14 (Ubuntu) PHP/5.4.39-1+deb.sury.org~lucid+2 configured – resuming normal operations

[Tue Mar 01 09:36:00 2016] [error] server reached MaxClients setting, consider raising the MaxClients setting

That's Apache error log, not MySQL error log.

If you're hitting MaxClients, don't listen to Apache's suggestion about raising it. That error just means the MaxClients setting is doing its job. If reaching MaxClients coincides with crashing, it means MaxClients is already too high. Have you tried reducing it to 25 as I suggested before, or even 20 just to be safe?

As for MySQL, have you implemented my previous suggestion of increasing the global buffer size and reducing the per-thread buffer size? If so, has it changed the mysqltuner result in a noticeable way? Large per-thread buffers cause a lot of variability in memory usage. Your server's memory usage should be fairly stable 24/7, not spiking all the time.

I have tried reducing the MaxClients setting to 25, but still been having lots of crashes recently. Site has been barely functional the past 5 days or so.

I would like to implement your suggestion, but what would you recommend setting the global buffer size and per-thread buffer size to? I'm very inexperienced at this, so I'm not too sure what to do. Thanks again for all your help, I do appreciate it.

Just noticed that you have two key_buffer settings in your MySQL configuration. The second one overrides the first, so the first one is meaningless.

Try setting the key buffer size to 512M, and sort/join/read/rnd buffers to 4M each. Re-run mysqltuner and see if the maximum possible memory usage has been reduced.

The query cache size is okay. You also really should be using InnoDB instead of MyISAM, but let's not worry about it now.

Okay I changed the settings in my.cnf so it looks like this:

[mysqld]

key_buffer = 720M
sort_buffer_size = 4M
read_buffer_size = 4M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 4M
query_cache_size = 128M

#
# * Basic Settings
#

#
# * IMPORTANT
#   If you make changes to these settings and your system uses apparmor, you may
#   also need to also adjust /etc/apparmor.d/usr.sbin.mysqld.
#

user        = mysql
socket        = /var/run/mysqld/mysqld.sock
port        = 3306
basedir        = /usr
datadir        = /var/lib/mysql
tmpdir        = /tmp
skip-external-locking
#
# 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
#
# * Fine Tuning
#
key_buffer        = 512M

Is that what you meant? I changed the second key_buffer value under Fine Tuning to 512M, then changed the buffer sizes to 4M.

This is mysqltuner output:

 >>  MySQLTuner 1.6.4 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.73-0ubuntu0.10.04.1
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
[--] Data in MyISAM tables: 4G (Tables: 325)
[--] Data in InnoDB tables: 29M (Tables: 71)
[!!] Total fragmented tables: 116

-------- Security Recommendations  -------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[!!] User 'wp_admin@%' hasn't specific host restriction.
[!!] There is no basic password file list!

-------- CVE Security Recommendations  ---------------------------------------
[--] Skipped due to --cvefile option undefined

-------- Performance Metrics -------------------------------------------------
[--] Up for: 23h 32m 50s (5M q [62.312 qps], 153K conn, TX: 275B, RX: 589M)
[--] Reads / Writes: 99% / 1%
[--] Binary logging is disabled
[--] Total buffers: 186.0M global + 96.2M per thread (35 max threads)
[OK] Maximum reached memory usage: 2.6G (33.55% of installed RAM)
[OK] Maximum possible memory usage: 3.5G (44.35% of installed RAM)
[OK] Slow queries: 0% (12/5M)
[OK] Highest usage of available connections: 74% (26/35)
[OK] Aborted connections: 0.00%  (0/153551)
[OK] Query cache efficiency: 88.3% (4M cached / 4M selects)
[!!] Query cache prunes per day: 31465
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 178K sorts)
[!!] Temporary tables created on disk: 45% (17K on disk / 37K total)
[OK] Thread cache hit rate: 99% (57 created / 153K connections)
[!!] Table cache hit rate: 0% (32 open / 4K opened)
[OK] Open file limit used: 4% (49/1K)
[OK] Table locks acquired immediately: 99% (457K immediate / 457K locks)

-------- MyISAM Metrics ------------------------------------------------------
[OK] Key buffer used: 100.0% (33M used / 33M cache)
[OK] Key buffer size / total MyISAM indexes: 32.0M/413.5M
[OK] Read Key buffer hit rate: 99.8% (273M cached / 627K reads)
[OK] Write Key buffer hit rate: 99.4% (153K cached / 929 writes)

-------- InnoDB Metrics ------------------------------------------------------
[--] InnoDB is enabled.
[!!] InnoDB buffer pool / data size: 8.0M/29.5M
[OK] InnoDB Used buffer: 100.00% (512 used/ 512 total)
[OK] InnoDB Read buffer efficiency: 95.97% (9510 hits/ 9909 total)
[!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)
[OK] InnoDB log waits: 0.00% (0 waits / 1 writes)

-------- ThreadPool Metrics --------------------------------------------------
[--] ThreadPool stat is disabled.

-------- AriaDB Metrics ------------------------------------------------------
[--] AriaDB is disabled.

-------- TokuDB Metrics ------------------------------------------------------
[--] TokuDB is disabled.

-------- Galera Metrics ------------------------------------------------------
[--] Galera is disabled.

-------- Replication Metrics -------------------------------------------------
[--] No replication slave(s) for this server.
[--] This is a standalone server..

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Restrict Host for user@% to user@SpecificDNSorIp
    MySQL started within last 24 hours - recommendations may be inaccurate
    Enable the slow query log to troubleshoot bad queries
    Increasing the query_cache size over 128M may reduce performance
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries which have no LIMIT clause
    Increase table_open_cache gradually to avoid file descriptor limits
    Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C
    Beware that open_files_limit (1024) variable
    should be greater than table_open_cache ( 32)
Variables to adjust:
    query_cache_size (> 128M) [see warning above]
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
    table_open_cache (> 32)
    innodb_buffer_pool_size (>= 29M) if possible.</major@mhtx.net>

Looks like you forgot to restart the MySQL service after editing your configuration file. Mysqltuner is still reporting your old configuration.

Delete one of the key_buffer settings, it's a recipe for confusion.

Reply

Please enter an answer
Tips:

You can mention users to notify them: @username

You can use Markdown to format your question. For more examples see the Markdown Cheatsheet.

> I’m a blockquote.

I’m a blockquote.

[I'm a link] (https://www.google.com)

I'm a link

**I am bold** I am bold

*I am italicized* I am italicized

Log in to Reply
Community Code of Conduct