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=arch...@jab.org
