MySQL Forums
Forum List  »  MyISAM

Problem with 4GB table limit
Posted by: Joe Ammann
Date: February 27, 2006 04:51AM

Hi everybody

I got the task to setup a rather big database with MySQL 4.1 on Solaris. I know a lot about Oracle, but I am not very familiar with MySQL, especially not with the quirks of managing big databases.

I learned quite some things during the last few days. But now I'm hitting a problem.

When I started loading one of the big tables, I did not know about the 4GB limitation on the datafile that MyISAM imposes. Now, I'm having a table, with 35 Mio rows loaded, about 3.8GB data size, and 4.7GB index size.

I need to load at least 2 more GB into that table. So I have to reorganize it.

I tried ALTER TABLE xxx MAX_ROWS 200000000 AVG_ROW_LENGTH 120. This seems adequate. I ran it with a key cache size of 4GB, and large MyISAM sort sizes (2GB). Nevertheless, it ran for about 24 hours, before it crashed with a SEGV. It spend around 12 hours "COPYING TO TMP TABLES" and then about 12 hours more "REPAIRING WITH KEYCACHE".

I don't care so much about the SEGV. My question is: How can I perform this statement in something like 12 hours? Any tipps. The machine is a 2-CPU 1.2 GHz Sparc III. I can spend up to 7 GB RAM. The disk system is capable of doing about 350 IO/sec.

What kind of tuning knobs do I have to make it go faster? Drop the index before doing it (though i made bad experiences with recreating the indices). Some more tuning variables I don't know about? Preloading the INDEX into cache?

Any pointers would be appreciated!

CU, Joe

Options: ReplyQuote


Subject
Views
Written By
Posted
Problem with 4GB table limit
4706
February 27, 2006 04:51AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.