0

I have a Ubuntu 12.04 server with mysql 5.5.37

I am using InnoDB tables only. One of the tables has about 30 mil records in it.

Everything is running smoothly until I run a big select query against that big 30-mil-rec table. The query would presumably render about 2 mil rows.

'show processlist' Displays the query in status "Copying to tmp table" for about 20 seconds. And then it disappears because mysql simply restarts.

Here is what syslog shows:

Mar  3 18:32:53 lf1 kernel: [22530141.649970] init: mysql main process (31675) terminated with status 1
Mar  3 18:32:53 lf1 kernel: [22530141.649990] init: mysql main process ended, respawning
Mar  3 18:32:53 lf1 kernel: [22530141.657028] type=1400 audit(1425432773.505:51): apparmor="STATUS" operation="profile_replace" name="/usr/sbin/mysqld" pid=24168 comm="apparmor_parser"
Mar  3 18:32:55 lf1 /etc/mysql/debian-start[24232]: Upgrading MySQL tables if necessary.
Mar  3 18:32:55 lf1 /etc/mysql/debian-start[24235]: /usr/bin/mysql_upgrade: the '--basedir' option is always ignored
Mar  3 18:32:55 lf1 /etc/mysql/debian-start[24235]: Looking for 'mysql' as: /usr/bin/mysql
Mar  3 18:32:55 lf1 /etc/mysql/debian-start[24235]: Looking for 'mysqlcheck' as: /usr/bin/mysqlcheck
Mar  3 18:32:55 lf1 /etc/mysql/debian-start[24235]: This installation of MySQL is already upgraded to 5.5.37, use --force if you still need to run mysql_upgrade
Mar  3 18:32:55 lf1 /etc/mysql/debian-start[24246]: Checking for insecure root accounts.
Mar  3 18:32:55 lf1 /etc/mysql/debian-start[24251]: Triggering myisam-recover for all MyISAM tables

What is causing it to restart? Clearly it runs out of memory or something similar to that. So here are the db server variables:

key_buffer = 512M
max_allowed_packet = 16M
sort_buffer_size = 24M
myisam_sort_buffer_size = 64M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
thread_cache_size = 8
query_cache_size= 216M
table_cache = 128
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 12
max_connections = 200
max_connect_errors = 500
max_heap_table_size = 512M
tmp_table_size = 512M
query_cache_limit = 32M

The box itself has 32 GB of RAM.

codemonkey
  • 7,325
  • 5
  • 22
  • 36
  • what is your innodb buffer pool size? – BK435 Mar 04 '15 at 06:23
  • I figured out what happened. The big table was actually corrupted and any select query that engaged the faulty index crashed the DB server. Since there is no easy way to repair a table in InnoDB, I stopped my application (to prevent any writes to DB server), and then did what 'genesis' suggested on this page: http://stackoverflow.com/questions/226172/how-do-i-repair-an-innodb-table – codemonkey Mar 04 '15 at 10:00

0 Answers0