3

I have this server that has only one site with one database on it. And yet, I cannot tweak it to make the load go back down to normal.

If I restart mysql service, memory and load go down to normal, but a few days later, memory and load are progressively increasing.

Server specs

  • Centos 7
  • total memory 7.8G
  • swap 1G

htop output

htop output

MySQL version

mysql Ver 14.14 Distrib 5.7.28, for Linux (x86_64) using EditLine wrapper

my.cnf

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

#[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
#datadir=/var/lib/mysql
#socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
#symbolic-links=0

#log-error=/var/log/mysqld.log
#pid-file=/var/run/mysqld/mysqld.pid

#max_allowed_packet=1024M


##################

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

plugin-load-add=validate_password.so


max_allowed_packet=100M

#------------------| query cach config
# https://easyengine.io/tutorials/mysql/query-cache
#------------------|
query_cache_type = 1
query_cache_size = 256M
query_cache_limit = 2M
#query_cache_strip_comments =1



port            = 3306
#socket         = /var/lib/mysql/mysql.sock
#skip-external-locking
key_buffer_size = 512M
max_allowed_packet=268435456
table_open_cache = 256
sort_buffer_size = 256
read_buffer_size = 256
read_rnd_buffer_size = 40
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 16M
# Try number of CPU's x 2 for thread_concurrency
#thread_concurrency = 2

# Point the following paths to different dedicated disks
#tmpdir         = /tmp/

# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (via the "enable-named-pipe" option) will render mysqld useless!
# 
#skip-networking

# Replication Master Server (default)
# binary logging is required for replication
#log-bin=mysql-bin

# binary logging format - mixed recommended
binlog_format=mixed

# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
#server-id      = 1



# ============================================================ #
# =============== Custom server tweaks [start] =============== #
# ============================================================ #

default_storage_engine = InnoDB
innodb_buffer_pool_size =  2G #4G # 8G # Use up to 70-80% of RAM & optionally check if /proc/sys/vm/swappiness is set to 0. Use Tuning Primer or MySQL Tuner to adjust accordingly.
innodb_file_per_table = 1
#--------------------------| 
# 2 means log buffer is flushed to OS file cache on every transaction commit. 
# The implication of 2 is optimal and improve performance 
# if you are not concerning ACID and can lose transactions for last second or two in case of OS crashes
# -------------------------|
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_log_buffer_size = 16M

#---------------------------| 
# Delete the ib_logfile0 & ib_logfile1 files in /var/lib/mysql whenever you change this, 
# even the first time you set it up, as the default value is 8M. 
# Don't go above 256M.
# ref: https://gist.github.com/wh4u/1fafe2bf98f630d7753717b5a8835bca
#---------------------------|
innodb_log_file_size = 256M


innodb_file_per_table=ON
innodb_stats_on_metadata = OFF
innodb_buffer_pool_instances = 8  #(or 1 if innodb_buffer_pool_size < 1GB)
#query_cache_type = 0
#query_cache_size = 0 #(disabling mutex)

#---------------|
# ref: http://techinfobest.com/optimize-mysql-table_open_cache/
# --------------|
#open_files_limit=10000
open_files_limit=19107
table_open_cache=39390

slow-query-log=1
long_query_time = 1
log-queries-not-using-indexes = 1


