MySQL InnoDB corruption and vm.pageout_update_period=0

CyberCr33p

CyberCr33p

Every few days I had some innodb corruption in random databases in random servers.

I believe it's related to vm.pageout_update_period=0

When I change it back to the default vm.pageout_update_period=300 everything is fine ( I check for innodb corruption every few days by doing a mysqldump).

Any thoughts about this?
 
Nothing from MySQL/MariaDB should get swapped, it's all active. How is MySQL configured? You can really mess things up with the wrong tuning parameters. Never, ever, give it more memory than the machine actually has. Rule of thumb, never give it more than 3/4 of RAM, even less if there are other services running on the machine too. But also never give it more than the size of your databases. Providing too much buffers/caches will actually be detrimental to the performance as it spends too much time managing those buffers/caches (too much overhead).
 
Machines have 32GB or 64GB of RAM.
Here is the my.cnf :
Code: 
[client]
port            = 3306
socket          = /tmp/mysql.sock

[mysqld]

# Performance
optimizer_search_depth=0
tmp_table_size=32M
max_heap_table_size=32M
skip_ssl
innodb_flush_method=O_DIRECT
innodb_lru_scan_depth=512
innodb_io_capacity=100
innodb_io_capacity_max=300
innodb_buffer_pool_instances=4
innodb_deadlock_detect=0
innodb_adaptive_hash_index=0
innodb_max_dirty_pages_pct=0

innodb_file_format=Barracuda
innodb_large_prefix=1
innodb_checksum_algorithm=crc32
innodb_buffer_pool_dump_at_shutdown=0
innodb_buffer_pool_load_at_startup=0
innodb_strict_mode=0
optimizer_switch=condition_fanout_filter=off,derived_merge=off
transaction-isolation=READ-COMMITTED
#optimizer_switch=derived_merge=off,block_nested_loop=off
#innodb_temp_data_file_path=ibtmp1:12M:autoextend:max:512M
#innodb_temp_data_file_path=../../../tmpfs/ibtmp1:12M:autoextend:max:512M
internal_tmp_disk_storage_engine=MYISAM

# SlowLog
min_examined_row_limit=3000
slow_query_log = 1
slow_query_log_file = /tmpfs/mysql-slow-query.log
long_query_time = 10
#log_queries_not_using_indexes = 1

sql-mode=NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

performance_schema=off
ft_min_word_len=3
low_priority_updates=1
concurrent_insert=2
max_connections=101
max_user_connections=15
#wait_timeout=60
#interactive_timeout=60
tmpdir=/tmpfs

port            = 3306
socket          = /tmp/mysql.sock
#skip-external-locking
key_buffer_size = 256M
max_allowed_packet = 128M
table_open_cache = 16384
table_definition_cache = 4096
sort_buffer_size = 4M
join_buffer_size = 1M
query_cache_type = 0
query_cache_size = 0

server-id       = 1

#innodb_data_home_dir = /var/db/mysql
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = /var/db/mysql
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 4G
#innodb_additional_mem_pool_size = 20M
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 512M
#innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 2
# innodb_lock_wait_timeout = 8

[mysqldump]
max_allowed_packet = 256M
quote_names
quick

[mysql]
prompt = \u@\h [\d]>\_
no-auto-rehash
 
mysqltuner shows that settings are ok.

innodb_flush_method=O_DIRECT disables the OS cache as MySQL has it's own cache. Isn't it waste of resources to have double cache?
 
CyberCr33p said:
Isn't it waste of resources to have double cache?
Click to expand...
There are more caches, even on the disks themselves. You don't turn this on to prevent wasting resources, you turn it on because it supposedly improves performance. And as far as I can tell that's debatable on FreeBSD.

CyberCr33p said:
Machines have 32GB or 64GB of RAM.
Click to expand...
Looking at your innodb_buffer_pool_size = 4G I don't expect this to be an issue. It's unlikely any of it gets swapped in or out, so vm.pageout_update_period should have zero influence on the actual data of the database. I suspect the corruption is caused by something else.
 
With vm.pageout_update_period=0 the swap usage is only few MB or not used at all. With the default setting some servers have 2-3GB of swap. But it doesn't swap in/out so it doesn't impact performance.

I will remove the O_DIRECT setting and add vm.pageout_update_period=0 to see how it goes.
 
For example with vm.pageout_update_period=600 I get this:

Mem: 3649M Active, 15G Inact, 9426M Laundry, 3097M Wired, 1567M Buf, 1136M Free
Swap: 64G Total, 3688M Used, 60G Free, 5% Inuse

With vm.pageout_update_period=0 the Laundry uses less GBs.
 
You must log in or register to reply here.
Back
Top