I'm trying to setup a linux server with low memory < 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
Best Answer
I'll assume 0.5GB.
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.
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.
That's a disk file limit; it has no impact on RAM allocation.
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.
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 hugeINSERT
statements. If you have 2GBBLOBs
, you may be in big trouble. Then cut that back to32M
(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
andquery_cache_type = OFF
.tmp_table_size
andmax_heap_table_size
-- Set to1M
. These may be allocated once per connection or even more often (in the case of a complexSELECT
). MySQL has a fallback if these are "too small", so you won't lose functionality.sort_buffer_size
-- 4MThere may be more.