Bug #5611 MySQL prevents disk spin down

Submitted: 16 Sep 2004 15:23 Modified: 4 Oct 2004 11:09
Reporter: [ name withheld ] Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1.4a-gamma-log OS:Linux (Slackware Linux 9.1)
Assigned to: CPU Architecture:Any

[16 Sep 2004 15:23] [ name withheld ]
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.)
[4 Oct 2004 11:09] MySQL Verification Team
This is a consequence of swapping, 4.1.XX has additional buffers than 4.0.XX.
My Slackware box doesn't presents that issue running on Pentium III 250 MB
RAM.

Thanks

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.