MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:mos Date:January 7 2009 5:51pm
Subject:Re: Locking database when 'creating sort index'
View as plain text  
At 11:20 AM 1/7/2009, you wrote:
>When we run a large query other queries start to back up when the large one
>gets to the 'creating sort index' phase, this lock seems to affect the whole
>server, all databases... does anyone know what may be causing this?
>Thanks in advance
>--
>David Scott

David,
        Can you provide us with more info?

1) Is this an InnoDb table or  MyISAM?
2) What version of MySQL are you using?
3) Are you using Create Index or Alter Table? Can you give us the syntax 
you are using?
4) How long does it take? Can you give us the table structure & # of indexes?
5) Are these queries that are backed up, referencing the table you are 
building the index on?
6) Can you provide us with a Show Process List?

This should help the members of this list give you a better more informed 
answer.

Offhand I suspect your key_buffer_size may be too low and MySQL is 
attempting to build the index on disk rather than in memory. If the index 
can be built in memory it will be 10x faster than building the index on 
disk. That is why adding as much ram as possible to your server will help.

This is set in your my.cnf file:

# Size of the Key Buffer, used to cache index blocks for MyISAM tables.
# Do not set it larger than 30% of your available memory, as some memory
# is also required by the OS to cache rows. Even if you're not using
# MyISAM tables, you should still set it to 8-64M as it will also be
# used for internal temporary disk tables.
key_buffer_size=500M

If you increase your key_buffer size from the default value to 30% of your 
memory, you should get indexes built faster.

Mike 

Thread
Locking database when 'creating sort index'David Scott7 Jan
  • Re: Locking database when 'creating sort index'Johan De Meersman7 Jan
  • Re: Locking database when 'creating sort index'mos7 Jan
    • Re: Locking database when 'creating sort index'David Scott7 Jan
      • Re: Locking database when 'creating sort index'David Scott7 Jan
        • Re: Locking database when 'creating sort index'Chandru8 Jan
      • Re: Locking database when 'creating sort index'mos7 Jan