… low selectivity is not good for fast inserts, but it is suitable for range selects by `id`. However it will interesting how both InnoDB… datadir=/mnt/data/mysql #for SSD innodb_flush_neighbor_pages = none innodb_adaptive_flushing_method = keep_average innodb_file_per_table = true innodb_data_file…
Comment: Follow these basics when migrating to Percona XtraDB Cluster for MySQL
May 2, 2013 By 10 Comments
… (and by extension, safer) to run (CREATE TABLE AS SELECT *, UPDATE host, INSERT) a few times than trying to recreate the grants… possible or wheter Oracle is planning to move the mysql db to InnoDB tables (try googling for that), but it would be…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…Innodb_truncated_status_writesblogpercona.commanual innodb_use_native_aioblogpercona.commanual innodb_use_sys_mallocblogpercona.commanual innodb_versionblogpercona.commanual innodb_write_io_threadsblogpercona.commanual insert…
Post: Avoiding auto-increment holes on InnoDB with INSERT IGNORE
November 29, 2011 By 8 Comments
… the new value to the column. Prior to MySQL 5.1.22 InnoDB used a method to access that counter values called…`), UNIQUE KEY `uniqname` (`name`) ) ENGINE=InnoDB; Insert a value using a LEFT OUTER JOIN: insert into foo(name) select 1 from mutex left outer… will see, the INSERT is ignored and no rows are inserted. The same behaviour as INSERT IGNORE: insert into foo(name) select 1 from mutex…
Post: INSERT INTO ... SELECT Performance with Innodb tables.
July 12, 2006 By 22 Comments
…Innodb would not locking rows in source table other transaction could modify the row and commit before transaction which is running INSERT .. SELECT… MySQL thread id 1794751, query id 6994931 localhost root Sending data insert into test select * from sample ——– As you can see INSERT… SELECT …
Post: InnoDB's gap locks
March 27, 2012 By 15 Comments
…> START TRANSACTION; transaction2> INSERT INTO t VALUES(26); transaction2> COMMIT; transaction1> select …SELECT. They only occur if you do UPDATE or DELETE or SELECT FOR UPDATE. InnoDB provides REPEATABLE READ for read-only SELECT…mysql.com/doc/refman/5.1/en/innodb-locks-set.html. Conclusion MySQL…
Post: Can MySQL temporary tables be made safe for statement-based replication?
May 26, 2008 By 8 Comments
…test.t(a int) engine=innodb; -> insert into test.t(a) values(1); -> insert into test.ins(a) select * from test.t; -> drop temporary…pos: 198 Info: use `test`; insert into test.ins select * from test.t *************************** 6. row *************************** Log_name: mysql-bin.000011 Pos: 477 …
Post: Innodb performance gotcha w Larger queries.
August 28, 2009 By 7 Comments
… 1000 rows” SQL doesn’t have “SELECT” so, InnoDB scan all of the SQL each times… (* “INSERT” may not use this function? “UPDATE…->isolation_level select_lock_type != LOCK_NONE && trx->mysql_thd != NULL && thd_is_select(trx->mysql_thd)) { /* It is a plain locking SELECT and the…
Post: SELECT LOCK IN SHARE MODE and FOR UPDATE
August 6, 2006 By 51 Comments
… nice article comparing locking hints in MySQL and SQL Server. In MySQL/Innodb LOCK IN SHARE MODE and SELECT FOR UPDATE are more than… example would be INSERT INTO MyISAMTable SELECT * FROM INNODBTable; – A lot of shared locks on Innodb table but no updates. Supporting MySQL hints “HIGH…
Post: Edge-case behavior of INSERT...ODKU
October 27, 2012 By 6 Comments
… ENGINE=InnoDB; Now consider the following sequence of events. (root@localhost) [test]> INSERT… defined first. So, MySQL checks our INSERT, sees that the… the case. (root@localhost) [test]> select * from update_test; +————+———-+———+———————+ | id | username | host_id |…
