I am running Percona Mysql Server 5.6.14-56 on CentOS 6.3 64bit.
Server has plenty ram (96gb) and storage free. File system type: ext4
dev/sda6 ext4 1093623528 56576512 981494148 6% /mysqlstorage
I tried to make some alterations to a table with 42M records (about 12.5gb in size (about 50% of that is indexes)), and today it showed me the Table Is Full error.
I checked the loaded variables, and innodb_data_file_path = ibdata1:12M:autoextend it shows in my mysql manager.
This DB is live, and I prefer to fix this issue without (or with minimal) downtime.
file-per-table is active
ibdata1 is about 3.9GB
From MYSQL LOGS:
2015-02-19 01:43:04 7fe921f4b700 InnoDB: Error: Write to file (merge) failed at offset 356515840. InnoDB: 1048576 bytes should have been written, only 1036288 were written. InnoDB: Operating system error number 28. InnoDB: Check that your OS and file system support files of this size. InnoDB: Check also that the disk is not full or a disk quota exceeded. InnoDB: Error number 28 means 'No space left on device'. InnoDB: Some operating system error numbers are described at InnoDB: http://dev.mysql.com/doc/refman/5.6/en/operating-system-error-codes.html
How would I go about doing this?
// EDIT
Since asking this question, I've learned that during this "alternation", my /tmp quickly fills up to 100%. Once it hits 100%, mysql pushes out that error msg.
Filesystem 1K-blocks Used Available Use% Mounted on
/dev/sda2 50395844 1745156 46090688 4% /
tmpfs 37105808 0 37105808 0% /dev/shm
/dev/sda1 495844 37092 433152 8% /boot
/dev/sda5 4080064 73740 3799068 2% /tmp
/dev/sda6 1093623528 56576524 981494136 6% /storage
How can I safely increase the size of /tmp without any downtime?
Best Answer
Table alteration involves /tmp partition as intermediate storage for explicitly and implicitly created temporary tables. You have to use the tmpdir on a partition that big enough. Add the next line to the
my.cnf