I've got '/tmp' directory mounted with 'tmpfs' and for some reason this is causing the following error:
mysql> SELECT DISTINCT table_schema FROM information_schema.tables WHERE table_schema NOT IN ('information_schema','mysql')
-> ;
ERROR 126 (HY000): Incorrect key file for table '/tmp/#sql_29ef_0.MYI'; try to repair it
–
# df -h /tmp/
Filesystem Size Used Avail Use% Mounted on
tmpfs 2.0G 12K 2.0G 1% /tmp
# df -i /tmp/
Filesystem Inodes IUsed IFree IUse% Mounted on
tmpfs 2041621 7 2041614 1% /tmp
# mount | grep /tmp
tmpfs on /tmp type tmpfs (rw,size=2048M)
Please note that the same query works fine when '/tmp' dir is mounted with ext4 file system.
EDIT:
Server_01
# cd /var/lib/mysql ; ls -lR | grep -c "\.frm$"
1876765
but this also happened on server with much less tables:
Server_02
# cd /var/lib/mysql ; ls -lR | grep -c "\.frm$"
49514
I was using this query to list all databases but as it didn't work with tmpfs
I've just replaced it with simpler one (SHOW DATABASES…).
I was watching disk space on /tmp
mounted with tmpfs
and there still was a plenty of space so I'm not sure how it could run out of space?
Basically I've got a problem with logical backups on the server with ~8000 DBs – it takes many hours (~24) to complete this task. I've created a simple BASH script (please see below) and instead of mysqldump
I'm using mydumper
as initial tests showed that it's much faster.
Backups running very fast initially and then slowing down dramatically:
# ./backup.sh
Backing up database number: 1
Completed in: 0.016
Backing up database number: 2
Completed in: 0.078
Backing up database number: 3
Completed in: 0.074
Backing up database number: 4
Completed in: 0.068
Backing up database number: 5
Completed in: 0.071
Backing up database number: 6
Completed in: 0.060
Backing up database number: 7
Completed in: 0.067
Backing up database number: 8
Completed in: 0.070
Backing up database number: 9
Completed in: 0.065
.....
Backing up database number: 107
Completed in: 10.749
Backing up database number: 108
Completed in: 12.125
Backing up database number: 109
Completed in: 11.313
Backing up database number: 110
Completed in: 11.572
Backing up database number: 111
Completed in: 11.371
.....
Script:
#!/usr/bin/env bash
DATA_DIR="/tmp/mysqlbackup"
LOCKFILE=/tmp/backup.lock
NOW=$(date +%Y%m%d)
COUNT=1
if [ -f "$LOCKFILE" ]; then
echo "$(basename $0) is already running: PID $(cat $LOCKFILE)"
exit 0
else
echo $$ > $LOCKFILE
if [ ! -d $DATA_DIR/$NOW ]; then
mkdir -m 700 -p $DATA_DIR/$NOW
fi
while read DB; do
(( COUNT++ ))
echo "Backing up database number: $COUNT"
START=$(date +%s.%N)
mydumper -e -o $DATA_DIR/$NOW/$DB -B "$DB"
ELAPSED=$(date +%s.%N)
printf "Completed in: %.3F\n" $(echo $ELAPSED - $START | bc)
done <<< "$(mysql -A -B -N -e "SHOW DATABASES" | egrep -v '(mysql|*_schema|log)')"
echo "Removing backup dir...";
rm -rf $DATA_DIR/$NOW
rm -f $LOCKFILE
exit 0
fi
Best Answer
ASPECT #1: BAD FILE
Something tells me you somehow ran out of space in
/tmpfs
.Whenever MySQL creates a temp table
.frm
is created.MYD
is 0 bytes.MYI
is 1024 bytes (1K).MYI
I find that strange that MySQL could not create a 1K
.MYI
.Since
ext3
has a default block size of 1K, whileext4
has 4K, perhaps the 4K blocksize may have allowed for more data. This, in itself, is not the real problem.ASPECT #2: YOUR QUERY
If you have tens of thousands of tables, the query you are running looks quite demanding. In fact, the query says "Get me all databases that have 1 or more tables in them." If you have a large number of tables, you could be looking at a rather large temp table in order collect
table_schema
values before doing theDISTINCT
.Sometimes it is easy to forget
SELECT DISTINCT
executes aGROUP BY
internallyThere are two Stackoverflow posts that mention this
Jun 06, 2012
: How to select distinct rows without using group by statement (My Post)Feb 24, 2009
: What's faster, SELECT DISTINCT or GROUP BY in MySQL?ANALYSIS
My guess is a combination of anomalies
ANOMALY #1 : You have a large number of tables
ANOMALY #2 : The temp table being made must have run out of space to have a damaged
.MYI
ANOMALY #3 : The query does not use an index.
Look at the table
A full table scan is required, resulting in the need for a temp table with an in-memory sort.
SUGGESTIONS
SUGGESTION #1 : Use
ext4
. You already said the query works fine inext4
.SUGGESTION #2 : Increase
tmpfs
to16G
SUGGESTION #3 : Query the information_schema so that it does not use as much memory
This counts all tables in all databases
table_count
to get the DISTINCT count.DROP TABLE mysql.dbtbcount;
Give it a Try !!!
UPDATE 2013-08-19 17:40 EDT
Your problem should have been posted as a separate question. Nevertheless, let's see what we can surmise from this.
You should not be using
/tmp
as a backup location. Why?/tmp
defined as the default folder for temp filesYou need to reorganize the script to do parallel dumps
SUGGESTION
You should collect the databases and parallel mysqldump 25 databases at a time, starting from the biggest databases to the smallest. It gets faster with each next set of 25...
UPDATE 2013-08-20 10:03 EDT
If you don't care about the size of the database here is an alternate method that will start the mysqldump process faster:
I wrote about doing parallel database and table dumps before : How can I optimize a mysqldump of a large database?
If you cannot get
mydumper
to throttle based on database size, this is the way to go.GIVE IT A TRY !!!