connection time out when trying to connect to remote database from app-CodePudding

I'm trying to connect to a remote database (It's a MySQL server on AWS EC2). Connection speed is okay when I try to connect using MySQL workbench or other DB management tools. But when I try to connect via my application (PHP/Laravel) the connection is too slow that it will time out.

Here is the result of dump(DB::connection());

I tried adding skip-external-locking and skip-name-resolve to the MySQL configuration file but it didn't change the result. I tried running a MySQL query manually from my terminal to the database server with this command to determine how much it will take to run:

time mysql -u'username' -p'password' -e'show status'

The result was 0.05s user 0.04s system 0% cpu 10.582 total

I'll put the configuration file here, please let me know if you know a way to fix this issue:

[mysqld_safe]
socket      = /var/run/mysqld/mysqld.sock
nice        = 0

[mysqld]

user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
skip-name-resolve


key_buffer_size = 256M
max_allowed_packet = 1M
thread_cache_size = 8

myisam-recover-options  = BACKUP

innodb_buffer_pool_size     =  1G
innodb_log_file_size=50M
innodb_flush_log_at_trx_commit=0
sync_binlog=0
innodb_flush_method=O_DIRECT

log_error = /var/log/mysql/error.log

expire_logs_days    = 10
max_binlog_size   = 100M
innodb_file_per_table = 1
myisam_sort_buffer_size = 64M
read_rnd_buffer_size = 4M
read_buffer_size = 1M
sort_buffer_size = 1M
table_open_cache = 256

Also, someone with the

Result of SHOW GLOBAL VARIABLES LIKE '%connect%';

Result of SHOW GLOBAL STATUS LIKE '%connect%';

CodePudding user response：

In your my.cnf [mysqld] section,

thread_cache_size=100  # from 8 for the cap suggested in 8.0 ref manual.
table_open_cache=3000  # from 256 to avoid table open thrashing
innodb_open_files=3000  # should always match table_open_cache

just a few details to consider with available information at this moment.

CodePudding user response：

In your my.cnf [mysqld] section,

connect_timeout=30  # from 10 (seconds) since you are are many KM away from your host in USA-East.

Please share your code that 'connect's, processes, closes connections.

You appear to be leaving threads_connected when finished with a user's activities.