Bug #30439 tmpdir is not followed for temporary tables in group by

• Submitted: 15 Aug 2007 14:45
• Modified: 16 Aug 2007 5:14
• Reporter: Peter Zaitsev (Basic Quality Contributor)
• Status: Duplicate
• Category: MySQL Server: General
• Severity: S3 (Non-critical)
• Version: 5.0.45
• OS: Linux
• CPU Architecture: Any
• Tags: qc

[15 Aug 2007 14:45] Peter Zaitsev

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

[15 Aug 2007 18:35] Valeriy Kravchuk

Thank you for a problem report. Sounds like a duplicate of Bug #30287. Please, check.

[15 Aug 2007 22:11] Peter Zaitsev

Quite likely :)

Is it new bug or fresh in latest 5.0 ?

[16 Aug 2007 5:14] Sveta Smirnova

Thank you for the feedback.

Marked as duplicate of Bug #30287. See new comments to bug #30287 for additional information.