1. Black Tiger

    Black Tiger Well-Known Member Affiliate Rep.

    Dec 22, 2011
    249
    hi we have problem on our server database high load problem on our server. some time 600% usage of cpu
    mysql . we use innodb and myislm both on our database . we have too many queries selected and inserted at a time.
    our server detail
    Intel Xeon E3-1230 3.20Ghz = 4core
    8GB Ram
    our my.cnf file this
    Code:
    local-infile=0
    max_connections = 300
    
    max_user_connections=300
    
    key_buffer_size = 512M
    
    myisam_sort_buffer_size = 64M
    read_buffer_size = 64M
    table_open_cache = 5000
    thread_cache_size = 384
    wait_timeout = 20
    connect_timeout = 10
    tmp_table_size = 256M
    max_heap_table_size = 128M
    max_allowed_packet = 64M
    net_buffer_length = 16384
    max_connect_errors = 10
    concurrent_insert = 2
    read_rnd_buffer_size = 786432
    bulk_insert_buffer_size = 8M
    query_cache_limit = 5M
    query_cache_size = 128M
    query_cache_type = 1
    query_prealloc_size = 262144
    query_alloc_block_size = 65535
    transaction_alloc_block_size = 8192
    transaction_prealloc_size = 4096
    max_write_lock_count = 8
    slow_query_log
    log-error
    external-locking=FALSE
    open_files_limit=50000
     
    [mysqld_safe]
    log-error=/var/log/mysqld.log
    pid-file=/var/run/mysqld/mysqld.pid
     
    [mysqldump]
    quick
    max_allowed_packet = 16M
     
    [isamchk]
    key_buffer = 512M
    
    sort_buffer = 384M
    read_buffer = 512M
    write_buffer = 256M
     
    [myisamchk]
    key_buffer = 512M
    
    sort_buffer = 384M
    read_buffer = 512M
    write_buffer = 256M
     
    #### Per connection configuration ####
    sort_buffer_size = 1M
    join_buffer_size = 1M
    thread_stack = 192K
    
    
    its high load on cpu . can any one tell me what i do . or its possible we transfer or divide load between ram and cpu?
     
  2. nignog

    nignog Member

    Aug 13, 2011
    20
    I'm no server admin but you should also look into nginx as it's helped me in the past with lowering server loads
     
  3. ILHFFS

    ILHFFS Well-Known Member

    Aug 11, 2010
    1,109
    I am not server admin but I think you have to provide the top command too this way server admin can solve your problem with this information what are you hosting and what other software you use.

    Thanks
     
  4. serveria

    serveria Member

    Oct 27, 2014
    10
    Did you try mysqltuner? Great script it should help you to tweak your mysql.
     
  5. wedge1001

    wedge1001 Well-Known Member

    May 29, 2012
    411
    this probably won't work, since it only reduces the load for www-request (and perhaps php)

    as for mysql, it is a stand-alone-process for itself, so you can't tune it, with nginx.
    though ... you can do some other things.

    e.g. install APC (for php) which will help reducing IO-Load, since it will cache the most used queries in your RAM for easier and quicker access.
    or install a caching system, that will display the users static html, instead of querying the database - though then you have to update the html-file if s.o. changes s.th.

    as for mysqltuner: yep, it's not a bad tool, use it :)
     

Share This Page