0

Help me figure out what my mistake is. I'm trying to set up a mysql 5.7 server in which 90% of requests are very large requests (2-10 MB) in which inner join, union all, case then, etc. From the main one - there are 3 tables for 50k records. One contains 280 columns, the other 2 by 20-30 - all double fields except two fields (id int and foreign key to the table) Requests are executed in a loop and can be up to 100 requests per second.

The server has 3.7 memory, of which 2.5 is free. You need to count on this amount. But within a few hours mysql starts to consume more and more memory and runs into the server memory limits and crashes with an out memory error

KiB Mem 3880296 total 133132 free 2993828 used, 753336 buff/cache
KiB Swap 0 total 0 free 0 used 462208
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
4667 mysql 20 0 3441964 2.1g 6980 S 130.0 55.8 132:33.69 mysqld
4696 apache 20 0 12.2g 681312 13492 S 61.3 17.6 125:27.81 dotnet
[mysqld_safe]
socket=/var/lib/mysql/mysql.sock
nice = 0

[mysqld]
datadir=/var/lib/mysql
tmpdir  = /tmp
lc-messages-dir = /usr/share/mysql
socket=/var/lib/mysql/mysql.sock
secure-file-priv = "/var/www/html/xg"
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
low-priority-updates
log-error=/var/log/mysqld_error.log
pid-file=/var/run/mysqld/mysqld.pid
bind-address=127.0.0.1
lower_case_table_names=1
explicit_defaults_for_timestamp = 1

innodb_buffer_pool_size = 650M
innodb_log_file_size = 32M
key_buffer_size = 256M
query_cache_size = 0
query_cache_limit = 0
thread_cache_size = 8
max_connections = 100
innodb_lock_wait_timeout = 100
performance_schema = 0
table_cache = 0
table_definition_cache = 0
6
  • 1
    280 columns sets alarm bells ringing, as does 'requests are executed in a loop'. I don't know what's causing your 'out of memory' problem, but your schema and the way you use it look very suspect.
    – Tangentially Perpendicular
    Aug 2, 2021 at 21:52
  • @TangentiallyPerpendicular A table with 280 columns contains object parameters. Not all columns are used in queries. often 10-20. The loop is needed in order to periodically perform repeated searches for matches for these queries. I read that it is possible to make one talitsa with many columns than join them later in the query.
    – Дмитрий Яковлев
    Aug 3, 2021 at 5:29
  • Please post A) your query using 3 tables in question and B) EXPLAIN SELECT .....; C) SHOW FULL PROCESSLIST; and D) SHOW GLOBAL STATUS LIKE 'threads_connected'; and then 15 minutes later E) SHOW GLOBAL STATUS LIKE 'threads_connected; and F) top OR htop from OS. Give the OS 6GB of swap space to allow survival before OOM issue. 100 of these queries per second for your available RAM would likely overwhelm the equipment. Also please post SHOW CREATE TABLE xxxx; for the 3 tables mentioned for analysis. Aug 5, 2021 at 11:15
  • @mustaccio - No, because max_connections is already low.
    – Rick James
    Aug 5, 2021 at 23:01
  • Memory will grow until some max. In fact it may have already stopped growing.
    – Rick James
    Aug 5, 2021 at 23:04

1 Answer 1

0

Consider these changes to your my.cnf [mysqld] section to improve performance

innodb_buffer_pool_size=1G  # from 650M to reduce innodb_buffer_pool_reads count
thread_cache_size=32  # from 8 to reduce threads_created count (conserve CPU cycles)
table_cache=200  # from 0 to reduce opened_tables count
innodb_open_files=200  # from ? - should always match table_cache value in cfg
table_definition_cache=100  # from 0 to reduce opened_table_definitions count

Provide comments, please after implementation.