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.

My.cnf config

Discussion in 'Workarounds and Optimization' started by khanbaba, Sep 10, 2013.

  1. khanbaba

    khanbaba Member

    Joined:
    Oct 12, 2006
    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    1
    Hello,

    I have Xeon 1230 v2 with 8GB of Ram, we are having Wordpress having about 800MB Database
    my.cnf configuration is :

    Code:
    [mysqld]
    back_log=300
    local-infile=0
    datadir=/var/lib/mysql
    long_query_time=2
    
    ;log_output=file
    slow_query_log
    slow_query_log_file=/var/log/mysql-slow-queries.log
    ;log_queries_not_using_indexes
    
    join_buffer_size=32M
    query_cache_limit=256M
    query_cache_size=192M
    query_cache_type=1
    max_user_connections=200
    max_connections=150
    thread_cache_size=128
    table_cache=6196
    key_buffer_size=32M
    #max_connect_errors=20
    max_allowed_packet=16M
    sort_buffer_size=2M
    read_buffer_size=8M
    read_rnd_buffer_size=1M
    thread_concurrency=4
    myisam_sort_buffer_size=16M
    concurrent_insert=2
    local-infile=0
    table_definition_cache=2500
    max_heap_table_size=256M
    tmp_table_size=256M
    
    open_files_limit=10632
    
    #####fixed innodb options
    innodb_buffer_pool_size = 1G
    innodb_write_io_threads = 8
    innodb_read_io_threads = 8
    innodb_thread_concurrency = 16
    innodb_log_buffer_size = 8M
    innodb_max_dirty_pages_pct = 90
    innodb_file_per_table
    
    #####for 5.5 trees ## Uncomment for 5.5
    innodb_buffer_pool_instances=16
    
    
    PHP handler is fcgi and we do use cache plugin and eaccelerator, also nginxcp is running. Also we are using MariaDB

    worth checking

    root@server [/usr/local/src/publicnginx]# free -m
    total used free shared buffers cached
    Mem: 7958 7869 89 0 415 4099
    -/+ buffers/cache: 3354 4603
    Swap: 2047 48 1998
    root@server [/usr/local/src/publicnginx]#


    Support suggesting to add more Ram.

    please help me figuring some value at my.cnf
     
  2. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,723
    Likes Received:
    660
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
  3. khanbaba

    khanbaba Member

    Joined:
    Oct 12, 2006
    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    1
    Code:
    root@server [~]# ./tuning-primer.sh
    
            -- MYSQL PERFORMANCE TUNING PRIMER --
                 - By: Matthew Montgomery -
    
    MySQL Version 5.5.32-MariaDB-log x86_64
    
    Uptime = 1 days 0 hrs 26 min 43 sec
    Avg. qps = 86
    Total Questions = 7586035
    Threads Connected = 3
    
    Warning: Server has not been running for at least 48hrs.
    It may not be safe to use these recommendations
    
    To find out more information on how each of these
    runtime variables effects performance visit:
    [url=http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html]MySQL :: MySQL 5.5 Reference Manual :: 5.1.4 Server System Variables[/url]
    Visit [url=http://www.mysql.com/products/enterprise/advisors.html]MySQL :: MySQL Enterprise Advisors[/url]
    for info about MySQL's Enterprise Monitoring and Advisory Service
    
    SLOW QUERIES
    The slow query log is enabled.
    Current long_query_time = 2.000000 sec.
    You have 16 out of 7586056 that take longer than 2.000000 sec. to complete
    Your long_query_time seems to be fine
    
    BINARY UPDATE LOG
    The binary update log is NOT enabled.
    You will not be able to do point in time recovery
    See [url=http://dev.mysql.com/doc/refman/5.5/en/point-in-time-recovery.html]MySQL :: MySQL 5.5 Reference Manual :: 7.5 Point-in-Time (Incremental) Recovery Using the Binary Log[/url]
    
    WORKER THREADS
    Current thread_cache_size = 128
    Current threads_cached = 25
    Current threads_per_sec = 0
    Historic threads_per_sec = 0
    Your thread_cache_size is fine
    
    MAX CONNECTIONS
    Current max_connections = 150
    Current threads_connected = 4
    Historic max_used_connections = 29
    The number of used connections is 19% of the configured maximum.
    Your max_connections variable seems to be fine.
    
    INNODB STATUS
    Current InnoDB index space = 526 M
    Current InnoDB data space = 746 M
    Current InnoDB buffer pool free = 39 %
    Current innodb_buffer_pool_size = 1.00 G
    Depending on how much space your innodb indexes take up it may be safe
    to increase this value to up to 2 / 3 of total system memory
    
    MEMORY USAGE
    Max Memory Ever Allocated : 2.46 G
    Configured Max Per-thread Buffers : 6.34 G
    Configured Max Global Buffers : 1.23 G
    Configured Max Memory Limit : 7.57 G
    Physical Memory : 7.77 G
    
    Max memory limit exceeds 90% of physical memory
    
    KEY BUFFER
    Current MyISAM index space = 129 M
    Current key_buffer_size = 32 M
    Key cache miss rate is 1 : 24976
    Key buffer free ratio = 39 %
    Your key_buffer_size seems to be too high.
    Perhaps you can use these resources elsewhere
    
    QUERY CACHE
    Query cache is enabled
    Current query_cache_size = 192 M
    Current query_cache_used = 182 M
    Current query_cache_limit = 256 M
    Current Query cache Memory fill ratio = 94.94 %
    Current query_cache_min_res_unit = 4 K
    However, 827553 queries have been removed from the query cache due to lack of memory
    Perhaps you should raise query_cache_size
    MySQL won't cache query results that are larger than query_cache_limit in size
    
    SORT OPERATIONS
    Current sort_buffer_size = 2 M
    Current read_rnd_buffer_size = 1 M
    Sort buffer seems to be fine
    
    JOINS
    ./tuning-primer.sh: line 402: export: `2097152': not a valid identifier
    Current join_buffer_size = 32.00 M
    You have had 29616 queries where a join could not use an index properly
    join_buffer_size >= 4 M
    This is not advised
    You should enable "log-queries-not-using-indexes"
    Then look for non indexed joins in the slow query log.
    
    OPEN FILES LIMIT
    Current open_files_limit = 12553 files
    The open_files_limit should typically be set to at least 2x-3x
    that of table_cache if you have heavy MyISAM usage.
    Your open_files_limit value seems to be fine
    
    TABLE CACHE
    Current table_open_cache = 6196 tables
    Current table_definition_cache = 2500 tables
    You have a total of 1620 tables
    You have 1685 open tables.
    The table_cache value seems to be fine
    
    TEMP TABLES
    Current max_heap_table_size = 256 M
    Current tmp_table_size = 256 M
    Of 248505 temp tables, 41% were created on disk
    Perhaps you should increase your tmp_table_size and/or max_heap_table_size
    to reduce the number of disk-based temporary tables
    Note! BLOB and TEXT columns are not allow in memory tables.
    If you are using these columns raising these values might not impact your
    ratio of on disk temp tables.
    
    TABLE SCANS
    Current read_buffer_size = 8 M
    Current table scan ratio = 1924 : 1
    read_buffer_size seems to be fine
    
    TABLE LOCKING
    Current Lock Wait ratio = 1 : 4001189
    Your table locking seems to be fine
    
    root@server [~]#
    root@server [~]# ./tuning-primer.sh
    
            -- MYSQL PERFORMANCE TUNING PRIMER --
                 - By: Matthew Montgomery -
    
    MySQL Version 5.5.32-MariaDB-log x86_64
    
    Uptime = 1 days 0 hrs 27 min 23 sec
    Avg. qps = 86
    Total Questions = 7589628
    Threads Connected = 5
    
    Warning: Server has not been running for at least 48hrs.
    It may not be safe to use these recommendations
    
    To find out more information on how each of these
    runtime variables effects performance visit:
    [url=http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html]MySQL :: MySQL 5.5 Reference Manual :: 5.1.4 Server System Variables[/url]
    Visit [url=http://www.mysql.com/products/enterprise/advisors.html]MySQL :: MySQL Enterprise Advisors[/url]
    for info about MySQL's Enterprise Monitoring and Advisory Service
    
    SLOW QUERIES
    The slow query log is enabled.
    Current long_query_time = 2.000000 sec.
    You have 16 out of 7589649 that take longer than 2.000000 sec. to complete
    Your long_query_time seems to be fine
    
    BINARY UPDATE LOG
    The binary update log is NOT enabled.
    You will not be able to do point in time recovery
    See [url=http://dev.mysql.com/doc/refman/5.5/en/point-in-time-recovery.html]MySQL :: MySQL 5.5 Reference Manual :: 7.5 Point-in-Time (Incremental) Recovery Using the Binary Log[/url]
    
    WORKER THREADS
    Current thread_cache_size = 128
    Current threads_cached = 26
    Current threads_per_sec = 0
    Historic threads_per_sec = 0
    Your thread_cache_size is fine
    
    MAX CONNECTIONS
    Current max_connections = 150
    Current threads_connected = 3
    Historic max_used_connections = 29
    The number of used connections is 19% of the configured maximum.
    Your max_connections variable seems to be fine.
    
    INNODB STATUS
    Current InnoDB index space = 526 M
    Current InnoDB data space = 746 M
    Current InnoDB buffer pool free = 38 %
    Current innodb_buffer_pool_size = 1.00 G
    Depending on how much space your innodb indexes take up it may be safe
    to increase this value to up to 2 / 3 of total system memory
    
    MEMORY USAGE
    Max Memory Ever Allocated : 2.46 G
    Configured Max Per-thread Buffers : 6.34 G
    Configured Max Global Buffers : 1.23 G
    Configured Max Memory Limit : 7.57 G
    Physical Memory : 7.77 G
    
    Max memory limit exceeds 90% of physical memory
    
    KEY BUFFER
    Current MyISAM index space = 129 M
    Current key_buffer_size = 32 M
    Key cache miss rate is 1 : 24981
    Key buffer free ratio = 39 %
    Your key_buffer_size seems to be too high.
    Perhaps you can use these resources elsewhere
    
    QUERY CACHE
    Query cache is enabled
    Current query_cache_size = 192 M
    Current query_cache_used = 182 M
    Current query_cache_limit = 256 M
    Current Query cache Memory fill ratio = 94.99 %
    Current query_cache_min_res_unit = 4 K
    However, 827660 queries have been removed from the query cache due to lack of memory
    Perhaps you should raise query_cache_size
    MySQL won't cache query results that are larger than query_cache_limit in size
    
    SORT OPERATIONS
    Current sort_buffer_size = 2 M
    Current read_rnd_buffer_size = 1 M
    Sort buffer seems to be fine
    
    JOINS
    ./tuning-primer.sh: line 402: export: `2097152': not a valid identifier
    Current join_buffer_size = 32.00 M
    You have had 29616 queries where a join could not use an index properly
    join_buffer_size >= 4 M
    This is not advised
    You should enable "log-queries-not-using-indexes"
    Then look for non indexed joins in the slow query log.
    
    OPEN FILES LIMIT
    Current open_files_limit = 12553 files
    The open_files_limit should typically be set to at least 2x-3x
    that of table_cache if you have heavy MyISAM usage.
    Your open_files_limit value seems to be fine
    
    TABLE CACHE
    Current table_open_cache = 6196 tables
    Current table_definition_cache = 2500 tables
    You have a total of 1620 tables
    You have 1685 open tables.
    The table_cache value seems to be fine
    
    TEMP TABLES
    Current max_heap_table_size = 256 M
    Current tmp_table_size = 256 M
    Of 248904 temp tables, 41% were created on disk
    Perhaps you should increase your tmp_table_size and/or max_heap_table_size
    to reduce the number of disk-based temporary tables
    Note! BLOB and TEXT columns are not allow in memory tables.
    If you are using these columns raising these values might not impact your
    ratio of on disk temp tables.
    
    TABLE SCANS
    Current read_buffer_size = 8 M
    Current table scan ratio = 1924 : 1
    read_buffer_size seems to be fine
    
    TABLE LOCKING
    Current Lock Wait ratio = 1 : 4003176
    Your table locking seems to be fine
    
    root@server [~]#
    
    well we do have tuning-primer sh script installed I just run that and above is the output.

    Please suggest.
     
  4. khanbaba

    khanbaba Member

    Joined:
    Oct 12, 2006
    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    1
    nobody to help :(
     
  5. thinkbot

    thinkbot Well-Known Member

    Joined:
    Oct 30, 2012
    Messages:
    326
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    correct those

    max_user_connections=75
    max_connections=150

    key_buffer_size = 150M
    join_buffer_size = 4M
    table_definition_cache = 1500
    table_open_cache = 2500

    sort_buffer_size=256K
    read_buffer_size=128K
    read_rnd_buffer_size=8M

    query_cache_limit=2M
    query_cache_size=100M

    long_query_time=0.1


    thread_concurrency=4 - remove that line


    innodb_write_io_threads = 8 - remove that line
    innodb_read_io_threads = 8 - remove that line
    innodb_thread_concurrency = 16 - remove that line

    innodb_log_buffer_size = 8M
    innodb_max_dirty_pages_pct = 90 - remove that line


    innodb_buffer_pool_instances=8

    add
    innodb_flush_log_at_trx_commit = 2
    innodb_flush_method = O_DIRECT
    innodb_log_file_size = 64M


    before restarting
    rm -rf /var/lib/mysql/ib_logfile*
    to remove old innodb log files

    you dont need any more RAM, its enough now
     
Loading...

Share This Page