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?
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