Announcement

Announcement Module
Collapse
No announcement yet.

spike CPU usage

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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

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

    Comment


    • #3
      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.

      Comment


      • #4
        It are your queries, most likely.

        Comment


        • #5
          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 ?

          Comment


          • #6
            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

            Comment


            • #7
              Quote:

              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?


              Yep. Take a look at the fetched / sent rows values. If fetched rows are a lot bigger than sent rows (more than 10%), then You need to look at the table indexes. You should also take a look at the tables and queries if sent rows value is bigger than, let say, 1000.

              Comment


              • #8
                hmmm I don't appear to have a fetched value?

                Query_time: 0 Lock_time: 0 Rows_sent: 2 Rows_examined: 317

                Is all I get for each query?

                Comment


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

                  Comment


                  • #10
                    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

                    Comment


                    • #11
                      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.

                      Comment


                      • #12
                        ok I see now thanks for the help

                        Comment


                        • #13
                          mrpink wrote on Wed, 26 August 2009 17:18

                          ok I see now thanks for the help

                          Seems like you got the help you deserved; you haven't said much but haven't gotten much help either.

                          Comment

                          Working...
                          X