CODEWITHSUNDEEP
mysqlinnodb
Christian Burgos
Christian Burgos

Reputation: 1591

MySQL has gone away on large queries

I have a MySQL installed and running on a CentOS 6.6 and MySQL version 5.5.40 on RackSpace. I always run into this error when running heavy queries.

Here is the settings of my.cnf

[mysqld]
  2 datadir=/mnt/data/mysql
  3 tmpdir=/mnt/data/temp
  4 socket=/var/lib/mysql/mysql.sock
  5 bind-address=0.0.0.0
  6 port=3306
  7 wait_timeout=432000
  8 max_allowed_packet=1G
  9 max_connections=500
 10 query-cache-size=0
 11 query-cache-type=0
 12 #query_cache_size=64M
 13 #query_cache_limit=64M
 14 key_buffer_size=1G
 15 sort_buffer_size=16M
 16 tmp_table_size=32M
 17 max_heap_table_size=32M
 18 read_buffer_size=512K
 19 read_rnd_buffer_size=512K
 20 thread_cache_size=50
 21 
 22 innodb_buffer_pool_size=12G
 23 innodb_buffer_pool_instance=2
 24 innodb_read_io_threads=12
 25 innodb_write_io_threads=12
 26 innodb_io_capacity=300
 27 innodb_log_file_size=128M
 28 innodb_thread_concurrency=0

Here is the error log I've caught after the crash:

150820 13:46:26 mysqld_safe Number of processes running now: 0

150820 13:46:26 mysqld_safe mysqld restarted

150820 13:46:26 [Note] Plugin 'FEDERATED' is disabled.

150820 13:46:26 [Warning] Using unique option prefix innodb_buffer_pool_instance instead of innodb-buffer-pool-instances is deprecated and will be removed in a future release. Please use the full name instead.

150820 13:46:26 InnoDB: The InnoDB memory heap is disabled

150820 13:46:26 InnoDB: Mutexes and rw_locks use GCC atomic builtins

150820 13:46:26 InnoDB: Compressed tables use zlib 1.2.3

150820 13:46:26 InnoDB: Using Linux native AIO

150820 13:46:26 InnoDB: Initializing buffer pool, size = 12.0G

InnoDB: mmap(6593445888 bytes) failed; errno 12

150820 13:46:27 InnoDB: Completed initialization of buffer pool

150820 13:46:27 InnoDB: Fatal error: cannot allocate memory for the buffer pool

150820 13:46:27 [ERROR] Plugin 'InnoDB' init function returned error.

150820 13:46:27 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.

150820 13:46:27 [ERROR] Unknown/unsupported storage engine: InnoDB

150820 13:46:27 [ERROR] Aborting



150820 13:46:27 [Note] /usr/libexec/mysqld: Shutdown complete



150820 13:46:27 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

EDIT:

RackSpace VM's specs are:

CPU: Intel(R) Xeon(R) CPU E5-2670 0 @ 2.60GHz RAM: 8GB

Upvotes: 0

Views: 1036

Answers (2)

julianH
julianH

Reputation: 119

In case it is useful, this is how I solve this problem. In my case the problem was caused by queries that were too large for the packets being sent to the server.

After running the following command against the server, the large queries process fine

SET GLOBAL max_allowed_packet=1073741824;

Upvotes: 0

Zafar Malik
Zafar Malik

Reputation: 6854

your server has only 8 GB RAM and you have assigned too much ram to mysql.

Even you need to change your configuration for many variables but first to comeout your issue do the below changes-

innodb_buffer_pool_instance=2 #comment it for the time being we can set it later.

innodb_buffer_pool_size=6G

key_buffer_size=20M #if your server is innodb but if you are also using myisam tables then keep as it is.

sort_buffer_size=2M # we can change later.

read_buffer_size=512K # comment it for time being.

read_rnd_buffer_size=512K #comment it for time being as used per session

tmp_table_size=1G #this can be reason of your problem so increase it.

max_heap_table_size=1G #this can be reason of your problem so increase it.

If possible decrease max_connections from 500 to 400 as each connection uses server resources.

Try and share the results.

Upvotes: 1

Related Questions