MariaDB

alter table from myisam to maria 'hung'

Asked by PetePDX

O/S: Fedora 11 x86_64

MySQL: mysql-server-5.1.42-7.fc11.x86_64 from Fedora 11

MariaDB: built from tar

# mysql --version
mysql Ver 14.16 Distrib 5.1.42-MariaDB, for unknown-linux-gnu (x86_64) using EditLine wrapper

Statement: MariaDB [cisd]> alter table TVP581BILLIMAGHIST engine = MARIA;

This is a medium to large table
/data4/DATABASES/mysql/cisd/TVP581BILLIMAGHIST.MYD 34,187,025,612 bytes
/data1/DATABASES/MYSQL/cisd/TVP581BILLIMAGHIST.MYI 8,368,602,112 bytes

where the files are now

9,087,475,712 2010-02-26 09:43 /data1/DATABASES/MYSQL/cisd/#sql-55af_2.MYI.MAI
8,368,602,112 2009-12-02 20:28 /data1/DATABASES/MYSQL/cisd/TVP581BILLIMAGHIST.MYI
28,721,569,792 2010-02-26 09:00 /data4/DATABASES/mysql/cisd/#sql-55af_2.MYD.MAD
34,187,025,612 2009-12-02 19:59 /data4/DATABASES/mysql/cisd/TVP581BILLIMAGHIST.MYD

Rows: 117,757,616

MariaDB [cisd]> describe TVP581BILLIMAGHIST;
+-------------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+--------------+------+-----+---------+-------+
| CD_COMPANY_SYSTEM | char(4) | NO | PRI | NULL | |
| NO_ACCOUNT | int(11) | NO | MUL | NULL | |
| DT_PRINT | datetime | NO | PRI | NULL | |
| NO_CYCLIC | smallint(6) | NO | | NULL | |
| NO_INVOICE | int(11) | NO | PRI | NULL | |
| CD_REC_TYPE | char(3) | NO | | NULL | |
| TXT_BILL_CONTENT | varchar(500) | NO | | NULL | |
| NO_LEGAL_ENTITY | int(11) | NO | PRI | NULL | |
| NO_COMBINE_PRT | int(11) | NO | PRI | NULL | |
| NO_BILL_SORT | int(11) | NO | PRI | NULL | |
+-------------------+--------------+------+-----+---------+-------+

