Be careful with using InnoDB with large tables. Performance drops
quickly and quite a bit once the size exceeds your RAM capabilities.
On Mar 1, 2009, at 3:41 PM, Claudio Nanni wrote:
Hi Baron,
I need to try some trick like that, a sort of offline index building.
Luckily I have a slave on th
Hi Baron,
I need to try some trick like that, a sort of offline index building.
Luckily I have a slave on that is basically a backup server.
Tomorrow I am going to play more with the dude.
Do you think that there would be any improvement in converting the table
to InnoDB
forcing to use multiple
Claudio,
http://www.mysqlperformanceblog.com/2007/10/29/hacking-to-make-alter-table-online-for-certain-changes/
Your mileage may vary, use at your own risk, etc.
Basically: convince MySQL that the indexes have already been built but
need to be repaired, then run REPAIR TABLE. As long as the ind
Yes I killed several times the query but now way, the server was continuing
to hog disk space and not even shutdown worked!
Thanks!
Claudio
2009/2/27 Brent Baisley
> MySQL can handle large tables no problem, it's large queries that it
> has issues with. You couldn't just kill the query instead
Hi Rolando,
I am going to give it a try, but the thing is that the creation of index
with MyISAM table causes
a re-copy of the table (using temporary table) and so it is the same thing,
and seens to take a lot of time.
I stopped it after 10 hours or so.
I think is the way mysql manages the creation
Have you tried disabling indexes while loading?
Here is what I mean...
CREATE TABLE tb1 (A INT NOT NULL AUTO INCREMENT PRIMARY KEY,B VARCHAR(20),C
VARCHAR(10));
Load tb1 with data
Create a new table, tb2, with new structure (indexing B and C columns)
CREATE TABLE tb2 LIKE tb1;
ALTER TABLE tb2 A
Great Brent, helps a lot!
it is very good to know your experience.
I will speak to developers and try to see if there is the opportunity to
apply the 'Divide et Impera' principle!
I am sorry to say MySQL it is a little out of control when dealing with
huge tables, it is the first time I had to k
