We've recently converted from MyISAM to BDB tables (transactions are
required and BDB is what was available), and have worked through all of
our problems except this one. Every so often (up to twice per day) mysql
decides to fill up gigs of space by writing out a seemingly endless
stream of 10MB log files. We have yet to determine what sort of actions
cause this process to start, but mysql becomes unresponsive and fills up
its 4GB partition with these binary log files
(log.0000000001->log.0000000***).
The load on the mysql server is extremely low right now, we're the only
ones on it and we're just doing simple inserts, updates, and a few
cross-table joins. The transactions are used sparingly, but they work
when called upon.
We're running 3.23.42 on FreeBSD 4.4 (specifics are below). Let me know
if there's any other information that might make the 'why' of this
problem more readily known.
We've been over and over the MySQL manual, and searched the mailing list
again and again, and we haven't found a single mention of a problem even
similar to this one. Any information would be greatly appreciated.
Thanks,
Ray
mysql> show status;
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| Aborted_clients | 0 |
| Aborted_connects | 0 |
| Bytes_received | 81843 |
| Bytes_sent | 98583 |
| Connections | 519 |
| Created_tmp_disk_tables | 11 |
| Created_tmp_tables | 19 |
| Created_tmp_files | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_delete | 2 |
| Handler_read_first | 59 |
| Handler_read_key | 690 |
| Handler_read_next | 10309 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 138 |
| Handler_read_rnd_next | 16507 |
| Handler_update | 60 |
| Handler_write | 93 |
| Key_blocks_used | 50 |
| Key_read_requests | 833 |
| Key_reads | 50 |
| Key_write_requests | 34 |
| Key_writes | 44 |
| Max_used_connections | 1 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 64 |
| Open_files | 60 |
| Open_streams | 0 |
| Opened_tables | 84 |
| Questions | 1980 |
| Select_full_join | 7 |
| Select_full_range_join | 1 |
| Select_range | 9 |
| Select_range_check | 0 |
| Select_scan | 47 |
| Slave_running | OFF |
| Slave_open_temp_tables | 0 |
| Slow_launch_threads | 0 |
| Slow_queries | 0 |
| Sort_merge_passes | 0 |
| Sort_range | 11 |
| Sort_rows | 78 |
| Sort_scan | 13 |
| Table_locks_immediate | 390 |
| Table_locks_waited | 0 |
| Threads_cached | 0 |
| Threads_created | 518 |
| Threads_connected | 1 |
| Threads_running | 1 |
| Uptime | 7131 |
+--------------------------+-------+
54 rows in set (0.00 sec)
mysql> show variables;
+---------------------------------+-------------------------------------
--------------------------+
| Variable_name | Value
|
+---------------------------------+-------------------------------------
--------------------------+
| back_log | 50
|
| basedir | /usr/local/
|
| bdb_cache_size | 8388600
|
| bdb_log_buffer_size | 32768
|
| bdb_home | /var/db/mysql/
|
| bdb_max_lock | 10000
|
| bdb_logdir |
|
| bdb_shared_data | OFF
|
| bdb_tmpdir | /var/tmp/
|
| bdb_version | Sleepycat Software: Berkeley DB
3.2.9a: (September 8, 2001) |
| binlog_cache_size | 32768
|
| character_set | latin1
|
| character_sets | latin1 dec8 dos german1 hp8 koi8_ru
latin2 swe7 usa7 cp1251 |
| | latin5 danish hebrew win1251 estonia
hungarian koi8_ukr |
| | win1251ukr greek win1250 croat
cp1257 |
| concurrent_insert | ON
|
| connect_timeout | 5
|
| datadir | /var/db/mysql/
|
| delay_key_write | ON
|
| delayed_insert_limit | 100
|
| delayed_insert_timeout | 300
|
| delayed_queue_size | 1000
|
| flush | OFF
|
| flush_time | 0
|
| have_bdb | YES
|
| have_gemini | NO
|
| have_innodb | NO
|
| have_isam | YES
|
| have_raid | NO
|
| have_ssl | NO
|
| init_file |
|
| interactive_timeout | 28800
|
| join_buffer_size | 131072
|
| key_buffer_size | 8388600
|
| language | /usr/local/share/mysql/english/
|
| large_files_support | ON
|
| log | OFF
|
| log_update | OFF
|
| log_bin | OFF
|
| log_slave_updates | OFF
|
| log_long_queries | OFF
|
| long_query_time | 10
|
| low_priority_updates | OFF
|
| lower_case_table_names | 0
|
| max_allowed_packet | 1048576
|
| max_binlog_cache_size | 4294967295
|
| max_binlog_size | 1073741824
|
| max_connections | 100
|
| max_connect_errors | 10
|
| max_delayed_threads | 20
|
| max_heap_table_size | 16777216
|
| max_join_size | 4294967295
|
| max_sort_length | 1024
|
| max_user_connections | 0
|
| max_tmp_tables | 32
|
| max_write_lock_count | 4294967295
|
| myisam_recover_options | 0
|
| myisam_max_extra_sort_file_size | 256
|
| myisam_max_sort_file_size | 2047
|
| myisam_sort_buffer_size | 8388608
|
| net_buffer_length | 16384
|
| net_read_timeout | 30
|
| net_retry_count | 1000000
|
| net_write_timeout | 60
|
| open_files_limit | 0
|
| pid_file | /var/db/mysql/seraph.pid
|
| port | 3306
|
| protocol_version | 10
|
| record_buffer | 131072
|
| record_rnd_buffer | 131072
|
| query_buffer_size | 0
|
| safe_show_database | OFF
|
| server_id | 0
|
| slave_net_timeout | 3600
|
| skip_locking | OFF
|
| skip_networking | OFF
|
| skip_show_database | OFF
|
| slow_launch_time | 2
|
| socket | /tmp/mysql.sock
|
| sort_buffer | 2097144
|
| sql_mode | 0
|
| table_cache | 64
|
| table_type | MYISAM
|
| thread_cache_size | 0
|
| thread_stack | 65536
|
| transaction_isolation | READ-COMMITTED
|
| timezone | EDT
|
| tmp_table_size | 33554432
|
| tmpdir | /var/tmp/
|
| version | 3.23.42
|
| wait_timeout | 28800
|
+---------------------------------+-------------------------------------
--------------------------+
89 rows in set (0.00 sec)
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
