Optimize /etc/my.cnf Requests

Discussion in 'Install/Configuration' started by grniyce, Jul 7, 2009.

  1. grniyce

    grniyce New Member

    I need to know if these settings are appropriate for a high sql usage environment running multiple forums. Please let me know if there are any areas you would recommend changing or tweaking. My load averages are typically 1-4%, however some sites are spiking 40% cpu usage. It would help to know if there are any cache'ing tricks I can use, or other methods which will help in using more memory and less cpu in the environment(s) I am speaking of. Any assistance is much appreciated. Obviously I'm using the latest LSWS.

    My server details are:
    Centos 5.3 (32 bit)
    Dual Xeon Quad Cores 2.66ghz
    4gb Ram
    10mbps with up to 100mbps burst

    Code:
    [mysqld]
    tmpdir=/home/sqltemp
    datadir=/var/lib/mysql
    local-infile = 0
    skip-locking
    skip-innodb
    skip-bdb
    safe-show-database
    max_connections = 800
    key_buffer = 64M
    myisam_sort_buffer_size = 64M
    join_buffer_size = 1M
    read_buffer_size = 2M
    sort_buffer_size = 2M
    read_rnd_buffer_size = 2M
    table_cache = 1024
    record_buffer = 1M
    thread_cache_size = 128
    wait_timeout = 30
    connect_timeout = 10
    interactive_timeout = 10
    tmp_table_size = 64M
    max_heap_table_size = 64M
    max_allowed_packet = 16M
    max_connect_errors = 10
    query_cache_limit = 1M
    query_cache_size = 64M
    query_cache_type = 1
    thread_concurrency = 4
    default-storage-engine = MyISAM
    
    [mysqld_safe]
    open_files_limit = 8192
    
    [mysqldump]
    quick
    max_allowed_packet = 100M
    
    [myisamchk]
    key_buffer = 64M
    sort_buffer = 64M
    read_buffer = 16M
    write_buffer = 16M
  2. Elfz

    Elfz New Member

    I personally use the following on my 4GB Ram Server which keeps mysql and everything running fast:

    Code:
    [mysqld]
    max_connections = 2500
    socket = /var/lib/mysql/mysql.sock
    key_buffer = 4096M
    table_cache = 36272
    open_files_limit = 16344
    join_buffer_size = 32M
    read_buffer_size = 32M
    sort_buffer_size = 32M
    tmp_table_size = 1024M
    read_rnd_buffer_size = 32M
    max_heap_table_size = 512M
    myisam_sort_buffer_size = 1024M
    thread_cache_size = 1024
    thread_cache = 8192
    query_cache_type = 1
    query_cache_limit = 512M
    query_cache_size = 2048M
    thread_concurrency = 16
    wait_timeout = 3000
    connect_timeout = 1200
    interactive_timeout = 1200
    long_query_time = 1
    log-slow-queries = /var/log/mysqlslowqueries.log
    max_allowed_packet = 256M
    Last edited: Jul 12, 2009
  3. h0tz

    h0tz New Member

    Thanks Elfz, that was a nice config file.
  4. Elfz

    Elfz New Member

    np mate glad i could help
  5. bhanuprasad1981

    bhanuprasad1981 New Member

    my /etc/my.cnf misses a lot of options shown above ? anything wrong ?
  6. anewday

    anewday Moderator

    ^ Nothing to worry about, if it's missing then it's likely to use the default values.
  7. brrr

    brrr New Member

    Haha. Very funny.

    You have a server with 4GB RAM in total, and yet you tell MySQL to allocate:

    - 4GB for the key_buffer
    - 2GB for the query cache
    - 1GB for myisam_sort_buffer_size
    + setting aside huge amounts of RAM for other variables
    + setting hugely long wait and connect timeouts
    + setting hugely high max connections...

    Enjoy your server choking up from too many open connections held open for too long, and from swapping itself extensively to disk.
  8. melinite

    melinite New Member

    Those settings apply to myisam tables, but who uses myisam these days? Innodb has evolved enough that the performance differences are negligable unless you have 1000+ shared users on same db, innodb supports transactions too.

    And yes agreed with brr, WTF kind of configuration is that @Elfz LOL.
    Hopefully no one actually tried it.;)
  9. grniyce

    grniyce New Member

    can you all comment on how mine looks? the first post?
  10. brrr

    brrr New Member

    Lrn2google.

    This all has hardly anything to do with LSWS.
  11. MindTooth

    MindTooth New Member

  12. grniyce

    grniyce New Member

    You are entirely incorrect, as many people (in fact ALL I know) switch to LSWS to ensure the stability of their forums which are high in SQL queries and usage.

    For this reason, the ability to effectively quantify the settings which are most compatible for the hardware, software, and hosting environment in combination with LSWS DOES MAKE it a valuable resource for all users.

    Someone with a blank "my.cnf" that runs a server hosting 20 high usage vBulletin sites will NOT experience the true capabilities of LSWS unless they understand that their MySQL configuration should also be optimized.

    I would suggest you use your knowledge to help others rather than make pointless posts that berate the community and posts which can and DO help a large majority of unknowing clients.

    Regards,

    Ant
  13. anewday

    anewday Moderator

Share This Page