MySQL setting that affects memory I'm trying to setup a linux server with low memory &lt; 1G+- available, so the goal is not to exceed 512mb of total mysql usage. I have below simple setup:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mysqld according to the
# instructions in http://fedoraproject.org/wiki/Systemd

max_connections=1501 #we only use up to 200, setting this to avoid too many conn error
innodb_flush_log_at_trx_commit=0 #we are not doing data critical application, we will sacrifice this to hit higher throughput, as we focus more on throughput
innodb_buffer_pool_size=128M
innodb_log_file_size=64M
max_allowed_packet=2G #due to large queries during data migration
sql-mode="STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"


[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid


As you can see, the buffer pool size is only configured to 128M, and file is at 64m. innodb_buffer_pool_instances is 1 by default, checked this using show variables too.

But based on my TOP graph, the mysql will occasionally use up to 700MB or my RAM, does anyone have a good guess what went wrong?

Thank you.

UPDATE:

SHOW VARIABLES LIKE '%buffer%';

Variable_name   Value
bulk_insert_buffer_size 8388608
innodb_buffer_pool_chunk_size   134217728
innodb_buffer_pool_dump_at_shutdown ON
innodb_buffer_pool_dump_now OFF
innodb_buffer_pool_dump_pct 25
innodb_buffer_pool_filename ib_buffer_pool
innodb_buffer_pool_instances    1
innodb_buffer_pool_load_abort   OFF
innodb_buffer_pool_load_at_startup  ON
innodb_buffer_pool_load_now OFF
innodb_buffer_pool_size 134217728
innodb_change_buffer_max_size   25
innodb_change_buffering all
innodb_log_buffer_size  16777216
innodb_sort_buffer_size 1048576
join_buffer_size    262144
key_buffer_size 8388608
myisam_sort_buffer_size 8388608
net_buffer_length   16384
preload_buffer_size 32768
read_buffer_size    131072
read_rnd_buffer_size    262144
sort_buffer_size    262144
sql_buffer_result   OFF


SHOW GLOBAL STATUS LIKE '%buffer%';

Variable_name   Value
Innodb_buffer_pool_dump_status  Dumping of buffer pool not started
Innodb_buffer_pool_load_status  Buffer pool(s) load completed at 190216  0:54:10
Innodb_buffer_pool_resize_status    
Innodb_buffer_pool_pages_data   5230
Innodb_buffer_pool_bytes_data   85688320
Innodb_buffer_pool_pages_dirty  0
Innodb_buffer_pool_bytes_dirty  0
Innodb_buffer_pool_pages_flushed    340624
Innodb_buffer_pool_pages_free   2903
Innodb_buffer_pool_pages_misc   58
Innodb_buffer_pool_pages_total  8191
Innodb_buffer_pool_read_ahead_rnd   0
Innodb_buffer_pool_read_ahead   128
Innodb_buffer_pool_read_ahead_evicted   0
Innodb_buffer_pool_read_requests    1251760651
Innodb_buffer_pool_reads    4327
Innodb_buffer_pool_wait_free    0
Innodb_buffer_pool_write_requests   2331193
low memory &lt; 1G+- available, so the goal is not to exceed 512mb of total mysql usage


I'll assume 0.5GB.

max_connections=1501 #we only use up to 200, setting this to avoid too many conn error


That will chew up a lot of RAM that you do not have.  I would set it to no more than 50 and then work on making sure the applications throttle back how many connections they ask for.

If you have more than, say, 10 active threads, you could encounter them stumbling over each other -- this leads to performance issues of its own.

innodb_buffer_pool_size=128M


This is probably all you can afford in that tiny RAM.  More than that will lead to swapping; less than that could lead to cases where it simply can't get the job done.

innodb_log_file_size=64M


That's a disk file limit; it has no impact on RAM allocation.

innodb_log_buffer_size  16777216


is in RAM.  Cut that back to 4M.

The 80% rule is fine if you have more than 4GB of RAM.  A better rule is "80% of available RAM".  The issue is that you need the OS, the mysql code, and many other caches, buffers, etc.

max_allowed_packet=2G #due to large queries during data migration


Duh?  That is allocated in RAM, but you don't have that much RAM??  Imagine how much swapping will happen!  If you are loading a dump from mysqldump, rebuild the dump so that it does not build huge INSERT statements.  If you have 2GB BLOBs, you may be in big trouble.  Then cut that back to 32M (I guess).

innodb_buffer_pool_chunk_size is relevant only if you dynamically resize the buffer_pool.  (You are not likely to do this.)

Toss the query cache:  query_cache_size = 0 and query_cache_type = OFF.

tmp_table_size and max_heap_table_size -- Set to 1M.  These may be allocated once per connection or even more often (in the case of a complex SELECT).  MySQL has a fallback if these are "too small", so you won't lose functionality.

sort_buffer_size -- 4M

There may be more.