[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[myisamchk]
key_buffer_size = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

htop output after a couple of days

If I don't restart MySQL, this is what happens after 3-4 days

htop output

Can someone point me to the right direction please?

Additional details

MySQL crashed a little later with following messages in the log:

2020-01-03T12:59:51.514413Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4887ms. The settings might not be optimal. (flushed=142 and evicted=0, during the time.)
2020-01-03T13:00:35.061257Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 11874ms. The settings might not be optimal. (flushed=89 and evicted=0, during the time.)
2020-01-03T13:01:13.857771Z 0 [Warning] Could not increase number of max_open_files to more than 5000 (request: 78941)
2020-01-03T13:01:13.858498Z 0 [Warning] Changed limits: table_open_cache: 2419 (requested 39390)
2020-01-03T13:01:14.177515Z 0 [Warning] option 'sort_buffer_size': unsigned value 256 adjusted to 32768
2020-01-03T13:01:14.177549Z 0 [Warning] option 'read_buffer_size': unsigned value 256 adjusted to 8192
2020-01-03T13:01:14.177630Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2020-01-03T13:01:14.420855Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.28-log) starting as process 15029 ...
2020-01-03T13:01:15.607997Z 0 [Warning] You need to use --log-bin to make --binlog-format work.
2020-01-03T13:01:15.758435Z 0 [Note] InnoDB: PUNCH HOLE support available
2020-01-03T13:01:15.758521Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2020-01-03T13:01:15.758535Z 0 [Note] InnoDB: Uses event mutexes
2020-01-03T13:01:15.758552Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2020-01-03T13:01:15.758563Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2020-01-03T13:01:15.758574Z 0 [Note] InnoDB: Using Linux native AIO
2020-01-03T13:01:15.759295Z 0 [Note] InnoDB: Number of pools: 1
2020-01-03T13:01:15.759589Z 0 [Note] InnoDB: Using CPU crc32 instructions
2020-01-03T13:01:15.773538Z 0 [Note] InnoDB: Initializing buffer pool, total size = 2G, instances = 8, chunk size = 128M
2020-01-03T13:01:16.072379Z 0 [Note] InnoDB: Completed initialization of buffer pool
2020-01-03T13:01:16.142472Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2020-01-03T13:01:16.237526Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2020-01-03T13:01:16.349890Z 0 [Note] InnoDB: Log scan progressed past the checkpoint lsn 82179195773
2020-01-03T13:01:16.349986Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 82179207508
2020-01-03T13:01:16.471207Z 0 [Note] InnoDB: Database was not shutdown normally!

I tried the solution that was provided in the duplicate question suggestion, but this has made things worse. The server runs out of memory within minutes.

UPDATE

BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 2198863872
Dictionary memory allocated 1654382
Buffer pool size   131072
Free buffers       1
Database pages     128972
Old database pages 47448
Modified db pages  1578
Pending reads      1
Pending writes: LRU 0, flush list 163, single page 0
Pages made young 17903348, not young 602752081
0.00 youngs/s, 0.00 non-youngs/s
Pages read 75403666, created 44758503, written 47149356
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 997 / 1000, young-making rate 0 / 1000 not 67 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 128972, unzip_LRU len: 0
I/O sum[106576]:cur[1912], unzip sum[0]:cur[0]

Are Old database pages too many? What issues do you see?

UPDATE #2 Here is what my.cnf looks like at the moment

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

#[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
#datadir=/var/lib/mysql
#socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
#symbolic-links=0

#log-error=/var/log/mysqld.log
#pid-file=/var/run/mysqld/mysqld.pid

#max_allowed_packet=1024M


##################

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

plugin-load-add=validate_password.so


max_allowed_packet=100M

#------------------| query cach config
# https://easyengine.io/tutorials/mysql/query-cache
#------------------|
query_cache_type = 1
query_cache_size = 256M
query_cache_limit = 2M
#query_cache_strip_comments =1



port            = 3306
#socket         = /var/lib/mysql/mysql.sock
#skip-external-locking
key_buffer_size = 512M
max_allowed_packet=268435456
#table_open_cache = 256
sort_buffer_size = 256
read_buffer_size = 256
read_rnd_buffer_size = 40
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 16M
# Try number of CPU's x 2 for thread_concurrency
#thread_concurrency = 2

# Point the following paths to different dedicated disks
#tmpdir         = /tmp/

# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (via the "enable-named-pipe" option) will render mysqld useless!
# 
#skip-networking

# Replication Master Server (default)
# binary logging is required for replication
#log-bin=mysql-bin

# binary logging format - mixed recommended
binlog_format=mixed

# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
#server-id      = 1



# ============================================================ #
# =============== Custom server tweaks [start] =============== #
# ============================================================ #

default_storage_engine = InnoDB
innodb_buffer_pool_size =  2G #4G # 8G # Use up to 70-80% of RAM & optionally check if /proc/sys/vm/swappiness is set to 0. Use Tuning Primer or MySQL Tuner to adjust accordingly.
innodb_file_per_table = 1
#--------------------------| 
# 2 means log buffer is flushed to OS file cache on every transaction commit. 
# The implication of 2 is optimal and improve performance 
# if you are not concerning ACID and can lose transactions for last second or two in case of OS crashes
# -------------------------|
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_log_buffer_size = 16M

#---------------------------| 
# Delete the ib_logfile0 & ib_logfile1 files in /var/lib/mysql whenever you change this, 
# even the first time you set it up, as the default value is 8M. 
# Don't go above 256M.
# ref: https://gist.github.com/wh4u/1fafe2bf98f630d7753717b5a8835bca
#---------------------------|
innodb_log_file_size = 256M


