Description:
Hi, my os server is currently running only mysql, and mysql is not using innodb, but using myisam engine.
What I'm wondering is why it uses swap memory even though there is available memory, so the swap memory keeps filling up without being released.
I know that the buffer/cache memory contains the query cache, so I wonder if it is normal to use swap memory instead of taking it from there.
The similar issues I found are as follows:
#83047, #84003
Can I prevent swap from filling up by applying the above settings?
The solutions I thought of are to increase the RAM or periodically initialize the swap.
I wonder if there is another way, or if it is a bug, can you help me?
os : Operating System: Rocky Linux 8.10
# free -h
total used free shared buff/cache available
Mem: 62Gi 13Gi 584Mi 1.0Gi 48Gi 46Gi
Swap: 8.0Gi 1.6Gi 6.4Gi
# cat /proc/3661008/status|grep Vm
VmPeak: 21319524 kB
VmSize: 19771240 kB
VmLck: 0 kB
VmPin: 0 kB
VmHWM: 14112756 kB
VmRSS: 12770808 kB
VmData: 17926176 kB
VmStk: 132 kB
VmExe: 21748 kB
VmLib: 11264 kB
VmPTE: 30700 kB
VmSwap: 1529616 kB
mysql> select engine,data,indexes,total
-> from (
-> select
-> ifnull(engine,'TOTALS') as engine,
-> concat(data,' GB') as data,
-> concat(indexes,' GB') as indexes,
-> concat(tot,' GB') as total,
-> if(engine is null,-1,tot) as ord
-> from (
-> select
-> engine,
-> round( sum(data_length)/1024/1024/1024, 2 ) as data,
-> round( sum(index_length)/1024/1024/1024, 2 ) as indexes,
-> round( sum(data_length+index_length)/1024/1024/1024, 2 ) as tot
-> from information_schema.tables
-> where engine is not null and engine not in('information_schema','performance_schema')
-> group by engine with rollup
-> ) sums
-> ) list
-> order by list.ord desc;
+--------+-----------+-----------+-----------+
| engine | data | indexes | total |
+--------+-----------+-----------+-----------+
| MyISAM | 398.18 GB | 154.33 GB | 552.50 GB |
| CSV | 0.00 GB | 0.00 GB | 0.00 GB |
| InnoDB | 0.00 GB | 0.00 GB | 0.00 GB |
| MEMORY | 0.00 GB | 0.00 GB | 0.00 GB |
| TOTALS | 398.18 GB | 154.33 GB | 552.51 GB |
+--------+-----------+-----------+-----------+
my.cnf
[client]
port = 3306
socket = /tmp/mysql.sock
default-character-set = utf8
[mysqld]
innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:10G
sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
port = 3306
socket = /tmp/mysql.sock
datadir = /mnt/disk2/db_data
basedir = /usr/local/mysql
skip-external-locking
key_buffer_size = 4096M
max_allowed_packet = 20M
table_open_cache = 512
sort_buffer_size = 20M
read_buffer_size = 20M
read_rnd_buffer_size = 80M
myisam_sort_buffer_size = 256M
thread_cache_size = 8
query_cache_size = 32M
explicit_defaults_for_timestamp
skip-name-resolve
tmp_table_size=100M
max_heap_table_size=100M
ngram_token_size=2
ft_min_word_len=2
innodb_ft_min_token_size=2
max_connections = 1000
max_connect_errors = 1000
wait_timeout = 7200
slow_query_log
long_query_time = 3
slow_query_log_file=slow_query.log
#symbolic-links=0
character-set-client-handshake=FALSE
character-set-server = utf8
collation-server = utf8_unicode_ci
default-storage-engine = MYISAM
bulk_insert_buffer_size = 128M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 2G
myisam_repair_threads = 1
server-id = 1
innodb_file_per_table=1
innodb_buffer_pool_size=8M
innodb_data_file_path = ibdata1:10M:autoextend
innodb_autoextend_increment=100
innodb_additional_mem_pool_size=2M
innodb_flush_log_at_trx_commit=1
sync_binlog=1
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_thread_concurrency = 8
innodb_log_buffer_size = 8M
innodb_log_file_size = 128M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
[mysqldump]
quick
max_allowed_packet = 256M
default-character-set = utf8
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
default-character-set = utf8
[myisamchk]
key_buffer_size = 512M
sort_buffer_size = 512M
read_buffer = 100M
write_buffer = 100M
[mysqlhotcopy]
interactive-timeout
How to repeat:
Run large queries until swap is full
Description: Hi, my os server is currently running only mysql, and mysql is not using innodb, but using myisam engine. What I'm wondering is why it uses swap memory even though there is available memory, so the swap memory keeps filling up without being released. I know that the buffer/cache memory contains the query cache, so I wonder if it is normal to use swap memory instead of taking it from there. The similar issues I found are as follows: #83047, #84003 Can I prevent swap from filling up by applying the above settings? The solutions I thought of are to increase the RAM or periodically initialize the swap. I wonder if there is another way, or if it is a bug, can you help me? os : Operating System: Rocky Linux 8.10 # free -h total used free shared buff/cache available Mem: 62Gi 13Gi 584Mi 1.0Gi 48Gi 46Gi Swap: 8.0Gi 1.6Gi 6.4Gi # cat /proc/3661008/status|grep Vm VmPeak: 21319524 kB VmSize: 19771240 kB VmLck: 0 kB VmPin: 0 kB VmHWM: 14112756 kB VmRSS: 12770808 kB VmData: 17926176 kB VmStk: 132 kB VmExe: 21748 kB VmLib: 11264 kB VmPTE: 30700 kB VmSwap: 1529616 kB mysql> select engine,data,indexes,total -> from ( -> select -> ifnull(engine,'TOTALS') as engine, -> concat(data,' GB') as data, -> concat(indexes,' GB') as indexes, -> concat(tot,' GB') as total, -> if(engine is null,-1,tot) as ord -> from ( -> select -> engine, -> round( sum(data_length)/1024/1024/1024, 2 ) as data, -> round( sum(index_length)/1024/1024/1024, 2 ) as indexes, -> round( sum(data_length+index_length)/1024/1024/1024, 2 ) as tot -> from information_schema.tables -> where engine is not null and engine not in('information_schema','performance_schema') -> group by engine with rollup -> ) sums -> ) list -> order by list.ord desc; +--------+-----------+-----------+-----------+ | engine | data | indexes | total | +--------+-----------+-----------+-----------+ | MyISAM | 398.18 GB | 154.33 GB | 552.50 GB | | CSV | 0.00 GB | 0.00 GB | 0.00 GB | | InnoDB | 0.00 GB | 0.00 GB | 0.00 GB | | MEMORY | 0.00 GB | 0.00 GB | 0.00 GB | | TOTALS | 398.18 GB | 154.33 GB | 552.51 GB | +--------+-----------+-----------+-----------+ my.cnf [client] port = 3306 socket = /tmp/mysql.sock default-character-set = utf8 [mysqld] innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:10G sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" port = 3306 socket = /tmp/mysql.sock datadir = /mnt/disk2/db_data basedir = /usr/local/mysql skip-external-locking key_buffer_size = 4096M max_allowed_packet = 20M table_open_cache = 512 sort_buffer_size = 20M read_buffer_size = 20M read_rnd_buffer_size = 80M myisam_sort_buffer_size = 256M thread_cache_size = 8 query_cache_size = 32M explicit_defaults_for_timestamp skip-name-resolve tmp_table_size=100M max_heap_table_size=100M ngram_token_size=2 ft_min_word_len=2 innodb_ft_min_token_size=2 max_connections = 1000 max_connect_errors = 1000 wait_timeout = 7200 slow_query_log long_query_time = 3 slow_query_log_file=slow_query.log #symbolic-links=0 character-set-client-handshake=FALSE character-set-server = utf8 collation-server = utf8_unicode_ci default-storage-engine = MYISAM bulk_insert_buffer_size = 128M myisam_sort_buffer_size = 128M myisam_max_sort_file_size = 2G myisam_repair_threads = 1 server-id = 1 innodb_file_per_table=1 innodb_buffer_pool_size=8M innodb_data_file_path = ibdata1:10M:autoextend innodb_autoextend_increment=100 innodb_additional_mem_pool_size=2M innodb_flush_log_at_trx_commit=1 sync_binlog=1 innodb_write_io_threads = 8 innodb_read_io_threads = 8 innodb_thread_concurrency = 8 innodb_log_buffer_size = 8M innodb_log_file_size = 128M innodb_log_files_in_group = 3 innodb_max_dirty_pages_pct = 90 innodb_lock_wait_timeout = 120 [mysqldump] quick max_allowed_packet = 256M default-character-set = utf8 [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates default-character-set = utf8 [myisamchk] key_buffer_size = 512M sort_buffer_size = 512M read_buffer = 100M write_buffer = 100M [mysqlhotcopy] interactive-timeout How to repeat: Run large queries until swap is full