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.

±------------------------±------

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

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 [url]http://www.mysqlperformanceblog.com/2014/03/14/tools-and-tips-for-analysis-of-mysqls-slow-query-log/[/url]
I foresee this will help to boost overall cluster performance after fixing the SQL