| TVP581BILLIMAGHIST | CREATE TABLE `TVP581BILLIMAGHIST` (
  `CD_COMPANY_SYSTEM` char(4) NOT NULL,
  `NO_ACCOUNT` int(11) NOT NULL,
  `DT_PRINT` datetime NOT NULL,
  `NO_CYCLIC` smallint(6) NOT NULL,
  `NO_INVOICE` int(11) NOT NULL,
  `CD_REC_TYPE` char(3) NOT NULL,
  `TXT_BILL_CONTENT` varchar(500) NOT NULL,
  `NO_LEGAL_ENTITY` int(11) NOT NULL,
  `NO_COMBINE_PRT` int(11) NOT NULL,
  `NO_BILL_SORT` int(11) NOT NULL,
  PRIMARY KEY (`CD_COMPANY_SYSTEM`,`DT_PRINT`,`NO_LEGAL_ENTITY`,`NO_INVOICE`,`NO_COMBINE_PRT`,`NO_BILL_SORT`),
  KEY `XVP5812` (`NO_INVOICE`,`CD_COMPANY_SYSTEM`),
  KEY `XVP5811` (`NO_ACCOUNT`,`CD_COMPANY_SYSTEM`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 DATA DIRECTORY='/data4/DATABASES/mysql/cisd/' INDEX DIRECTORY='/data1/DATABASES/MYSQL/cisd/' |

The conversion of the data file completed in about 4-5 hours. But the conversion of the index file
seems to have become frozen. I say that in the file has not changed (checked using sum) in about 4 hours.
The build of the index file started approx 17 hours ago. The new data and index files are being accessed in that
their time stamps are changing

My question comes from both I/O and CPU usage being basically zero.

Ideas ? or just wait a day or two ?

Question information

Language:
English Edit question
Status:
Solved
For:
MariaDB Edit question
Assignee:
No assignee Edit question
Last query:
Last reply:
Revision history for this message
Colin Charles (ccharles) said : 		#1

Hi!

Can you provide us a copy of the MyISAM files? At least samples of what the data is in the fields?

We'd like to run this on a machine, because we do believe this to be a bug.

Cheers,
-c

Revision history for this message
Colin Charles (ccharles) said : 		#2

Incidentally, if you need a place to upload some of this (because we do need a repeatable test case), use the FTP service:
http://askmonty.org/wiki/index.php/FTP

Cheers!

Revision history for this message
PetePDX (pete-petelancashire) said : 		#3

ok how much in GB do you want ? The sizes of the MYD & MYI are:

TVP581BILLIMAGHIST.MYD 34,187,025,612 bytes
TVP581BILLIMAGHIST.MYI 8,368,602,112 bytes

just shy of 40 GB

-pete

Revision history for this message
Colin Charles (ccharles) said : 		#4

If the data is repetitive (which is likely), we just need some small samples, so we can repeat it (i.e. make it 40GB in size ourselves).

If you can also repeat this with say, 1GB of the same structure, i.e. a smaller repeatable test case, we'll gladly take that.

Thanks!

Revision history for this message
PetePDX (pete-petelancashire) said : 		#5

Although the FTP is hung you should have the whole table. The file was created using mysqldump
then compress with bzip2

The size of the file is 1640157027 bytes the decompress size is 38881234777 bytes.

sha1sum is 45cd7c8eb6377342c9cf4243ea2ea262bc533fa5

md5sum is 74fd23c4104397d47514dcc17d4db990

let me know if it is not

btw the alter table is still 'running' :-)

Time 1029685, State copy to tmp table

-pete

Revision history for this message
Launchpad Janitor (janitor) said : 		#6

This question was expired because it remained in the 'Open' state without activity for the last 15 days.

Revision history for this message
PetePDX (pete-petelancashire) said : 		#7

any chance to try to perform the alter table ?

-pete

Revision history for this message
Launchpad Janitor (janitor) said : 		#8

This question was expired because it remained in the 'Open' state without activity for the last 15 days.

Revision history for this message
Michael Widenius (monty) said : 		#9

Sorry for not noticing this sooner (We have not been actively monitoring 'Answers', only Bugs)

I have now uploaded the your test case to my computer and will try to repeat this shortly.

Revision history for this message
Michael Widenius (monty) said : 		#10

This sounds like a bug I fixed some months back. I still wanted to ensure this was fixed:

The uploaded file was only 1322773072 bytes, so I assume something is missing from the end.

I did use what I got to try to repeat the problem.
Note: To get a quick ALTER you should set maria_pagecache_buffer_size and maria_sort_buffer_size to reasonable values. For example to at least 128M each. (If this is a dedicated machine, then set maria.pagecache_buffer_size to 1/4 of your memory).

I loaded your file into a MyISAM table with MariaDB 5.1.49 run in ddd and compiled for debugging (my normal testing environment) with a key cache of 16M (very small). This took 2 h, 40 min. (The time is here just for those curious to know how much things are slower when debugging).

After loading the file I had 95882090 rows in the table. Size of data file was: 27897214596.

I did then run the ALTER with an optimized MariaDB (not under ddd) with an 128M cache.
This took 33 min minutes (even if I was compiling and testing MariaDB on the same computer at the same time). The size of the data file in the Aria format was: 30685454336 (a bit bigger, but not that much. This is because the Aria format allows in-place updates and it needs a little meta data information to be able to do this.

A little background on what takes time in ALTER TABLE.
- First data is copied from MyISAM to Aria with only the unique keys enabled.
- Then we scan the record file and generate the other keys with sorting. One scan per key.

Conclusion: The problem you described has been fixed. Try again with 5.1.49!

Revision history for this message
Michael Widenius (monty) said : 		#11

Change status to solved as the problem doesn't exist anymore