Database issue

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

 

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

 

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

 

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