Not the answer you need?
Register and ask your own question!

Slow alter table

ale.capuale.capu EntrantInactive User Role Participant
Hi,
I've a BIG table of 80.000.000 record (quite 30GByte of data and 7GByte of index).
I need to do some alter table. Before do that I make a DISABLE KEYS and when I finish all ALTER TABLE i make a ENABLE KEYS on the tables.
My alters table are simply ADD 3 INDEX and 2 FIELDs...
I use a Intel Core 2 DUO with 2GHz and 3G of RAM on a 400GByte IDE disk at 10k rounds...
All this operations have take 4 Days... is normal ???

Tnk

Comments

  • sterinsterin Mentor Inactive User Role Contributor
    You have a lot of rows but it still sounds like an awful lot of time.

    Which storage engine are you using?

    Is it CPU load or IO load that is high on the server while creating the indexes?
  • ale.capuale.capu Entrant Inactive User Role Participant
    Hi,
    tnk you for you reply...
    I'm using a MyIsam storare engine and my CPU load is very very low (3-4%)... my problem is that MySQL use massively my IDE disk about 200.000.000 of read and 50.000.000 of write !!!!

    Probably MySQL is very wrong configured...
    I'm using my-medium coniguration file.

    Tnk a lot for your help,
    best regards,
    Ale
  • sterinsterin Mentor Inactive User Role Contributor
    Is this server doing a lot of other things or why have you chosen the my-medium configuration?

    My-medium sounds to small for a setup like yours.

    One of the main things that slowes down index creation is the server variable:
    myisam_sort_buffer_size

    If this is to small then basically all sorting when creating the index will be against disk and that is as you noticed _very_ slow. ;)

    Can you provide the output for SHOW PROCESSLIST?
    This tells us how mysql is creating the index.
  • ale.capuale.capu Entrant Inactive User Role Participant
    Oh... I need to run again my ALTER TABLEs for test SHOW PROCESSLIST... I'm preparing a TEST machine (like one on service) for tuning it...
    Have you some configuration to suggest me?? Or, do you know if exists a tools for automatic tuning MySql ??

    Tnk you very much for your help,
    Ale
  • sterinsterin Mentor Inactive User Role Contributor
    I din't understand if this was a standalone server or not but hare comes a few tip.

    Start the OS and the other applications that need to run on that server.
    Check how much RAM you have free.
    As a rule of thumb, when you are running MyISAM tables, 25% of this is what you should reserve for MySQL internal cache and the remaining 75% should be used for OS file cache.

    Then you can take a look at my-large or my-huge configs and decide which mostly relates to your needs.

    The most important variables that you can tune is:
    key_buffer_size
    sort_buffer_size

    and for the index creation:
    myisam_sort_buffer_size
    myisam_max_sort_file_size
  • tanjtanj Contributor Inactive User Role Advisor
    Hello,

    MySQL can be very slow when it comes to data warehousing. Trust me, I've got the same problems as yours ) You can use the variables that sterin gave you, but I did not see the performance rise by a large amount.

    ALTER TABLE operations are typically slower the more rows you have. I've already posted some bugs at MySQL and the guys there seem to be aware of this problem.

    I'm considering migrating to PostgreSQL for large data, the 8+ versions are considered to provide good performance for data logging purposes.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.