Has anyone experimented with changing the tmpdir engine variable from it’s default of /tmp to a tmpfs filesystem to speed things up ?
I’m curious to figure out if this would help Singlestore on queries with large datasets to group/sort; and if it does where would that operation be taking place (ie: is the tmpdir being used on the aggregators or the leafs or both)
The tmpdir variable has no effect. We’ll get the documentation updated to clarify that. It used to be used by the memsql-ops management tool that is no longer supported.
Large sort and hash operations may spill, but not to tmpdir. Spilling files are written into the spill folder under datadir. E.g. on my test system they are at
/var/lib/memsql/2473a6e7-f6c3-44eb-837a-9bddc75b9a77/data/spill
Do show variables like 'datadir' to see the location of your datadir.
Unless a large sort or hash operation spills to disk (which it rarely will), the spilling location of course won’t have any effect.
I wouldn’t recommend trying to put the spill folder on a different device to speed things up. Typically you will put your datadir on a fast device. Plus, there is a disk broker that tries to make sure there is enough disk for different purposes under datadir, including spilling. Putting the spill folder elsewhere would confuse that.
Many thanks for the info and insights @hanson ; I’ll resist the temptation to go sym-linking directories.