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
2 Answers 2
low memory < 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.
MySQL setting that affects memory
Definitely Yes, the wrong memory parameter setting will effect to the MySQL Server performance. As I am able to see from your my.cnf file configuration setting parameters of innodb_buffer_pool_size=128M , innodb_log_file_size=64M and innodb_buffer_pool_instances=1.
InnoDB, unlike MyISAM, uses a buffer pool to cache both indexes and row data. The bigger you set this the less disk I/O is needed to access data in tables. On a dedicated database server you may set this parameter up to 80% of the machine physical memory size. Do not set it too large, though, because competition of the physical memory may cause paging in the operating system. Note that on 32bit systems you
might be limited to 2-3.5G of user level memory per process, so do not
set it too high.
Size of each log file in a log group. You should set the combined size of log files to about 25%-100% of your buffer pool size to avoid unneeded buffer pool flush activity on log file overwrite. However, note that a larger logfile size will increase the time needed for the recovery process.
Note : The minimum innodb_log_file_size value was increased from
1MBto4MBin MySQL5.7.11.
The number of regions that the InnoDB buffer pool is divided into. For systems with buffer pools in the multi-gigabyte range, dividing the buffer pool into separate instances can improve concurrency, by reducing contention as different threads read and write to cached pages.
Note: On all other platforms, the default value is 8 when
innodb_buffer_pool_sizeis greater than or equal to1GB. Otherwise, the default is1.
Buffer pool size must always be equal to or a multiple of innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances. If you configure innodb_buffer_pool_size to a value that is not equal to or a multiple of innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances, buffer pool size is automatically adjusted to a value that is equal to or a multiple of innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances.
For example, innodb_buffer_pool_size is set to 8G, and innodb_buffer_pool_instances is set to 16. innodb_buffer_pool_chunk_size is 128M, which is the default value.
8G is a valid innodb_buffer_pool_size value because 8G is a multiple of innodb_buffer_pool_instances=16 * innodb_buffer_pool_chunk_size=128M, which is 2G.
In Linux environment
shell> mysqld --innodb-buffer-pool-size=8G --innodb-buffer-pool-instances=16
In Windows Environment
mysql> SELECT @@innodb_buffer_pool_size/1024/1024/1024;
+------------------------------------------+
| @@innodb_buffer_pool_size/1024/1024/1024 |
+------------------------------------------+
| 8.000000000000 |
+------------------------------------------+
SHOW GLOBAL STATUS LIKE '%buffer%'.