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:mo...@fastmail.fm>mo...@fastmail.fm>
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=critt...@desktopcreatures.com>http://lists.mysql.com/mysql?unsub=critt...@desktopcreatures.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
