Announcement

Announcement Module
Collapse
No announcement yet.

High cpu utilization on solaris server

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

  • High cpu utilization on solaris server

    Hi,

    I am new to mysql, basically i am oracle dba,

    Greately appreciated if anybody suggest me on below scenerio.

    for few queries from php (which i checked it is properly using index) cpu utilization is 98%, and later server becomes hangs.

    os : sun solaris 5.9

    frontendhp

    mysql> show variables like '%version%';
    +-------------------------+------------------------------+
    | Variable_name | Value |
    +-------------------------+------------------------------+
    | protocol_version | 10 |
    | version | 5.0.77-log |
    | version_comment | MySQL Community Server (GPL) |
    | version_compile_machine | sparc |
    | version_compile_os | sun-solaris2.9 |
    +-------------------------+------------------------------+
    5 rows in set (0.00 sec)

    load averages: 7.82, 7.88, 7.44 12:36:51
    112 processes: 108 sleeping, 1 zombie, 1 stopped, 2 on cpu
    CPU states: 0.0% idle, 97.1% user, 2.9% kernel, 0.0% iowait, 0.0% swap
    Memory: 2048M real, 262M free, 1923M swap in use, 3585M swap free

    PID USERNAME THR PRI NICE SIZE RES STATE TIME CPU COMMAND
    12850 mysql 30 59 0 0K 0K cpu/1 117:34 98.81% mysqld
    372 root 6 59 0 4000K 2464K sleep 51:26 0.09% picld
    1619 root 1 59 0 80M 2512K sleep 0:05 0.01% Xsun
    643 root 24 59 0 3784K 2448K sleep 0:18 0.00% nscd
    723 root 1 59 0 2688K 1256K sleep 0:16 0.00% arraymon
    526 root 17 59 0 65M 3584K sleep 0:10 0.00% vxsvc
    586 root 1 59 0 2504K 1448K sleep 0:08 0.00% inetd
    16 root 1 59 0 9192K 4000K sleep 0:02 0.00% vxconfigd
    4344 root 2 49 0 10M 2896K sleep 0:01 0.00% dtsession
    4352 root 1 59 0 7520K 2600K sleep 0:01 0.00% sdtperfmeter
    1401 root 7 59 0 2624K 1912K sleep 0:01 0.00% mibiisa
    1 root 1 59 0 1288K 192K sleep 0:01 0.00% init
    13080 oracle 1 59 0 2264K 1336K cpu/0 0:00 0.00% top
    10332 oracle 1 60 0 0K 0K stop 0:00 0.00% mysql
    5673 root 1 39 10 4400K 1768K sleep 0:00 0.00% dtscreen



    mysql> show variables;
    +---------------------------------+------------------------- ---------------+
    | Variable_name | Value |
    +---------------------------------+------------------------- ---------------+
    | auto_increment_increment | 1 |
    | auto_increment_offset | 1 |
    | automatic_sp_privileges | ON |
    | back_log | 50 |
    | basedir | /opt/mysql/mysql/ |
    | 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 | /opt/mysql/mysql/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 | 10 |
    | datadir | /oratranslog/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 | 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 | 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 | DISABLED |
    | 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 | drportal |
    | init_connect | |
    | init_file | |
    | init_slave | |
    | innodb_additional_mem_pool_size | 104857600 |
    | innodb_autoextend_increment | 8 |
    | innodb_buffer_pool_awe_mem_mb | 0 |
    | innodb_buffer_pool_size | 1048576000 |
    | innodb_checksums | ON |
    | innodb_commit_concurrency | 0 |
    | innodb_concurrency_tickets | 500 |
    | innodb_data_file_path | ibdata1:2000M;ibdata2:10M:autoextend |
    | innodb_data_home_dir | /oratranslog/mysql/ |
    | innodb_adaptive_hash_index | ON |
    | 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 | /oratranslog/mysql/ |
    | innodb_log_archive | OFF |
    | innodb_log_buffer_size | 52428800 |
    | innodb_log_file_size | 104857600 |
    | innodb_log_files_in_group | 2 |
    | innodb_log_group_home_dir | /oratranslog/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 | 501 |
    | innodb_thread_sleep_delay | 10000 |
    | interactive_timeout | 28800 |
    | join_buffer_size | 10485760 |
    | key_buffer_size | 402653184 |
    | key_cache_age_threshold | 300 |
    | key_cache_block_size | 1024 |
    | key_cache_division_limit | 100 |
    | language | /opt/mysql/mysql/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 | 1 |
    | max_allowed_packet | 1048576 |
    | max_binlog_cache_size | 18446744073709547520 |
    | max_binlog_size | 1073741824 |
    | max_connect_errors | 10 |
    | max_connections | 200 |
    | max_delayed_threads | 20 |
    | max_error_count | 64 |
    | max_heap_table_size | 67108864 |
    | 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 | 9223372036853727232 |
    | myisam_recover_options | OFF |
    | myisam_repair_threads | 1 |
    | myisam_sort_buffer_size | 8388608 |
    | myisam_stats_method | nulls_unequal |
    | ndb_autoincrement_prefetch_sz | 1 |
    | 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 | OFF |
    | open_files_limit | 1234 |
    | optimizer_prune_level | 1 |
    | optimizer_search_depth | 62 |
    | pid_file | /oratranslog/mysql/drportal.pid |
    | plugin_dir | |
    | 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 | 52428800 |
    | query_cache_type | ON |
    | query_cache_wlock_invalidate | OFF |
    | query_prealloc_size | 8192 |
    | range_alloc_block_size | 4096 |
    | read_buffer_size | 131072 |
    | read_only | OFF |
    | read_rnd_buffer_size | 262144 |
    | relay_log | |
    | relay_log_index | |
    | relay_log_info_file | relay-log.info |
    | 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 | /tmp/mysql.sock |
    | sort_buffer_size | 10485760 |
    | sql_big_selects | ON |
    | sql_mode | |
    | sql_notes | ON |
    | sql_warnings | OFF |
    | ssl_ca | |
    | ssl_capath | |
    | ssl_cert | |
    | ssl_cipher | |
    | ssl_key | |
    | storage_engine | InnoDB |
    | sync_binlog | 0 |
    | sync_frm | ON |
    | system_time_zone | IST |
    | table_cache | 512 |
    | table_lock_wait_timeout | 50 |
    | table_type | InnoDB |
    | thread_cache_size | 8 |
    | thread_concurrency | 8 |
    | thread_stack | 262144 |
    | time_format | %H:%i:%s |
    | time_zone | SYSTEM |
    | timed_mutexes | OFF |
    | tmp_table_size | 67108864 |
    | tmpdir | /tmp/ |
    | transaction_alloc_block_size | 8192 |
    | transaction_prealloc_size | 4096 |
    | tx_isolation | REPEATABLE-READ |
    | updatable_views_with_limit | YES |
    | version | 5.0.77-log |
    | version_comment | MySQL Community Server (GPL) |
    | version_compile_machine | sparc |
    | version_compile_os | sun-solaris2.9 |
    | wait_timeout | 28800 |
    +---------------------------------+------------------------- ---------------+



    where should i concentrate to tune the mysql.

    Thanks in advance.

    Prakash GR

  • #2
    try command "show proccesslist" to see what mysql threads are doing.


    load averages: 7.82, 7.88, 7.44 12:36:51
    112 processes: 108 sleeping, 1 zombie, 1 stopped, 2 on cpu
    CPU states: 0.0% idle, 97.1% user, 2.9% kernel, 0.0% iowait, 0.0% swap
    Memory: 2048M real, 262M free, 1923M swap in use, 3585M swap free

    PID USERNAME THR PRI NICE SIZE RES STATE TIME CPU COMMAND
    12850 mysql 30 59 0 0K 0K cpu/1 117:34 98.81% mysqld


    Is it true values such 0 Kb of memory used ?
    It seams like solaris specific or sparc specific bug. To avoid build bugs, try install binary vanilla version from mysql.com

    Comment


    • #3
      prakashgrp@gmail.com wrote on Fri, 15 May 2009 17:03


      for few queries from php (which i checked it is properly using index) cpu utilization is 98%, and later server becomes hangs.


      So what does those queries look like?
      And what is the EXPLAIN output for them?

      Because if a single query consumes 100% CPU I would say that there is a problem with the query and/or the indexes, not with any of the server variables.

      Comment

      Working...
      X