innodb_file_per_table=ON
innodb_stats_on_metadata = OFF
innodb_buffer_pool_instances = 8  #(or 1 if innodb_buffer_pool_size < 1GB)
#query_cache_type = 0
#query_cache_size = 0 #(disabling mutex)

#---------------|
# ref: http://techinfobest.com/optimize-mysql-table_open_cache/
# --------------|
table_open_cache=1501
#table_open_cache=30000
open_files_limit=3003
#open_files_limit=19107
#table_open_cache=39390

slow-query-log=0
long_query_time = 1
log-queries-not-using-indexes = 1


[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[myisamchk]
key_buffer_size = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

I noticed some parameters were double in the file so I commented out the duplicates. e.g. table_open_cache was in two places under mysqld, one with value 256 and the other with value 39390.

8
  • 1
    I am not running multiple servers on this machine. Just one database. – Vasiliki Jan 6 '20 at 14:53
  • 1
    @Vasiliki ulimit -n nnnn and the Enter key is used to SET the ulimit. ulimit -a and the Enter key will reveal the current limits from the os perspective. Could you post current htop complete 1st page? – Wilson Hauck Jan 6 '20 at 18:46
  • 1
    @Vasiliki Please post your complete current my.cnf. The one you posted had TWO [mysqld] sections which is abnormal. The following are abnormal. sort_buffer_size = 256 read_buffer_size = 256 read_rnd_buffer_size = 40 - reasonable alternatives are: sort_buffer_size=512K - read_buffer_size=256K - read_rnd_buffer_size=128K – Wilson Hauck Jan 6 '20 at 18:56
  • 1
    @WilsonHauck I have updated with my.cnf. – Vasiliki Jan 7 '20 at 9:58
  • 1
    Thanks for the second my.cnf posted. – Wilson Hauck Jan 7 '20 at 17:46
2

table_open_cache=1501,open_files_limit=3003 (as suggested by Vasiliki) should be fine.

Note: When open_files_limit is more than ulimit -n, the former is lowered during startup. So, do SHOW VARIABLES LIKE 'open_files_limit'; to see what the setting really is. (Ditto for table_open_cache.)

table_open_cache is a "cache". As such, too-large a value will waste memory; too-small a value will slow certain things down. The value is the number of tables, not the number of bytes. 2000 is the median value used by over a hundred servers I polled. (10th percentile: 400; 90th percentile: 10000. Your 39390 is about 95th percentile--a red flag.) Set it to 1501 and forget it unless you are doing something really abnormal.

ulimit -n is a per-process file limit imposed by Linux (and its cousins). 1024 is the default in certain Operating System versions, and is a reasonable limit for casual programs. However, it is too low for MySQL, as has been discovered by many users doing non-trivial database work. It is, however, mostly a limit to keep you from going hog-wild in opening files. Setting it to 10000 is probably reasonable; that is helpful for MySQL, and not 'harmful'.

open_files_limit is initialized to ulimit -n. (I do not understand how you have 1024 and 19107. But note "[Warning] Changed limits: table_open_cache: 2419 (requested 39390)") But, again, this is merely a limit to keep the program from getting out-of-hand.

table_open_cache is the critical one in all this. However, you may be stopped from setting it because of the other two settings.

But...

You asked about "high load" -- Almost always that comes from inefficient queries. Such can usually be fixed by improving indexing (think "composite") and/or reformulating queries. Let's have a look at a slow query and discuss it.

You showed a large number of processes. Please provide SHOW VARIABLES LIKE 'max_connections'; It may be a default of 151, which is OK. (Note: this default has changed a few times based on version.) Consider lowering it to 50. Also, let's see SHOW GLOBAL STATUS LIKE 'Max_used_connections'; If this is more than, say, 10, then that could be a symptom of other issues.

You mention that memory keeps growing. Well, this is 'normal', but only to a point. The biggest user of RAM should be the buffer_pool; innodb_buffer_pool_size = 2G says that that part will grow until it occupies 2GB, then stop growing. The excessively large table cache (controlled by table_open_cache) is another memory user. (No, I don't know how many bytes in each entry.) Each "connection" consumes some memory. And connections are "cached", so my comments about max_connections indicate another memory consumer and way to control it. (Again, I don't know the per-connection memory usage.)

The htop output does not back your "high load". You have 6 cores? And they average about 3-4%? On the other hand, Load average is more than 3? (I trust the CPU percentage more than the LA.) Again; let's look at a slow query; you "can't tune your way out of a performance problem".

4
  • 1
    I have set table_open_cache=1501, open_files_limit=3003. max_connections | 151, Max_used_connections | 3. Also innodb_buffer_pool_size = 2G, haven't changed it. I will observe behaviour and update the post. – Vasiliki Jan 6 '20 at 17:35
  • 1
    @Vasiliki - and you restarted mysqld to have them take effect? – Rick James Jan 6 '20 at 17:38
  • 1
    Yes, I did. Load reduced dramatically, memory of process is 5.5% and hasn't increased at all since the edit. I will observe until tomorrow though. – Vasiliki Jan 6 '20 at 17:40
  • 1
    It seems that this edit didn't make much difference. Process memory is again 35% after a few hours. It is starting to look more like a problem in the code. Query enhancements have been prepared and will be applied. If that will bring the load and memory down, then it wasn't mysql performance after all. – Vasiliki Jan 7 '20 at 9:59
1

With performance issues, it's never one thing that is causing the problem. I will write all the tweaking I did and hopefully this will help others too. Everyone's comments were soooo helpful, you all showed the way to finding the solution(s).

I followed this guide to calculate correct numbers.

mysql> show global status  like 'open%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| Open_files               | 43    |
| Open_streams             | 0     |
| Open_table_definitions   | 375   |
| Open_tables              | 2288  |
| Opened_files             | 6842  |
| Opened_table_definitions | 376   |
| Opened_tables            | 3286  |
+--------------------------+-------+

mysql> SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE';
+----------+
| COUNT(*) |
+----------+
|      273 |
+----------+

mysql> show global status like '%Threads_connected%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_connected | 11    |
+-------------------+-------+

Table_open_cache = total_tables*Threads_connected
                    = 273 * 11
                    = 3003


table_open_cache=1501
open_files_limit=3003

Rick James was right about the previous values being too big.

Then the following parameters were not correct:

sort_buffer_size = 256   (Default value: 262144, as per official documentation)
read_buffer_size = 256    (Better 128K)
read_rnd_buffer_size = 40     (Default Value: 262144, as per official documentation) 

reasonable alternatives are: 
sort_buffer_size=512K 
read_buffer_size=256K 
read_rnd_buffer_size=128K 

as Wilson Hauck suggested.

max_connections were 150 so I reduced to 50.

Also, php-fpm configuration needed editing.

pm.max_children = 50
pm.start_servers = 5
pm.min_spare_servers = 5
pm.max_spare_servers = 35


pm.start_servers= min_spare_servers + (max_spare_servers - min_spare_servers) / 2

changing to 
pm.max_children = 25
pm.start_servers = 11
pm.min_spare_servers = 5
pm.max_spare_servers = 17

It's better to always follow the formulas provided in the official documentation.

All these brought memory down to normal usage. But CPU load was still too high.

Last, but not least, I used the tool JetProfiler. Even the free version helped. I identified queries that were just working for too long. The great thing is that jetprofiler is even giving you a suggestion and you may optimise the query.

Now htop looks a lot better.

the-good-htop

5
  • Please post with date/time current 1st page of htop so we all know what a 'good' htop looks like. Welcome to dba.stackexchange.com – Wilson Hauck Jan 7 '20 at 18:18
  • 1
    @WilsonHauck I updated with the htop. The number of processes has decreased as well. Memory percentage never exceeds 18. At the beginning of the question I have added screenshot of what htop used to look like. Thank you for your comments and suggestions. – Vasiliki Jan 7 '20 at 18:27
  • You have made much progress. Could you post results of SELECT @@version; Please view my profile, Network profile and get in touch. – Wilson Hauck Jan 7 '20 at 18:59
  • Could you also post TEXT results of SHOW FULL PROCESSLIST; – Wilson Hauck Jan 7 '20 at 21:55
  • What is your threads_connected count at this time? – Wilson Hauck Jan 8 '20 at 12:48
0

Suggestions to consider for your my.cnf [mysqld] section

REMOVE the following 3 lines that have other values to be used on other lines

innodb_file_per_table = 1
max_allowed_packet=100M
query_cache_size = 256M

CHANGE

read_buffer_size=256K  # from 256 to improve performance
read_rnd_buffer_size=128K  # from 40 bytes to improve performance
sort_buffer_size=512K  # from 256 bytes to improve performance
thread_cache_size=32  # from 8 to reduce threads_created count

stop start your server. Post a fresh 1st page of TOP.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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