Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2012
    Posts
    44

    Unanswered: query needs more than 27 GB tmpdir space?

    Hi

    Code:
    mysql> SELECT   *
        ->   FROM spt_bundle bundle
        ->        JOIN spt_profile profile
        ->           ON profile.bundle_id = bundle.id
        ->        JOIN spt_profile_constraints constraints
        ->           ON constraints.profile = profile.id
        ->        JOIN spt_identity_bundles identitybundle
        ->           ON identitybundle.bundle = bundle.id
        ->        JOIN spt_identity_entitlement entitlement
        ->           ON entitlement.identity_id = identitybundle.identity_id
        ->              AND constraints.elt LIKE CONCAT ('%', entitlement.VALUE, '%')
        ->        JOIN spt_identity identity
        ->           ON identity.id = entitlement.identity_id
        ->        JOIN spt_application application
        ->           ON application.id = entitlement.application
        ->  WHERE application.id = profile.application
        ->  ORDER BY FROM_UNIXTIME(entitlement.modified DIV 1000) DESC;
    Error:
    Code:
    ERROR 126 (HY000): Incorrect key file for table '/opt/test/mysql/tmp/#sql_6338_0.MYI'; try to repair it
    Google:
    exception - MySQL: Can't create/write to file '/tmp/#sql_3c6_0.MYI' (Errcode: 2) - What does it even mean? - Stack Overflow

    What I did:

    Code:
    $ grep tmpdir /etc/my.cnf
    tmpdir=/opt/test/mysql/tmp
    Code:
    $ df -h /opt/test/
    Filesystem            Size  Used Avail Use% Mounted on
    /dev/mapper/vdatavg-opttestlv
                          144G  111G   27G  81% /opt/test

    explain plan

    Code:
    mysql> EXPLAIN EXTENDED SELECT   *   FROM spt_bundle bundle        JOIN spt_profile profile           ON profile.bundle_id = bundle.id        JOIN spt_profile_constraints constraints           ON constraints.profile = profile.id        JOIN spt_identity_bundles identitybundle           ON identitybundle.bundle = bundle.id        JOIN spt_identity_entitlement entitlement           ON entitlement.identity_id = identitybundle.identity_id              AND constraints.elt LIKE CONCAT ('%', entitlement.VALUE, '%')        JOIN spt_identity identity           ON identity.id = entitlement.identity_id        JOIN spt_application application           ON application.id = entitlement.application  WHERE application.id = profile.application  ORDER BY FROM_UNIXTIME(entitlement.modified DIV 1000) DESC;
    +----+-------------+----------------+--------+---------------------------------------------------------------------+-------------------------------+---------+----------------------------------------------------------------------+-------+----------+----------------------------------------------+
    | id | select_type | table          | type   | possible_keys                                                       | key                           | key_len | ref                                                                  | rows  | filtered | Extra                                        |
    +----+-------------+----------------+--------+---------------------------------------------------------------------+-------------------------------+---------+----------------------------------------------------------------------+-------+----------+----------------------------------------------+
    |  1 | SIMPLE      | identitybundle | index  | PRIMARY,FK2F3B433828E03F44,FK2F3B433856651F3A                       | FK2F3B433828E03F44            | 386     | NULL                                                                 | 36628 |   100.00 | Using index; Using temporary; Using filesort |
    |  1 | SIMPLE      | bundle         | eq_ref | PRIMARY                                                             | PRIMARY                       | 386     | testdb.identitybundle.bundle                                     |     1 |   100.00 |                                              |
    |  1 | SIMPLE      | profile        | ref    | PRIMARY,FK6BFE472122D068BA,FK6BFE472139D71460                       | FK6BFE472122D068BA            | 387     | testdb.bundle.id                                                 |     3 |   100.00 | Using where                                  |
    |  1 | SIMPLE      | constraints    | ref    | PRIMARY,FKEFD7A218B236FD12                                          | PRIMARY                       | 386     | testdb.profile.id                                                |     1 |   100.00 |                                              |
    |  1 | SIMPLE      | application    | eq_ref | PRIMARY                                                             | PRIMARY                       | 386     | testdb.profile.application                                       |     1 |   100.00 |                                              |
    |  1 | SIMPLE      | identity       | eq_ref | PRIMARY                                                             | PRIMARY                       | 386     | testdb.identitybundle.identity_id                                |     1 |   100.00 |                                              |
    |  1 | SIMPLE      | entitlement    | ref    | FK1134F4B456651F3A,FK1134F4B439D71460,spt_identity_entitlement_comp | spt_identity_entitlement_comp | 773     | testdb.identitybundle.identity_id,testdb.profile.application |    18 |   100.00 | Using where                                  |
    +----+-------------+----------------+--------+---------------------------------------------------------------------+-------------------------------+---------+----------------------------------------------------------------------+-------+----------+----------------------------------------------+
    7 rows in set, 1 warning (0.00 sec)
    Is this really true that this query uses more than 27GB tmp space?

    it means bad query?

    what to do?

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Why did you used "ORDER BY FROM_UNIXTIME(entitlement.modified DIV 1000) DESC"?

    Would't "ORDER BY entitlement.modified DESC" be enough?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •