Got an error writing communication packets

MySQL & MariaDB Other MySQL® Questions
1

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

2

check wait_timeout parameter

3

[COLOR=#252C2F]wait_timeout parameter has already been increased to 80 hours, query doesn’t last more than 5 hours…

4

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’.

5

This was fixed by increasing the read_timeout [COLOR=#252C2F]parameter