Large MySQL operations slowing down SELECT queries on unrelated tables On a fairly busy SQL server, we sometimes need to do large index-building operations (after importing ~6GB tables).  The indexes are built using the more efficient "Repair by sorting" method, rather than keycache.

We have found that when these operations are running, SELECT queries on other tables in the database can get bogged down.  These tend to be queries that were already fairly expensive in terms of rows scanned; for example, we see queries that normally would take ~5s taking ~25s instead.  All relevant tables are MyISAM, as they are effectively read-only.  (We do these large imports daily, and when they are finished, atomically replace the live table with the newly imported one.  Aside from that, the live tables are not modified.)

We track CPU load and usage, memory, and disk access times, and there appears to be plenty of headroom on all fronts.  So the problem doesn't appear to be that we are simply maxing out on disk I/O for instance.  Therefore my guess is that we're running into a MySQL-specific limit - some sort of buffer or cache that's shared between connections.  I'm not a DBA though, so that's about as far as I've gotten.  Any ideas on what could be causing this?  I'm happy to provide any relevant info.  Here's our my.cnf:

[mysql]
no-auto-rehash
[mysqld]
tmp_table_size=2048M
max_heap_table_size=2048M
init-file="/etc/mysqlinit.sql"
thread_cache_size=8
query_cache_size=256M
query_cache_type=DEMAND
innodb_log_file_size=10485760
character-set-server=utf8
default-storage-engine=MyISAM
myisam_sort_buffer_size=64M
skip-external-locking
ft_min_word_len=3
innodb_buffer_pool_size=2048M
thread_concurrency=8
open_files_limit=10000
max_allowed_packet=268435456
sort_buffer_size=2M
read_rnd_buffer_size=2M
socket="/db/mysql/mysql.sock"
key_buffer=512M
tmpdir="/db/mysql-tmp"
datadir="/db/mysql"
read_buffer_size=2M
innodb_file_per_table=1
wait_timeout=30
max_connections=500
[mysqldump]
quick
max_allowed_packet=16M
[myisamchk]
key_buffer=128M
read_buffer=2M
write_buffer=2M
sort_buffer_size=128M
[isamchk]
key_buffer=128M
read_buffer=2M
write_buffer=2M
sort_buffer_size=128M
[client]
socket="/db/mysql/mysql.sock"


The server has 16GB of RAM, of which we rarely use more than 6 except for caching.  (Although would like to leave some headroom for traffic spikes, as the web server runs on the same box.)  It has 8 cores, and the databases are on a RAID 10 array of 120GB SSDs (at /db).

Any tuning suggestions would be appreciated.  (Please give reasoning though, not just suggested settings.)

Edit: based on this page, it appears that key_buffer_size might be the culprit.  This buffer is indeed shared between connections (by default), and I expect the indexing operation is using it up, bogging down other queries.  I will experiment with the multiple caches approach recommended there and report back:


  For key_buffer_size, you must take into account that the buffer is
  shared with those users. [...]
  An alternative that avoids this problem is to use a separate key cache,
  assign to it the indexes from the table to be repaired, and deallocate
  it when the repair is complete. See Section 8.9.2.2, “Multiple Key
  Caches”.


Edit 2:
Tangentially related: based on this MariaDB doc page, I increased our overall key buffer size from 512MB to 2GB and noticed an order of magnitude better ratio of key_read_requests to key_reads in the status.  (From about 1:150 to 1:1000. So that change improved key cache hits by about 7 times.)

Edit 3:
While increasing the key buffer size seems to have helped performance overall, using a separate key buffer for the table being imported does not prevent it from slowing queries on other tables, so the initial question remains unanswered.
The real problem is all the I/O that is needed in creating indexes on huge tables.

When the index is being created, does SHOW PROCESSLIST say "Repair by key_buffer" or "by sorting"?  Sorting is better for large tables.

Are you running only MyISAM?  If so, make these changes:

innodb_buffer_pool_size = 0
key_buffer_size = 3G


However, if you switched to InnoDB and a new enough version of MySQL, you could CREATE/DROP indexes "online" -- that is, without recreating the table (as happens in MyISAM).  This would significantly decrease the I/O, hence cut back on the interference with the other queries.

A 5 second query using SSDs and RAID-10?  Yikes!  What is the query doing?  Please show us the query, together with SHOW CREATE TABLE.  It can possibly be sped up to be sub-second.

You say it is not I/O -- would you share the details of the evidence.

Yes, into "Multiple Key Caches".

query_cache_size=256M


is too high.  Don't go above about 50M, so as to avoid lots of overhead in "pruning".

Summary:


Change some settings;
Switch to InnoDB;
Speed up the 5-25s query.