Not the answer you need?
Register and ask your own question!

Got an error writing communication packets

markbusumarkbusu ContributorCurrent User Role Beginner
Hi,

We are currently evaluating some BI software which is attempting to retrieve data from our Percona Database.. This is done by running a "select * from Table".... The size of the table is around 400 Million rows and always seems to be failing once approaching approx 178M ... The duration is always approximatly 4:30 - 5 Hours

The error results in:

Aborted connection 178 to db: **** user: ****** host: ******** (Got an error writing communication packets)

Following numerous documentations, we have updated our values to:

net_read_timeout = 120
interactive_timeout = 288000
wait_timeout = 288000
innodb_buffer_pool_size = 22G
max_allowed_packet = 1024M

However this yields no result... Does anyone of you have experience with trying to query very large tables ?

Thanks

Comments

  • yogesh777yogesh777 Contributor Current User Role Beginner
    check wait_timeout parameter
  • markbusumarkbusu Contributor Current User Role Beginner
    wait_timeout parameter has already been increased to 80 hours, query doesn't last more than 5 hours..
  • niljoshiniljoshi MySQL Sage Inactive User Role Beginner
    Hi,

    Can you provide some more information?

    1. Are you using Native MySQL Or Percona Server? If PS then Can you give the exact Percona Server version?
    2. "SHOW TABLE STATUS" for that table
    3. "SHOW ENGINE INNODB STATUS" while select is running.
    4. How your BI software connects to MySQL/PS?

    Seems you are selecting data more then max packet size. Also selecting data from this much big table at a time is not a good idea.
    You should select it by running "order by * limit N,M'.
  • markbusumarkbusu Contributor Current User Role Beginner
    This was fixed by increasing the read_timeout parameter
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.