spike CPU usage

Hi,

I am experiencing a issue with the Mysqld process spiking its CPU usage, I was investigating a reported issue with performance with our application and noticed in top that it was jumping to 300%+ for short bursts. There are a few Mysql querys in the slow log that took a long time but theres only three max per day

here are the variables (they have been optimized)

±--------------------------------±------------------------ ----+
| Variable_name | Value |
±--------------------------------±------------------------ ----+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
| automatic_sp_privileges | ON |
| 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_filesystem | binary |
| 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 |
| completion_type | 0 |
| concurrent_insert | 1 |
| 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 |
| div_precision_increment | 4 |
| keep_files_on_create | OFF |
| engine_condition_pushdown | OFF |
| expire_logs_days | 10 |
| 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 | YES |
| have_bdb | NO |
| have_blackhole_engine | YES |
| have_compress | YES |
| have_crypt | YES |
| have_csv | YES |
| have_dynamic_loading | YES |
| have_example_engine | NO |
| have_federated_engine | YES |
| have_geometry | YES |
| have_innodb | YES |
| have_isam | NO |
| have_merge_engine | YES |
| have_ndbcluster | DISABLED |
| have_openssl | DISABLED |
| have_ssl | DISABLED |
| have_query_cache | YES |
| have_raid | NO |
| have_rtree_keys | YES |
| have_symlink | YES |
| hostname | db01 |
| 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 | 2147483648 |
| innodb_checksums | ON |
| innodb_commit_concurrency | 0 |
| innodb_concurrency_tickets | 500 |
| innodb_data_file_path | ibdata1:10M:autoextend |
| innodb_data_home_dir | |
| innodb_doublewrite | ON |
| innodb_fast_shutdown | 1 |
| innodb_file_io_threads | 4 |
| innodb_file_per_table | OFF |
| innodb_flush_log_at_trx_commit | 2 |
| innodb_flush_method | |
| innodb_force_recovery | 0 |
| innodb_lock_wait_timeout | 50 |
| innodb_locks_unsafe_for_binlog | OFF |
| innodb_log_arch_dir | /var/lib/mysql/ |
| innodb_log_archive | OFF |
| innodb_log_buffer_size | 8388608 |
| innodb_log_file_size | 524288000 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | /var/lib/mysql/ |
| innodb_max_dirty_pages_pct | 90 |
| innodb_max_purge_lag | 0 |
| innodb_mirrored_log_groups | 1 |
| innodb_open_files | 300 |
| innodb_rollback_on_timeout | OFF |
| innodb_support_xa | ON |
| innodb_sync_spin_loops | 20 |
| innodb_table_locks | ON |
| innodb_thread_concurrency | 8 |
| innodb_thread_sleep_delay | 10000 |
| interactive_timeout | 28800 |
| join_buffer_size | 131072 |
| key_buffer_size | 16777216 |
| key_cache_age_threshold | 300 |
| key_cache_block_size | 1024 |
| key_cache_division_limit | 100 |
| language | /usr/share/mysql/english/ |
| large_files_support | ON |
| large_page_size | 0 |
| large_pages | OFF |
| lc_time_names | en_US |
| license | GPL |
| local_infile | ON |
| locked_in_memory | OFF |
| log | OFF |
| log_bin | ON |
| log_bin_trust_function_creators | OFF |
| log_error | |
| log_queries_not_using_indexes | OFF |
| log_slave_updates | OFF |
| log_slow_queries | ON |
| log_warnings | 1 |
| long_query_time | 10 |
| low_priority_updates | OFF |
| lower_case_file_system | OFF |
| lower_case_table_names | 0 |
| max_allowed_packet | 16776192 |
| max_binlog_cache_size | 18446744073709551615 |
| max_binlog_size | 524288000 |
| 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_prepared_stmt_count | 16382 |
| max_relay_log_size | 0 |
| max_seeks_for_key | 18446744073709551615 |
| max_sort_length | 1024 |
| max_sp_recursion_depth | 0 |
| max_tmp_tables | 32 |
| max_user_connections | 0 |
| max_write_lock_count | 18446744073709551615 |
| multi_range_count | 256 |
| myisam_data_pointer_size | 6 |
| myisam_max_sort_file_size | 9223372036854775807 |
| myisam_recover_options | OFF |
| myisam_repair_threads | 1 |
| myisam_sort_buffer_size | 268435456 |
| myisam_stats_method | nulls_unequal |
| ndb_autoincrement_prefetch_sz | 32 |
| ndb_force_send | ON |
| ndb_use_exact_count | ON |
| ndb_use_transactions | ON |
| ndb_cache_check_time | 0 |
| ndb_connectstring | |
| net_buffer_length | 16384 |
| net_read_timeout | 30 |
| net_retry_count | 10 |
| net_write_timeout | 60 |
| new | OFF |
| old_passwords | ON |
| open_files_limit | 5000 |
| optimizer_prune_level | 1 |
| optimizer_search_depth | 62 |
| pid_file | /var/run/mysqld/mysqld.pid |
| port | 3306 |
| preload_buffer_size | 32768 |
| profiling | OFF |
| profiling_history_size | 15 |
| protocol_version | 10 |
| query_alloc_block_size | 8192 |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 134217728 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 8192 |
| range_alloc_block_size | 2048 |
| read_buffer_size | 8384512 |
| read_only | OFF |
| read_rnd_buffer_size | 33550336 |
| relay_log_purge | ON |
| relay_log_space_limit | 0 |
| rpl_recovery_rank | 0 |
| secure_auth | OFF |
| secure_file_priv | |
| server_id | 1 |
| skip_external_locking | ON |
| skip_networking | OFF |
| skip_show_database | OFF |
| slave_compressed_protocol | OFF |
| slave_load_tmpdir | /tmp/ |
| slave_net_timeout | 3600 |
| slave_skip_errors | OFF |
| slave_transaction_retries | 10 |
| slow_launch_time | 2 |
| socket | /var/run/mysqld/mysqld.sock |
| sort_buffer_size | 8388600 |
| sql_big_selects | ON |
| sql_mode | |
| sql_notes | ON |
| sql_warnings | OFF | |
| storage_engine | InnoDB |
| sync_binlog | 0 |
| sync_frm | ON |
| system_time_zone | BST |
| table_cache | 1024 |
| table_lock_wait_timeout | 50 |
| table_type | InnoDB |
| thread_cache_size | 32 |
| thread_stack | 131072 |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| tmp_table_size | 33554432 |
| tmpdir | /tmp |
| transaction_alloc_block_size | 8192 |
| transaction_prealloc_size | 4096 |
| tx_isolation | REPEATABLE-READ |
| updatable_views_with_limit | YES |
| version | 5.0.51a-3ubuntu5.1-log |
| version_comment | (Ubuntu) |
| version_compile_machine | x86_64 |
| version_compile_os | debian-linux-gnu |
| wait_timeout | 28800 |
±--------------------------------±------------------------ ----+
232 rows in set (0.00 sec)

