Archive for the ‘SHOW TEMPORARY TABLES’ Category

When indexes are created in internal temporary tables

Март 8th, 2010

During my previous post on how to improve derived tables performance, I patched the code to add indexes forcefully on internal derived table results, which made a huge difference in the performance. It was just an experiment and a thought to see if it really works without re-writing the queries, so that the logic can be pushed towards the engine rather than query re-write. \

But I got few emails in my inbox today asking whether MySQL really create any keys on internal temporary tables.

The answer is YES; and MySQL does create two keys on internal temporary tables namely ‘group_key‘ and ‘distinct_key‘ on the following conditions:

  • If there is any aggregate function and/or group-by (group_key)
  • Distinct column name(group_key)
  • Distinct in combination with group-by/aggregation functions (distinct_key)

Provided the query results are yielded in temporary table (Using temporary from the explain), else they get optimized away by the existing indexes from the regular table itself. These keys are added to both memory and disk based (MyISAM) internal temporary tables; so it does not matter if the internal temporary table is in memory or disk.

Here is a simple dump of internal temporary table index stats for some of the basic queries related to Information schema [Warning: these queries are really bad, and can't be used for any production use as they are meant for demonstration of different internal keys ]. This is a patch that I might be using for SHOW TEMPORARY TABLES when internal tables are included in the second version. The first version of the patch is already pushed to Maria branch, hoping that it gets pushed to 5.1.

-----------------------------
 TMP TABLE STATS, SESSION: 1
   temp file  : \tmp\#sqlf90_1_1f
   temp type  : MEMORY
   index count: 1
    key 1-1   : distinct_key
    field     : (null)
    key 1-2   : distinct_key
    field     : ENGINE
 query: select count(distinct engine) from information_schema.tables
-----------------------------
 
-----------------------------
 TMP TABLE STATS, SESSION: 1
   temp file  : \tmp\#sqlf90_1_21
   temp type  : MEMORY
   index count: 1
    key 1-1   : group_key
    field     : TABLE_NAME
 query: select table_name, sum(data_length+index_length) from information_schema.tables 
        where table_schema='mysql' group by 1
-----------------------------
 
-----------------------------
 TMP TABLE STATS, SESSION: 1
   temp file  : \tmp\#sqlf90_1_24
   temp type  : MEMORY
   index count: 1
    key 1-1   : group_key
    field     : TABLE_SCHEMA
    key 1-2   : group_key
    field     : TABLE_NAME
    key 1-3   : group_key
    field     : COLUMN_NAME
 query: select tab.table_schema, tab.table_name, column_name, index_name, seq_in_index 
        from Information_schema.tables tab join information_schema.statistics stast 
        using(table_schema,table_name) group by  1,2,3
-----------------------------
 
-----------------------------
 TMP TABLE STATS, SESSION: 1
   temp file  : \tmp\#sqlf90_1_bd
   temp type  : MEMORY
   index count: 1
    key 1-1   : group_key
    field     : TABLE_NAME
    key 1-2   : group_key
    field     : TABLE_TYPE
    key 1-3   : group_key
    field     : ENGINE
    key 1-4   : group_key
    field     : INDEX_SCHEMA
    key 1-5   : group_key
    field     : INDEX_NAME
 query: select tab.table_schema,  tab.table_name, table_type, engine, index_schema, 
        index_name from information_schema.tables tab join information_schema.statistics
        stats using(table_schema, table_name) where table_schema='mysql' group by 
        1,2,3,4,5, 6 order by 4,3,2,1
-----------------------------
 
-----------------------------
 TMP TABLE STATS, SESSION: 1
   temp file  : \tmp\#sqlf90_1_e0
   temp type  : MEMORY
   index count: 1
    key 1-1   : group_key
    field     : TABLE_NAME
 query: select  table_name, sum(data_length+index_length) from information_schema.tables
        where table_schema='mysql' group by 1
-----------------------------

PlanetMySQL Voting: Vote UP / Vote DOWN
 

Posted in database, how to query internal temporary tables, indexes on internal temporary tables, mysql, mysql internal temporary tables, optimizing mysql, SHOW TEMPORARY TABLES

Комментарии отключены

 

SHOW TEMPORARY TABLES

Февраль 3rd, 2010

I had this patch for a while where one can get listing of both session and global temporary tables across all sessions. It really helped lot of times to understand the bottlenecks of some of the temporary table issues as MySQL never exposed them in the form of SHOW TABLES.

I also added a new status variable called ‘Created_tmp_heap_to_disk_tables‘, which keeps track of how many memory based temp tables are re-created back to disk based.

The patch is now ported to newer MySQL versions, both 5.0 and 5.1; and it works great on most of the platforms that I tested (Mac, Linux and Windows)

It introduces two new INFORMATION_SCHEMA tables, TEMPORARY_TABLES and GLOBAL_TEMPORARY_TABLES along with supporting regular SHOW syntax

 
SHOW [SESSION/GLOBAL] TEMPORARY TABLES [FROM db]

Some examples of how it works at present

Session Temp Tables

mysql> show session temporary tables;
+----+------+-------+--------+--------------+
| Id | Db   | Table | Engine | Name         |
+----+------+-------+--------+--------------+
|  1 | test | t2    | MEMORY | #sql29da_1_3 |
|  1 | test | t1    | MyISAM | #sql29da_1_2 |
+----+------+-------+--------+--------------+
2 rows in set (0.00 sec)
 
mysql> show temporary tables;
+----+------+-------+--------+--------------+
| Id | Db   | Table | Engine | Name         |
+----+------+-------+--------+--------------+
|  1 | test | t2    | MEMORY | #sql29da_1_3 |
|  1 | test | t1    | MyISAM | #sql29da_1_2 |
+----+------+-------+--------+--------------+
2 rows in set (0.00 sec)
 
mysql> select * from information_schema.temporary_tables;
+------------+----------+------------+--------+--------------+
| SESSION_ID | DATABASE | TABLE_NAME | ENGINE | NAME         |
+------------+----------+------------+--------+--------------+
|          1 | test     | t2         | MEMORY | #sql29da_1_3 |
|          1 | test     | t1         | MyISAM | #sql29da_1_2 |
+------------+----------+------------+--------+--------------+
2 rows in set (0.00 sec)

Global Temp Tables Across All Sessions:

mysql> select * from information_schema.global_temporary_tables;
+------------+----------+------------+--------+--------------+
| SESSION_ID | DATABASE | TABLE_NAME | ENGINE | NAME         |
+------------+----------+------------+--------+--------------+
|          6 | test     | t3         | MyISAM | #sql29da_6_0 |
|          5 | test     | t2         | MEMORY | #sql29da_5_3 |
|          5 | test     | t1         | MyISAM | #sql29da_5_2 |
|          4 | venu     | v1         | InnoDB | #sql29da_4_0 |
+------------+----------+------------+--------+--------------+
4 rows in set (0.00 sec)
 
mysql> show global temporary tables;
+----+------+-------+--------+--------------+
| Id | Db   | Table | Engine | Name         |
+----+------+-------+--------+--------------+
|  6 | test | t3    | MyISAM | #sql29da_6_0 |
|  5 | test | t2    | MEMORY | #sql29da_5_3 |
|  5 | test | t1    | MyISAM | #sql29da_5_2 |
|  4 | venu | v1    | InnoDB | #sql29da_4_0 |
+----+------+-------+--------+--------------+
4 rows in set (0.00 sec)
  • Id: Session ID
  • Db: Database Name
  • Engine: Engine Type
  • Name: Internal Name, how its stored in the file system

Will publish the patch once am done with porting internal temp tables listing by introducing two new columns Table_Type and Info where Table_Type will indicate if its internal or external and Info will have initial 1024 bytes of the query that caused the internal temp table.


PlanetMySQL Voting: Vote UP / Vote DOWN
 

Posted in database, Find temporary tables in MySQL, mysql, mysql patches, MySQL Temp Tables, SHOW TEMPORARY TABLES

Комментарии отключены

 

SHOW TEMPORARY TABLES

Февраль 3rd, 2010

I had this patch for a while where one can get listing of both session and global temporary tables across all sessions. It really helped lot of times to understand the bottlenecks of some of the temporary table issues as MySQL never exposed them in the form of SHOW TABLES.

I also added a new status variable called ‘Created_tmp_heap_to_disk_tables‘, which keeps track of how many memory based temp tables are re-created back to disk based.

The patch is now ported to newer MySQL versions, both 5.0 and 5.1; and it works great on most of the platforms that I tested (Mac, Linux and Windows)

It introduces two new INFORMATION_SCHEMA tables, TEMPORARY_TABLES and GLOBAL_TEMPORARY_TABLES along with supporting regular SHOW syntax

 
SHOW [SESSION/GLOBAL] TEMPORARY TABLES [FROM db]

Some examples of how it works at present

Session Temp Tables

mysql> show session temporary tables;
+----+------+-------+--------+--------------+
| Id | Db   | Table | Engine | Name         |
+----+------+-------+--------+--------------+
|  1 | test | t2    | MEMORY | #sql29da_1_3 |
|  1 | test | t1    | MyISAM | #sql29da_1_2 |
+----+------+-------+--------+--------------+
2 rows in set (0.00 sec)
 
mysql> show temporary tables;
+----+------+-------+--------+--------------+
| Id | Db   | Table | Engine | Name         |
+----+------+-------+--------+--------------+
|  1 | test | t2    | MEMORY | #sql29da_1_3 |
|  1 | test | t1    | MyISAM | #sql29da_1_2 |
+----+------+-------+--------+--------------+
2 rows in set (0.00 sec)
 
mysql> select * from information_schema.temporary_tables;
+------------+----------+------------+--------+--------------+
| SESSION_ID | DATABASE | TABLE_NAME | ENGINE | NAME         |
+------------+----------+------------+--------+--------------+
|          1 | test     | t2         | MEMORY | #sql29da_1_3 |
|          1 | test     | t1         | MyISAM | #sql29da_1_2 |
+------------+----------+------------+--------+--------------+
2 rows in set (0.00 sec)

Global Temp Tables Across All Sessions:

mysql> select * from information_schema.global_temporary_tables;
+------------+----------+------------+--------+--------------+
| SESSION_ID | DATABASE | TABLE_NAME | ENGINE | NAME         |
+------------+----------+------------+--------+--------------+
|          6 | test     | t3         | MyISAM | #sql29da_6_0 |
|          5 | test     | t2         | MEMORY | #sql29da_5_3 |
|          5 | test     | t1         | MyISAM | #sql29da_5_2 |
|          4 | venu     | v1         | InnoDB | #sql29da_4_0 |
+------------+----------+------------+--------+--------------+
4 rows in set (0.00 sec)
 
mysql> show global temporary tables;
+----+------+-------+--------+--------------+
| Id | Db   | Table | Engine | Name         |
+----+------+-------+--------+--------------+
|  6 | test | t3    | MyISAM | #sql29da_6_0 |
|  5 | test | t2    | MEMORY | #sql29da_5_3 |
|  5 | test | t1    | MyISAM | #sql29da_5_2 |
|  4 | venu | v1    | InnoDB | #sql29da_4_0 |
+----+------+-------+--------+--------------+
4 rows in set (0.00 sec)
  • Id: Session ID
  • Db: Database Name
  • Engine: Engine Type
  • Name: Internal Name, how its stored in the file system

Will publish the patch once am done with porting internal temp tables listing by introducing two new columns Table_Type and Info where Table_Type will indicate if its internal or external and Info will have initial 1024 bytes of the query that caused the internal temp table.


PlanetMySQL Voting: Vote UP / Vote DOWN
 

Posted in database, mysql, mysql patches, MySQL Temp Tables, SHOW TEMPORARY TABLES

Комментарии отключены