John Davidson

Message:

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%';

---

My Answer:
Based on the information provided, it seems like the issue might be related to the network latency between your application server and the remote database server. Here are a few suggestions to try and improve the connection speed:

1. Check the network latency between your application server and the remote database server using tools like ping or traceroute. If there is high latency, consider optimizing the network connection or using a different hosting provider closer to your location.

2. Make sure that the firewall settings on the remote database server allow incoming connections from your application server. Check if there are any restrictions or limitations that could be causing the slow connection.

3. Consider optimizing your PHP/Laravel code to reduce the number of database queries or improve the efficiency of the queries being executed. This can help reduce the overall load on the database server and improve the connection speed.

4. Update the MySQL configuration on the remote database server to optimize performance. You can adjust settings like innodb_buffer_pool_size, innodb_log_file_size, and other parameters based on the specific requirements of your application.

5. Consider using a database connection pooling mechanism to manage connections more efficiently and reduce the overhead of establishing new connections for each request.

6. If possible, try connecting to the remote database server from a different location or network to see if the issue persists. This can help determine if the problem is specific to the network connection between your application server and the remote database server.

By following these suggestions and troubleshooting steps, you should be able to improve the connection speed and resolve the timeout issue when connecting to the remote database from your PHP/Laravel application.