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.

Please Help Optimize my.cnf!

Discussion in 'Workarounds and Optimization' started by sqlboost, Oct 14, 2012.

  1. sqlboost

    sqlboost Registered

    Joined:
    Oct 14, 2012
    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    Hi guys,

    I'm importing 50 large .csv files (200MB/each).

    I Would like to dedicate all resources and utilize ALL available memory / CPU on my server to get this task done ASAP!

    Processor Information: Intel(R) Xeon(R) CPU E5620 @ 2.40GHz, 8 cores

    Memory: 7GB

    #top
    Code:
    top - 23:32:33 up 2 days, 19:35,  1 user,  load average: 0.32, 0.33, 0.17
    
    Tasks:  30 total,   1 running,  26 sleeping,   3 stopped,   0 zombie
    Cpu0  :  1.1%us,  0.0%sy,  0.0%ni, 98.9%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
    Cpu1  :  0.0%us,  0.0%sy,  0.0%ni,100.0%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
    Cpu2  :  0.0%us,  0.0%sy,  0.0%ni,100.0%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
    Cpu3  :  0.0%us,  0.0%sy,  0.0%ni,100.0%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
    Cpu4  : 52.8%us,  9.9%sy,  0.0%ni, 36.6%id,  0.0%wa,  0.0%hi,  0.0%si,  0.7%st
    Cpu5  :  0.0%us,  0.0%sy,  0.0%ni,100.0%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
    Cpu6  :  0.0%us,  0.0%sy,  0.0%ni,100.0%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
    Cpu7  :  0.0%us,  0.0%sy,  0.0%ni,100.0%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
    
    Mem:   7340032k total,  5180964k used,  2159068k free,        0k buffers
    Swap:        0k total,        0k used,        0k free,  3250248k cached
    
    PID USER        PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
    2241 mysql      20   0 8313m 1.6g 6324 S 57.8 23.4   0:09.45 mysqld
    7243 apache    20   0  317m  23m 3960 S  0.0  0.3   1:00.84 httpd           
    
    mysqld memory usage is currently at around 23.4%. I would like to increase this to close to 100%.

    Here is my "my.cnf" file:

    Code:
    #1300 / minute
    [mysqld]
    datadir = /var/lib/mysql
    socket = /var/lib/mysql/mysql.sock
    user=mysql
    
    skip-external-locking
    skip-networking
    skip-name-resolve
    default-table-type=innodb
    symbolic-links=0
    
    #########################
    #  max connections 
    #########################
    max_connections = 80
    
    #########################
    # Increase until memory usage is at 90% 
    #########################
    tmp_table_size = 128M
    max_heap_table_size = 128M
    
    #########################
    # Table Cache - Set to 20% higher than the number of tables on server (>64)
    #########################
    table_cache = 100
    table_definition_cache = 400
    #3x the table_cache 
    open_files_limit = 300
    
    #########################
    # Query Cache
    #########################
    query_cache_type=1
    query_cache_size = 128M
    query_cache_limit = 148M
    
    ##########################
    # Thread Cache 
    ##########################
    thread_cache_size = 16K
    max_allowed_packet = 24M
    
    ##########################
    # Buffer - 20% of _all_ of RAM, but not more than 3G 
    ##########################
    key_buffer=64M
    key_buffer_size=512M
    
    ##########################
    # InnoDB Settings
    ##########################
    thread_concurrency = 16
    low_priority_updates=1
    concurrent_insert=1
    
    # buffer_pool_size to 80% of your total RAM
    innodb_file_per_table
    innodb_buffer_pool_size = 4G
    innodb_additional_mem_pool_size = 512M
    
    # Set _log_file_size to 25% of buffer pool size
    innodb_log_file_size = 1G
    innodb_log_buffer_size = 1G
    
    # Write logging is set to "Flush every second"
    innodb_flush_log_at_trx_commit = 1
    innodb_lock_wait_timeout = 50
    
    ##########################
    # Slow query log 
    ##########################
    long_query_time=1
    log-slow-queries=/var/log/mysql/log-slow-queries.log
    
    [mysqld_safe]
    log-error=/var/log/mysqld.log
    pid-file=/var/run/mysqld/mysqld.pid
    

    MySQLtuner
    Code:
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 12s (16 q [1.333 qps], 10 conn, TX: 9K, RX: 896)
    [--] Reads / Writes: 100% / 0%
    [--] Total buffers: 6.2G global + 2.7M per thread (80 max threads)
    [!!] Maximum possible memory usage: 6.5G (92% of installed RAM)
    [OK] Slow queries: 0% (0/16)
    [OK] Highest usage of available connections: 1% (1/80)
    [OK] Key buffer size / total MyISAM indexes: 512.0M/4.5M
    [!!] Query cache efficiency: 0.0% (0 cached / 7 selects)
    [OK] Query cache prunes per day: 0
    [OK] Temporary tables created on disk: 0% (0 on disk / 3 total)
    [OK] Thread cache hit rate: 90% (1 created / 10 connections)
    [OK] Table cache hit rate: 53% (8 open / 15 opened)
    [OK] Open file limit used: 3% (17/490)
    [OK] Table locks acquired immediately: 100% (18 immediate / 18 locks)
    [!!] Connections aborted: 20%
    [OK] InnoDB data size / buffer pool: 301.3M/4.0G
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        MySQL started within last 24 hours - recommendations may be inaccurate
        Reduce your overall MySQL memory footprint for system stability
        Your applications are not closing MySQL connections properly
    Variables to adjust:
      *** MySQL's maximum memory usage is dangerously high ***
      *** Add RAM before increasing MySQL buffer variables ***
        query_cache_limit (> 148M, or use smaller result sets)
    
    Thanks for the help!
     
Loading...

Share This Page