0

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.

1

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.
  • We are using InnoDB for most tables. These tables though are read-only, so MyISAM should be faster. Also we use the Merge feature, so need MyISAM for that as well. - As mentioned in the question, it is "by sorting". - I know it's not I/O because I track the queue times of all drives and partitions using Cacti, and the SQL array never exceeds a few ms. - query cache: note that we're using "DEMAND" type. The only queries set to use query cache are on tables that change very rarely, so we can afford a large setting. (We did performance testing at several levels and found this to be optimal.) – Nathan Stretch Feb 7 '15 at 6:23
  • 1
    MyISAM for this table -- OK. QC usage -- OK. Can you use a "compound index"? Another approach: LOAD the table on another machine, build the indexes there, then play file games with the 3 MyISAM files, plus a RENAME. Bonus: essentially no 'downtime' for that table. – Rick James Feb 7 '15 at 6:42
  • 1
    SELECT ... WHERE a=1 AND b=2 ORDER BY c -- should use all of INDEX(a,b,c). But change that to "b>2" and it won't. That is, a limited set of things will work, and it is all too easy to trip over an exception. Furthermore, if you have a LIMIT on the end, you must get all the way through the index to be able to stop at the LIMIT and not end up with a big sort. – Rick James Feb 7 '15 at 17:29
  • 1
    Yes, "too many" indexes is somewhat bad: (a) slowing down INSERTs, and (b) filling up disk, and (c) making more work for the optimizer. None of those are necessarily show-stoppers, so there is no hard and fast rule against having a lot of indexes. (Usually a lot of indexes implies a novice who does not understand much about indexes.) – Rick James Feb 7 '15 at 17:31
  • 1
    I have put a bunch of comments in the chat. – Rick James Feb 7 '15 at 21:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.