Mysql – Repairing Myisam Table when there was no additional Disk space & Table corrupted

myisamMySQLmysql-5.1temporary-tables

I had a table which MyIsam as storage Engine contains million rows. There is a purging task and need to remove 35 days old data. i measured up the rows in following manner :

mysql> select count(*) from table_date;

+----------+
| count(*) |
+----------+
| 53217368 |
+----------+

2. Here are the min and max value for the  table1:

mysql> select min(table_date),max(table_date) from table1;
+---------------------+---------------------+
| min(table_date)     | max(table_date)     |
+---------------------+---------------------+
| 2011-08-09 04:05:01 | 2012-01-13 04:04:16 |
+---------------------+---------------------+
row in set (0.01 sec)

3. Date and time 35 days ago from the current date & time will be:

mysql> select now(),now() - interval 35 day;
+---------------------+-------------------------+
| now()               | now() - interval 35 day |
+---------------------+-------------------------+
| 2012-01-13 21:41:36 | 2011-12-09 21:41:36     |
+---------------------+-------------------------+

4. Number of rows which contains dataentry_date less than the above date is:

mysql> select count(*) from table1 where table_date < '2011-12-09 21:41:36';
+----------+
| count(*) |
+----------+
| 30729315 |
+----------+
1 row in set (3 min 3.34 sec)

Now purged the 3 million rows using :

mysql> delete from table1 where table_date < (now() - interval 35 day);

After running  the  delete query has been terminated with the below error message
and mysql has been restarted.

Tried to Repair using :

mysql> check table table_date;
+----------------------------+-------+----------+-----------------------------------------------------------------------------------+
| Table                      | Op    | Msg_type | Msg_text                                                                          |
+----------------------------+-------+----------+-----------------------------------------------------------------------------------+
| tabe1.table_date           | check | warning  | 7 clients are using or haven't closed the table properly                          |
| tabe1.table_date           | check | error    | Can't read key from filepos: 2048                                                 |
| tabe1.table_date           | check | Error    | Incorrect key file for table './table1/table_date.MYI'; try to repair it |
| tabe1.table_date           | check | error    | Corrupt                                                                           |
+----------------------------+-------+----------+-----------------------------------------------------------------------------------+
4 rows in set (0.01 sec)

No luck & then tried with repair table :

mysql> repair table table_date;
+----------------------------+--------+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table                      | Op     | Msg_type | Msg_text                                                                                                                                                                                 |
+----------------------------+--------+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tabe1.table_date           | repair | error    | 28 when writing to datafile                                                                                                                                                              |
| tabe1.table_date           | repair | Error    | Disk is full writing './table/tabl1.TMD' (Errcode: 28). Waiting for someone to free space... (Expect up to 60 secs delay for server to continue after freeing disk space)                |
| tabe1.table_date           | repair | Error    | Retry in 60 secs. Message reprinted in 600 secs                                                                                                                                          |
| tabe1.table_date           | repair | Error    | Retry in 60 secs. Message reprinted in 600 secs                                                                                                                                          |
| tabe1.table_date           | repair | Error    | Error writing file './table/table_data.TMD' (Errcode: 28)                                                                                                                                |
| tabe1.table_date           | repair | status   | Operation failed                                                                                                                                                                         |
+----------------------------+--------+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
6 rows in set (43 min 5.53 sec)

No luck again.

Error Log :
--------------------
Version: '5.1.45-community' socket: '/var/lib/mysql/mysql.sock' port: 3306
MySQL Community Server (GPL)
120120 4:38:05 [ERROR] /usr/sbin/mysqld: Incorrect key file for
table './tabl1/table_data.MYI'; try to repair it
120120 4:39:14 [ERROR] /usr/sbin/mysqld: Incorrect key file for
table './table1/table_date.MYI'; try to repair it
120120 5:05:13 [ERROR] /usr/sbin/mysqld: Disk is full
writing './table1/table_date.TMD' (Errcode: 28). Waiting for someone to free
space... (Expect up to 60 secs delay for server to continue after freeing disk
space)
120120 5:05:13 [ERROR] /usr/sbin/mysqld: Retry in 60 secs. Message reprinted
in 600 secs
120120 5:15:13 [ERROR] /usr/sbin/mysqld: Retry in 60 secs. Messagemysql>

I tried with Myisamchk -r & -o  also. But no luck.

Here is my present table status :

mysql> show table status like 'table_date'\G
        *************************** 1. row ***************************
                   Name: table_date
                 Engine: NULL
                Version: NULL
             Row_format: NULL
                   Rows: NULL
         Avg_row_length: NULL
            Data_length: NULL
        Max_data_length: NULL
           Index_length: NULL
              Data_free: NULL
         Auto_increment: NULL
            Create_time: NULL
            Update_time: NULL
             Check_time: NULL
              Collation: NULL
               Checksum: NULL
         Create_options: NULL
                Comment: Table './table1/table_date'
                         is marked as crashed and last (automatic?)
      1 row in set (0.01 sec)

Q1. When i used myisamchk .TMD got created.It is a intermediate data file i could understand.if i can delete this will it work out. ?

Q2. The table occupies 17GB space. And got crashed after excuting the delete. When trying to repair disk space issue. Any tricky solution to get back this table to working state. Solution will appreciated.

Best Answer

