GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

Slow Create Temporary Table

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Slow Create Temporary Table

    Hello everybody,

    we have a performance issue with "Create Temporary Table" querys.
    "CREATE TEMPORARY TABLE tempdata.SPHINXV36clal32eo8oaj8n3mjv1o7ukjc4 (SPHID INTEGER(12) NOT NULL PRIMARY KEY, SORT INTEGER(12))"

    The problem is not permanently, but there are about 10 slow querys an hour.

    We have a Percona XtraDB Cluster with 3 nodes.
    mysql> show global status like '%tmp%' ;
    +-------------------------+-------+
    | Variable_name | Value |
    +-------------------------+-------+
    | Created_tmp_disk_tables | 1807 |
    | Created_tmp_files | 6 |
    | Created_tmp_tables | 9891 |
    +-------------------------+-------


    Many tmp tables goes to disk, so I increase the tmp_table_size and the max_heap_table_size from 256M to 512M, but it takes no effect.

    Maybe someone have a idea ?

    Thanks in advanced.

    Regards
    Patrick
    Last edited by pathet; 05-07-2014, 02:48 AM. Reason: head=>heap :D

  • #2
    created_tmp_disk_tables is quite high which suggests your queries requires tuning. I would recommend to enable slow query log in peak time with long_query_time=0 for some specific time 1-2 hour should be enough and analyze those queries with the help of pt-query-digest tool from percona-toolkit. I would suggest to read my post here for analysis of slow query log http://www.mysqlperformanceblog.com/...low-query-log/
    I foresee this will help to boost overall cluster performance after fixing the SQL

    Comment

    Working...
    X