… dataset (InnoDB tables) with a Scale Factor of 2 (InnoDB dataset size ~5G). I did not use Scale Factor of 40 (InnoDB dataset size… i_o_orderdate 4 NULL 232722 100.00 Using where; Rowid-ordered scan; Using temporary; Using filesort 1 SIMPLE customer eq_ref PRIMARY,i_c_nationkey PRIMARY 4 dbt3.orders.o_custkey 1 100.00 Using where…
Post: How FLUSH TABLES WITH READ LOCK works with Innodb Tables
March 23, 2012 By 20 Comments
… tools including Percona Xtrabackup, MyLVMBackup and others use FLUSH TABLES WITH READ LOCK to temporary make MySQL read only. In many cases… server gridlock one way or another. If you’re just using Innodb tables and you’re not actively changing users, stored procedures… (which are stored in MyISAM tables anyway) you can consider using –no-lock option with Percona Xtrabackup. Many other tools have…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 3 Comments
…identify potential causes. Is it working with large blobs ? Using user variables ? Prepared Statements ? memory tables ? In a …schema.global_temporary_tables \G *************************** 1. row *************************** SESSION_ID: 7234 TABLE_SCHEMA: test TABLE_NAME: my ENGINE: InnoDB NAME: #…
Post: Best kept MySQLDump Secret
March 23, 2012 By 11 Comments
Many people use mysqldump –single-transaction to get consistent backup for their Innodb tables without making database read only. In most cases… ALTER TABLE is Performed in many cases it will Create temporary table with modified structure, copy data to that table and when drop original table and rename such temporary table to original name. How does data visibility works in…
Post: Can MySQL temporary tables be made safe for statement-based replication?
May 26, 2008 By 8 Comments
…from the temporary table into a non-temporary InnoDB table: master > delimiter // master > create procedure test_temp() begin -> start transaction; -> create temporary table test… In summary, I still don’t see any way to use temporary tables with MySQL statement-based replication without some risk of…
Post: Improved InnoDB fast index creation
November 6, 2011 By 4 Comments
… a temporary table without updating indexes took 18 seconds, and about the same time was spent on rebuilding the index using fast… copy the data to a temporary table is constant. The reason is that when using merge sort, InnoDB has to scan the clustered…, InnoDB fast index creation uses temporary files in tmpdir for all indexes being created. So make sure you have enough tmpdir space when using…
Post: How much overhead is caused by on disk temporary tables
August 16, 2007 By 26 Comments
… | 1000000 | Using temporary | +—-+————-+——-+——+—————+——+———+——+———+—————–+ 1 row in set (0.01 sec) As you can see I’m using …temporary table. Do not forget about it especially if running only Innodb tables so you may think there is no use…
Post: Choosing innodb_buffer_pool_size
November 3, 2007 By 38 Comments
… to check if there are any restrictions on Innodb Buffer Size you can use. Typically you would see restriction applying only on…_buffer, mysql threads, temporary tables, per thread sort buffer which can be allocated. There are also things like innodb additional memory pool… – MyISAM tables (mysql database, temporary etc) will need it, .frm file, binary logs, or relay logs, Innodb Transactional Logs also like to…
Post: How much memory can MySQL use in the worst case?
February 12, 2009 By 17 Comments
… to consider the added memory for InnoDB: specifically, the buffer pool. (There’s also an innodb_additional_mem_pool_size but it…, like 20M). But even that’s not enough. Staying with InnoDB, we also need to count the data dictionary. This can… (triggers, stored routines, etc) uses memory to execute; so do temporary tables. The following query creates at least 2 temporary tables: select * from…
Post: Enum Fields VS Varchar VS Int + Joined table: What is Faster?
January 24, 2008 By 27 Comments
… be larger. It is also interesting to note performance of Innodb tables in this case: for VARCHAR it takes about 0… key: NULL key_len: NULL ref: NULL rows: 1439943 Extra: Using temporary; Using filesort *************************** 2. row *************************** id: 1 select_type: SIMPLE table: s…
