I am setting up a mysql server for a ~12GB database on an extra large ec2 instance. I am using 4 striped (with lvm) EBS volumes as the disks.
All the tables are innodb.
I’m starting to load test this setup and I’ve noticed that the occasional query is extremely slow. The queries that appear in the slow query log are all ones that create temporary tables. Watching lsof, I can see that the tables are being written to disk.
What’s weird, though, is that I have tmp_table_size and max_heap_table_size set to 1G. I’m 99% certain that none of these temp tables are even close to that size. So, why are the tables being written to disk?
Another weird fact here is that I’m not seeing any iowait watching top or iostat -x 1. Yet, some queries are still taking 14s.
My key_buffer is also set to 512M
I realize that the better approach here would be to rearchitect those queries. That’s the subject of another thread, though.