4

I'm getting the following warning in MySQL:

Aborted connection (Got an error reading communication packets)

I'm using Amazon RDS for MySQL hosting. Both MyISAM and InnoDB storage engines are used (I know it's a bit weird, but it's legacy code). The database is used as storage for PHP application. MySQL version is 5.5.27. PHP connects to MySQL using old mysql extension (http://www.php.net/manual/en/ref.mysql.php)

What I've tried:

  1. Increase max_allowed_packet to 1G (as suggested on MySQL Error Reading Communication Packets)
  2. Tried to change innodb_log_file_size and innodb_log_buffer_size (as suggested on MySQL: Error reading communication packets), but unfortunately Amazon RDS doesn't allow to change innodb_log_file_size, and when I tried to change innodb_log_buffer_size to the maximum possible value (4294967295), database instance didn't boot because of incompatible settings.
  3. PHP application that uses this database inserts data on every request, and I thought it might cause an issue, so I removed INSERT statements that run on every request, still the same result.
  4. It turned out that PHP application creates new MySQL connection for each query it executes. I've changed that so now it uses the same connection for all queries.
  5. Moved my Amazon RDS instance to the same availability zone where EC2 instance with PHP application is located.
  6. Upgraded my database instance from db.m1.small to db.m1.medium.
  7. Changed max_connections setting from 128 to 256.

All these steps didn't help me to solve the problem.

Also, I noticed that this error always happens every 15 or every 30 minutes. From error log:

140528 16:30:00 [Warning] Aborted connection 8709 to db: 'multisite5' user: 'uo_proxy' host: 'ip-10-180-232-23.ec2.internal' (Got an error reading communication packets)

140528 16:30:00 [Warning] Aborted connection 8706 to db: 'uoserver' user: 'uo_proxy' host: 'ip-10-180-232-23.ec2.internal' (Got an error reading communication packets)

140528 16:30:00 [Warning] Aborted connection 8708 to db: 'uoserver' user: 'uo_proxy' host: 'ip-10-180-232-23.ec2.internal' (Got an error reading communication packets)

140528 16:45:00 [Warning] Aborted connection 11675 to db: 'multisite5' user: 'uo_proxy' host: 'ip-10-180-232-23.ec2.internal' (Got an error reading communication packets)

140528 16:45:00 [Warning] Aborted connection 11674 to db: 'uoserver' user: 'uo_proxy' host: 'ip-10-180-232-23.ec2.internal' (Got an error reading communication packets)

I was under impression that the error is triggered by some cron script. I disabled all cron scripts to test the theory, but the error still happens.

Perhaps it's not possible to solve this problem on the server side because of RDS limitations, but I wonder which type of SQL queries can trigger this error? For example I've read that someone might want to increase max_allowed_packet if SQL queries use large BLOB fields, or when you insert many rows in one statement. So the question is - what can I change to make MySQL server happy with current values of innodb_log_file_size and innodb_log_buffer_size? Perhaps it's slow queries, or too many concurrent connections, or something else? Unfortunately moving my database from RDS to EC2 is the last option I would choose, because as I said I'm not experienced in database tuning so I'll have hard time trying to setup backups, replication etc.

Please let me know if I need to provide some additional details, I'm not database expert so I can miss something important.

Thanks in advance!

Improve this question
3
  • Are the hosts mentioned in the warning errors under your control? Could you use tcpdump to capture the MySQL TCP traffic to the RDS instance? That might shed some light on what is going on.
    – Razvan Musaloiu-E.
    May 31, 2014 at 1:04
  • @RazvanMusaloiu-E. The host that acts as mysql client is under my control. How can I capture TCP traffic using tcpdump? I've found an article, but it's focused on SQL queries, not error messages. Can you please guide me?
    – Vladimir Kadalashvili
    Jun 3, 2014 at 15:19
  • 1
    You can use something like sudo tcpdump -s0 -w /tmp/$(date +%Y-%m-%d.%H%M).dump tcp host x.x.x.x where x.x.x.x is the IP of your RDS instance. wireshark is a really nice too to examine a tcpdump.
    – Razvan Musaloiu-E.
    Jun 3, 2014 at 19:16

1 Answer 1

Reset to default
2

Since you tried the above steps, your sticking point seems to be the one you can't alter.

I wrote a post back in August 2012 (Local database vs Amazon RDS), mentioning how the number of DB connections and the Buffer Size are fixed and immutable for each in RDS server model. I also mentioned that the Transactions Logs are fixed at 128M, no matter what RDS server model you use.

SUGGESTION: Be kind to yourself. Migrate the DB from RDS to EC2. Then, you have the necessary flexibility to change the InnoDB Infrastructure. Consequently, you will have to write your own backups and setup replication by hand.

I know you said going to EC2 is your last resort. Since you cannot tune InnoDB effectively in an RDS environment, EC2 is the next logical step.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.