Professional Web Applications Themes

How long does indexing via ALTER TABLE take? - MySQL

I loaded about 15 million records into a MySQL table, and then did an ALTER TABLE to add an index on a BIGINT field. The ALTER TABLE has been running for about 30 hours now. About how long should that take? It took less than 24 hours to create the table with INSERT statements. Does an ALTER TABLE force a rebuild of all the table indices, or are the existing ones copied? Thanks. John Nagle...

  1. #1

    Default How long does indexing via ALTER TABLE take?

    I loaded about 15 million records into a MySQL table, and
    then did an ALTER TABLE to add an index on a BIGINT field.
    The ALTER TABLE has been running for about 30 hours now.
    About how long should that take? It took less than
    24 hours to create the table with INSERT statements.

    Does an ALTER TABLE force a rebuild of all the table
    indices, or are the existing ones copied?

    Thanks.

    John Nagle
    John Guest

  2. #2

    Default Re: How long does indexing via ALTER TABLE take?

    John Nagle wrote:
     


    I would say 3-20 minutes depending on the hardware, so long as box is doing
    nothing else.

    What does SHOW PROCESSLIST say?


    --
    Brian Wakem
    Email: http://homepage.ntlworld.com/b.wakem/myemail.png
    Brian Guest

  3. #3

    Default Re: How long does indexing via ALTER TABLE take?

    Brian Wakem wrote: 
    >
    >
    >
    > I would say 3-20 minutes depending on the hardware, so long as box is doing
    > nothing else.
    >
    > What does SHOW PROCESSLIST say?
    >[/ref]

    +-------+-----------+-----------+-----------+---------+-------+----------------------+------------------------------------------+
    | Id | User | Host | db | Command | Time | State
    | Info |
    +-------+-----------+-----------+-----------+---------+-------+----------------------+------------------------------------------+
    | 38101 | sitetruth | localhost | sitetruth | Query | 85334 | Repair with
    keycache | alter table infotus add primary key (id) |
    | 38268 | sitetruth | localhost | NULL | Query | 0 | NULL
    | show processlist |
    +-------+-----------+-----------+-----------+---------+-------+----------------------+------------------------------------------+
    2 rows in set (0.04 sec)

    It's been running for about 30 hours now, on an otherwise-idle dedicated
    server. MySQL 5 on Fedora Core 6, default Plesk installation.

    -bash-3.1$ ps ax
    PID TTY STAT TIME COMMAND
    1793 ? S 0:00 /bin/sh /usr/bin/mysqld_safe
    --defaults-file=/etc/my.cnf --pid-file=/var/run/mysqld/mysqld.pid
    --log-error=/var/log/mysqld.log
    1829 ? Sl 541:14 /usr/libexec/mysqld --defaults-file=/etc/my.cnf
    --basedir=/usr --datadir=/var/lib/mysql --user=mysql
    --pid-file=/var/run/mysqld/mysqld.pid --skip-external-locking
    --socket=/var/lib/mysql/mysql.sock

    It's continuing to use CPU time, and the MySQL server is responding properly
    to other requests. The "Time" value in SHOW PROCESSLIST continues to go up.

    John Nagle
    John Guest

  4. #4

    Default Re: How long does indexing via ALTER TABLE take?

    John Nagle <com> wrote: [/ref][/ref]

    This clearly depends on your hardware and the configuration of the
    MySQL server.
     
    >
    > +-------+-----------+-----------+-----------+---------+-------+----------------------+------------------------------------------+
    >| Id | User | Host | db | Command | Time | State
    > | Info |
    > +-------+-----------+-----------+-----------+---------+-------+----------------------+------------------------------------------+
    >| 38101 | sitetruth | localhost | sitetruth | Query | 85334 | Repair with
    > keycache | alter table infotus add primary key (id) |
    > +-------+-----------+-----------+-----------+---------+-------+----------------------+------------------------------------------+[/ref]

    Repair (correctly: add index) with keycache is the slow method. The
    faster one would use the sort buffer. But even the slower keycache
    method should not take *so* long. Probably you have configured
    key_buffer very small (or not at all, using the default of 8MB).

    You should configure key_buffer to 25-50% of your memory and
    myisam_sort_buffer to approx. half that size. If you intend to add
    a lot of indexes, you can even temporarily make myisam_sort_buffer
    the bigger setting. This will greatly increase performance of
    indexing operations.

    For 15.000.000 rows with a BIGINT sort column you will need
    (8 Byte (BIGINT) + 4 Byte (row pointer)) * 15.000.000 = 180MB
    sort buffer to do the sorting in memory. This should finish in
    few minutes then.
     
     

    The repair table is running since 85334 seconds while mysqld used only
    541:14 = 32474 seconds of cpu time (and I guess mysqld consumed some
    of this time even before you started that ALTER TABLE statement).
    The computer is clearly I/O bound, waiting mostly for the disk.


    XL
    --
    Axel Schwenke, Support Engineer, MySQL AB

    Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
    MySQL User Forums: http://forums.mysql.com/
    Axel Guest

  5. #5

    Default Re: How long does indexing via ALTER TABLE take?

    John Nagle wrote: [/ref][/ref]

    Apparently MySQL is known to be very slow when "Repair with keycache"
    is being used, and it will use that when the MySQL configuration parameters
    or the disk space situation prevent sorting. See

    http://forums.mysql.com/read.php?21,86150,86150#msg-86150

    MySQL support recommended making

    myisam_max_sort_file_size
    myisam_max_extra_sort_file_size

    large enough to allow a full sort of the database. SHOW VARIABLES gives me

    myisam_max_sort_file_size 2147483647

    which is only 2GB. There's no value for

    myisam_max_extra_sort_file_size

    but that didn't do anything anyway and was removed, per

    http://bugs.mysql.com/bug.php?id=10163

    So apparently I need to restart the server, then, as SUPER,

    SET GLOBAL myisam_max_sort_file_size = 30000000000;

    then try the ALTER TABLE again. Is that correct?

    What's the proper way to shut down the long-running ALTER TABLE
    job?

    John Nagle
    John Guest

  6. #6

    Default Re: How long does indexing via ALTER TABLE take?

    John Nagle wrote:
     
    >>
    >>
    >>
    >> I would say 3-20 minutes depending on the hardware, so long as box is
    >> doing nothing else.
    >>
    >> What does SHOW PROCESSLIST say?
    >>[/ref]
    >
    >[/ref]
    +-------+-----------+-----------+-----------+---------+-------+----------------------+------------------------------------------+ 
    +-------+-----------+-----------+-----------+---------+-------+----------------------+------------------------------------------+ 
    +-------+-----------+-----------+-----------+---------+-------+----------------------+------------------------------------------+ 


    'Repair with keycache' is the key to this problem - it is VERY slow.

    If you google for it you should find some info about 'Repair by sort' being
    much faster, you need to increase your myisam_max_sort_file_size.



    --
    Brian Wakem
    Email: http://homepage.ntlworld.com/b.wakem/myemail.png
    Brian Guest

  7. #7

    Default Re: How long does indexing via ALTER TABLE take?

    Axel Schwenke wrote: 
     [/ref][/ref]
     

    I'll definitely reconfigure for next time. Thanks.

    The ALTER TABLE finally finished successfully after 32 hours.

    John Nagle
    John Guest

Similar Threads

  1. alter table alter column syntax problem
    By Chuck in forum MySQL
    Replies: 4
    Last Post: September 5th, 05:17 PM
  2. ALTER TABLE ALTER COLUMN
    By Jacco in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: August 7th, 05:59 PM
  3. alter a DMS table space
    By xixi in forum IBM DB2
    Replies: 0
    Last Post: August 4th, 09:18 PM
  4. alter table/tables
    By Jacco Schalkwijk in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 11th, 08:41 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139