MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:mos Date:January 7 2009 8:16pm
Subject:Re: Locking database when 'creating sort index'
View as plain text  
At 01:07 PM 1/7/2009, David Scott wrote:
>1) InnoDb
>2) 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?

David,
      I was looking to see if the other queries were waiting on table 
locks. So from what you're telling me the other queries are executing, just 
more slowly. Is that correct? Or are they queued up waiting for your large 
query to finish?

I'm not that familiar with Innodb but I did find a thread by Heikki a while 
back with a user having a similar problem. 
http://markmail.org/message/kmplwau22ualufmg#query:related%3Akmplwau22ualufmg+page:1+mid:s6ufcutbxika2a44+state:results

So you need to determine if the MySQL server is disk bound or CPU bound and 
that will help in solving the problem.

Mike


>2009/1/7 mos <<mailto:mos99@stripped>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>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