Bug #5611 MySQL prevents disk spin down
Description:
Problem:
MySQL keeps doing _something_ with the disk every
12-15:th second. This PREVENTS disk to spin down.
Version 4.0 ALLOWED spin down (Upgraded from 4.0 to 4.1).
Actions taken (without result):
- mysqld_safe --memlock.
- cron-directories are empty.
- root mounted with noatime option.
- tried noflushd.
- /etc/syslog.conf is filled with '-' before paths.
- "hdparm -Y /dev/hda1" (sleep mode) cause spin down, but spins up again after a few seconds.
- STOPPING mysqld ALLOWS disk to spin down.
System: Intel Pentium 166 MHz, 32 MB RAM, 1x 2 GB HDD (/dev/hda1).
Filesystem: ReiserFS.
Attaching SHOW VARIABLES + "ps -a" + free
SHOW VARIABLES:
+---------------------------------+----------------------------+
| Variable_name | Value |
+---------------------------------+----------------------------+
| back_log | 50 |
| basedir | /usr/ |
| binlog_cache_size | 32768 |
| bulk_insert_buffer_size | 8388608 |
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
| collation_connection | latin1_swedish_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
| concurrent_insert | ON |
| connect_timeout | 5 |
| datadir | /var/lib/mysql/ |
| date_format | %Y-%m-%d |
| datetime_format | %Y-%m-%d %H:%i:%s |
| default_week_format | 0 |
| delay_key_write | ON |
| delayed_insert_limit | 100 |
| delayed_insert_timeout | 300 |
| delayed_queue_size | 1000 |
| expire_logs_days | 0 |
| flush | OFF |
| flush_time | 0 |
| ft_boolean_syntax | + -><()~*:""&| |
| ft_max_word_len | 84 |
| ft_min_word_len | 4 |
| ft_query_expansion_limit | 20 |
| ft_stopword_file | (built-in) |
| group_concat_max_len | 1024 |
| have_archive | NO |
| have_bdb | NO |
| have_compress | YES |
| have_crypt | YES |
| have_csv | NO |
| have_example_engine | NO |
| have_geometry | YES |
| have_innodb | YES |
| have_isam | NO |
| have_ndbcluster | NO |
| have_openssl | NO |
| have_query_cache | YES |
| have_raid | NO |
| have_rtree_keys | YES |
| have_symlink | YES |
| init_connect | |
| init_file | |
| init_slave | |
| innodb_additional_mem_pool_size | 1048576 |
| innodb_buffer_pool_awe_mem_mb | 0 |
| innodb_buffer_pool_size | 8388608 |
| innodb_data_file_path | ibdata1:10M:autoextend |
| innodb_data_home_dir | |
| innodb_fast_shutdown | ON |
| innodb_file_io_threads | 4 |
| innodb_file_per_table | OFF |
| innodb_locks_unsafe_for_binlog | OFF |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_flush_method | |
| innodb_force_recovery | 0 |
| innodb_lock_wait_timeout | 50 |
| innodb_log_arch_dir | |
| innodb_log_archive | OFF |
| innodb_log_buffer_size | 1048576 |
| innodb_log_file_size | 5242880 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | ./ |
| innodb_max_dirty_pages_pct | 90 |
| innodb_mirrored_log_groups | 1 |
| innodb_open_files | 300 |
| innodb_thread_concurrency | 8 |
| interactive_timeout | 28800 |
| join_buffer_size | 131072 |
| key_buffer_size | 8388600 |
| key_cache_age_threshold | 300 |
| key_cache_block_size | 1024 |
| key_cache_division_limit | 100 |
| language | /usr/share/mysql/english/ |
| large_files_support | ON |
| license | GPL |
| local_infile | ON |
| locked_in_memory | ON |
| log | OFF |
| log_bin | ON |
| log_error | |
| log_slave_updates | OFF |
| log_slow_queries | OFF |
| log_update | OFF |
| log_warnings | 1 |
| long_query_time | 10 |
| low_priority_updates | OFF |
| lower_case_file_system | OFF |
| lower_case_table_names | 0 |
| max_allowed_packet | 1048576 |
| max_binlog_cache_size | 4294967295 |
| max_binlog_size | 1073741824 |
| max_connect_errors | 10 |
| max_connections | 100 |
| max_delayed_threads | 20 |
| max_error_count | 64 |
| max_heap_table_size | 16777216 |
| max_insert_delayed_threads | 20 |
| max_join_size | 4294967295 |
| max_length_for_sort_data | 1024 |
| max_relay_log_size | 0 |
| max_seeks_for_key | 4294967295 |
| max_sort_length | 1024 |
| max_tmp_tables | 32 |
| max_user_connections | 0 |
| max_write_lock_count | 4294967295 |
| myisam_data_pointer_size | 4 |
| myisam_max_extra_sort_file_size | 2147483648 |
| myisam_max_sort_file_size | 2147483647 |
| myisam_recover_options | OFF |
| myisam_repair_threads | 1 |
| myisam_sort_buffer_size | 8388608 |
| net_buffer_length | 16384 |
| net_read_timeout | 30 |
| net_retry_count | 10 |
| net_write_timeout | 60 |
| new | OFF |
| old_passwords | OFF |
| open_files_limit | 1024 |
| pid_file | /var/run/mysql/mysql.pid |
| port | 3306 |
| preload_buffer_size | 32768 |
| protocol_version | 10 |
| query_alloc_block_size | 8192 |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 0 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 8192 |
| range_alloc_block_size | 2048 |
| read_buffer_size | 131072 |
| read_only | OFF |
| read_rnd_buffer_size | 262144 |
| relay_log_purge | ON |
| rpl_recovery_rank | 0 |
| secure_auth | OFF |
| server_id | 1 |
| skip_external_locking | ON |
| skip_networking | OFF |
| skip_show_database | OFF |
| slave_net_timeout | 3600 |
| slow_launch_time | 2 |
| socket | /var/run/mysql/mysql.sock |
| sort_buffer_size | 2097144 |
| sql_mode | |
| storage_engine | MyISAM |
| sync_binlog | 0 |
| sync_frm | ON |
| system_time_zone | CEST |
| table_cache | 64 |
| table_type | MyISAM |
| thread_cache_size | 0 |
| thread_stack | 196608 |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| tmp_table_size | 33554432 |
| tmpdir | |
| transaction_alloc_block_size | 8192 |
| transaction_prealloc_size | 4096 |
| tx_isolation | REPEATABLE-READ |
| version | 4.1.4a-gamma-log |
| version_comment | Source distribution |
| version_compile_machine | i586 |
| version_compile_os | slackware-linux |
| wait_timeout | 28800 |
+---------------------------------+----------------------------+
ps -a:
PID TTY TIME CMD
1 ? 00:00:04 init
2 ? 00:00:00 keventd
3 ? 00:00:03 ksoftirqd_CPU0
4 ? 00:01:47 kswapd
5 ? 00:00:00 bdflush
6 ? 00:01:54 kupdated
10 ? 00:00:00 mdrecoveryd
11 ? 00:00:00 kreiserfsd
38 ? 00:00:00 kapmd
105 ? 00:00:00 eth0
131 ? 00:00:00 eth1
181 ? 00:00:00 khubd
197 ? 00:00:01 syslogd
200 ? 00:00:00 klogd
247 ? 00:00:00 inetd
250 ? 00:00:01 sshd
253 ? 00:00:01 dhcpd
261 ? 00:00:00 atd
265 ? 00:00:01 apmd
301 ? 00:00:00 httpd
303 ? 00:00:06 httpd
304 ? 00:00:06 httpd
305 ? 00:00:08 httpd
306 ? 00:00:11 httpd
307 ? 00:00:08 httpd
308 ? 00:00:00 smbd
315 ? 00:01:51 nmbd
327 tty2 00:00:00 agetty
328 tty3 00:00:00 agetty
329 tty4 00:00:00 agetty
330 tty5 00:00:00 agetty
331 tty6 00:00:00 agetty
404 ? 00:00:07 httpd
656 ? 00:00:02 httpd
805 ? 00:00:01 crond
911 ? 00:00:01 crond
950 ? 00:01:39 noflushd
1109 tty1 00:00:00 agetty
1874 ? 00:00:09 sshd
1876 pts/0 00:00:04 bash
1906 pts/0 00:00:02 mysqld_safe
1934 pts/0 00:00:09 mysqld
1952 pts/0 00:00:01 ps
total used free shared buffers cached
Mem: 29320 28452 868 0 712 8324
-/+ buffers/cache: 19416 9904
Swap: 0 0 0
How to repeat:
Upgraded from MySQL 4.0 to MySQL 4.1.4a-gamma-log, running Slackware 9.1. Maybe it's about low memory, or configuration (see 'Description' for variables).
Suggested fix:
(Note: if this is a configuration problem and NOT a bug, please
direct HOW to fix it as it cannot be/is hard to find on the manual pages.)
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.042 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.
Description: Problem: MySQL keeps doing _something_ with the disk every 12-15:th second. This PREVENTS disk to spin down. Version 4.0 ALLOWED spin down (Upgraded from 4.0 to 4.1). Actions taken (without result): - mysqld_safe --memlock. - cron-directories are empty. - root mounted with noatime option. - tried noflushd. - /etc/syslog.conf is filled with '-' before paths. - "hdparm -Y /dev/hda1" (sleep mode) cause spin down, but spins up again after a few seconds. - STOPPING mysqld ALLOWS disk to spin down. System: Intel Pentium 166 MHz, 32 MB RAM, 1x 2 GB HDD (/dev/hda1). Filesystem: ReiserFS. Attaching SHOW VARIABLES + "ps -a" + free SHOW VARIABLES: +---------------------------------+----------------------------+ | Variable_name | Value | +---------------------------------+----------------------------+ | back_log | 50 | | basedir | /usr/ | | binlog_cache_size | 32768 | | bulk_insert_buffer_size | 8388608 | | character_set_client | latin1 | | character_set_connection | latin1 | | character_set_database | latin1 | | character_set_results | latin1 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | | collation_connection | latin1_swedish_ci | | collation_database | latin1_swedish_ci | | collation_server | latin1_swedish_ci | | concurrent_insert | ON | | connect_timeout | 5 | | datadir | /var/lib/mysql/ | | date_format | %Y-%m-%d | | datetime_format | %Y-%m-%d %H:%i:%s | | default_week_format | 0 | | delay_key_write | ON | | delayed_insert_limit | 100 | | delayed_insert_timeout | 300 | | delayed_queue_size | 1000 | | expire_logs_days | 0 | | flush | OFF | | flush_time | 0 | | ft_boolean_syntax | + -><()~*:""&| | | ft_max_word_len | 84 | | ft_min_word_len | 4 | | ft_query_expansion_limit | 20 | | ft_stopword_file | (built-in) | | group_concat_max_len | 1024 | | have_archive | NO | | have_bdb | NO | | have_compress | YES | | have_crypt | YES | | have_csv | NO | | have_example_engine | NO | | have_geometry | YES | | have_innodb | YES | | have_isam | NO | | have_ndbcluster | NO | | have_openssl | NO | | have_query_cache | YES | | have_raid | NO | | have_rtree_keys | YES | | have_symlink | YES | | init_connect | | | init_file | | | init_slave | | | innodb_additional_mem_pool_size | 1048576 | | innodb_buffer_pool_awe_mem_mb | 0 | | innodb_buffer_pool_size | 8388608 | | innodb_data_file_path | ibdata1:10M:autoextend | | innodb_data_home_dir | | | innodb_fast_shutdown | ON | | innodb_file_io_threads | 4 | | innodb_file_per_table | OFF | | innodb_locks_unsafe_for_binlog | OFF | | innodb_flush_log_at_trx_commit | 1 | | innodb_flush_method | | | innodb_force_recovery | 0 | | innodb_lock_wait_timeout | 50 | | innodb_log_arch_dir | | | innodb_log_archive | OFF | | innodb_log_buffer_size | 1048576 | | innodb_log_file_size | 5242880 | | innodb_log_files_in_group | 2 | | innodb_log_group_home_dir | ./ | | innodb_max_dirty_pages_pct | 90 | | innodb_mirrored_log_groups | 1 | | innodb_open_files | 300 | | innodb_thread_concurrency | 8 | | interactive_timeout | 28800 | | join_buffer_size | 131072 | | key_buffer_size | 8388600 | | key_cache_age_threshold | 300 | | key_cache_block_size | 1024 | | key_cache_division_limit | 100 | | language | /usr/share/mysql/english/ | | large_files_support | ON | | license | GPL | | local_infile | ON | | locked_in_memory | ON | | log | OFF | | log_bin | ON | | log_error | | | log_slave_updates | OFF | | log_slow_queries | OFF | | log_update | OFF | | log_warnings | 1 | | long_query_time | 10 | | low_priority_updates | OFF | | lower_case_file_system | OFF | | lower_case_table_names | 0 | | max_allowed_packet | 1048576 | | max_binlog_cache_size | 4294967295 | | max_binlog_size | 1073741824 | | max_connect_errors | 10 | | max_connections | 100 | | max_delayed_threads | 20 | | max_error_count | 64 | | max_heap_table_size | 16777216 | | max_insert_delayed_threads | 20 | | max_join_size | 4294967295 | | max_length_for_sort_data | 1024 | | max_relay_log_size | 0 | | max_seeks_for_key | 4294967295 | | max_sort_length | 1024 | | max_tmp_tables | 32 | | max_user_connections | 0 | | max_write_lock_count | 4294967295 | | myisam_data_pointer_size | 4 | | myisam_max_extra_sort_file_size | 2147483648 | | myisam_max_sort_file_size | 2147483647 | | myisam_recover_options | OFF | | myisam_repair_threads | 1 | | myisam_sort_buffer_size | 8388608 | | net_buffer_length | 16384 | | net_read_timeout | 30 | | net_retry_count | 10 | | net_write_timeout | 60 | | new | OFF | | old_passwords | OFF | | open_files_limit | 1024 | | pid_file | /var/run/mysql/mysql.pid | | port | 3306 | | preload_buffer_size | 32768 | | protocol_version | 10 | | query_alloc_block_size | 8192 | | query_cache_limit | 1048576 | | query_cache_min_res_unit | 4096 | | query_cache_size | 0 | | query_cache_type | ON | | query_cache_wlock_invalidate | OFF | | query_prealloc_size | 8192 | | range_alloc_block_size | 2048 | | read_buffer_size | 131072 | | read_only | OFF | | read_rnd_buffer_size | 262144 | | relay_log_purge | ON | | rpl_recovery_rank | 0 | | secure_auth | OFF | | server_id | 1 | | skip_external_locking | ON | | skip_networking | OFF | | skip_show_database | OFF | | slave_net_timeout | 3600 | | slow_launch_time | 2 | | socket | /var/run/mysql/mysql.sock | | sort_buffer_size | 2097144 | | sql_mode | | | storage_engine | MyISAM | | sync_binlog | 0 | | sync_frm | ON | | system_time_zone | CEST | | table_cache | 64 | | table_type | MyISAM | | thread_cache_size | 0 | | thread_stack | 196608 | | time_format | %H:%i:%s | | time_zone | SYSTEM | | tmp_table_size | 33554432 | | tmpdir | | | transaction_alloc_block_size | 8192 | | transaction_prealloc_size | 4096 | | tx_isolation | REPEATABLE-READ | | version | 4.1.4a-gamma-log | | version_comment | Source distribution | | version_compile_machine | i586 | | version_compile_os | slackware-linux | | wait_timeout | 28800 | +---------------------------------+----------------------------+ ps -a: PID TTY TIME CMD 1 ? 00:00:04 init 2 ? 00:00:00 keventd 3 ? 00:00:03 ksoftirqd_CPU0 4 ? 00:01:47 kswapd 5 ? 00:00:00 bdflush 6 ? 00:01:54 kupdated 10 ? 00:00:00 mdrecoveryd 11 ? 00:00:00 kreiserfsd 38 ? 00:00:00 kapmd 105 ? 00:00:00 eth0 131 ? 00:00:00 eth1 181 ? 00:00:00 khubd 197 ? 00:00:01 syslogd 200 ? 00:00:00 klogd 247 ? 00:00:00 inetd 250 ? 00:00:01 sshd 253 ? 00:00:01 dhcpd 261 ? 00:00:00 atd 265 ? 00:00:01 apmd 301 ? 00:00:00 httpd 303 ? 00:00:06 httpd 304 ? 00:00:06 httpd 305 ? 00:00:08 httpd 306 ? 00:00:11 httpd 307 ? 00:00:08 httpd 308 ? 00:00:00 smbd 315 ? 00:01:51 nmbd 327 tty2 00:00:00 agetty 328 tty3 00:00:00 agetty 329 tty4 00:00:00 agetty 330 tty5 00:00:00 agetty 331 tty6 00:00:00 agetty 404 ? 00:00:07 httpd 656 ? 00:00:02 httpd 805 ? 00:00:01 crond 911 ? 00:00:01 crond 950 ? 00:01:39 noflushd 1109 tty1 00:00:00 agetty 1874 ? 00:00:09 sshd 1876 pts/0 00:00:04 bash 1906 pts/0 00:00:02 mysqld_safe 1934 pts/0 00:00:09 mysqld 1952 pts/0 00:00:01 ps total used free shared buffers cached Mem: 29320 28452 868 0 712 8324 -/+ buffers/cache: 19416 9904 Swap: 0 0 0 How to repeat: Upgraded from MySQL 4.0 to MySQL 4.1.4a-gamma-log, running Slackware 9.1. Maybe it's about low memory, or configuration (see 'Description' for variables). Suggested fix: (Note: if this is a configuration problem and NOT a bug, please direct HOW to fix it as it cannot be/is hard to find on the manual pages.)