MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:David Scott Date:January 7 2009 7:07pm
Subject:Re: Locking database when 'creating sort index'
View as plain text  
1) InnoDb2) 5.0.51 on Linux
3) No, a Select with a bunch of Joins, a Where, group and order
4) 37 seconds
5) Yes
6) Show Processlist does not show anything, just the user, what are you
looking for?

2009/1/7 mos <mos99@stripped>

> 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
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=1
>
>
>

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