… files with DROP 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; Unfortunately as it… table files are deleted: | 3 | root | localhost | test | Query | 7 | rename result table | ALTER TABLE large_table ENGINE=MyISAM | | 679 | root | localhost | test | Query | 6 | Opening tables…
Post: Reasons for run-away main Innodb Tablespace
June 10, 2010 By 2 Comments
… way around it. Be careful in particular with update or delete transactions which go over a lot of rows. In many…, updating/deleting may be thousands of rows per transaction may be better if your application can handle it. Note ALTER TABLE will not require excessive amount of undo space even for very large tables as it internally commits every…
Post: Resyncing table on MySQL Slave
June 29, 2008 By 11 Comments
… TABLE tbl WRITE; SELECT * FROM table INTO OUTFILE ‘/tmp/tbl.txt’; DELETE FROM tbl; LOAD DATA INFILE ‘tmp/tbl.txt’ INTO TABLE tbl; UNLOCK TABLES; This will dump master’s table content to… small tables when you do not want to shut down the master and slave. If you need quick resync of large amount…
Comment: Why you don't want to shard.
August 13, 2009 By 34 Comments
…. This involved DELETEing large chunks and INSERTing new data. There is a single field that is primarily used when doing the DELETE, which… the DELETE command, which is now able to DELETE 8.2m records from a 1.5b record table faster than a similar DELETE of 3.6m records from a 500m record table…
Comment: Why MySQL could be slow with large tables ?
August 22, 2010 By 141 Comments
Hello, I need to delete all 300,000 records found in one table from a table that has 20,000,000 records, and… 1.86 Ghz Cpu – while nothing else is happening. The large table has 2 indexes on it and totals 3 GB – more… this normal – for a delete involving 2 tables to take so long? Is there something special about a delete that makes it MUCH…
Post: Using VIEW to reduce number of tables used
September 28, 2007 By 12 Comments
… inefficient. It is especially inefficient with Innodb tables both in terms of space (some tables would keep only couple of small rows… significant because table_cache can’t be made large enough and so a lot of table reopens needs to happen which requires table header… will be invisible. The VIEW approach works for SELECT, UPDATE, DELETE queries but not for INSERT: mysql> insert into post123 values…
Comment: Database problems in MySQL/PHP Applications
August 11, 2006 By 13 Comments
… > becomes managable, your ALTER TABLE or OPTIMIZE TABLE > now locks small table for few seconds rather than giant 100GB table > for few hours so…. Read more in my > Why MySQL Could be slow with Large Tables article. Deciding whether or not to let the database maintain… key references may add processing time to inserts, updates, and deletes but there are other benefits. Also – if you’re replicating…
Post: Efficient Boolean value storage for Innodb Tables
April 23, 2008 By 6 Comments
… can have for this table – myisam_data_pointer_size is 6 default plus we need space for delete flag which makes 7…% smaller. This is modest space savings of course but considering large per row overhead Innodb has this will transform to much…. Lets see how things look for MyISAM for same tables: mysql> show table status like “%bool%” \G *************************** 1. row *************************** Name: bbool Engine…
Post: A quest for the full InnoDB status
October 31, 2008 By 4 Comments
…some valuable information from your sight. With large deadlocks you can actually deal by intentionally… to create a special InnoDB table called innodb_monitor: CREATE TABLE innodb_monitor (a INT) ENGINE… that were deleted. garfield ~ # cd /proc/11886/fd garfield fd # ls -l | grep deleted lrwx—— 1…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…delete…table_definition_cacheblogpercona.commanual table_lock_wait_timeoutblogpercona.commanual Table_locks_immediateblogpercona.commanual Table_locks_waitedblogpercona.commanual table_open_cacheblogpercona.commanual table…
