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.
Rate this post
3 of 5 based on 9366 votesComments