0

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
9
  • Install and launch the mysqltuner script for detailed description of your setup. Also that script gives some reasonable advices about configuration. – Kondybas Mar 11 '19 at 12:22
  • Why max_connection=1501 and why max_allowed_packet=2G ??? – Jesus Uzcanga Mar 11 '19 at 15:38
  • show the result of SHOW VARIABLES LIKE '%buffer%'; – Jesus Uzcanga Mar 11 '19 at 15:39
  • And why 'innodb_flush_log_at_trx_commit=0'? Do you really not care about your data? MySQL version may be useful too. @jesus-uzcanga I think you mean SHOW GLOBAL STATUS LIKE '%buffer%'. – danblack Mar 12 '19 at 2:26
  • @JesusUzcanga updated with answers to your questions, will those affect total memory used? – Chor Wai Chun Mar 13 '19 at 3:28
1
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.

0

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_buffer_pool_size

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.

innodb_log_file_size

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 1MB to 4MB in MySQL 5.7.11.

innodb_buffer_pool_instances

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_size is greater than or equal to 1GB. Otherwise, the default is 1.

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

For further your ref here and here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.