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.

Database issue

Discussion in 'Workarounds and Optimization' started by lfait, Jun 18, 2014.

  1. lfait

    lfait Member

    Joined:
    Nov 14, 2013
    Messages:
    10
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    DataCenter Provider
    Hello,
    I'm facing a strange issue regarding to sql connections i'd like to get some help with it .

    The issue located between 2 servers
    1 HTTP server which is running scripts ( 15,000 ) php script
    2 SQL Server


    Now those 15,000 Scripts are supposed to do 1 Select 1 Update queries in range of 30~120 seconds , and run for about 12 Hours , the script have mysql_close and open when need to do an update in SQL .


    I have a well optimized server for SQL and replication server for it etc...


    Now scripts from Server1 ( HTTP ) are connecting directly into IP address of SQL Server , The IP of Server1 is accepted and all connections are working but i keep facing some issues with around 500 scripts from time to time .

    example of Issue :
    [18-Jun-2014 14:42:56 Europe/Berlin] PHP Warning: mysql_connect() [<a href='function.mysql-connect'>function.mysql-connect</a>]: Host 'MY HTTP SERVER IP is not allowed to connect to this MySQL server in /home/***


    Now, if i call again the same script it will function normal, but from time to time it show me that the IP is not accepted even though it's running another 14,500 scripts without issues .


    My SQL server tuneup report

    Code:
    [OK] Maximum possible memory usage: 63.5G (67% of installed RAM)
    [OK] Slow queries: 0% (213/2M)
    [OK] Highest usage of available connections: 2% (148/5000)
    [OK] Key buffer size / total MyISAM indexes: 8.0G/37.9M
    [OK] Key buffer hit rate: 99.9% (18M cached / 15K reads)
    [!!] Query cache efficiency: 4.0% (6K cached / 165K selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 152K sorts)
    [!!] Temporary tables created on disk: 49% (146K on disk / 292K total)
    [OK] Thread cache hit rate: 99% (148 created / 521K connections)
    [OK] Table cache hit rate: 97% (249 open / 256 opened)
    [OK] Open file limit used: 0% (143/25K)
    [OK] Table locks acquired immediately: 99% (1M immediate / 1M locks)
    [OK] InnoDB buffer pool / data size: 1.0G/380.0M
    [OK] InnoDB log waits: 0
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        MySQL started within last 24 hours - recommendations may be inaccurate
        Temporary table size is already large - reduce result set size
        Reduce your SELECT DISTINCT queries without LIMIT clauses
    Variables to adjust:
        query_cache_limit (> 4M, or use smaller result sets)


    That is showing i'm not using more than 2% of the connections allowed for mysql, so i doubt it's not accepting connections.


    My my.cnf settings are

    Code:
    [mysqld]
    server-id=HIDDEN
    log-bin = mysql-bin
    binlog_do_db=HIDDEN
    long_query_time         = 1
    back_log = 2048
    log-slow-queries        = /var/log/mysql/mysql-slow.log
    long_query_time= 0.1
    max_connections = 2000
    max_user_connections= 2000
    key_buffer_size = 8G
    myisam_sort_buffer_size = 8M
    myisam_max_sort_file_size = 1G
    join_buffer_size  = 64K
    wait_timeout = 90
    interactive_timeout = 90
    connect_timeout = 90
    tmp_table_size = 512M
    thread_cache_size = 2048
    max_heap_table_size = 512M
    table_open_cache = 2048
    max_connect_errors = 1000
    thread_concurrency = 96
    read_rnd_buffer_size = 8M
    bulk_insert_buffer_size = 32M
    query_cache_limit = 4M
    query_cache_size = 2048M
    query_cache_type = 1
    default-storage-engine = MyISAM
    max_write_lock_count = 128
    innodb_buffer_pool_size = 1G
    innodb_log_buffer_size= 1024M
    innodb_flush_log_at_trx_commit=2
    skip_name_resolve
    slave_net_timeout = 3600
    delayed_insert_timeout = 4200
    innodb_flush_method = O_DIRECT
    max_allowed_packet = 256M
    myisam_use_mmap=1
    
    
    [mysqld_safe]
    nice = -10
    open_files_limit = 10000
    
    
    [mysqldump]
    quick
    max_allowed_packet = 256M
    
    [myisamchk]
    sort_buffer_size = 64K
    read_buffer_size = 64K
    write_buffer_size = 64K
    
    [mysqlhotcopy]
    local-infile=0
    

    in tail -f hostname.err i can see this warning .



    Code:
    140618 15:42:55 [Warning] 'db' entry 'USERNAME\_test\_2 [email]USERNAME@replication.MY[/email] DOMAIN.com' ignored in --skip-name-resolve mode.
    140618 15:42:55 [Warning] 'db' entry 'USERNAME\_test [email]USERNAME@replication.MY[/email] DOMAIN.com' ignored in --skip-name-resolve mode.
    140618 15:42:55 [Warning] 'db' entry 'USERNAME\_test ##@replication.MY DOMAIN.com' ignored in --skip-name-resolve mode.
    Not sure if that is related but i thought i should put it .



    Also, while i'm digging i saw this Configuring MySQL For High Number of Connections per Second - MySQL Performance Blog
    and i configured my back_log to be 2048 but still on same issue.


    Any advice's ?

    Regards
     
  2. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,854
    Likes Received:
    676
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
  3. lfait

    lfait Member

    Joined:
    Nov 14, 2013
    Messages:
    10
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    DataCenter Provider
    Yes, from error_log inside the script that is the only error i'm facing now .


    the remote server is dealing about 100+ connections each 1Second, 90% are passing okay about 5% randomly are giving such errors like not allowed to connect etc..


    Is there anything i didn't change to enable more connections per second ?

    Even with back_log it's set to accept 2048 connections per 1 sec .

    Regards
     
  4. lfait

    lfait Member

    Joined:
    Nov 14, 2013
    Messages:
    10
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    DataCenter Provider
    Still struggling with this issue

    I have increase OS limitations


    Code:
    root@replication [~]# cat /proc/sys/fs/file-max
    1000000

    Code:
    root@replication [~]# ulimit -a
    core file size          (blocks, -c) 0
    data seg size           (kbytes, -d) unlimited
    scheduling priority             (-e) 0
    file size               (blocks, -f) unlimited
    pending signals                 (-i) 773762
    max locked memory       (kbytes, -l) 64
    max memory size         (kbytes, -m) unlimited
    open files                      (-n) 65503
    pipe size            (512 bytes, -p) 8
    POSIX message queues     (bytes, -q) 819200
    real-time priority              (-r) 0
    stack size              (kbytes, -s) 10240
    cpu time               (seconds, -t) unlimited
    max user processes              (-u) 773762
    virtual memory          (kbytes, -v) unlimited
    file locks                      (-x) unlimited
    root@replication [~]#


    Code:
    cat /etc/security/limits.conf
    httpd            soft    nofile          45503
    httpd            hard    nofile          55503
    mysql soft nofile 50000
    mysql hard nofile 65000
    mysql soft nproc 50000
    mysql hard nproc 65000
    Still can't find any clue why MySQL is loosing about 3000 connections randomly from 17,000 .


    Any suggestions would be great, also if a paid solution is there I'm ready for it .

    Regards
     
  5. InterServed

    InterServed Well-Known Member

    Joined:
    Jul 10, 2007
    Messages:
    255
    Likes Received:
    2
    Trophy Points:
    18
    cPanel Access Level:
    DataCenter Provider
    Hi,

    What is your MySQL version ? Personally i would try to test if you face the same problem by using MariaDB or Percona feature "Thread Pool" , do some research about it:

    Code:
    thread_handling                = pool-of-threads
     
  6. lfait

    lfait Member

    Joined:
    Nov 14, 2013
    Messages:
    10
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    DataCenter Provider


    Server version: 5.5.37-cll - MySQL Community Server (GPL)


    I guess i found some clues regarding to my issue .


    Now it seems i'm getting the max cPanel connections as each script need to have 1 SSL + 1 SQL ports for it


    Is there a way to maximize my dedicated server to handle 65500 connection and not the default cpanel which is 16xxx on my server .

    My server is 256GB ram + 40 Cores Xeons
    I'm only using about 120 GB ram and less than 15% of CPU

    I need to use all possible connections on that server .

    Regards
     
  7. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

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

Share This Page