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 22.214.171.124, “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.