June 18, 2013

Post: Is your MySQL buffer pool warm? Make it sweat!

April 16, 2013 By 6 Comments

…-line option, we set the option innodb_fake_changes to prevent INSERTs, UPDATEs, and DELETEs from corrupting the data on the standby server. You will need Percona Server in order to use innodb_fake_changes… is an important step because update statements that match no rows or insert statements that have duplicate key errors may be…

Filed Under: Insight for DBAs, Insight for Developers, MySQL, Percona Software Tagged With: , , , , , , ,

Post: How to recover deleted rows from an InnoDB Tablespace

February 20, 2012 By 4 Comments

…’ll extract those deleted rows from the ibd file and import them into the database. The time between the deletion of rows and the…. In the salaries.recovery you can find lot of deleted rows, not only rows deleted accidentally. You should manually find the data you need… recovered! 10008 has recovered his salary again :) Conclusion With InnoDB the deleted rows are not lost. You can recover them from the original…

Filed Under: Insight for DBAs, MySQL

Post: STOP: DELETE IGNORE on Tables with Foreign Keys Can Break Replication

February 2, 2012 By 1 Comment

… of rows for simplicity. CREATE TABLE `t1` ( `t1_c1` int(10) unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (`t1_c1`) ) ENGINE=InnoDB… for DELETE IGNORE is that if the statement fails to delete all rows, none should be deleted at all, after all this is InnoDB right… will stop deleting rows if it encounters constraint errors. To demonstrate on 5.5.17: I delete some rows from t2 so some rows on…

Filed Under: Insight for DBAs

Post: Recovering Innodb table Corruption

July 4, 2008 By 22 Comments

… Running in innodb_force_recovery mode Innodb becomes read only for data operations and so you can’t insert or delete any data… may wish to use Innodb Recovery Toolkit which is also helpful in cases you’ve want to recover deleted rows or dropped table. I should also mention at Percona we offer assistance in MySQL Recovery, including recovery from Innodb corruptions and deleted…

Filed Under: Insight for DBAs Tagged With:

Post: Innodb locking and Foreign Keys

December 12, 2006 By 2 Comments

… a quick check so could be missing some details. All Innodb Foreign Key related operations happen on data modification. So for… in PARENT table and row will be locked. Same applies to PARENT table – if you will try to delete row in PARENT table lockup in CHILD table will be performed with row lock performed. Innodb is smart enough…

Filed Under: Insight for DBAs Tagged With:

Post: InnoDB thread concurrency

June 5, 2006 By 4 Comments

…: read_row (there are a couple of methods like read by index or sequential read or random read); write_row; update_row; delete_row; At start of each of these methods InnoDB checks count of already entered threads, and if this count is over innodb… to be normal behavior. About innodb_commit_concurrency. As you saw innodb_thread_concurrency protects only access to row, but there is also…

Filed Under: Insight for DBAs Tagged With: , , ,

Post: Innodb Undelete and Sphinx Support

November 12, 2007 By 4 Comments

… broken down RAID. We can undelete Innodb data – Heikki was very nice and treats deleted rows very gently allowing them to be 100% recovered if they were not overwritten yet, as well as recover dropped Innodb… and Alexey had series of sleepless nights digging into Innodb page and row structure. When few more companies followed, so we continued…

Filed Under: Insight for DBAs Tagged With:

Post: InnoDB Full-text Search in MySQL 5.6 (part 1)

February 26, 2013 By 1 Comment

…_state | 0 | +—————————+———+ 10 rows in set (0.00 sec) mysql> delete from dir_test_innodb LIMIT 200000; Query OK, 200000 rows affected (8.65 sec) mysql> optimize table dir_test_innodb; +———————-+———-+———-+———-+ | Table…

Filed Under: Insight for DBAs, Insight for Developers, MySQL Tagged With: , , , , ,

Post: Tuning InnoDB Concurrency Tickets

May 24, 2010 By 5 Comments

… (parent_id) REFERENCES parent(id) -> ON DELETE CASCADE -> ) ENGINE=INNODB; — 0 Tickets Used Query OK, 0 rows affected (0.00 sec) mysql> INSERT… per row returned UPDATE: 1 ticket per row examined + 1 ticket per row updated DELETE: 1 ticket per row examined + 1 ticket per row deleted ALTER: (2 * rows…

Filed Under: Benchmarks, Insight for DBAs, MySQL Tagged With: , , , ,

Post: Concatenating MyISAM files

November 19, 2012 By 5 Comments

… myself involved in the migration of a large read-only InnoDB database to MyISAM (eventually packed). The only issue was that… comment, I added varchar and deleted rows to the mix: mysql> truncate table test_concat; Query OK, 0 rows affected (0.00 sec… | | 6 | six | +—-+——-+ 6 rows in set (0.00 sec) So varchar columns are supported without any issue but, deleted rows prevent the use…

Filed Under: MySQL
« Older Posts