Description:
Testing how using --tmpdir=/dev/shm affects performance I'm seeing it is not used to
create temporary tables:
SHOW VARIABLES:
| tmpdir | /dev/shm/ |
QUERY:
mysql> select count(*) cnt,c from gt group by c order by null limit 10;
(the table is big so temporary table has to be created on disk)
lsof shows:
mysqld 16066 peterz 3uW REG 8,3 10485760 6324226
/cache/db/ibdata1
mysqld 16066 peterz 4u REG 0,19 0 312615
/dev/shm/ib3OI8L6 (deleted)
mysqld 16066 peterz 5u REG 0,19 0 312616
/dev/shm/ibF7yukv (deleted)
mysqld 16066 peterz 6u REG 0,19 0 312617
/dev/shm/ibdqGQST (deleted)
mysqld 16066 peterz 7u REG 0,19 0 312618
/dev/shm/ib9O2dsi (deleted)
mysqld 16066 peterz 8uW REG 8,3 5242880 6324227
/cache/db/ib_logfile0
mysqld 16066 peterz 9uW REG 8,3 5242880 6324228
/cache/db/ib_logfile1
mysqld 16066 peterz 10u IPv4 312620 TCP *:mysql
(LISTEN)
mysqld 16066 peterz 11u REG 0,19 0 312619
/dev/shm/ibhmCr3G (deleted)
mysqld 16066 peterz 12u unix 0xf207da80 312621 /tmp/mysql.sock
mysqld 16066 peterz 13u unix 0xd63a1a80 312625 /tmp/mysql.sock
mysqld 16066 peterz 14u REG 8,3 1024 6324236
/cache/db/test/gt.MYI
mysqld 16066 peterz 15u REG 8,3 55000000 6324237
/cache/db/test/gt.MYD
mysqld 16066 peterz 16u REG 8,3 1024 6324233
/cache/db/test/gtest.MYI
mysqld 16066 peterz 17u REG 8,3 230686720 6324234
/cache/db/test/gtest.MYD
mysqld 16066 peterz 18u REG 8,3 36679680 6324240
/cache/db/#sql_3ec2_0.MYI
mysqld 16066 peterz 19u REG 8,3 33373763 6324241
/cache/db/#sql_3ec2_0.MYD
Note even though some temporary files are created in tmpfs temporary table is created in
database home directory instead.
How to repeat:
See above
