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.

Mysql Optimization

Discussion in 'Workarounds and Optimization' started by Neme, May 25, 2014.

  1. Neme

    Neme Registered

    Joined:
    Sep 14, 2013
    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Reseller Owner
    hi,

    I have a VDS server with Intel(R) Core(TM) i5-3470 CPU @ 3.20GHz and 8 GB memory.

    One of my website have a INSERT and POST queries to another website's API then i have to check result and UPDATE my database. There isn't any problem under 1000 queries but when its larger then 1000, mysql stuck, give me "too many connections" error and need restart :/


    I know i have to wait at least 24 hours but i cant, i have restart mysql ( too many conn. ) after 1000 queries because of the other websites :/

    This is mysqltuner.pl result;

    Code:
     >>  MySQLTuner 1.3.0 - Major Hayden <major@mhtx.net>
     >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
     >>  Run with '--help' for additional options and output filtering
    [OK] Currently running supported MySQL version 5.5.36-cll
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM 
    [--] Data in MyISAM tables: 110M (Tables: 605)
    [--] Data in InnoDB tables: 65M (Tables: 272)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [!!] Total fragmented tables: 78
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 1h 14m 10s (341K q [76.716 qps], 661 conn, TX: 306M, RX: 44M)
    [--] Reads / Writes: 1% / 99%
    [--] Total buffers: 656.0M global + 4.1M per thread (400 max threads)
    [OK] Maximum possible memory usage: 2.3G (29% of installed RAM)
    [OK] Slow queries: 0% (0/341K)
    [OK] Highest usage of available connections: 25% (100/400)
    [OK] Key buffer size / total MyISAM indexes: 256.0M/30.5M
    [OK] Key buffer hit rate: 100.0% (1M cached / 397 reads)
    [OK] Query cache efficiency: 72.6% (12K cached / 17K selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 1K sorts)
    [OK] Temporary tables created on disk: 2% (68 on disk / 2K total)
    [OK] Thread cache hit rate: 84% (100 created / 661 connections)
    [OK] Table cache hit rate: 81% (1K open / 1K opened)
    [OK] Open file limit used: 25% (1K/4K)
    [OK] Table locks acquired immediately: 99% (342K immediate / 342K locks)
    [OK] InnoDB buffer pool / data size: 128.0M/65.0M
    [OK] InnoDB log waits: 0
    -------- Recommendations -----------------------------------------------------
    and this is my.cnf , i found on the net then i try to optimize for my server settings :/

    Code:
    [mysqld]
    innodb_file_per_table=1
    open_files_limit=4950
    local-infile=0
    max_connections = 400
    max_user_connections=600
    key_buffer_size = 256M
    myisam_sort_buffer_size = 64M
    read_buffer_size = 1M
    table_open_cache = 2000
    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
    
    [mysqld_safe]
    
    [mysqldump]
    quick
    max_allowed_packet = 16M
    
    [isamchk]
    key_buffer = 384M
    sort_buffer = 384M
    read_buffer = 256M
    write_buffer = 256M
    
    [myisamchk]
    key_buffer = 384M
    sort_buffer = 384M
    read_buffer = 256M
    write_buffer = 256M
    
    #### Per connection configuration ####
    sort_buffer_size = 1M
    join_buffer_size = 1M
    thread_stack = 192K
    
    Thanks.
     
  2. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,678
    Likes Received:
    649
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
Loading...

Share This Page