Re: Repair with keycache
Mohamed Badri wrote: Hi, just had a problem with a myisam table who reached 4GB of data, I increased the number of rows by doing : ALTER TABLE foo MAX_ROWS=10 This is EXACTLY what you want: http://www.peerfear.org/rss/permalink/2004/10/16/MySQLAndALTERTABLEGuiltyAsCharged http://www.peerfear.org/rss/permalink/2004/10/15/MaxTableSizeInMySQL REPAIR TABLE considered harmful would be a better blog post title for these guys! Kevin -- Use Rojo (RSS/Atom aggregator). Visit http://rojo.com. Ask me for an invite! Also see irc.freenode.net #rojo if you want to chat. Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html If you're interested in RSS, Weblogs, Social Networking, etc... then you should work for Rojo! If you recommend someone and we hire them you'll get a free iPod! Kevin A. Burton, Location - San Francisco, CA AIM/YIM - sfburtonator, Web - http://peerfear.org/ GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 99F1 4412 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Repair with keycache
thank you for the links, I can't change system variables at runtime, so the only choice I have is, probably, to stop mysql server set variables and then run another ALTER TABLE. ;-( I'm going to siwtch to mysql4 as soon as possible. Selon Kevin A. Burton [EMAIL PROTECTED]: Mohamed Badri wrote: Hi, just had a problem with a myisam table who reached 4GB of data, I increased the number of rows by doing : ALTER TABLE foo MAX_ROWS=10 This is EXACTLY what you want: http://www.peerfear.org/rss/permalink/2004/10/16/MySQLAndALTERTABLEGuiltyAsCharged http://www.peerfear.org/rss/permalink/2004/10/15/MaxTableSizeInMySQL REPAIR TABLE considered harmful would be a better blog post title for these guys! Kevin -- Use Rojo (RSS/Atom aggregator). Visit http://rojo.com. Ask me for an invite! Also see irc.freenode.net #rojo if you want to chat. Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html If you're interested in RSS, Weblogs, Social Networking, etc... then you should work for Rojo! If you recommend someone and we hire them you'll get a free iPod! Kevin A. Burton, Location - San Francisco, CA AIM/YIM - sfburtonator, Web - http://peerfear.org/ GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 99F1 4412 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] --- http://webmail.netbadri.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Repair with keycache
Mohamed Badri wrote: thank you for the links, I can't change system variables at runtime, so the only choice I have is, probably, to stop mysql server set variables and then run another ALTER TABLE. ;-( I'm going to siwtch to mysql4 as soon as possible. OH!... yeah... if you're on a 4 ver of mysql then I don't know what to tell you ;) Kevin -- Use Rojo (RSS/Atom aggregator). Visit http://rojo.com. Ask me for an invite! Also see irc.freenode.net #rojo if you want to chat. Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html If you're interested in RSS, Weblogs, Social Networking, etc... then you should work for Rojo! If you recommend someone and we hire them you'll get a free iPod! Kevin A. Burton, Location - San Francisco, CA AIM/YIM - sfburtonator, Web - http://peerfear.org/ GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 99F1 4412 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: repair with keycache during ADD INDEX
Hi Franz,
Check out
MAX_ROWS = #
and
AVG_ROW_LENGTH = #
options in http://www.mysql.com/doc/en/CREATE_TABLE.html
Set MAX_ROWS to a very very large number
You can set these with ALTER TABLE. See section 6.5.4 ALTER TABLE
Syntax using table options at the end. Use the MAX_ROWS
parameter and set it to some really large value (anything over 2^32
like 1000). This ALTER statement will cause your indexes to
be rebuilt using long pointers. You can also create the table this
way using MAX_ROWS option in create statement.
You can verify your changes by running SHOW TABLE STATUS before the
change then again after the change.
Have you done this already? Ohterwisde your key size will be too
small.
Ken
- Original Message -
From: Franz, Fa. PostDirekt MA [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Friday, October 17, 2003 2:34 AM
Subject: repair with keycache during ADD INDEX
Dear Sirs and Ladies,
I tried to put some keys on a large table (more than 100.000.000 in
13G)
with the Statement:
ALTER TABLE mytable
ADD INDEX i1 (COL1(4)),
ADD INDEX i2 (COL2(4)),
ADD INDEX i3 (COL3(12)),
ADD INDEX i4 (COL4(12)),
ADD INDEX i5 (COL5(5)),
ADD INDEX i5 (COL6(11));
After about 7 hours, a 'SHOW PROCESSLIST' doesn't say 'copy to
temp-table'
anymore but 'repair with keycache'.
Watching the the directory for this database, it seems to happpen at
the
time when all data from .MYD is copied to
the temp-table.
The 'repair with keycache' now lasts about 12 hours and I don' know
when it
comes to an end.
Is this a usual thing or did I do something wrong?
I thought 'repair with keycache' just happens while recovering of a
crashed
table.
How can I speed up this process ?
Is innodb on MySQL 4.x faster doing this ?
In order ro increase speed of creating an index, I increased the
key_buffer-varibale to 1024M,
which is 60% of the memory on that engine.
OS is SUSE-LINUX 8.0 (Kernel 2.4.4)
MySQL is 2.23.52.
FS is Reiser.
The Table is myisam and looks like this:
Field Type Null Key Default Extra
COL_A char(2)
COL1 varchar(4)
COL2 varchar(4)
COL6 varchar(11)
COL_B enum('-1','0','1','2') YES (NULL)
COL3 varchar(35)
COL4 varchar(55)
COL_C varchar(35)
COL_D varchar(55)
COL_E varchar(61)
COL_F varchar(30)
COL_5 varchar(5)
COL_G varchar(50)
COL_H varchar(50)
COL_I varchar(50)
COL_J varchar(14)
COL_K int(11) 0
COL_O int(11) 0
To make it a bit easier to read, i named all collumns with an index
with a
number (like COL_1).
This table will be filled once a week and there will be no further
updates
or inserts till one wwek later,
when it will be dropped and rebuild with new data.
It is the only table in this database and (except mysql's
privilege-tables)
the only table on this engine at all.
Please Help me
Klaus
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Repair with keycache state while ALTERing TABLE
Hi! On Nov 22, Robert Citek wrote: Hello Sergei, In going through the archives I found this message which is similar to an issue I am currently having. You mention to Consult the manual for details. I've check through the on-line manual: http://www.mysql.com/doc/en/index.html specifically, http://www.mysql.com/doc/en/SHOW_VARIABLES.html Unfortunately, this section does not provide an in-depth explanation of the variables, their settings, suggestions for values, min/max/range, etc. For example, you mention that myisam_max_sort_file_size should be 'big enough for MySQL to use repair by sort method'. Can you recommend any guidelines for setting this value? Or pointers to a place in the manual where I can find an explanation or set of guidlines? Currently, I have set myisam_max_sort_file_size to 3,145,728,000 (megabytes? since I'm using 3.23) and myisam_sort_buffer_size to 268,435,456. Yet, my database is still using the repair with keycache when I add an index with alter table ... Here's the relevant part from the code (myisam/mi_check.c, mi_too_big_key_for_sort()): rows * key-maxlength myisam_max_temp_length || rows * (key-maxlength - key-minlength) / 2 myisam_max_extra_temp_length in CREATE TABLE a ( t VARCHAR(254) NOT NULL, i INT NOT NULL, KEY k1 (t), KEY k2 (i)); k1-minlength=1, k1-maxlength=255 k2-minlength=k2-maxlength=4; (I don't remember exact values, but they should be close to what I said) Regards, Sergei -- MySQL Development Team __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/ /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Repair with keycache state while ALTERing TABLE
Hello Sergei, In going through the archives I found this message which is similar to an issue I am currently having. You mention to Consult the manual for details. I've check through the on-line manual: http://www.mysql.com/doc/en/index.html specifically, http://www.mysql.com/doc/en/SHOW_VARIABLES.html Unfortunately, this section does not provide an in-depth explanation of the variables, their settings, suggestions for values, min/max/range, etc. For example, you mention that myisam_max_sort_file_size should be 'big enough for MySQL to use repair by sort method'. Can you recommend any guidelines for setting this value? Or pointers to a place in the manual where I can find an explanation or set of guidlines? Currently, I have set myisam_max_sort_file_size to 3,145,728,000 (megabytes? since I'm using 3.23) and myisam_sort_buffer_size to 268,435,456. Yet, my database is still using the repair with keycache when I add an index with alter table ... BTW, I am using the latest stable version of MySQL (3.23.53a) on a Red Hat Linux 7.1 machine. Thanks in advance for any pointers/suggestions, - Robert - Hi! On Nov 05, Quentin Bennett wrote: Hi, When doing an Alter Table, what does state Repair with keycache Mean, and should it take 27 hours (so far) on a 26,000,000 record table. It means that MySQL - for some reasons - decided to add indexes with old slow method. It can easily take 27 hours for such a big table. Take a look at myisam_max_sort_file_size, myisam_max_extra_sort_file_size varialbles, and myisam_sort_buffer_size. The most important for you is the first one. It should be big enough for MySQL to use repair by sort method. Increasing the third can give you additional speed then. Consult the manual for details. Regards, Sergei - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: repair with keycache
http://www.mysql.com/doc/en/SHOW_PROCESSLIST.html Repair with keycache - The repair code is using creating keys one by one through the key cache. This is much slower than Repair by sorting. It is going to take a long time to build an index on a table that big, especially if you still have questions coming into the machine. -Original Message- From: Robert Citek [mailto:[EMAIL PROTECTED]] Sent: Thursday, November 21, 2002 12:33 PM To: MySQL Subject: repair with keycache Hello all, What does it mean to be in the Repair with keycache state, and how do I fix this? I am running MySQL v3.23.36 on a Red Hat 7.1 machine. One of my tables has about 21,000,000 (21 Million) records in it. I am trying to index one of the fields with the alter table command: mysql alter table foo add index (bar); bar is an unsigned integer. Unfortunately, the indexing never completes. So I ran a mysqladmin command: # mysqladmin processlist +--+--+-+ Command | Time | State| Info| +--+--+-+ Query | 3753 | Repair with keycache | alter table foo add index (bar) | +--+--+-+ What does Repair with keycache mean? Where can I find more information about this? What other information would I need to diagnose and fix this problem? I've searched the documentation and the archives, but nothing shows with replair with keycache. One course I have tried is to change these variables: key_buffer_size=256M sort_buffer=16M record_buffer=4M but I still get this error. Regards, - Robert - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: repair with keycache
Hello Jennifer, Thanks for your reply. At 02:07 PM 11/21/2002 -0800, Jennifer Goodie wrote: http://www.mysql.com/doc/en/SHOW_PROCESSLIST.html I guess I have an older/different version of the doc's. I was using the version that came with the Windows install of MySQL. Thanks for the URL. Repair with keycache - The repair code is using creating keys one by one through the key cache. This is much slower than Repair by sorting. So, now I know that repair with keycache is slower than repair by sorting. What can I do about that? That is, how can I make the repair use sorting? I would have thought they would be part of the alter table ... syntax, but I haven't seen anything obvious. It is going to take a long time to build an index on a table that big, especially if you still have questions coming into the machine. How can I speed up creating the index? Any pointers (i.e. URLs) for a machine that has 1GB of RAM, 2GB of swap, and 2 1GHZ PIII processors? BTW, I also have Paul DuBois's MySQL book as a resource. Again, I looked but did not find anything regarding keycache vs sorting. Thanks in advance, - Robert - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: repair with keycache
At 04:41 PM 11/21/2002 -0600, Robert Citek wrote: So, now I know that repair with keycache is slower than repair by sorting. What can I do about that? That is, how can I make the repair use sorting? I would have thought they would be part of the alter table ... syntax, but I haven't seen anything obvious. I just found this from the on-line MySQL manual: For MyISAM tables, you can speed up the index recreation part (which is the slowest part of the recreation process) by setting the myisam_sort_buffer_size variable to a high value. The current value is ~8M. What would be an appropriate size for a 1GB RAM machine with 2GB swap? Put another way, is there any reason I would not want to set this to 800M or more? Regards, - Robert - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Repair with keycache
Mike Baranski writes: Question about this, when I do mysqladmin processlist, I get Repair with keycache as the state, what exactly is this? Can this be optomized? Mike. P.S. Thanks for the gcc3 patch! You are welcome !! The above means that REPAIR table is using keycache. There is an option that will enable it to use sort_buffer instead. Read a manual on the subject. MySQL 4.0 will have REPAIR TABLE that only uses key_cache, but it's own, so it does not interfere with other tables -- Regards, __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, FullTime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