If you cannot free up or add any disk space, you could copy the table_data.* files (MYD,MYI,frm,etc) to another machine with plenty of free disk, run the repair there with myisamchk, then copy the files back to the original machine. If the server needs to stay running, do a FLUSH TABLES WITH READ LOCK table_data before copying the data to the other server and UNLOCK TABLES after you copy the data back. You will need to keep the FLUSH TABLES ... session running for this duration.

So, it would look like this

server1:

# keep this session running for the duration of the repair!
mysql> FLUSH TABLES WITH READ LOCK table_data;
/var/lib/mysql# rsync -aP table_date.* server2:/somedir/

server2:

# make a backup
/somedir# tar -czvf table_data_backup.tgz table_date.*
# run the repair
/somedir# myisamchk -r table_data
# copy the files back
/somedir# rsync -aP table_date.* server1:/var/lib/mysql/

server1:

# make sure the permissions are correct on /var/lib/mysql/table_date.*
mysql> UNLOCK TABLES;

Remember, do not close the FLUSH TABLES WITH READ LOCK session.

You may want to give myisamchk additional memory for the following parameters to speed it up: --key_buffer_size --sort_buffer_size --read_buffer_size --write_buffer_size

Next, convert your table to InnoDB. There's almost no good reason to use MyISAM these days.

YOUR QUERY

SELECT post.postid, post.attach FROM newbb_innopost AS post WHERE post.threadid = 51506;

At first glance, that query should only touches 1.1597% (62510 out of 5390146) of the table. It should be fast given the key distribution of threadid 51506.

REALITY CHECK

No matter which version of MySQL (Oracle, Percona, MariaDB) you use, none of them can fight to one enemy they all have in common : The InnoDB Architecture.

InnoDB Architecture

CLUSTERED INDEX

Please keep in mind that the each threadid entry has a primary key attached. This means that when you read from the index, it must do a primary key lookup within the ClusteredIndex (internally named gen_clust_index). In the ClusteredIndex, each InnoDB page contains both data and PRIMARY KEY index info. See my post Best of MyISAM and InnoDB for more info.

REDUNDANT INDEXES

You have a lot of clutter in the table because some indexes have the same leading columns. MySQL and InnoDB has to navigate through the index clutter to get to needed BTREE nodes. You should reduced that clutter by running the following:

ALTER TABLE newbb_innopost
    DROP INDEX threadid,
    DROP INDEX threadid_2,
    DROP INDEX threadid_visible_dateline,
    ADD INDEX threadid_visible_dateline_index (`threadid`,`visible`,`dateline`,`userid`)
;

Why strip down these indexes ?

The first three indexes start with threadid
threadid_2 and threadid_visible_dateline start with the same three columns
threadid_visible_dateline does not need postid since it's the PRIMARY KEY and it's embedded

BUFFER CACHING

The InnoDB Buffer Pool caches data and index pages. MyISAM only caches index pages.

Just in this area alone, MyISAM does not waste time caching data. That's because it's not designed to cache data. InnoDB caches every data page and index page (and its grandmother) it touches. If your InnoDB Buffer Pool is too small, you could be caching pages, invalidating pages, and removing pages all in one query.

TABLE LAYOUT

You could shave of some space from the row by considering importthreadid and importpostid. You have them as BIGINTs. They take up 16 bytes in the ClusteredIndex per row.

You should run this

SELECT importthreadid,importpostid FROM newbb_innopost PROCEDURE ANALYSE();

This will recommend what data types these columns should be for the given dataset.

CONCLUSION

MyISAM has a lot less to contend with than InnoDB, especially in the area of caching.

While you revealed the amount of RAM (32GB) and the version of MySQL (Server version: 10.0.12-MariaDB-1~trusty-wsrep-log mariadb.org binary distribution, wsrep_25.10.r4002), there are still other pieces to this puzzle you have not revealed

The InnoDB settings
The Number of Cores
Other settings from my.cnf

If you can add these things to the question, I can further elaborate.

UPDATE 2014-08-28 11:27 EDT

You should increase threading

innodb_read_io_threads = 64
innodb_write_io_threads = 16
innodb_log_buffer_size = 256M

I would consider disabling the query cache (See my recent post Why query_cache_type is disabled by default start from MySQL 5.6?)

query_cache_size = 0

I would preserve the Buffer Pool

innodb_buffer_pool_dump_at_shutdown=1
innodb_buffer_pool_load_at_startup=1

Increase purge threads (if you do DML on multiple tables)

innodb_purge_threads = 4

GIVE IT A TRY !!!

MySql replication from 5.1 master to 5.6 slave keep crashing

Your master is not compatible with the 5.6 slave. binlog_checksum is only available on 5.6, and so is UUID. I have successfully replicated servers between 5.5 and 5.6. Perhaps you can check if these options can be disabled.

2014-11-13 14:03:07 1022 [Warning] Slave I/O: Notifying master by SET @master_binlog_checksum= @@global.binlog_checksum failed with error: Unknown system variable 'binlog_checksum', Error_code: 1193 2014-11-13 14:03:07 1022 [Warning] Slave I/O: Unknown system variable 'SERVER_UUID' on master. A probable cause is that the variable is not supported on the master (version: 5.1.71-log), even though it is on the slave (version: 5.6.21-log), Error_code: 1193