Question :
I’m using MariaDB 10.5 on Centos7 with 4G RAM and 2 CPU.
Here’s my.cnf configuration
[mysqld]
log-error=/var/lib/mysql/mysql-3.kannel.com.err
max_allowed_packet=1G
event_scheduler = ON
innodb_file_per_table=ON
#innodb_file_format=Barracuda
innodb_buffer_pool_size=3G
innodb_buffer_pool_instances=6
innodb_log_file_size=1G
innodb_log_files_in_group=1
innodb_log_buffer_size=32M
innodb_log_write_ahead_size=32M
innodb_flush_log_at_trx_commit=2
innodb_read_io_threads=32
innodb_write_io_threads=128
innodb_io_capacity=10000
innodb_thread_concurrency=6
innodb_flush_method=o_direct
join_buffer_size=128M
sort_buffer_size=20M
read_rnd_buffer_size=128M
datadir=/var/lib/mysql
open_files_limit=10000
default-storage-engine=InnoDB
max_connections = 500
# CACHES AND LIMITS #
tmp-table-size = 20M
max-heap-table-size = 32M
query-cache-type = 1
thread-cache-size = 50M
table-definition-cache = 128M
table-open-cache = 128M
query_cache_size = 50M
query_cache_limit=128M
But when I see InnoDB engine status using SHOW ENGINE INNODB STATUSG;
it gives me below information where Buffer pool size 193296
but Total large memory allocated 3288334336
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 3288334336
Dictionary memory allocated 19166328
Buffer pool size 193296
Free buffers 179141
Database pages 49186
Old database pages 18176
So if Buffer pool size 193296
, where rest of the memory is being allocated?
Thanks!
Answer :
It is unsafe to raise values arbitrarily. It often leads to swapping, which makes performance worse.
max_allowed_packet=1G -- dangerously high; wastes RAM; set to 1% of RAM
innodb_buffer_pool_size=3G --Since you have only 4GB, lower to 2G
innodb_buffer_pool_instances=6 -- only 1 per GB, so: 2
innodb_log_files_in_group=1 -- Use the standard of 2
innodb_log_write_ahead_size=32M -- waste of RAM; leave at default (8K)
innodb_write_io_threads=128 -- too high
innodb_io_capacity=10000 -- do you have a super-duper SSD? Else too high
join_buffer_size=128M -- lower to 1% of RAM
read_rnd_buffer_size=128M -- lower to 1% of RAM
max_connections = 500 -- the default will save RAM
As for “Buffer pool size 193296” — That is pages. Multiply by 16K to get about 3G. So it is not “small”.