The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

http high CPU load and Mysql Tuner Question

Discussion in 'Workarounds and Optimization' started by ramindia, Apr 6, 2011.

  1. ramindia

    ramindia Well-Known Member

    Joined:
    Apr 3, 2011
    Messages:
    71
    Likes Received:
    0
    Trophy Points:
    6
    Hi

    we have 2 servers

    1. web server Dedicated
    2. Mysql Dedicated Server
    cpanel 11.28
    CentOS release 5.5 (Final)
    Server version: 5.0.77 Source distribution

    Both are identical hardware config

    i5 with 8GB RAM ,500GB

    Dedicated Hosting Server

    we see some high load in Apache, iam reading the forum how to fine tune http and mysql to optimize the server for the better performance

    we have hardly 100-200request per second..i believe its not a big load on this hardware, may be iam wrong.

    i have changed some configs compare to default installation of httpd.conf, and i see http still taking lot of CPU

    top - 14:07:31 up 41 days, 22:51, 1 user, load average: 4.11, 5.53, 5.92
    Tasks: 200 total, 10 running, 188 sleeping, 2 stopped, 0 zombie
    Cpu(s): 52.3%us, 16.0%sy, 0.0%ni, 31.1%id, 0.0%wa, 0.1%hi, 0.5%si, 0.0%st
    Mem: 8133716k total, 7222716k used, 911000k free, 241780k buffers
    Swap: 2096472k total, 176k used, 2096296k free, 5067364k cached

    PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
    12492 nobody 16 0 123m 21m 4132 R 55.2 0.3 0:12.15 /usr/local/apache/bin/httpd -k start -DSSL
    12114 nobody 16 0 132m 30m 4444 R 52.3 0.4 0:17.98 /usr/local/apache/bin/httpd -k start -DSSL
    12608 nobody 15 0 121m 19m 3968 S 27.1 0.2 0:04.00 /usr/local/apache/bin/httpd -k start -DSSL
    12877 nobody 15 0 119m 16m 3936 S 21.2 0.2 0:01.80 /usr/local/apache/bin/httpd -k start -DSSL
    12840 nobody 16 0 122m 19m 4120 R 19.0 0.3 0:01.90 /usr/local/apache/bin/httpd -k start -DSSL
    12860 nobody 15 0 122m 20m 4156 S 18.0 0.3 0:02.66 /usr/local/apache/bin/httpd -k start -DSSL
    12540 nobody 16 0 125m 23m 3984 R 14.4 0.3 0:03.31 /usr/local/apache/bin/httpd -k start -DSSL
    12414 nobody 15 0 129m 27m 4384 S 8.8 0.3 0:19.95 /usr/local/apache/bin/httpd -k start -DSSL
    12858 nobody 16 0 121m 18m 4020 S 8.5 0.2 0:05.17 /usr/local/apache/bin/httpd -k start -DSSL
    12854 nobody 15 0 120m 17m 4000 S 8.2 0.2 0:01.02 /usr/local/apache/bin/httpd -k start -DSSL
    12842 nobody 15 0 119m 17m 4104 S 7.5 0.2 0:02.70 /usr/local/apache/bin/httpd -k start -DSSL
    12132 nobody 15 0 128m 26m 4120 S 6.9 0.3 0:13.21 /usr/local/apache/bin/httpd -k start -DSSL
    12806 nobody 15 0 129m 26m 4104 S 5.9 0.3 0:05.67 /usr/local/apache/bin/httpd -k start -DSSL
    12862 nobody 15 0 132m 29m 4084 S 5.9 0.4 0:06.57 /usr/local/apache/bin/httpd -k start -DSSL
    12617 nobody 15 0 120m 18m 4084 R 4.6 0.2 0:05.30 /usr/local/apache/bin/httpd -k start -DSSL
    12463 nobody 16 0 122m 19m 4128 S 4.2 0.3 0:08.70 /usr/local/apache/bin/httpd -k start -DSSL
    12619 nobody 15 0 125m 23m 4060 S 3.9 0.3 0:07.74 /usr/local/apache/bin/httpd -k start -DSSL
    12467 nobody 15 0 125m 23m 4112 R 1.6 0.3 0:23.13 /usr/local/apache/bin/httpd -k start -DSSL
    12496 nobody 15 0 129m 27m 4384 S 0.3 0.3 0:12.97 /usr/local/apache/bin/httpd -k start -DSSL


    /usr/local/cpanel/bin/rebuild_phpconf --current
    Available handlers: suphp dso cgi none
    DEFAULT PHP: 5
    PHP4 SAPI: none
    PHP5 SAPI: dso
    SUEXEC: enabled


    so i was thinking to run Mysql Tuner to see any changes and improvement i can see the performance.


    in the database server /etc/my.cnf

    [mysqld]
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    user=mysql
    # Default to using old password format for compatibility with mysql 3.x
    # clients (those using the mysqlclient10 compatibility package).
    old_passwords=1

    # Disabling symbolic-links is recommended to prevent assorted security risks;
    # to do so, uncomment this line:
    # symbolic-links=0
    [mysqld_safe]
    log-error=/var/log/mysqld.log
    pid-file=/var/run/mysqld/mysqld.pid


    i do not see any file
    ls /usr/local/cpanel/whostmgr/my.cnf
    ls: /usr/local/cpanel/whostmgr/my.cnf: No such file or directory


    so do i need to create one at /usr/local/cpanel/whostmgr/my.cnf or use the /etc/my.cnf file
    to modify the settings recommended by mysqltuner

    any advice or assistance will be appreciated
    thanks
    Ram
     
  2. ramindia

    ramindia Well-Known Member

    Joined:
    Apr 3, 2011
    Messages:
    71
    Likes Received:
    0
    Trophy Points:
    6
    I missed in the last post mysqladmin var information.

    mysqladmin var
    +---------------------------------+------------------------------------------------------------+
    | Variable_name | Value |
    +---------------------------------+------------------------------------------------------------+
    | auto_increment_increment | 1 |
    | auto_increment_offset | 1 |
    | automatic_sp_privileges | ON |
    | back_log | 50 |
    | basedir | /usr/ |
    | bdb_cache_size | 8384512 |
    | bdb_home | /var/lib/mysql/ |
    | bdb_log_buffer_size | 262144 |
    | bdb_logdir | |
    | bdb_max_lock | 10000 |
    | bdb_shared_data | OFF |
    | bdb_tmpdir | /tmp/ |
    | 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 | /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 | /var/lib/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 | NO |
    | have_bdb | YES |
    | have_blackhole_engine | NO |
    | have_compress | YES |
    | have_crypt | YES |
    | have_csv | NO |
    | have_dynamic_loading | YES |
    | have_example_engine | NO |
    | have_federated_engine | NO |
    | have_geometry | YES |
    | have_innodb | YES |
    | have_isam | NO |
    | have_merge_engine | YES |
    | have_ndbcluster | NO |
    | have_openssl | DISABLED |
    | have_ssl | DISABLED |
    | have_query_cache | YES |
    | have_raid | NO |
    | have_rtree_keys | YES |
    | have_symlink | YES |
    | hostname | x.x.x.x ( changed due to security reason) |
    | init_connect | |
    | init_file | |
    | init_slave | |
    | innodb_additional_mem_pool_size | 1048576 |
    | innodb_autoextend_increment | 8 |
    | innodb_buffer_pool_awe_mem_mb | 0 |
    | innodb_buffer_pool_size | 8388608 |
    | innodb_checksums | ON |
    | innodb_commit_concurrency | 0 |
    | innodb_concurrency_tickets | 500 |
    | innodb_data_file_path | ibdata1:10M:autoextend |
    | innodb_data_home_dir | |
    | 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 | 1 |
    | innodb_flush_method | |
    | innodb_force_recovery | 0 |
    | innodb_lock_wait_timeout | 50 |
    | innodb_locks_unsafe_for_binlog | OFF |
    | innodb_log_arch_dir | |
    | innodb_log_archive | 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_support_xa | ON |
    | innodb_sync_spin_loops | 20 |
    | innodb_table_locks | ON |
    | innodb_thread_concurrency | 8 |
    | innodb_thread_sleep_delay | 10000 |
    | interactive_timeout | 28800 |
    | join_buffer_size | 131072 |
    | key_buffer_size | 8384512 |
    | key_cache_age_threshold | 300 |
    | key_cache_block_size | 1024 |
    | key_cache_division_limit | 100 |
    | language | /usr/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 | OFF |
    | log_bin_trust_function_creators | OFF |
    | log_error | |
    | log_queries_not_using_indexes | OFF |
    | log_slave_updates | OFF |
    | log_slow_queries | OFF |
    | log_warnings | 1 |
    | long_query_time | 10 |
    | low_priority_updates | OFF |
    | lower_case_file_system | OFF |
    | lower_case_table_names | 0 |
    | max_allowed_packet | 1048576 |
    | max_binlog_cache_size | 4294963200 |
    | max_binlog_size | 1073741824 |
    | max_connect_errors | 10 |
    | max_connections | 100 |
    | max_delayed_threads | 20 |
    | max_error_count | 64 |
    | max_heap_table_size | 16777216 |
    | max_insert_delayed_threads | 20 |
    | max_join_size | 4294967295 |
    | max_length_for_sort_data | 1024 |
    | max_prepared_stmt_count | 16382 |
    | max_relay_log_size | 0 |
    | max_seeks_for_key | 4294967295 |
    | max_sort_length | 1024 |
    | max_sp_recursion_depth | 0 |
    | max_tmp_tables | 32 |
    | max_user_connections | 0 |
    | max_write_lock_count | 4294967295 |
    | multi_range_count | 256 |
    | myisam_data_pointer_size | 6 |
    | myisam_max_sort_file_size | 2146435072 |
    | myisam_recover_options | OFF |
    | myisam_repair_threads | 1 |
    | myisam_sort_buffer_size | 8388608 |
    | myisam_stats_method | nulls_unequal |
    | net_buffer_length | 16384 |
    | net_read_timeout | 30 |
    | net_retry_count | 10 |
    | net_write_timeout | 60 |
    | new | OFF |
    | old_passwords | ON |
    | open_files_limit | 1024 |
    | optimizer_prune_level | 1 |
    | optimizer_search_depth | 62 |
    | pid_file | /var/run/mysqld/mysqld.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 | 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 |
    | 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 | 0 |
    | 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 | /var/lib/mysql/mysql.sock |
    | sort_buffer_size | 2097144 |
    | sql_big_selects | ON |
    | sql_mode | |
    | sql_notes | ON |
    | sql_warnings | OFF |
    | ssl_ca | |
    | ssl_capath | |
    | ssl_cert | |
    | ssl_cipher | |
    | ssl_key | |
    | storage_engine | MyISAM |
    | sync_binlog | 0 |
    | sync_frm | ON |
    | system_time_zone | EST |
    | table_cache | 64 |
    | table_lock_wait_timeout | 50 |
    | table_type | MyISAM |
    | thread_cache_size | 0 |
    | thread_stack | 196608 |
    | time_format | %H:%i:%s |
    | time_zone | SYSTEM |
    | timed_mutexes | OFF |
    | tmp_table_size | 33554432 |
    | tmpdir | /tmp/ |
    | transaction_alloc_block_size | 8192 |
    | transaction_prealloc_size | 4096 |
    | tx_isolation | REPEATABLE-READ |
    | updatable_views_with_limit | YES |
    | version | 5.0.77 |
    | version_bdb | Sleepycat Software: Berkeley DB 4.1.24: (January 29, 2009) |
    | version_comment | Source distribution |
    | version_compile_machine | i686 |
    | version_compile_os | redhat-linux-gnu |
    | wait_timeout | 28800 |
    +---------------------------------+------------------------------------------------------------+
     
  3. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    The file to edit would be /etc/my.cnf and you should remove the values that are already the defaults in it, which makes it have the following:

    Code:
    [mysqld]
    max_connections=500
    
    [mysqld_safe]
    log-error=/var/log/mysqld.log
    pid-file=/var/run/mysqld/mysqld.pid
    I added max_connections so you would limit the number of MySQL connections on the machine to 500 at any one time. Normally, you'd want the max_connections value to match your Apache MaxClients setting, so if you have 512 for it, you'd want to change to that.

    I cannot state what else to put into it without the MySQLtuner details, so if you want to provide those as well for the output on the recommendations, I could then advise for what to put if you needed help on it.
     
  4. ramindia

    ramindia Well-Known Member

    Joined:
    Apr 3, 2011
    Messages:
    71
    Likes Received:
    0
    Trophy Points:
    6
    My Apcahe Config Looks like as below


    so can i add above in to my.cnf , restart mysql and run the mysqltuner or i need to wait for 24hours after restarting my Mysql ?
     
  5. ramindia

    ramindia Well-Known Member

    Joined:
    Apr 3, 2011
    Messages:
    71
    Likes Received:
    0
    Trophy Points:
    6
    mysqltuner.pl output


    /usr/local/cpanel/3rdparty/mysqltuner/mysqltuner.pl

    >> MySQLTuner 1.1.2 - Major Hayden <major@mhtx.net>
    >> Bug reports, feature requests, and downloads at http://mysqltuner.com/
    >> Run with '--help' for additional options and output filtering

    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.0.77
    [OK] Operating on 64-bit architecture

    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: -Archive +BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 161M (Tables: 344)
    [--] Data in InnoDB tables: 1M (Tables: 12)
    [!!] BDB is enabled but isn't being used
    [!!] Total fragmented tables: 21

    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 49d 1h 21m 33s (151M q [35.805 qps], 10M conn, TX: 4B, RX: 1B)
    [--] Reads / Writes: 99% / 1%
    [--] Total buffers: 34.0M global + 2.7M per thread (100 max threads)
    [OK] Maximum possible memory usage: 302.7M (3% of installed RAM)
    [OK] Slow queries: 0% (1K/151M)
    [!!] Highest connection usage: 100% (101/100)
    [OK] Key buffer size / total MyISAM indexes: 8.0M/9.2M
    [OK] Key buffer hit rate: 99.9% (11B cached / 12M reads)
    [!!] Query cache is disabled
    [OK] Sorts requiring temporary tables: 0% (45 temp sorts / 22M sorts)
    [!!] Joins performed without indexes: 289870
    [OK] Temporary tables created on disk: 15% (737K on disk / 4M total)
    [!!] Thread cache is disabled
    [!!] Table cache hit rate: 0% (64 open / 1M opened)
    [OK] Open file limit used: 11% (115/1K)
    [OK] Table locks acquired immediately: 99% (140M immediate / 140M locks)
    [OK] InnoDB data size / buffer pool: 1.7M/8.0M

    -------- Recommendations -----------------------------------------------------
    General recommendations:
    Add skip-bdb to MySQL configuration to disable BDB
    Run OPTIMIZE TABLE to defragment tables for better performance
    Enable the slow query log to troubleshoot bad queries
    Reduce or eliminate persistent connections to reduce connection usage
    Adjust your join queries to always utilize indexes
    Set thread_cache_size to 4 as a starting value
    Increase table_cache gradually to avoid file descriptor limits
    Variables to adjust:
    max_connections (> 100)
    wait_timeout (< 28800)
    interactive_timeout (< 28800)
    query_cache_size (>= 8M)
    join_buffer_size (> 128.0K, or always use indexes with joins)
    thread_cache_size (start at 4)
    table_cache (> 64)
     
  6. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    You could put the following into /etc/my.cnf file:

    Code:
    [mysqld]
    interactive_timeout=300
    log-slow-queries=/var/lib/mysql/slow.log
    max_connections=500
    query_cache_limit=1M
    query_cache_size=8M
    query_cache_type=1
    skip-bdb
    table_cache=80
    thread_cache_size=4
    wait_timeout=300
    Since you are using MySQL 5.0, you'll need to create the slow query log after adding the option in the /etc/my.cnf file. You'd use these commands to create the log file:

    Code:
    cd /var/lib/mysql && touch slow.log
    chown mysql:mysql slow.log && chmod 660 slow.log
    After doing the above, then restart MySQL.
     
  7. ramindia

    ramindia Well-Known Member

    Joined:
    Apr 3, 2011
    Messages:
    71
    Likes Received:
    0
    Trophy Points:
    6
    hi

    ok here it look like my new /etc/my.cnf file


    so above settings are after my addition to conf file,

    after that i need to reboot mysqld and wait for 24hours to

    re run mysqltuner.pl is this correct.



    is there any apache configuration tuning required, since i see httpd process also eating lot of CPU.
     
  8. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    For MySQL, correct that you would restart MySQL and then wait 24 hours to run mysqltuner.pl again.

    For Apache, you may want to work on it after you've spent a couple of days on optimizing MySQL to ensure you get a clear picture of which is helping with any issues.
     
  9. ramindia

    ramindia Well-Known Member

    Joined:
    Apr 3, 2011
    Messages:
    71
    Likes Received:
    0
    Trophy Points:
    6
    ok thanks for the assistance we will repost after 24hours mysqltuner.pl output and take assistance from you to tune further
    with mysql and apache to achieve best performance

    can you suggest me, with the above Hardware configuration can i easily server 50-100 request per second. for web
     
  10. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    The question on your hardware configuration and the number of simultaneous requests is outside my area of expertise, so someone else would have to help in answering that question. The hardware is not the only limiting factor for connectivity; however, since the network speeds and connections can also have an impact. Additionally, the type of requests being made can have an impact, since serving up static html pages can differ dramatically in results over dynamic ones.
     
  11. ramindia

    ramindia Well-Known Member

    Joined:
    Apr 3, 2011
    Messages:
    71
    Likes Received:
    0
    Trophy Points:
    6
    I agree with you. since i need to lookout many factors to serve optimum and best performance and easy and fast serving the pages to my visitors,

    i have been searching in google for the document to find out more on same topic to achieve the better.

    but iam running in my server more of PHP and XML files and Unlimited Bandwidth with 10MB Pipe on co-lo place, dedicated servers both.

    since i saw that you are CP team, you might have handle many clients for the same, so thought of take advice for the same.
     
  12. ramindia

    ramindia Well-Known Member

    Joined:
    Apr 3, 2011
    Messages:
    71
    Likes Received:
    0
    Trophy Points:
    6
    Hi after 24 hours here is my mysqltuner report, what is is the next step to proceed further



    ./mysqltuner.pl

    >> MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net>
    >> Bug reports, feature requests, and downloads at http://mysqltuner.com/
    >> Run with '--help' for additional options and output filtering

    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.0.77-log
    [!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM

    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 164M (Tables: 346)
    [--] Data in InnoDB tables: 1M (Tables: 12)
    [!!] Total fragmented tables: 2

    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 1d 12h 5m 2s (5M q [42.486 qps], 382K conn, TX: 2B, RX: 514M)
    [--] Reads / Writes: 99% / 1%
    [--] Total buffers: 42.0M global + 2.7M per thread (500 max threads)
    [OK] Maximum possible memory usage: 1.4G (17% of installed RAM)
    [OK] Slow queries: 0% (8/5M)
    [OK] Highest usage of available connections: 11% (59/500)
    [OK] Key buffer size / total MyISAM indexes: 8.0M/8.6M
    [OK] Key buffer hit rate: 99.8% (148M cached / 366K reads)
    [OK] Query cache efficiency: 74.6% (3M cached / 4M selects)
    [!!] Query cache prunes per day: 714413
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 194K sorts)
    [!!] Joins performed without indexes: 5419
    [OK] Temporary tables created on disk: 18% (16K on disk / 90K total)
    [OK] Thread cache hit rate: 86% (49K created / 382K connections)
    [!!] Table cache hit rate: 0% (80 open / 23K opened)
    [OK] Open file limit used: 6% (151/2K)
    [OK] Table locks acquired immediately: 99% (1M immediate / 1M locks)
    [OK] InnoDB data size / buffer pool: 1.7M/8.0M

    -------- Recommendations -----------------------------------------------------
    General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Adjust your join queries to always utilize indexes
    Increase table_cache gradually to avoid file descriptor limits
    Variables to adjust:
    query_cache_size (> 8M)
    join_buffer_size (> 128.0K, or always use indexes with joins)
    table_cache (> 80)
     
  13. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    You may want to change /etc/my.cnf to the following now:

    Code:
    [mysqld]
    interactive_timeout=300
    log-slow-queries=/var/lib/mysql/slow.log
    max_connections=500
    query_cache_limit=1M
    query_cache_size=10M
    query_cache_type=1
    skip-bdb
    table_cache=120
    thread_cache_size=4
    wait_timeout=300
    
    
    [mysqld_safe]
    log-error=/var/log/mysqld.log
    pid-file=/var/run/mysqld/mysqld.pid 
     
  14. ramindia

    ramindia Well-Known Member

    Joined:
    Apr 3, 2011
    Messages:
    71
    Likes Received:
    0
    Trophy Points:
    6
    Ok I have changed the Config as per recommendation, will wait for 24 hours and re run again the mysqltuner and ask for assistance.
     
  15. ramindia

    ramindia Well-Known Member

    Joined:
    Apr 3, 2011
    Messages:
    71
    Likes Received:
    0
    Trophy Points:
    6
    After 24hours here my mysqltuner report

    ./mysqltuner.pl

    >> MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net>
    >> Bug reports, feature requests, and downloads at http://mysqltuner.com/
    >> Run with '--help' for additional options and output filtering

    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.0.77-log
    [!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM

    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 165M (Tables: 346)
    [--] Data in InnoDB tables: 1M (Tables: 12)
    [!!] Total fragmented tables: 4

    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 9h 25m 46s (1M q [56.713 qps], 144K conn, TX: 2B, RX: 178M)
    [--] Reads / Writes: 99% / 1%
    [--] Total buffers: 44.0M global + 2.7M per thread (500 max threads)
    [OK] Maximum possible memory usage: 1.4G (17% of installed RAM)
    [OK] Slow queries: 0% (4/1M)
    [OK] Highest usage of available connections: 62% (312/500)
    [OK] Key buffer size / total MyISAM indexes: 8.0M/8.7M
    [OK] Key buffer hit rate: 99.8% (46M cached / 96K reads)
    [OK] Query cache efficiency: 78.6% (1M cached / 1M selects)
    [!!] Query cache prunes per day: 756889
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 54K sorts)
    [!!] Joins performed without indexes: 2446
    [OK] Temporary tables created on disk: 15% (4K on disk / 28K total)
    [OK] Thread cache hit rate: 82% (24K created / 144K connections)
    [!!] Table cache hit rate: 2% (120 open / 4K opened)
    [OK] Open file limit used: 8% (221/2K)
    [OK] Table locks acquired immediately: 99% (406K immediate / 407K locks)
    [OK] InnoDB data size / buffer pool: 1.7M/8.0M

    -------- Recommendations -----------------------------------------------------
    General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Adjust your join queries to always utilize indexes
    Increase table_cache gradually to avoid file descriptor limits
    Variables to adjust:
    query_cache_size (> 10M)
    join_buffer_size (> 128.0K, or always use indexes with joins)
    table_cache (> 120)



    what is the suggestions now.
     
  16. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    Please try adjusting those two variables again (query_cache_size and table_cache) this time to new values that you select. Now that you've seen me do it a couple of times, it is a great time to start using the recommendations to update the settings on your end so that you can use the script in the future and adjust those values using what you've learnt.
     
  17. ramindia

    ramindia Well-Known Member

    Joined:
    Apr 3, 2011
    Messages:
    71
    Likes Received:
    0
    Trophy Points:
    6
    I have made my own changes as per the mysqltuner suggestion
    let me know if this correct.
     
  18. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    I would avoid changing the join_buffer_size at this time, since that can cause memory issues sometimes. The other changes are fine.
     
  19. ramindia

    ramindia Well-Known Member

    Joined:
    Apr 3, 2011
    Messages:
    71
    Likes Received:
    0
    Trophy Points:
    6
    But i see mysqltuner suggest like below

     
  20. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    I'm suggesting otherwise to not edit the value or change it and already explained why in my last post :)
     
Loading...

Share This Page