Results 1 to 7 of 7

Thread: my.cnf help

  1. #1
    Join Date
    Oct 2006
    Posts
    210

    my.cnf help

    Hello,

    We are running a turbo vps having 8GB Ram, and 8CPUs, here is what we have in my.cnf

    [email protected] [~]# cat /etc/my.cnf
    #
    # This group is read both both by the client and the server
    # use it for options that affect everything
    #
    [client-server]

    #
    # include all files from the config directory
    #
    !includedir /etc/my.cnf.d
    [mysqld]
    innodb_file_per_table=1
    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=3M
    query_cache_limit=10M
    query_cache_size=600M
    query_cache_type=1
    max_user_connections=200
    max_connections=200
    thread_cache_size=128
    table_cache=1228
    key_buffer_size=1024M
    #max_connect_errors=20
    max_allowed_packet=8M
    sort_buffer_size=1M
    read_buffer_size=1M
    read_rnd_buffer_size=1M
    thread_concurrency=4
    myisam_sort_buffer_size=8M
    concurrent_insert=2
    local-infile=0
    table_definition_cache=2500
    max_heap_table_size=1024M
    tmp_table_size=1024M

    open_files_limit=10632

    #####fixed innodb options
    innodb_buffer_pool_size = 1024M
    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

    #tmpdir=/var/tmp
    [email protected] [~]#
    Pertinent to mention we have large wordpress database around 800mb, using nginxcp along with fcgi.

    Please help how to further tune it.

    Thanks.

  2. #2
    Join Date
    May 2013
    Location
    India
    Posts
    748
    You may use mysqltuner and/or mysqlprimer to check and tune your MySQL parameters. That is the best way to start tweaking the values, as it will be like unpredictable to give values without knowing actual usage.

  3. #3
    Join Date
    Dec 2007
    Location
    LocalHost
    Posts
    1,303
    I think you are running cPanel, since you are using nginxcp. Run below command in SSH to run mysqltuner
    Code:
    /usr/local/cpanel/bin/mysqltuner.pl
    YagHost - Pure SSD Hosting | Since 2007 | Average Response Time: 15 min
    Web Hosting | Reseller Hosting | Managed VPS Hosting
    99.9% Server Uptime Guarantee | 24/7 Rapid Response Tech Support | 30 Day Money Back Guarantee
    LopHost.com - Web Hosting Tutorials

  4. #4
    Join Date
    Oct 2006
    Posts
    210
    Hi,

    here is the result of tuner script:

    [email protected] [~]# ./mysqltuner.pl

    >> MySQLTuner 1.2.0 - Major Hayden <[email protected]>
    >> 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.5.34-MariaDB-log
    [OK] Operating on 64-bit architecture

    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB +Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 1G (Tables: 327)
    [--] Data in InnoDB tables: 1M (Tables: 47)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [--] Data in MEMORY tables: 0B (Tables: 3)
    [!!] Total fragmented tables: 14

    -------- Security Recommendations -------------------------------------------
    [OK] All database users have passwords assigned

    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 1d 19h 43m 8s (13M q [86.450 qps], 218K conn, TX: 21B, RX: 2B)
    [--] Reads / Writes: 99% / 1%
    [--] Total buffers: 4.1G global + 13.3M per thread (100 max threads)
    [OK] Maximum possible memory usage: 5.4G (71% of installed RAM)
    [OK] Slow queries: 0% (284/13M)
    [OK] Highest usage of available connections: 21% (21/100)
    [OK] Key buffer size / total MyISAM indexes: 2.0G/393.9M
    [OK] Key buffer hit rate: 100.0% (2B cached / 213K reads)
    [OK] Query cache efficiency: 46.5% (10M cached / 23M selects)
    [!!] Query cache prunes per day: 473328
    [OK] Sorts requiring temporary tables: 0% (468 temp sorts / 357K sorts)
    [!!] Joins performed without indexes: 68401
    [!!] Temporary tables created on disk: 27% (118K on disk / 427K total)
    [OK] Thread cache hit rate: 99% (21 created / 218K connections)
    [OK] Table cache hit rate: 39% (536 open / 1K opened)
    [OK] Open file limit used: 7% (808/10K)
    [OK] Table locks acquired immediately: 99% (2M immediate / 2M locks)
    [OK] InnoDB data size / buffer pool: 1.6M/1.0G

    -------- Recommendations -----------------------------------------------------
    General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Adjust your join queries to always utilize indexes
    Temporary table size is already large - reduce result set size
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Variables to adjust:
    query_cache_size (> 128M)
    join_buffer_size (> 10.0M, or always use indexes with joins)

    [email protected] [~]#


    and here is what we have in my.cnf

    [email protected] [~]# cat /etc/my.cnf
    #
    # This group is read both both by the client and the server
    # use it for options that affect everything
    #
    [client-server]

    #
    # include all files from the config directory
    #
    !includedir /etc/my.cnf.d
    [mysqld]
    innodb_file_per_table=1
    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=10M
    query_cache_limit=10M
    query_cache_size=128M
    query_cache_type=1
    max_user_connections=100
    max_connections=100
    thread_cache_size=128
    table_cache=2048
    key_buffer_size=2048M
    #max_connect_errors=20
    max_allowed_packet=8M
    sort_buffer_size=1M
    read_buffer_size=1M
    read_rnd_buffer_size=1M
    thread_concurrency=4
    myisam_sort_buffer_size=8M
    concurrent_insert=2
    local-infile=0
    table_definition_cache=2500
    max_heap_table_size=1024M
    tmp_table_size=1024M

    wait_timeout = 100
    interactive_timeout = 100
    open_files_limit=10632

    #####fixed innodb options
    innodb_buffer_pool_size = 1024M
    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

    #tmpdir=/var/tmp
    [email protected] [~]#

  5. #5
    Join Date
    Mar 2003
    Location
    WebHostingTalk
    Posts
    16,950
    mysqltuner is just a guide and not meant to be the exact tune for your sites.

    You need to monitor your databases and adjust the setting accordingly.
    Specially 4 You
    .
    JoneSolutions.Com ( Jones.Solutions ) is on the net 24/7 providing stable and reliable web hosting solutions and services since 2001

  6. #6
    Join Date
    Oct 2006
    Posts
    210
    Quote Originally Posted by net View Post
    mysqltuner is just a guide and not meant to be the exact tune for your sites.

    You need to monitor your databases and adjust the setting accordingly.
    Hi,

    I am not that much expert, will you be able to provide command so I may paste results over here.

  7. #7
    Join Date
    Jun 2003
    Location
    California
    Posts
    2,766
    The output from mySQL Tuner looks pretty good. You can increase the two variables it provides by a little bit, restart mySQL and wait 48 hours or so and run it again. By a little bit, I mean upping Query_cache to something like 132M instead of the current 128M.

    But I have to ask why you're doing this. Do you feel the databases are presenting a bottleneck or are you just wanting to tweak to make sure its running optimally?

    Like you, I'm not an expert in mySQL, and I've screwed up mySQL in the past trying to tweak it too much. Increase a setting too much and you can corrupt the database. If mySQL is presenting a real bottleneck, I would hire an expert or work very, very carefully by tweaking a setting by 8 or 16, restarting mySQL and then waiting at least 48 hours to check it again.

Similar Threads

  1. Is /etc/my.cnf is OK?
    By bdwebservices in forum Hosting Security and Technology
    Replies: 1
    Last Post: 10-16-2011, 05:47 AM
  2. what is the best my.cnf will be?
    By threemints in forum Hosting Security and Technology
    Replies: 9
    Last Post: 04-30-2010, 12:49 AM
  3. my.cnf ignored?
    By zoli in forum Hosting Security and Technology
    Replies: 10
    Last Post: 01-25-2010, 07:56 AM
  4. help with my.cnf
    By orlando_myers in forum Dedicated Server
    Replies: 4
    Last Post: 06-10-2006, 11:52 AM
  5. My.cnf
    By Serverplan in forum Hosting Security and Technology
    Replies: 4
    Last Post: 02-09-2004, 02:14 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •