0

I have an Ubuntu 12.04 LEMP server with shop app ontop, running MySQL 5.5 as DB (currently since this problem started I've already switched to Percona with no success).

So, current facts: every night my monitoring tells me that the server is not responsive. Exact time varies. It can be down for several minutes, or even couple of hours. It can start at around 1 in the night or at any other time up through 5. Munin and atop shows that at this time iowait takes all resources, used disk space increases in steps by ~2Gb. Even up to taking all space available.

Mysql slow query log shows nothing. No slow queries at the time.

Web server log shows no special activity - just general web site use, static, shop, products, products listings. Nothing extra.

There are no entries in cron that might be executed at that time and that might do anything with DB.

Mysql SHOW EVENTS is empty on all databases. MySQL error log (with warnings logging enabled) has nothing at a time. Something appears there but only when disk is full, such as:

151028  4:03:54 [ERROR] Got an error from unknown thread, /mnt/workspace/percona-server-5.5-debian-binary/label_exp/ubuntu-precise-64bit/percona-server-5.5-5.5.45-rel37.4/storage/myisam/mi_write.c:226
151028  4:03:54 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_5e90_21.MYI'; try to repair it
151028  4:03:54 [ERROR] Got an error from unknown thread, /mnt/workspace/percona-server-5.5-debian-binary/label_exp/ubuntu-precise-64bit/percona-server-5.5-5.5.45-rel37.4/storage/myisam/mi_write.c:226
151028  4:03:54 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_5e90_20.MYI'; try to repair it
151028  4:03:54 [ERROR] Got an error from unknown thread, /mnt/workspace/percona-server-5.5-debian-binary/label_exp/ubuntu-precise-64bit/percona-server-5.5-5.5.45-rel37.4/storage/myisam/mi_write.c:226

Here are some munin graphs: CPU usage, Disk space usage (you can see some peaks on this graph - this is not normal for our server, there were no peaks prior to when this started).

This has started some time ago, before that server was okay, and server never demanded such amounts of disk space.

TiGR
  • 121
  • 4
  • Have you looked to see if there are any cron jobs running at the same time ? Is there a mysqldump running ? – user9517 Oct 28 '15 at 12:53
  • It seems your /tmp get full, so it can't create the temporary table required. Maybe it's because your data has increased ? So temporary tables have become much bigger, you should check the size of your biggest table. http://dev.mysql.com/doc/refman/5.1/en/temporary-files.html – Pierre-Alain TORET Oct 28 '15 at 13:06
  • @Iain as I said there are no related jobs. There are backup jobs, but these run at another time. – TiGR Oct 28 '15 at 13:31
  • @pat yes, root directory gets full and then unrelated errors occur. Data has not increased (it stays around the same size, ~200Mb). If you take a look at the disk space usage graph, you'll see that at Wed midnight free space is ~30% (6 GB), and then it drops down to zero in couple of chunks, each around 2 GB. The error in log occured at 4:03, but this space usage problem appeared at around 3:30. At 4:03 entire disk was full. – TiGR Oct 28 '15 at 14:50

1 Answers1

0

This error usually means disk full in my experience.

Try increasing the tmpfs partition size by remounting it:

mount -t tmpfs -o remount,size=1G tmpfs /tmp

Or, you can set the tmpdir=/some/drive in mysql config to move the tmp storage to some place with more available space.

Gmck
  • 389
  • 1
  • 8
  • As I said, that error is irrelevant, since drive has 6Gb free, and then suddenly MySQL takes up all that space. Sure, then it gets errors that occur when disk is full. The problem is: MySQL then releases that space and everything returns to normal. But it does not always take all space. It can take just 2g, and then release them back, but during this process server becomes unresponsive. – TiGR Oct 28 '15 at 19:02