space dedicated to the sort file. It started
using Repair with Filesort for the first 7 hours, then it switched to
Repair with KeyCache.
It should only use one of the Repair methods. If it switched then
something was wrong with Repair by filesort (maybe out of disk space?)
Look in your error
into the table using Load Data Infile with the keys disabled,
but over 49 hours (maybe a lot more) to rebuild 3 indexes. Even with 100g
of disk space dedicated to the sort file. It started using Repair with
Filesort for the first 7 hours, then it switched to Repair with
KeyCache. I don't know anyone who
. It started
using Repair with Filesort for the first 7 hours, then it switched
to Repair with KeyCache.
It should only use one of the Repair methods. If it switched then
something was wrong with Repair by filesort (maybe out of disk space?)
Look in your error log and see if there is any message
Process List shows Info=load data infile ... and State=Repair
with keycache. The table has a few hundred million rows of data. I assume
it is using Repair with keycache to rebuild the indexes after the data
has been loaded.
From what I've read Repair with keycache is going to take a huge
reasonably fast that because running Load Data Infile on an empty table
will disable the keys until all the data is loaded, so explicitly disabling
before hand them won't help. After the data is loaded, Load Data Infile
will then rebuild the keys and will choose to use Repair with keycache
and will choose to
use Repair with keycache whereas Repair with filesort would be
100x-1000x faster. There doesn't seem to be any way to get it to use
Repair with filesort.
So I could use disable keys as you had suggested and then rebuild them
manually with myisamchk repair with sort provided
Data Infile will then rebuild the keys and will choose to use Repair
with keycache whereas Repair with filesort would be 100x-1000x faster.
There doesn't seem to be any way to get it to use Repair with filesort.
So I could use disable keys as you had suggested and then rebuild them
manually
won't help. After the data is
loaded, Load Data Infile will then rebuild the keys and will choose
to use Repair with keycache whereas Repair with filesort would
be 100x-1000x faster. There doesn't seem to be any way to get it to
use Repair with filesort.
So I could use disable keys as you had
I have a 50g CSV file that I am trying to import into an empty MyISAM
table. It appears to go fine except after 10 hours it hasn't completed. A
Show Process List shows Info=load data infile ... and State=Repair
with keycache. The table has a few hundred million rows of data. I assume
is in state :
Repair with keycache
It seems to be really slow, how can I improve speed ?
or how can I get more infos about what mysql is doing ?
I try to set global variables like myisam_sort_buffer_size
but it didn't work.
I use mysql v 3.23.57
any help wil be appreciated.
thanks
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
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
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
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
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
) 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
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
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
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
.
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
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
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.
And another issue: When searching the archives, I'll get something like
4. Re: Repair with keycache
http://lists.mysql.com/cgi-ez/ezmlm-cgi?1:mss:79389
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!
-
Before posting, please check:
http
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
24 matches
Mail list logo