vendredi 6 février 2015

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. (MyISAM sort buffer maybe?) 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.)





Aucun commentaire:

Enregistrer un commentaire