1

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
RolandoMySQLDBA
  • 185,223
  • 33
  • 326
  • 536
HTF
  • 451
  • 4
  • 13
  • 28

1 Answers1

2

ASPECT #1: BAD FILE

Something tells me you somehow ran out of space in /tmpfs.

Whenever MySQL creates a temp table

  • It is created as a MyISAM table
  • No .frm is created
  • Initial .MYD is 0 bytes
  • Initial .MYI is 1024 bytes (1K)
  • There are never any indexes in the .MYI

I find that strange that MySQL could not create a 1K .MYI.

Since ext3 has a default block size of 1K, while ext4 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 the DISTINCT.

Sometimes it is easy to forget

There are two Stackoverflow posts that mention this

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

mysql> show create table information_schema.tables\G
*************************** 1. row ***************************
       Table: TABLES
Create Table: CREATE TEMPORARY TABLE `TABLES` (
  `TABLE_CATALOG` varchar(512) NOT NULL DEFAULT '',
  `TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT '',
  `TABLE_NAME` varchar(64) NOT NULL DEFAULT '',
  `TABLE_TYPE` varchar(64) NOT NULL DEFAULT '',
  `ENGINE` varchar(64) DEFAULT NULL,
  `VERSION` bigint(21) unsigned DEFAULT NULL,
  `ROW_FORMAT` varchar(10) DEFAULT NULL,
  `TABLE_ROWS` bigint(21) unsigned DEFAULT NULL,
  `AVG_ROW_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `DATA_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `MAX_DATA_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `INDEX_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `DATA_FREE` bigint(21) unsigned DEFAULT NULL,
  `AUTO_INCREMENT` bigint(21) unsigned DEFAULT NULL,
  `CREATE_TIME` datetime DEFAULT NULL,
  `UPDATE_TIME` datetime DEFAULT NULL,
  `CHECK_TIME` datetime DEFAULT NULL,
  `TABLE_COLLATION` varchar(32) DEFAULT NULL,
  `CHECKSUM` bigint(21) unsigned DEFAULT NULL,
  `CREATE_OPTIONS` varchar(255) DEFAULT NULL,
  `TABLE_COMMENT` varchar(2048) NOT NULL DEFAULT ''
) ENGINE=MEMORY DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql>

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 in ext4.

SUGGESTION #2 : Increase tmpfs to 16G

SUGGESTION #3 : Query the information_schema so that it does not use as much memory

DROP TABLE IF EXISTS mysql.dbtbcount;
CREATE TABLE mysql.dbtbcount
(
    table_schema VARCHAR(64) NOT NULL,
    table_count INT NOT NULL DEFAULT 0,
    PRIMARY KEY (table_schema)
) ENGINE=MEMORY;
INSERT INTO mysql.dbtbcount (table_schema)
SELECT schema_name FROM information_schema.schemata
WHERE schema_name NOT IN ('information_schema','mysql'); 
INSERT INTO mysql.dbtbcount (table_schema)
SELECT table_schema FROM information_schema.tables
ON DUPLICATE KEY UPDATE table_count = table_count + 1;
SELECT * FROM mysql.dbtbcount;
SELECT COUNT(1) DistinctDBs FROM mysql.dbtbcount WHERE table_count > 0;

This counts all tables in all databases

  • When any row has a zero table_count, that database is empty.
  • Count all rows that have a nonzero table_count to get the DISTINCT count.
  • When done, run 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?

You 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...

BACKUP_FOLDER=/any/path/but/tmp

MYSQL_HOST=localhost
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-h${MYSQL_HOST} -u${MYSQL_USER} -p${MYSQL_PASS}"

SQLSTMT="SELECT db FROM (SELECT table_schema db,SUM(data_length) datasize"
SQLSTMT-"${SQLSTMT} FROM information_schema.tables"
SQLSTMT-"${SQLSTMT} WHERE table_schema NOT ('mysql','log')"
SQLSTMT-"${SQLSTMT} AND RIGHT(table_schema,7)<>'_schema'"
SQLSTMT-"${SQLSTMT} GROUP BY table_schema) A"
SQLSTMT-"${SQLSTMT} ORDER BY datasize DESC"
mysql ${MYSQL_CONN} -ANe"${SQLSTMT}" > /tmp/ListOfDatabases.txt

COMMIT_COUNT=0
COMMIT_LIMIT=25
MYSQLDUMP_OPTIONS="--hex-blob --routines --triggers"
for DB in `cat /tmp/ListOfDatabases.txt`
do
    BACKUP_FILE=${BACKUP_FOLDER}/${DB}.sql.gz
    mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} ${DB} | gzip > ${BACKUP_FILE} &
    (( COMMIT_COUNT++ ))
    if [ ${COMMIT_COUNT} -eq ${COMMIT_LIMIT} ]
    then
        COMMIT_COUNT=0
        wait
    fi
done
if [ ${COMMIT_COUNT} -gt 0 ]
then
    wait
fi

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:

BACKUP_FOLDER=/any/path/but/tmp

MYSQL_HOST=localhost
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-h${MYSQL_HOST} -u${MYSQL_USER} -p${MYSQL_PASS}"

SQLSTMT-"${SQLSTMT} SELECT schema_name FROM information_schema.schemata"
SQLSTMT-"${SQLSTMT} WHERE schema_name NOT ('mysql','log')"
SQLSTMT-"${SQLSTMT} AND RIGHT(schema_name ,7)<>'_schema'"

COMMIT_COUNT=0
COMMIT_LIMIT=25
MYSQLDUMP_OPTIONS="--hex-blob --routines --triggers"
for DB in `cat /tmp/ListOfDatabases.txt`
do
    BACKUP_FILE=${BACKUP_FOLDER}/${DB}.sql.gz
    mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} ${DB} | gzip > ${BACKUP_FILE} &
    (( COMMIT_COUNT++ ))
    if [ ${COMMIT_COUNT} -eq ${COMMIT_LIMIT} ]
    then
        COMMIT_COUNT=0
        wait
    fi
done
if [ ${COMMIT_COUNT} -gt 0 ]
then
    wait
fi

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 !!!

Community
  • 1
RolandoMySQLDBA
  • 185,223
  • 33
  • 326
  • 536