List:General Discussion« Previous MessageNext Message »
From:Stut Date:January 21 2008 4:52pm
Subject:Re: MySql at 100% CPU
View as plain text  
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 

Thread
MySql at 100% CPUBen Clewett17 Jan
  • Re: MySql at 100% CPUKevin Hunter17 Jan
    • Re: MySql at 100% CPUBen Clewett17 Jan
      • Re: MySql at 100% CPUKevin Hunter17 Jan
        • Re: MySql at 100% CPUBen Clewett18 Jan
          • Re: MySql at 100% CPUShawn Green21 Jan
            • Re: MySql at 100% CPUStut21 Jan