Mysql-5.6.30 running on Debian, uses InnoDB, DB size about 100Gb. Dual Xeon 5645 with 48Gb RAM, 4 HDD 15K in RAID 10 (/var) and system on RAID1
There're many "heavy" ORDER BY/GROUP BY, JOIN/sort queries, they create many TMP tables on HDD. And, OFC, there're many "unoptimal" queries, which doesn't use indexes and also creates TMP tables.
How can I determine good values for sorting/grouping? 'Cause now these kind of queries extremely slow. Reading tons of articles, using optimizing scripts like mysqltuner only made things confusing about value settings...
[mysqld]
port = 3306
connect_timeout=100
expire_logs_days=7
innodb_log_buffer_size = 8M
innodb_log_file_size = 900M
innodb_file_per_table
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT
innodb_buffer_pool_size = 32G
innodb_buffer_pool_instances = 6
innodb_lock_wait_timeout = 200
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_thread_concurrency = 8
join_buffer_size = 2M
key_buffer_size = 384M
max_allowed_packet = 200M
max_heap_table_size = 64M
open_files_limit = 10240
query_cache_limit = 2M
query_cache_type = 1
read_buffer_size = 2M
read_rnd_buffer_size = 14M
socket = /var/run/mysqld/mysqld.sock
sort_buffer_size = 48M
table_open_cache = 640
thread_cache_size = 32
### tmp_table_size = max_heap_table_size
tmp_table_size = 64M
thread_concurrency = 0
##### Replication #####
server-id= 1
log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 5
max_binlog_size = 1024M
##### End replication #####
##### Logs #####
slow_query_log=1
slow_query_log_file = /var/log/mysql/slow.log
log_queries_not_using_indexes = 1
##### End Logs #####
query_cache_size = 96M
max_connections = 300
max-connect-errors = 1000
skip-name-resolve
Especially sort_buffer_size, join_buffer_size, tmp_table_size. And, any recommendations about config will be good and pleased :)
UPD: running next script:
SET @SleepTime = 300;
SELECT variable_value INTO @SMP1
FROM information_schema.global_status WHERE variable_name = 'Sort_merge_passes';
SELECT SLEEP(@SleepTime) INTO @x;
SELECT variable_value INTO @SMP2
FROM information_schema.global_status WHERE variable_name = 'Sort_merge_passes';
SET @SMP = @SMP2 - @SMP1;
SET @SMP_RATE = @SMP * 3600 / @SleepTime;
SELECT @SMP,@SMP_RATE;
gave @SMP=24, @SMP_RATE=288
