Database Administrators Asked by Rajesh Ranjan on October 28, 2021
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!
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".
Answered by Rick James on October 28, 2021
Get help from others!
Recent Answers
Recent Questions
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP