…temporary tables (both in memory and not): mysql> select * from information_schema.global_temporary_tables \G *************************** 1. row *************************** SESSION_ID: 7234 TABLE_SCHEMA: test TABLE… + 128560) Recovery system 0 (0 + 0) Threads 425080 (406936 + 18144) Dictionary memory…
Post: MySQL Server Variables - SQL layer or Storage Engine specific.
June 8, 2006 By 14 Comments
…_size Buffer used to sort result set (allocated by each thread once sorting needs to be done) SQL Layer so works… MEMORY table before it will be converted to on disk MyISAM table. It does not limit size of temporary table, neither it applies to tables created as TEMPORARY TABLE, even in memory tables.
Post: Percona-XtraDB-9.1: released and new coming features
March 17, 2010 By 14 Comments
… waited features from 5.0: extended slow.log USER/TABLE/INDEX/CLIENT_STATISTICS + THREAD_STATISTICS ( coming in release-10) Extended slow.log…_tables: 1 Tmp_disk_tables: 1 Tmp_table_sizes: 7808 That is you can see how many bytes was returned by query, was temporary table used, was it disk table or in-memory, and how big was temporary table. Also you can profile…
Post: MySQL Server Memory Usage
May 17, 2006 By 21 Comments
… memory consumption for given thread, however if table will perform complex operations such as full table scans, sorts, or need temporary tables as much as read_buffer_size, sort_buffer_size, read_rnd_buffer_size, tmp_table_size…
Post: Announcing Percona Server 5.5.24-26.0
June 1, 2012 By 9 Comments
…_SSL, has been added in the CLIENT_STATISTICS, THREAD_STATISTICS and USER_STATISTICS tables in the INFORMATION_SCHEMA database. Bugs Fixed: A… Ormond Lorch III). A crash could leave behind an InnoDB temporary table with temporary indexes resulting in an unbootable server. Bug fixed #999147…
Post: Wanted: Better memory profiling for MySQL
May 18, 2008 By 4 Comments
… opened tables, prepared statements etc ? Could it be some of the queries currently running is using some of the buffers temporary based… management for MySQL, ie so I could restrict global temporary space for temporary tables or sort memory. Though I’m not expecting this… example. I would see it implemented similary to SHOW STATUS – threads could account statistics for their own memory usage which is…
Post: How much memory can MySQL use in the worst case?
February 12, 2009 By 17 Comments
… what about the query cache and the table cache and the size needed for each thread (thread stack, etc)? Those take memory too…, etc) uses memory to execute; so do temporary tables. The following query creates at least 2 temporary tables: select * from ( select * from ( select 1… grouping and DISTINCT and UNION may/will also create temp tables; the same applies to views and probably some other things…
Post: What to tune in MySQL Server after installation
September 29, 2006 By 118 Comments
…-32Mb so it is large enough to accommodate indexes for temporary tables which are created on disk. innodb_buffer_pool_size This… many connections or many tables increase it larger. I’ve seen values over 100.000 used. thread_cache Thread creation/destructions can be… see fast growth of Threads_Created variable I boost it higher. The goal is not to have threads created in normal operation…
Comment: The Doom of Multiple Storage Engines
May 10, 2010 By 22 Comments
… does Drizzle design is posed to solve completely ? Now regarding temporary storage for resolving queries it does not have to be… one can argue it should not. Internal temporary tables are handled just by single thread which has created them which includes all kind of concurrently. Regarding treating In memory and on disk tables equally…
Post: Slow DROP TABLE
June 16, 2009 By 21 Comments
… happen – write lock on a table so it cannot be used by any other thread, the data file(s) removal by…_rm_table_part2(thd, tables, if_exists, drop_temporary, 0, 0); pthread_mutex_unlock(&LOCK_open); The entire code which removes a table is… TABLE to minimize the effect, such as: TRUNCATE TABLE large_table; ALTER TABLE large_table ENGINE=…; DROP TABLE large_table; TRUNCATE TABLE large_table; OPTIMIZE TABLE large_table; DROP TABLE large_table…
