I have an application that has few concurrent connections but does a lot of uncacheable fulltext searches sorting, limit with offset, etc. I have an xlarge instance with innodb buffer pool size @ 75% of available memory, tmp table size is set at 64M. temp table status shows lots of in memory and in disk temp table creation. client vars are:
sort_buffer_size 2M read_buffer_size 2M read_rnd_... 4M
I am thinking of bumping up the temp table size and other sort related vars by scaling down the innodb buffer pool. Does that even make sense? By the way we are not using provisioned iops either.