Members Members   Search Search   Help Help   Register Register   Login Login   Home Home
Home » Performance » MySQL » Problem with compressed tables
Problem with compressed tables [message #221] Wed, 04 October 2006 05:51 Go to next message
Bielie is currently offline Bielie
Messages: 3
Registered: October 2006
Junior Member
Hello,


I'm busy developing an application that uses a large database and would like to compress my tables to save on diskspace.

I tried to compress a myisam table with 150M rows and everything went fine according to the commandline tools

Initial table:
-rw-rw---- 1 mysql mysql 5482838160 2006-10-04 10:33 netflow_old.MYD
-rw-rw---- 1 mysql mysql 2120808448 2006-10-04 10:51 netflow_old.MYI

Then I did: 'myisampack -v netflow_old' and 5 minutes later the directory showed:
-rw-rw---- 1 mysql mysql 3810187229 2006-10-04 10:33 netflow_old.MYD
-rw-rw---- 1 mysql mysql 1024 2006-10-04 11:08 netflow_old.MYI

There where no errors. Next I did 'myisamchk -rq netflow_old' and 30 minutes later I got:
-rw-rw---- 1 mysql mysql 3810187229 2006-10-04 10:33 netflow_old.MYD
-rw-rw---- 1 mysql mysql 2388990976 2006-10-04 11:38 netflow_old.MYI

No errors...so far so good Smile

But when I did 'myisamchk netflow_old', it returned:
Checking MyISAM file: netflow_old
Data records: 152301060 Deleted blocks: 0
myisamchk: warning: Table is marked as crashed
- check file-size
myisamchk: warning: Size of indexfile is: 2388990976 Should be: 2120808448
myisamchk: error: Size of datafile is: 3810187229 Should be: 5482838160
- check record delete-chain
- check key delete-chain
- check index reference
- check data record references index: 1
myisamchk: error: Found key at page 426564608 that points to record outside datafile
MyISAM-table 'netflow_old' is corrupted
Fix it using switch "-r" or "-o"



Did I do something wrong? (I can't use the table anymore now)

I use the mysql package from debian-stable (mysql Ver 12.22 Distrib 4.0.24, for pc-linux-gnu (i386))



Kind regards,
Dirk
Report message to a moderator
Re: Problem with compressed tables [message #224 is a reply to message #221 ] Wed, 04 October 2006 08:39 Go to previous message
Peter is currently offline Peter
Messages: 405
Registered: August 2006
Senior Member
Super Guru
Note:

-rw-rw---- 1 mysql mysql 2120808448 2006-10-04 10:51 netflow_old.MYI

myisamchk: warning: Size of indexfile is: 2388990976 Should be: 2120808448
myisamchk: error: Size of datafile is: 3810187229 Should be: 5482838160

It looks like index header was not updated. Could MySQL server be running on this data or something like it.

I would recheck it and try to repair table again.

Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/
Report message to a moderator
Previous Topic:Performance optimisation for very large datasets
Next Topic:Binaries / Compiled
Goto Forum:
  

[ Generate printable PDF ] [ Syndicate this forum (XML) ] [ ]

Current Time: Fri Jul 10 01:46:57 EDT 2009

Total time taken to generate the page: 0.01023 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 2.7.7.
Copyright ©2001-2007 FUD Forum Bulletin Board Software

MySQL is a trademark of Sun Microsystems.
InnoDB is a trademark of Oracle Corp.

Percona Performance Forums are a service of Percona, Inc.
Not affiliated with Sun Microsystems or Oracle Corp.