MySQL Forums
Forum List  »  Performance

how to configure my.cnf for mysql 5.1, using too much cpu?
Posted by: Aamir Sohail
Date: June 05, 2014 06:15AM

I am new Database engineer. I am having trouble in my server which is using too much CPU on database query(simple count statement). I have googled about it, so i found out it depends on system configuration. So I am posting the same. Here are the following stats of my server:

32 GB Ram
2.7 TB hard drive
150 GB database size(with 2 myisam tables contains billions of record)
mysql version 5.1
Centos
And here's my my.cnf file:

#datadir=/var/lib/mysql
datadir=/home/mysql
socket=/var/lib/mysql/mysql.sock
#socket=/home/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
There is not much details I think my.cnf is not configured according to system configuration.

Please let me know what is the best way I can set parameters in my.cnf so mysql works properly.

Output of Show variable;
SHOW VARIABLES;

| Variable_name | Value |
+-----------------------------------------+-------------------------------------------------------------------------------------------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
| autocommit | ON |
| automatic_sp_privileges | ON |
| back_log | 50 |
| basedir | / |
| big_tables | OFF |
| binlog_cache_size | 32768 |
| binlog_direct_non_transactional_updates | OFF |
| binlog_format | STATEMENT |
| bulk_insert_buffer_size | 8388608 |
| 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 | 10 |
| datadir | /home/mysql/ |
| default_week_format | 0 |
| delay_key_write | ON |
| delayed_insert_limit | 100 |
| delayed_insert_timeout | 300 |
| delayed_queue_size | 1000 |
| div_precision_increment | 4 |
| engine_condition_pushdown | ON |
| error_count | 0
| event_scheduler | OFF
| expire_logs_days | 0
| flush | OFF
| flush_time | 0
| foreign_key_checks | ON
| general_log | OFF
| general_log_file | /home/mysql/localhost.log |
| group_concat_max_len | 1024
| identity | 0
| ignore_builtin_innodb | OFF
| innodb_adaptive_hash_index | ON
| innodb_additional_mem_pool_size | 1048576
| innodb_autoextend_increment | 8
| innodb_autoinc_lock_mode | 1
| innodb_buffer_pool_size | 8388608
| innodb_checksums | ON
| innodb_commit_concurrency | 0
| innodb_concurrency_tickets | 500
| innodb_data_file_path | ibdata1:10M:autoextend
| innodb_doublewrite | ON
| innodb_fast_shutdown | 1
| 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_buffer_size | 1048576 |
| 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_rollback_on_timeout | OFF
| innodb_stats_method | nulls_equal |
| innodb_stats_on_metadata | ON
| innodb_support_xa | ON
| innodb_sync_spin_loops | 20
| innodb_table_locks | ON |
| innodb_thread_concurrency | 8
| innodb_thread_sleep_delay | 10000
| innodb_use_legacy_cardinality_algorithm | ON
| insert_id | 0
| interactive_timeout | 28800
| join_buffer_size | 131072
| keep_files_on_create | OFF
| key_buffer_size | 8384512
| key_cache_age_threshold | 300
| key_cache_block_size | 1024
| key_cache_division_limit | 100
| long_query_time | 10.000000 |
| max_allowed_packet | 1048576 |
| max_binlog_cache_size | 18446744073709547520 |
| max_binlog_size | 1073741824 |
| max_connect_errors | 10 |
| max_connections | 151 |
| 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_long_data_size | 1048576 |
| 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 |
| min_examined_row_limit | 0 |
| multi_range_count | 256 |
| myisam_data_pointer_size | 6 |
| myisam_max_sort_file_size | 9223372036853727232 |
| myisam_mmap_size | 18446744073709551615 |
| myisam_recover_options | OFF |
| myisam_repair_threads | 1 |
| myisam_sort_buffer_size | 8388608 |
| myisam_stats_method | nulls_unequal |
| myisam_use_mmap | OFF |
| net_buffer_length | 16384 |
| net_read_timeout | 30 |
| net_retry_count | 10 |
| net_write_timeout | 60 |
| open_files_limit | 1024 |
| optimizer_prune_level | 1 |
| optimizer_search_depth | 62 |
| optimizer_switch | index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on |
| pid_file | /home/mysql/localhost.localdomain.pid |
| plugin_dir | /usr/lib64/mysql/plugin |
| port | 3306 |
| preload_buffer_size | 32768 |
| profiling | OFF |
| profiling_history_size | 15 |
| protocol_version | 10 |
| pseudo_thread_id | 18 |
| query_alloc_block_size | 8192 |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 0 |
| 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 |
| rpl_recovery_rank | 0 |
| secure_auth | OFF | |skip_external_locking | ON |
| slow_launch_time | 2 |
| slow_query_log | OFF |
| slow_query_log_file | /home/mysql/localhost-slow.log |
| socket | /var/lib/mysql/mysql.sock |
| sort_buffer_size | 2097144 |
| sql_auto_is_null | ON |
| sql_big_selects | ON |
| sql_big_tables | OFF |
| sql_buffer_result | OFF |
| sql_log_bin | ON |
| sql_log_off | OFF |
| sql_log_update | ON |
| sql_low_priority_updates | OFF |
| sql_max_join_size | 18446744073709551615 |
| sql_mode | |
| sql_notes | ON |
| sql_quote_show_create | ON |
| sql_safe_updates | OFF |
| sql_select_limit | 18446744073709551615 |
| sql_slave_skip_counter | |
| sql_warnings | |
| storage_engine | MyISAM |
| table_definition_cache | 256 |
| table_lock_wait_timeout | 50 |
| table_open_cache | 64 |
| table_type | MyISAM |
| thread_cache_size | 0 |
| thread_handling | one-thread-per-connection |
| thread_stack | 262144 |
|
| timestamp | 1401967364 |
| tmp_table_size | 16777216 |
| tmpdir | /tmp |
| transaction_alloc_block_size | 8192 |
| transaction_prealloc_size | 4096 |
| tx_isolation | REPEATABLE-READ

Options: ReplyQuote


Subject
Views
Written By
Posted
how to configure my.cnf for mysql 5.1, using too much cpu?
2777
June 05, 2014 06:15AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.