I have a question about:
If you want to regain some of the space used by the INNODB file you
will have to convert all INNODB tables to MYISAM (or dump them to
a SQL file), recreate the INNODB file (s) and then recreate the
original INNODB tables.
So, just to be clear, is this the right procedure:
1 - Dump INNODB tables to SQL, double and triple check integrity
2 - Shut down MySQL
3 - Remove data and log files at the shell level:
ib_logfile0
ib_logfile1
innodb_data_1
4 - Start MySQL
5 - Regenerate tables from SQL dumped in step 1
I assume I could also rename the files in step 3, just in case,
right?
Jeff;
On Fri, 8 Oct 2004 16:34:31 +0300, Dobromir Velev [EMAIL PROTECTED]
said:
Hi,
According to the manual -
http://dev.mysql.com/doc/mysql/en/OPTIMIZE_TABLE.html
http://dev.mysql.com/doc/mysql/en/InnoDB_File_Defragmenting.html
running a null ALTER statement - ALTER TABLE tbl-name type=INNODB; will
rebuild the table thus optimizing the way the table is written to the
disk.
It will fix the physical ordering of the index pages on the disk thus
improving the time MySQL needs to perform an index seek. It will not
decrease
the space used by the INNODB file but it could speed things up. If you
want
to regain some of the space used by the INNODB file you will have to
convert
all INNODB tables to MYISAM (or dump them to a SQL file), recreate the
INNODB
file (s) and then recreate the original INNODB tables. This process could
take a lot of time depending on the size of your tables so you should
proceed with care.
HTH
--
Dobromir Velev
[EMAIL PROTECTED]
http://www.websitepulse.com/
On Thursday 07 October 2004 22:07, Boyd E. Hemphill wrote:
The documentation is not clear on this point. Here is a quote:
'For BDB tables, OPTIMIZE TABLE is currently mapped to ANALYZE TABLE. It
was also the case for InnoDB tables before MySQL 4.1.3; starting from this
version it is mapped to ALTER TABLE.'
What is meant by its being mapped to ALTER TABLE? Too, what exactly
happens after 4.1.3? Is space, in fact, recovered and defragged?
Thanks for your time!
Best Regards,
Boyd E. Hemphill
MySQL Certified Professional
[EMAIL PROTECTED]
Triand, Inc.
www.triand.com
O: (512) 248-2278
M: (713) 252-4688
-Original Message-
From: Christopher L. Everett [mailto:[EMAIL PROTECTED]
Sent: Wednesday, October 06, 2004 6:23 PM
To: 'Mysql List'
Subject: Re: Repeated corruption with MySQL 4.1.x using FULLTEXT indexes
Ed Lazor wrote:
-Original Message-
From: Christopher L. Everett [mailto:[EMAIL PROTECTED]
Sent: Wednesday, October 06, 2004 1:47 AM
To: Mysql List
Subject: Repeated corruption with MySQL 4.1.x using FULLTEXT indexes
I have an application where I create a faily large table (835MB) with a
fulltext index. One of our development workstations and our production
server will run the script to load the table, but afterwards we have a
pervasive corruption, with out of range index index pointer errors.
Oddly, my development workstation doesn't have those problems.
My box and the ones having the problems have the following differences:
- my box runs ReiserFS, the problem boxes run XFS
- my box has a nice SCSI HD subsystem, the problem boxes do IDE.
All three boxes run Linux 2.6.x kernels, and my workstation and
production server share the same mobo. Come to think of it, I saw
similar corruption issues under 2.4.x series kernels and MySQL v4.0.x,
it just wasn't the show stopper it is now.
Also, on all three boxes, altering the table to drop an index and create
a new one requires a myisamchk -rq run afterwards when a fulltext index
either exists or gets added or dropped, which I'd also call a bug.
The problems you're describing are similar to what I've run into when
there have been hardware related problems.
One system had a problem with ram. Memory tests would test and report ram
as ok, but everything started working when I replaced the ram. I think it
was just brand incompatibility or something odd, because the ram never
gave any problems in another system.
I can generate the problem on much smaller data sets, in the mid tens of
thousands of records rather than the millions of records.
I'll do a memtest86 run on the development boxes overnight, but as I did
that
just after I installed linux on them and used the linux badram patch to
exclude
iffy sections of RAM, I don't think thats a problem.
One system had hard drive media slowly failing and this wasn't obvious
until
we ran several full scan chkdsks.
3 hard drives all of different brand, model size, and the problem
happening
in the same place on both? Not likely.
The funniest situation was where enough dust had collected in the CPU fan
to
cause slight over heating, which resulted in oddball errors.
This