That actual server load average is actually really low (under 1)

I am really a novice when it comes to performance tuning Mysql so its likely that it is something blazingly obvious

EDIT Apologies for the lack of indentation not sure why it is doing that

Thanks,
Will

You expect us to tell the cause based on just your configuration?

No actually I didn’t but it maybe possible that certain variables may not be correct causing issues, it certainly isn’t a hardware issue as everything is fine from what I can see (apart from high memory usage but I have always been told this is normal) I imagine there are others out there who have experienced the same issues and could suggest some things to try and resolve the issue.

It are your queries, most likely.

Please set log_queries_not_using_indexes to ON, and change long query time to 2

Does this cpu usage go with higher hdd i/o ?
And could You say which queries were running at high cpu usage ?

The slow query log is filled up with lots of query’s with a query time of under a second now I presume there ones that don’t use a index?

The spike happens so quickly (under a second) that it doesn’t appear to affect/be effected by anything else

Here is the top output

top - 13:48:25 up 134 days, 23:31, 4 users, load average: 1.04, 0.88, 0.79
Tasks: 105 total, 1 running, 104 sleeping, 0 stopped, 0 zombie
Cpu(s): 27.6%us, 0.6%sy, 0.0%ni, 70.7%id, 0.4%wa, 0.0%hi, 0.7%si, 0.0%st
Mem: 8190008k total, 8141204k used, 48804k free, 147032k buffers
Swap: 11622988k total, 444712k used, 11178276k free, 774492k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
7601 root 20 0 3290m 2.5g 5308 S 2 32.2 2357:04 java
3396 mysql 20 0 2745m 2.5g 6232 S 112 31.9 1993:01 mysqld

Which apart from the high memory usage looks fine to me

Thanks for your suggestions

Right, rows_examined
So, mysql reads 317 rows, but query limits them to 2.

Is that a bad thing I really wouldn’t know? Looking at the query’s they all appear to be the same 1 or 2 rows sent but lots of Rows examined

Rows_sent and Rows_examined should be as near as possible. The best case is rows_sent = rows_examined. The worst case is when mysql has to examine all the rows.

ok I see now thanks for the help