0

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

0

Changed values to

join_buffer_size = 2M
sort_buffer_size = 2M
read_buffer_size = 2M

Now server creates many on-disk tmp tables with zero-length. Then, re-run script and it gives: @SMP=258, @SMP_RATE=3096

TUNING-PRIMER script gives advise to increase tmp_table_size/max_heap_table_size. Will set tmp_table_size=96M, max_heap_table_size=96M and see the reaction.

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.