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
Best Answer
First of all, for that large data, you should consider upgrading MySQL 5.6 and converting your tables to InnoDB, because even though MyISAM is faster on especially reading, this feature is lost as your database grows. And your database is way more more larger than that limit. MyISAM tables use table-level locking. Based on your traffic estimates, you have close to 200 writes per second. With MyISAM, only one of these could be in progress at any time. You have to make sure that your hardware can keep up with these transaction to avoid being overrun, i.e., a single query can take no more than 5ms.
And as my.cnf, this could be a start point for your system to check performance.