How to configure my.cnf for Mysql 5.1, which is using too much CPU?
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ margin-bottom:0;
}
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
| version | 5.1.66-community
mysql performance myisam mysql-5.1 my.cnf
asked Jun 5 '14 at 11:11
AamirAamir
138310
bumped to the homepage by Community♦ 13 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
|
show 5 more comments
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
| version | 5.1.66-community
mysql performance myisam mysql-5.1 my.cnf
asked Jun 5 '14 at 11:11
AamirAamir
138310
bumped to the homepage by Community♦ 13 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
3
Just a suggestion, but if you _could_ upgrade to 5.6 (and since you're using the community edition - why not?), you could then take advantage of the performance schema enhancements which are the MySQL team's efforts to remove the "vary-the-parameters-until-it-works" approach to tuning and instead put efforts like yours onto a firmer "scientific" footing. If you can find out where the system is spending its time, then you can tackle the problem.
– Vérace
Jun 5 '14 at 12:45
Have you tried mysqltuner.pl? Good thread here
– Vérace
Jun 5 '14 at 12:51
I cannot install such plugins on production. All i can do right now is changes in my.cnf
– Aamir
Jun 5 '14 at 12:58
1
You can refer Percona's Configuration Wizard to configure your MySQL server settings I hope this helps tools.percona.com/wizard
– Glenn McKay
Jun 6 '14 at 4:37
1
@Aarmir "I am having trouble in my server which is using too much CPU on database query(simple count statement)." If you post 1) the query, 2) the EXPLAIN output for the query, 3) SHOW TABLE definitions, and 4) some information about the CPUs in the server, someone can probably advise you on whether it's possible to reduce CPU usage from the query.
– James L
Sep 14 '14 at 11:48
|
show 5 more comments
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
| version | 5.1.66-community
mysql performance myisam mysql-5.1 my.cnf
asked Jun 5 '14 at 11:11
AamirAamir
138310
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
| version | 5.1.66-community
mysql performance myisam mysql-5.1 my.cnf
mysql performance myisam mysql-5.1 my.cnf
asked Jun 5 '14 at 11:11
AamirAamir
138310
asked Jun 5 '14 at 11:11
AamirAamir
138310
edited Jun 5 '14 at 12:21
Aamir
asked Jun 5 '14 at 11:11
AamirAamir
138310
asked Jun 5 '14 at 11:11
AamirAamir
138310
asked Jun 5 '14 at 11:11
AamirAamir
138310
138310
bumped to the homepage by Community♦ 13 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
bumped to the homepage by Community♦ 13 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
3
Just a suggestion, but if you _could_ upgrade to 5.6 (and since you're using the community edition - why not?), you could then take advantage of the performance schema enhancements which are the MySQL team's efforts to remove the "vary-the-parameters-until-it-works" approach to tuning and instead put efforts like yours onto a firmer "scientific" footing. If you can find out where the system is spending its time, then you can tackle the problem.
– Vérace
Jun 5 '14 at 12:45
Have you tried mysqltuner.pl? Good thread here
– Vérace
Jun 5 '14 at 12:51
I cannot install such plugins on production. All i can do right now is changes in my.cnf
– Aamir
Jun 5 '14 at 12:58
1
You can refer Percona's Configuration Wizard to configure your MySQL server settings I hope this helps tools.percona.com/wizard
– Glenn McKay
Jun 6 '14 at 4:37
1
@Aarmir "I am having trouble in my server which is using too much CPU on database query(simple count statement)." If you post 1) the query, 2) the EXPLAIN output for the query, 3) SHOW TABLE definitions, and 4) some information about the CPUs in the server, someone can probably advise you on whether it's possible to reduce CPU usage from the query.
– James L
Sep 14 '14 at 11:48
|
show 5 more comments
3
Just a suggestion, but if you _could_ upgrade to 5.6 (and since you're using the community edition - why not?), you could then take advantage of the performance schema enhancements which are the MySQL team's efforts to remove the "vary-the-parameters-until-it-works" approach to tuning and instead put efforts like yours onto a firmer "scientific" footing. If you can find out where the system is spending its time, then you can tackle the problem.
– Vérace
Jun 5 '14 at 12:45
Have you tried mysqltuner.pl? Good thread here
– Vérace
Jun 5 '14 at 12:51
I cannot install such plugins on production. All i can do right now is changes in my.cnf
– Aamir
Jun 5 '14 at 12:58
1
You can refer Percona's Configuration Wizard to configure your MySQL server settings I hope this helps tools.percona.com/wizard
– Glenn McKay
Jun 6 '14 at 4:37
1
@Aarmir "I am having trouble in my server which is using too much CPU on database query(simple count statement)." If you post 1) the query, 2) the EXPLAIN output for the query, 3) SHOW TABLE definitions, and 4) some information about the CPUs in the server, someone can probably advise you on whether it's possible to reduce CPU usage from the query.
– James L
Sep 14 '14 at 11:48
3
3
Just a suggestion, but if you _could_ upgrade to 5.6 (and since you're using the community edition - why not?), you could then take advantage of the performance schema enhancements which are the MySQL team's efforts to remove the "vary-the-parameters-until-it-works" approach to tuning and instead put efforts like yours onto a firmer "scientific" footing. If you can find out where the system is spending its time, then you can tackle the problem.
– Vérace
Jun 5 '14 at 12:45
Just a suggestion, but if you _could_ upgrade to 5.6 (and since you're using the community edition - why not?), you could then take advantage of the performance schema enhancements which are the MySQL team's efforts to remove the "vary-the-parameters-until-it-works" approach to tuning and instead put efforts like yours onto a firmer "scientific" footing. If you can find out where the system is spending its time, then you can tackle the problem.
– Vérace
Jun 5 '14 at 12:45
Have you tried mysqltuner.pl? Good thread here
– Vérace
Jun 5 '14 at 12:51
Have you tried mysqltuner.pl? Good thread here
– Vérace
Jun 5 '14 at 12:51
I cannot install such plugins on production. All i can do right now is changes in my.cnf
– Aamir
Jun 5 '14 at 12:58
I cannot install such plugins on production. All i can do right now is changes in my.cnf
– Aamir
Jun 5 '14 at 12:58
1
1
You can refer Percona's Configuration Wizard to configure your MySQL server settings I hope this helps tools.percona.com/wizard
– Glenn McKay
Jun 6 '14 at 4:37
You can refer Percona's Configuration Wizard to configure your MySQL server settings I hope this helps tools.percona.com/wizard
– Glenn McKay
Jun 6 '14 at 4:37
1
1
@Aarmir "I am having trouble in my server which is using too much CPU on database query(simple count statement)." If you post 1) the query, 2) the EXPLAIN output for the query, 3) SHOW TABLE definitions, and 4) some information about the CPUs in the server, someone can probably advise you on whether it's possible to reduce CPU usage from the query.
– James L
Sep 14 '14 at 11:48
@Aarmir "I am having trouble in my server which is using too much CPU on database query(simple count statement)." If you post 1) the query, 2) the EXPLAIN output for the query, 3) SHOW TABLE definitions, and 4) some information about the CPUs in the server, someone can probably advise you on whether it's possible to reduce CPU usage from the query.
– James L
Sep 14 '14 at 11:48
|
show 5 more comments
2 Answers
2
active
oldest
votes
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.
[mysqld]
open-files-limit=65535
expire-logs-days=14
max-allowed-packet=64M
max-connect-errors=10000
tmp-table-size=512M
max-heap-table-size=256M
query-cache-type=0
query-cache-size=0
max-connections=1000
thread-cache-size=100
open-files-limit=655350
table-definition-cache=1024
table-open-cache=2048
innodb-lock-wait-timeout=240
slow-query-log=0
long-query-time=10
log-error=/var/log/mysql-error.log
innodb-purge-batch-size=10000
innodb-rollback-on-timeout
[mysqldump]
quick
single-transaction
max_allowed_packet = 16M
[mysql]
no_auto_rehash
[myisamchk]
key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 64M
write_buffer = 64M
[mysqld_safe]
open-files-limit = 32768
myisam_recover = backup,force
answered Jun 5 '14 at 17:14
bkaratatarbkaratatar
413
add a comment |
I am sorry I did not answer this one right away. Here it goes:
Since the MySQL version was MySQL 5.1.66, there was something that could have been done to increase CPU performance the right way. Historically speaking, MySQL 5.1.38 was the first version of MySQL 5 to introduce innodb_read_io_threads and innodb_write_io_threads as configurable options.
I wrote about this many times
Sep 26, 2013: Why would one use the Innodb Plugin instead of the builtin Innodb in Mysql 5.1? (GPL)
Jun 01, 2012: I've got 16GB of ram, how should I configure MySQL Server?
May 17, 2012: MySQL - Master/Slave Replication with different server specs
The main idea would be to have installed InnoDB Plugin 1.0. to start using those options. After installing the InnoDB Plugin, I would have referred you to these posts on tuning:
Mar 16, 2012: Using multiple cores for single MySQL queries on Debian
Sep 20, 2011: Multi cores and MySQL Performance
Sep 12, 2011: Possible to make MySQL use more than one core?
May 26, 2011: About single threaded versus multithreaded databases performance
Leaving the time warp, I would say simply upgrade to MySQL 5.6 and use my tuning suggestions. I would also convert all MyISAM to InnoDB.
edited Apr 13 '17 at 12:42
Community♦
1
answered Oct 8 '15 at 20:49
RolandoMySQLDBARolandoMySQLDBA
144k24228386
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f66645%2fhow-to-configure-my-cnf-for-mysql-5-1-which-is-using-too-much-cpu%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
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.
[mysqld]
open-files-limit=65535
expire-logs-days=14
max-allowed-packet=64M
max-connect-errors=10000
tmp-table-size=512M
max-heap-table-size=256M
query-cache-type=0
query-cache-size=0
max-connections=1000
thread-cache-size=100
open-files-limit=655350
table-definition-cache=1024
table-open-cache=2048
innodb-lock-wait-timeout=240
slow-query-log=0
long-query-time=10
log-error=/var/log/mysql-error.log
innodb-purge-batch-size=10000
innodb-rollback-on-timeout
[mysqldump]
quick
single-transaction
max_allowed_packet = 16M
[mysql]
no_auto_rehash
[myisamchk]
key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 64M
write_buffer = 64M
[mysqld_safe]
open-files-limit = 32768
myisam_recover = backup,force
answered Jun 5 '14 at 17:14
bkaratatarbkaratatar
413
add a comment |
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.
[mysqld]
open-files-limit=65535
expire-logs-days=14
max-allowed-packet=64M
max-connect-errors=10000
tmp-table-size=512M
max-heap-table-size=256M
query-cache-type=0
query-cache-size=0
max-connections=1000
thread-cache-size=100
open-files-limit=655350
table-definition-cache=1024
table-open-cache=2048
innodb-lock-wait-timeout=240
slow-query-log=0
long-query-time=10
log-error=/var/log/mysql-error.log
innodb-purge-batch-size=10000
innodb-rollback-on-timeout
[mysqldump]
quick
single-transaction
max_allowed_packet = 16M
[mysql]
no_auto_rehash
[myisamchk]
key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 64M
write_buffer = 64M
[mysqld_safe]
open-files-limit = 32768
myisam_recover = backup,force
answered Jun 5 '14 at 17:14
bkaratatarbkaratatar
413
add a comment |
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.
[mysqld]
open-files-limit=65535
expire-logs-days=14
max-allowed-packet=64M
max-connect-errors=10000
tmp-table-size=512M
max-heap-table-size=256M
query-cache-type=0
query-cache-size=0
max-connections=1000
thread-cache-size=100
open-files-limit=655350
table-definition-cache=1024
table-open-cache=2048
innodb-lock-wait-timeout=240
slow-query-log=0
long-query-time=10
log-error=/var/log/mysql-error.log
innodb-purge-batch-size=10000
innodb-rollback-on-timeout
[mysqldump]
quick
single-transaction
max_allowed_packet = 16M
[mysql]
no_auto_rehash
[myisamchk]
key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 64M
write_buffer = 64M
[mysqld_safe]
open-files-limit = 32768
myisam_recover = backup,force
answered Jun 5 '14 at 17:14
bkaratatarbkaratatar
413
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.
[mysqld]
open-files-limit=65535
expire-logs-days=14
max-allowed-packet=64M
max-connect-errors=10000
tmp-table-size=512M
max-heap-table-size=256M
query-cache-type=0
query-cache-size=0
max-connections=1000
thread-cache-size=100
open-files-limit=655350
table-definition-cache=1024
table-open-cache=2048
innodb-lock-wait-timeout=240
slow-query-log=0
long-query-time=10
log-error=/var/log/mysql-error.log
innodb-purge-batch-size=10000
innodb-rollback-on-timeout
[mysqldump]
quick
single-transaction
max_allowed_packet = 16M
[mysql]
no_auto_rehash
[myisamchk]
key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 64M
write_buffer = 64M
[mysqld_safe]
open-files-limit = 32768
myisam_recover = backup,force
answered Jun 5 '14 at 17:14
bkaratatarbkaratatar
413
answered Jun 5 '14 at 17:14
bkaratatarbkaratatar
413
answered Jun 5 '14 at 17:14
bkaratatarbkaratatar
413
answered Jun 5 '14 at 17:14
bkaratatarbkaratatar
413
413
add a comment |
add a comment |
I am sorry I did not answer this one right away. Here it goes:
Since the MySQL version was MySQL 5.1.66, there was something that could have been done to increase CPU performance the right way. Historically speaking, MySQL 5.1.38 was the first version of MySQL 5 to introduce innodb_read_io_threads and innodb_write_io_threads as configurable options.
I wrote about this many times
Sep 26, 2013: Why would one use the Innodb Plugin instead of the builtin Innodb in Mysql 5.1? (GPL)
Jun 01, 2012: I've got 16GB of ram, how should I configure MySQL Server?
May 17, 2012: MySQL - Master/Slave Replication with different server specs
The main idea would be to have installed InnoDB Plugin 1.0. to start using those options. After installing the InnoDB Plugin, I would have referred you to these posts on tuning:
Mar 16, 2012: Using multiple cores for single MySQL queries on Debian
Sep 20, 2011: Multi cores and MySQL Performance
Sep 12, 2011: Possible to make MySQL use more than one core?
May 26, 2011: About single threaded versus multithreaded databases performance
Leaving the time warp, I would say simply upgrade to MySQL 5.6 and use my tuning suggestions. I would also convert all MyISAM to InnoDB.
edited Apr 13 '17 at 12:42
Community♦
1
answered Oct 8 '15 at 20:49
RolandoMySQLDBARolandoMySQLDBA
144k24228386
add a comment |
I am sorry I did not answer this one right away. Here it goes:
Since the MySQL version was MySQL 5.1.66, there was something that could have been done to increase CPU performance the right way. Historically speaking, MySQL 5.1.38 was the first version of MySQL 5 to introduce innodb_read_io_threads and innodb_write_io_threads as configurable options.
I wrote about this many times
Sep 26, 2013: Why would one use the Innodb Plugin instead of the builtin Innodb in Mysql 5.1? (GPL)
Jun 01, 2012: I've got 16GB of ram, how should I configure MySQL Server?
May 17, 2012: MySQL - Master/Slave Replication with different server specs
The main idea would be to have installed InnoDB Plugin 1.0. to start using those options. After installing the InnoDB Plugin, I would have referred you to these posts on tuning:
Mar 16, 2012: Using multiple cores for single MySQL queries on Debian
Sep 20, 2011: Multi cores and MySQL Performance
Sep 12, 2011: Possible to make MySQL use more than one core?
May 26, 2011: About single threaded versus multithreaded databases performance
Leaving the time warp, I would say simply upgrade to MySQL 5.6 and use my tuning suggestions. I would also convert all MyISAM to InnoDB.
edited Apr 13 '17 at 12:42
Community♦
1
answered Oct 8 '15 at 20:49
RolandoMySQLDBARolandoMySQLDBA
144k24228386
add a comment |
I am sorry I did not answer this one right away. Here it goes:
Since the MySQL version was MySQL 5.1.66, there was something that could have been done to increase CPU performance the right way. Historically speaking, MySQL 5.1.38 was the first version of MySQL 5 to introduce innodb_read_io_threads and innodb_write_io_threads as configurable options.
I wrote about this many times
Sep 26, 2013: Why would one use the Innodb Plugin instead of the builtin Innodb in Mysql 5.1? (GPL)
Jun 01, 2012: I've got 16GB of ram, how should I configure MySQL Server?
May 17, 2012: MySQL - Master/Slave Replication with different server specs
The main idea would be to have installed InnoDB Plugin 1.0. to start using those options. After installing the InnoDB Plugin, I would have referred you to these posts on tuning:
Mar 16, 2012: Using multiple cores for single MySQL queries on Debian
Sep 20, 2011: Multi cores and MySQL Performance
Sep 12, 2011: Possible to make MySQL use more than one core?
May 26, 2011: About single threaded versus multithreaded databases performance
Leaving the time warp, I would say simply upgrade to MySQL 5.6 and use my tuning suggestions. I would also convert all MyISAM to InnoDB.
edited Apr 13 '17 at 12:42
Community♦
1
answered Oct 8 '15 at 20:49
RolandoMySQLDBARolandoMySQLDBA
144k24228386
I am sorry I did not answer this one right away. Here it goes:
Since the MySQL version was MySQL 5.1.66, there was something that could have been done to increase CPU performance the right way. Historically speaking, MySQL 5.1.38 was the first version of MySQL 5 to introduce innodb_read_io_threads and innodb_write_io_threads as configurable options.
I wrote about this many times
Sep 26, 2013: Why would one use the Innodb Plugin instead of the builtin Innodb in Mysql 5.1? (GPL)
Jun 01, 2012: I've got 16GB of ram, how should I configure MySQL Server?
May 17, 2012: MySQL - Master/Slave Replication with different server specs
The main idea would be to have installed InnoDB Plugin 1.0. to start using those options. After installing the InnoDB Plugin, I would have referred you to these posts on tuning:
Mar 16, 2012: Using multiple cores for single MySQL queries on Debian
Sep 20, 2011: Multi cores and MySQL Performance
Sep 12, 2011: Possible to make MySQL use more than one core?
May 26, 2011: About single threaded versus multithreaded databases performance
Leaving the time warp, I would say simply upgrade to MySQL 5.6 and use my tuning suggestions. I would also convert all MyISAM to InnoDB.
edited Apr 13 '17 at 12:42
Community♦
1
answered Oct 8 '15 at 20:49
RolandoMySQLDBARolandoMySQLDBA
144k24228386
edited Apr 13 '17 at 12:42
Community♦
1
edited Apr 13 '17 at 12:42
Community♦
1
edited Apr 13 '17 at 12:42
Community♦
1
1
answered Oct 8 '15 at 20:49
RolandoMySQLDBARolandoMySQLDBA
144k24228386
answered Oct 8 '15 at 20:49
RolandoMySQLDBARolandoMySQLDBA
144k24228386
answered Oct 8 '15 at 20:49
RolandoMySQLDBARolandoMySQLDBA
144k24228386
144k24228386
add a comment |
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f66645%2fhow-to-configure-my-cnf-for-mysql-5-1-which-is-using-too-much-cpu%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
3
Just a suggestion, but if you _could_ upgrade to 5.6 (and since you're using the community edition - why not?), you could then take advantage of the performance schema enhancements which are the MySQL team's efforts to remove the "vary-the-parameters-until-it-works" approach to tuning and instead put efforts like yours onto a firmer "scientific" footing. If you can find out where the system is spending its time, then you can tackle the problem.
– Vérace
Jun 5 '14 at 12:45
Have you tried mysqltuner.pl? Good thread here
– Vérace
Jun 5 '14 at 12:51
I cannot install such plugins on production. All i can do right now is changes in my.cnf
– Aamir
Jun 5 '14 at 12:58
1
You can refer Percona's Configuration Wizard to configure your MySQL server settings I hope this helps tools.percona.com/wizard
– Glenn McKay
Jun 6 '14 at 4:37
1
@Aarmir "I am having trouble in my server which is using too much CPU on database query(simple count statement)." If you post 1) the query, 2) the EXPLAIN output for the query, 3) SHOW TABLE definitions, and 4) some information about the CPUs in the server, someone can probably advise you on whether it's possible to reduce CPU usage from the query.
– James L
Sep 14 '14 at 11:48