June 19, 2013

Post: Percona XtraDB Cluster 5.5.30-23.7.4 for MySQL now available

April 17, 2013 By 2 Comments

…table when appending exclusive key for cascading delete operation. Bug fixed #1089490 (Seppo Jaakola). …Alex Yurchenko). If variable innodb_thread_concurrency has been defined to throttle InnoDB access, and work load… did our best to eliminate bugs and problems, but this is a software, so bugs …

Filed Under: MySQL, XtraDB Cluster Tagged With: , ,

Post: Heikki Tuuri Innodb answers - Part I

October 26, 2007 By 19 Comments

… you delete a lot of scattered rows you may not see a “free space” in Innodb tablespace to grow significantly. Q6: Does Innodb… of little known aspects of Innodb operations while I see it causing problems every so often. Q16: How Innodb decided how many pages… often solves your thrashing problem. PZ: Values 1,2 may solve your thrashing problem but also will limit innodb to be able to…

Filed Under: Insight for Developers Tagged With: ,

Post: My Innodb Feature wishes

May 4, 2006 By 30 Comments

… delete. For Insert operation Innodb uses nice tecnique called Insert Buffer which speeds up inserts for non-unique indexes a lot. However deletes… it is so bad I will list it. Basically Innodb has problems scaling both with large number of active threads and with… it is too bad even with 4 concurrent queries). The problem seems to be at least partially due to global mutexes…

Filed Under: Insight for Developers Tagged With:

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

February 26, 2013 By 1 Comment

… explicit FTS_DOC_ID column, however, MySQL apparently has no problem with either of the following statements: mysql> insert into dir… two, delete some data, and see what happens: mysql> set global innodb_ft_aux_table=’test/dir_test_innodb‘; mysql> set global innodb_optimize…) 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: A quest for the full InnoDB status

October 31, 2008 By 4 Comments

… with one simple command — SHOW ENGINE InnoDB STATUS. On most occasions it works beautifully. The problems appear when you have a large… all the files that were deleted. garfield ~ # cd /proc/11886/fd garfield fd # ls -l | grep deleted lrwx—— 1 root root 64… InnoDB status is. You can also help yourself with lsof tool available for many platforms: garfield fd # lsof -c mysqld | grep deleted…

Filed Under: Insight for DBAs Tagged With: ,

Post: InnoDB's gap locks

March 27, 2012 By 15 Comments

…. They only occur if you do UPDATE or DELETE or SELECT FOR UPDATE. InnoDB provides REPEATABLE READ for read-only SELECT, but…? Is possible to detect those gap locks using SHOW ENGINE INNODB STATUS: —TRANSACTION 72C, ACTIVE 755 sec 4 lock struct(s… phantom reads (non repeatable reads) so in order to prevent problems with the replication you should change the binary log format…

Filed Under: MySQL

Post: INSERT INTO ... SELECT Performance with Innodb tables.

July 12, 2006 By 22 Comments

… extra locks. If you use this aproach make sure to delete file after it is loaded back (it has to be… cause the problems. To complete this article I should show how wait caused by this statement will look in SHOW INNODB STATUS: —TRANSACTION 0 42304626, ACTIVE 14 sec, process no 29895, OS thread id 2894768 updating or deleting mysql tables…

Filed Under: Insight for DBAs Tagged With: ,

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 (though you can create or drop Innodb tables): mysql> optimize table test; +———–+———-+———-+———————————-+ | Table… same time I saw some data could be selected. The problem is there is some buffering taking place and as MySQL…

Filed Under: Insight for DBAs Tagged With:

Post: SHOW INNODB STATUS walk through

July 17, 2006 By 40 Comments

… thousands of OS waits per second it may be the problem. The other way to look at it is context switch… thread is not runniing inside innodb kernel status could be “waiting in InnoDB queue” or “sleeping before joining InnoDB queue”. Latest one is… inside InnoDB Main thread process no. 10099, id 88021936, state: waiting for server activity Number of rows inserted 143, updated 3000041, deleted…

Filed Under: Insight for DBAs Tagged With:

Post: Innodb undo segment size and transaction isolation

January 12, 2011 By 3 Comments

… for execution, this however only applies to read transactions while problem with undo space growth exists for read only transactions as… record in undo space so row can be deleted on rollback, update and delete however need to put the old row version… queries will cause problems in this case anyway, even if you set transaction isolation to lowest READ-UNCOMMITTED level Innodb will not…

Filed Under: Insight for DBAs, MySQL Tagged With:
« Older Posts