Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
2  
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 at 12:45
    
Have you tried mysqltuner.pl? Good thread here –  Vérace Jun 5 at 12:51
    
I cannot install such plugins on production. All i can do right now is changes in my.cnf –  Aamir Jun 5 at 12:58
    
And your test system? –  Vérace Jun 5 at 13:04
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 at 4:37

1 Answer 1

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.