Shawn Green wrote:
What you might be seeing is the "purge" process of the InnoDB engine.
http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html#option_mysqld_innodb_max_purge_lag
and from
http://dev.mysql.com/doc/refman/5.0/en/innodb-multi-versioning.html
>>In a scenario where the user inserts and deletes rows in smallish
batches at about the same rate in the table, it is possible that the
purge thread starts to lag behind, and the table grows bigger and
bigger, making everything disk-bound and very slow. Even if the table
carries just 10MB of useful data, it may grow to occupy 10GB with all
the “dead” rows.<<
What happens during idle times is the purge thread takes over for a
short period of time and completes its work. However if you have a huge
purge backlog, that "short period of time" becomes noticably large.
I recommend reducing your innodb_max_purge_lag variable to something
that will prevent as large a backlog as you are seeing today.
Sorry to hijack the thread but I'm experiencing a similar problem, and
innodb_max_purge_lag is set to 0.
The database appears to be idle as far as show processlist goes, but
it's using 100% CPU and load averages are currently 5.31, 4.70, 4.65
(it's a dual-CPU machine). The server is dedicated to MySQL and has
nothing else running on it.
What's really strange is that the database is responsive. It's not
causing the website to slow down at all, but at the same time it's
worrying me greatly.
I've tried stopping and restarting MySQL. I haven't tried rebooting the
machine yet because I don't see how that would help (and I'm currently
quite proud of the 837 days uptime).
I've appended the output from show variables below. The server has 8G of
RAM and is being used like this...
Mem: 8016796k total, 7978944k used, 37852k free, 100696k buffers
Swap: 2048248k total, 652k used, 2047596k free, 4574884k cached
And finally, in top the MySQL process looks like this...
29988 mysql 15 0 1662m 1.5g 4956 S 99.9 19.3 116418:22 mysqld
Any help would be appreciated.
-Stut
--
http://stut.net/
| back_log | 50
| basedir | /usr/
| bdb_cache_size
| 8388600 | bdb_home
| /var/lib/mysql/
| bdb_log_buffer_size | 32768
| bdb_logdir |
| bdb_max_lock
| 10000 |
bdb_shared_data | OFF
| bdb_tmpdir | /tmp/
| 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 | YES
| have_blackhole_engine | NO
| have_compress
| YES |
have_crypt | YES
| have_csv | NO
| have_example_engine
| NO | have_geometry
| YES
| have_innodb | YES
| have_isam | YES
| have_ndbcluster
| NO |
have_openssl | YES
| 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_autoextend_increment | 8
| innodb_buffer_pool_awe_mem_mb | 0
| innodb_buffer_pool_size
| 1073741824 |
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_flush_log_at_trx_commit
| 1 |
innodb_flush_method |
| innodb_force_recovery | 0
| innodb_lock_wait_timeout
| 50 |
innodb_locks_unsafe_for_binlog | OFF
| innodb_log_arch_dir |
| innodb_log_archive
| OFF |
innodb_log_buffer_size | 33554432
| innodb_log_file_size | 5242880
| innodb_log_files_in_group
| 2 |
innodb_log_group_home_dir | ./
| innodb_max_dirty_pages_pct | 90
| innodb_max_purge_lag
| 0 |
innodb_mirrored_log_groups | 1
| innodb_open_files | 300
| innodb_table_locks
| ON |
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 | OFF
| log | OFF
| log_bin
| OFF | log_error
|
| log_slave_updates | OFF
| log_slow_queries | ON
| log_update
| OFF |
log_warnings | 1
| long_query_time | 5
| low_priority_updates
| OFF |
lower_case_file_system | OFF
| lower_case_table_names | 0
| max_allowed_packet
| 33553408 |
max_binlog_cache_size | 18446744073709551615
| max_binlog_size | 1073741824
| max_connect_errors
| 10 |
max_connections | 1000
| max_delayed_threads | 20
| max_error_count
| 64 |
max_heap_table_size | 16777216
| max_insert_delayed_threads | 20
| max_join_size
| 18446744073709551615 |
max_length_for_sort_data | 1024
| max_relay_log_size | 0
| max_seeks_for_key
| 18446744073709551615 |
max_sort_length | 1024
| max_tmp_tables | 32
| max_user_connections
| 0 |
max_write_lock_count | 18446744073709551615
| myisam_data_pointer_size | 4
| myisam_max_extra_sort_file_size
| 2147483648 |
myisam_max_sort_file_size | 9223372036854775807
| 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
| ON
| open_files_limit | 5010
| pid_file |
/var/run/mysqld/mysqld.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
| 268435456 |
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
| relay_log_space_limit
| 0 |
rpl_recovery_rank | 0
| secure_auth | OFF
| server_id
| 0 |
skip_external_locking | ON
| skip_networking | OFF
| skip_show_database
| OFF |
slave_net_timeout | 3600
| slave_transaction_retries | 0
| slow_launch_time
| 2 | socket
| /var/lib/mysql/mysql.sock
| sort_buffer_size | 2097144
| sql_mode |
| storage_engine
| MyISAM |
sql_notes | ON
| sql_warnings | ON
| sync_binlog
| 0 |
sync_replication | 0
| sync_replication_slave_id | 0
| sync_replication_timeout
| 0 | sync_frm
| ON
| system_time_zone | GMT
| 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.12-log
| version_bdb | Sleepycat Software: Berkeley
DB 4.1.24: (May 13, 2005)
| version_comment | Source distribution
| version_compile_machine | x86_64
| version_compile_os
| redhat-linux-gnu |
wait_timeout | 28800
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
