mysql: mysql closing connection with error "mysql server gone away" while searching records from the table

Question

I am using below select query to search from the table.

"Select * from authentication_codes where client_id=6"

column client_id is of type integer and i have also indexed that column. There are total 36751694 records in the table authentication_codes and out of that 20200000 belongs to client_id 6.

Whenever i hit above SQL query 4 times simultaneously that time mysql is crashing with error "mysql server gone away" and CPU utilization goes to 95%.

output with explain command for same query is as below

mysql> explain Select * from authentication_codes where client_id=6 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: authentication_codes
         type: ref
possible_keys: index_authentication_codes_on_client_id
          key: index_authentication_codes_on_client_id
      key_len: 5
          ref: const
         rows: 18475849
        Extra: Using where
1 row in set (0.00 sec)

After hitting 3 SQL request statement simultaneously CPU utilization with top command is as below

top - 09:13:43 up  2:28,  4 users,  load average: 1.94, 0.67, 0.52
Tasks: 123 total,   2 running, 121 sleeping,   0 stopped,   0 zombie
%Cpu(s): 91.0 us,  2.8 sy,  0.0 ni,  5.9 id,  0.0 wa,  0.0 hi,  0.0 si,  0.4 st
KiB Mem:   4046840 total,  2439948 used,  1606892 free,     6836 buffers
KiB Swap:        0 total,        0 used,        0 free.  1929160 cached Mem

  PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND
 4543 mysql     20   0  886304 176544   7068 S 188.0  4.4   2:53.83 mysqld

My server has 4GB of ram and 2 CPU core. Mysql table is using database engine Innodb.

Thanks,

Possible duplicate of [MySQL error 2006: mysql server has gone away](http://stackoverflow.com/questions/7942154/mysql-error-2006-mysql-server-has-gone-away) — MusicLovingIndianGirl, Dec 24 '15 at 07:30
One possibility is 1. there is no index available for the `client_id`, what does the following say `explain Select * from authentication_codes where client_id=6` 2. max_allowed_packet is set to a lower number than the number of returned records from the query. — Abhik Chakraborty, Dec 24 '15 at 07:31
see edited question for output of explain command and also i have set max_allowed_packet to 1GB. — user2274074, Dec 24 '15 at 08:24
@AbhikChakraborty - i am doing bulk insert of 0.5(5 Lakh) million records at a time using insert into sql statment and that's why i have set max_allowed_packet to 1GB. — user2274074, Dec 24 '15 at 08:41
What do you see in the MySQL error log? Is the server restarting? — Richard St-Cyr, Dec 24 '15 at 16:42

score 0 · Answer 1 · answered Dec 24 '15 at 23:27

The server is not "crashing"; the client gave up because the server was taking so long.

Get rid of the index on client_id; it is faster to scan the entire table to find that most of them have '6'.

What the heck is the client going to do with 20 million (200 lakh) rows? Possibly blow out memory in the client. If you need to look at all of them, read the table in chunks of 1000. If you need to COUNT() or SUM() something, do that in SQL.

You have a tiny machine (only 4GB)? If the table is InnoDB, you should have innodb_buffer_pool_size set to perhaps 1500M.

As for max_allowed_packet, consider whether your connection is waiting for all the rows before returning anything, then returning all 20M at once. Maybe 1G will work today; maybe it won't tomorrow. Can you set the connection to deliver the rows "as it gets them". This will take longer overall, but won't hit packet limits.

mysql: mysql closing connection with error "mysql server gone away" while searching records from the table

1 Answers1