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

Question

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 same problem here solve it by commented out gethostbyaddr() but they didn't provide enough information.

UPDATE When I use DB::select('select * from users limit 100'); in my laravel code, it's returning records from the database. END OF UPDATE

Result of SHOW GLOBAL VARIABLES LIKE '%timeout%'; I'm in eastern Europe and the AWS EC2 server is in Ohio (east usa).

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

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

Check the MySQL server firewall. Maybe your IP address is not allowed to connect to the remote server. — Tohid Dadashnezhad, Sep 22 '21 at 13:48
I tried `sudo ufw allow in 3306/tcp` and on AWS security group I allowed inbound connection on port 3306. I also tried `bind-address = *` in MySQL configuration file. — Fariman Kashani, Sep 22 '21 at 13:52
Your 504 error message means your nginx reverse proxy sent a request to your php/laravel server but got no response. Is it possible your php / laravel program runs on a different machine from where you try your connections with Workbench or the mysql command-line client? What do you get if you say `SHOW FULL PROCESSLIST` to MySQL (that is: how busy is the server)? What happens if you hit the php/laravel server directly without going through nginx? — O. Jones, Sep 22 '21 at 14:40
The server is not busy. It's a development server I implemented. When I send a web request I can see my process in `PROCESSLIST `. The command is on Sleep, Time is 0 and Info is Null. — Fariman Kashani, Sep 22 '21 at 14:51
Please post as TEXT, A) your php code that requests CONNECT, PROCESS, CLOSE for analysis. And when logged in as root, please post TEXT results of B) SHOW GLOBAL VARIABLES LIKE '%connect%'; and C) SHOW GLOBAL STATUS LIKE '%connect%'; — Wilson Hauck, Sep 22 '21 at 20:00
How many kilometers is the server located from your location? Approximately or time zones. Also post TEXT results of SHOW GLOBAL VARIABLES LIKE '%timeout%'; please. — Wilson Hauck, Sep 22 '21 at 20:03
@WilsonHauck I'm in eastern Europe and the AWS EC2 server is in Ohio (us-east). I have attached the MySQL results you asked for to the question above. I couldn't add them as TEXT. The tables were unreadable. — Fariman Kashani, Sep 23 '21 at 08:03
@WilsonHauck I tried running `DB::select('select * from users limit 100');` from my laravel application and it's returning the correct value. But queries inside the app are not working. — Fariman Kashani, Sep 23 '21 at 13:17
After you detect your queries are not running, please FLUSH LOGS; and post last 400 lines of your MySQL Error Log for analysis. Thanks — Wilson Hauck, Sep 25 '21 at 11:32

score 0 · Answer 1 · answered Sep 22 '21 at 20:24

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.

score 0 · Answer 2 · answered Sep 23 '21 at 14:42

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.

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

2 Answers2