1

So I have a Server that's constantly receiving 30k clicks/day. However, in the meantime does certain insert/update/select cronjobs as well.

My server has 4CPUs, 8GM RAM. Using MySQL 5.7.18 + PHP.

I did some changes in my mysqld.cnf, however, it doesn't seem to help. Maybe the buffer pool size is too much?

Here's my configuration:

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

[mysqld]
#
# * Basic Settings
#
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
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        = 0.0.0.0
#
# * Fine Tuning
#
key_buffer_size     = 16M
max_allowed_packet  = 16M
thread_stack        = 192K
thread_cache_size       = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover-options  = BACKUP
#max_connections        = 100
#table_cache            = 64
#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
#
# Error log - should be very few entries.
#
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
# CACHES AND LIMITS #
tmp-table-size                 = 32M
max-heap-table-size            = 32M
query-cache-type               = 0
query-cache-size               = 0
#max-connections                = 500
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           = 256M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 6G

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

Here are two images of the memory charts:

enter image description here

enter image description here

Not entirely sure what's causing this. But what I've gathered is that it's keep increasing until it crashes.

Any thoughts on this?

EDIT - After chnaging the buffer pool size to 4G - RAM usage is 85-86% now consistently enter image description here

Improve this question
2
  • Did you end up resolving this issue? This is exactly the problem I'm having right now.
    – Steve de Niese
    Sep 13, 2018 at 4:44
  • @StevedeNiese Sort of. I figured that I had quite a few 'unfinished' PHP processes that increased the memory. So make sure that each function that you have end/return something, otherwise the PHP process might not be able to close down. I've refactored my code, resintalled the server and it works ok now.
    – Radical_Activity
    Sep 24, 2018 at 7:42

1 Answer 1

Reset to default
1

What else is running on the machine? There seem to be 2 or 3 jumps in memory usage. MySQL (usually) has one jump or gradual rise.

Nothing looks wrong in my.cnf. But change innodb_buffer_pool_size to 5G; that will probably keep it from crashing as much.

What OS are you using? If it has "swappiness", set that to 1, not 0.

Improve this answer
5
  • Thanks for your comment. Only PHP is running on the machine nothing else really. I'm monitoring the processes, and it looks like that MySQL's using all of the memory (it goes up to 90-98% usage). There are a few cronjobs running, executing MySQL queries, etc - and what I've noticed is that that's when it jumps. Using Ubuntu 16.04 - MySQL 5.7.18. I've also read that this MySQL version might have some memory leak and worth updating it to 5.7.19. I've tried reducing the buffer size to 4G yesterday, and here's the chart since then. (check the main post edit please). There's no swapping either.
    – Radical_Activity
    Nov 16, 2017 at 13:34
  • That's an awful graphing program -- with more civilized x and y coordinates, the cron job would be obvious.
    – Rick James
    Nov 16, 2017 at 14:17
  • PHP can (but is not likely to) use a lot of memory.
    – Rick James
    Nov 16, 2017 at 14:19
  • Do you know any other softwares for better graphs?
    – Radical_Activity
    Nov 16, 2017 at 14:25
  • Please post your error log on next crash ( if it crashes ) - there is usually a clue. Since you are keeping 10 days of logs, maybe you could post your last error with a crash cause listed.
    – Wilson Hauck
    Nov 16, 2017 at 18:38

Not the answer you're looking for? Browse other questions tagged or ask your own question.