Skip to main content

Database issue

lfait
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() [function.mysql-connect]: 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 [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 [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 . 140618 15:42:55 [Warning] 'db' entry 'USERNAME\_test\_2 USERNAME@replication.MY DOMAIN.com' ignored in --skip-name-resolve mode. 140618 15:42:55 [Warning] 'db' entry 'USERNAME\_test USERNAME@replication.MY 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 [url=http://www.mysqlperformanceblog.com/2012/01/06/mysql-high-number-connections-per-secon/]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

Comments

6 comments

Date Votes
  • cPanelMichael
    Hello :) Are those the only entries you could find in the MySQL error log? Do the time stamps match when the issue occurred? Thank you.
    0
  • lfait
    [quote="cPanelMichael, post: 1668412">Hello :) Are those the only entries you could find in the MySQL error log? Do the time stamps match when the issue occurred? Thank you.
    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
    0
  • lfait
    Still struggling with this issue I have increase OS limitations root@replication [~]# cat /proc/sys/fs/file-max 1000000
    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 [~]#
    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
    0
  • InterServed
    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: thread_handling = pool-of-threads
    0
  • lfait
    [quote="InterServed, post: 1670751">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: thread_handling = pool-of-threads

    Server version: 5.5.37-cll - MySQL Community Server (GPL) I guess i found some clues regarding to my issue . [QUOTE]root@power [/home/runner/www]# netstat -an | grep :* | wc -l 15389 root@power [/home/runner/www]# netstat -an | grep :80 | wc -l 1105 root@power [/home/runner/www]# netstat -an | grep :443 | wc -l 7649 root@power [/home/runner/www]# netstat -an | grep :3306 | wc -l 7334 root@power [/home/runner/www]#
    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
    0
  • cPanelMichael
    I moved this thread to the "Optimization" forum where you will likely receive more user-feedback for this type of question. Thank you.
    0

Please sign in to leave a comment.

Didn't find what you were looking for?

New post