Fine tuning my.cnf. Help?

Discussion in 'General' started by crypted, Apr 28, 2009.

  1. crypted

    crypted New Member

    Can someone with some SQL knowledge verify my SQL my.cnf looks allright? I am trying to fine tune it as most of my websites are SQL intensive.

    Code:
    [mysqld]
    socket=/var/lib/mysql/mysql.sock
    datadir=/var/lib/mysql
    skip-locking
    skip-innodb
    # MySQL 4.x has query caching available.
    # Enable it for vast improvement and it may be all you need to tweak.
    query_cache_type=1
    query_cache_limit=1M
    query_cache_size=32M
    # max_connections=500
    # Reduced to 200 as memory will not be enough for 500 connections.
    # memory=key_buffer+(sort_buffer_size+read_buffer_size)*max_connections
    # which is now: 64 + (1 + 1) * 200 = 464 MB
    # max_connections = approx. MaxClients setting in httpd.conf file
    # Default set to 100.
    #max_connections=200
    #interactive_timeout=180
    interactive_timeout=100
    #wait_timeout=180
    #wait_timeout=100
    # Reduced wait_timeout to prevent idle clients holding connections.
    #wait_timeout=30
    wait_timeout=15
    connect_timeout=10
    # max_connect_errors is set to 10 by default
    #max_connect_errors=10
    #table_cache=256
    #table_cache=1024
    # Checked opened tables and adjusted accordingly after running for a while.
    table_cache=512
    #tmp_table_size=32M by default
    #thread_cache=128
    # Reduced it to 32 to prevent memory hogging. Also, see notes below.
    thread_cache=32
    # key_buffer=258M
    # Reduced it by checking current size of *.MYI files, see notes below.
    key_buffer=128M
    # Commented out the buffer sizes and keeping the default.
    # sort_buffer_size=2M by default.
    #sort_buffer_size=1M
    # read_buffer_size=128K by default.
    #read_buffer_size=1M
    # 1Mb of read_rnd_buffer_size for 1GB RAM -- see notes below.
    # read_rnd_buffer_size=256K by default.
    #read_rnd_buffer_size=1M
    # myisam_sort_buffer_size used for ALTER, OPTIMIZE, REPAIR TABLE commands.
    # myisam_sort_buffer_size=8M by default.
    #myisam_sort_buffer_size=64M
    # thread_concurrency = 2 * (no. of CPU)
    thread_concurrency=2
    # log slow queries is a must. Many queries that take more than 2 seconds. 
    # If so, then your tables need enhancement.
    log_slow_queries=/var/log/mysqld.slow.log
    long_query_time=2
    
    [mysql.server]
    user=mysql
    basedir=/var/lib
    
    [safe_mysqld]
    err-log=/var/log/mysqld.log
    pid-file=/var/run/mysqld/mysqld.pid
    open_files_limit=8192
    
    [mysqldump]
    quick
    max_allowed_packet=16M
    
    [mysql]
    no-auto-rehash
    # Remove the next comment character if you are not familiar with SQL
    #safe-updates
    
    [isamchk]
    key_buffer=64M
    sort_buffer=64M
    read_buffer=16M
    write_buffer=16M
    
    [myisamchk]
    key_buffer=64M
    sort_buffer=64M
    read_buffer=16M
    write_buffer=16M
    
    [mysqlhotcopy]
    interactive-timeout 
    Again, I have very intensive SQL usage on my websites as they are fully SQL driven. I'm not sure how to fine tune the system best. However, after a clean OS install and reinstall of ISPCONFIG (lost my old my.cnf in a crash) I have noticed a horrible slow down in response time of SQL. Hence trying to make the best my.cnf I can.

    Thanks!
     
  2. edge

    edge Active Member Moderator

  3. crypted

    crypted New Member

    That script isn't working. When it asks for the User/Password I assume that is the root user and its password for SQL. I gave it that and it said that it's login failed.
     
  4. falko

    falko Super Moderator ISPConfig Developer

Share This Page