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.)
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.)