… we try to insert another value on the second session: transaction2 > START TRANSACTION; transaction2 > INSERT INTO t … with the replication you should change the binary log format to “row”. Depending on the ….1/en/innodb-locks-set.html. Conclusion MySQL uses REPEATABLE READ as the default isolation…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 12 Comments
…refeshed. The incremental method is somewhat conceptually similar to using mysql binary logs for point-in-time recovery after a restoring a…flexviews.create(‘demo’, ‘dashboard_customer_sales’, ‘INCREMENTAL’); SET @mvid := LAST_INSERT_ID(); CALL flexviews.add_expr(@mvid,’GROUP’,'customer_id’,'customer…
Post: Fighting MySQL Replication Lag
September 22, 2008 By 6 Comments
… and small and fast updates which go after it in MySQL binary log can’t proceed. It is either more than than…, controlling maximum amount of values per batch in multiple row insert statement or Fetching data you’re planning to update/delete… rows examined than modified. The next common mistake is using INSERT … SELECT – which is in similar to what I just described…
Post: Statement based replication with Stored Functions, Triggers and Events
December 16, 2011 By 3 Comments
…_data AFTER INSERT on t FOR EACH ROW INSERT INTO t_copy VALUE(NEW.i); mysql> insert into t VALUES(1),(2),(3); Binary Log… slaves servers. Example: mysql> create procedure this_pyear() BEGIN INSERT INTO t VALUES(YEAR(CURDATE())); END;// mysql> CALL this_pyear(); Binary Log: #111213 23… replicated on the slave. Example: mysql> SELECT YEAR(CURDATE()) INTO @this_year; mysql> insert into t VALUES(@this_year) Binary Log: #111213 23:58…
Post: Using Flexviews - part two, change data capture
March 25, 2011 By 11 Comments
… name. The Binary Log is the MySQL log which records changes to tables in the database. FlexCDC reads the binary log to determine… create a table, insert some rows, add a change log, delete rows and then insert some more of them: mysql> create table > test… to `test`.`demo` will automatically be captured. Insert data in one transaction (two rows): mysql> insert into test.demo values (NULL,2),(NULL…
Post: Can MySQL temporary tables be made safe for statement-based replication?
May 26, 2008 By 8 Comments
… logged to the binary log. Let’s create a stored procedure to hold the transaction, which this time will insert data from… what’s in the binary log: master > show binlog events in ‘mysql-bin.000011′\G *************************** 1. row *************************** Log_name: mysql-bin.000011 Pos…
Post: Concurrent inserts on MyISAM and the binary log
May 14, 2008 By 7 Comments
… query, INSERT INTO tbl1 SELECT * FROM tbl2 The concurrent inserts into tbl2 can block. This happens if you have the binary log enabled… MySQL manual actually says this, but not in the clearest way. It just says If you are using the binary log, concurrent inserts are converted to normal inserts for CREATE … SELECT or INSERT … SELECT statements. If you use…
Post: INSERT INTO ... SELECT Performance with Innodb tables.
July 12, 2006 By 21 Comments
… innodb_locks_unsafe_for_binlog option with caution. Note disabling binary logs is not enough to trigger relaxed locks. You have… MySQL Server) as otherwise the script will fail second time. If you need result to be even closer to one of INSERT… 224576 MySQL thread id 1794751, query id 6994931 localhost root Sending data insert into test select * from sample ——– As you can see INSERT…
Post: 5.0.75-build12 Percona binaries
January 23, 2009 By 18 Comments
… important fixes to our patches we made binaries for build12. Fixes include: Control of InnoDB insert buffer to address problems Peter mentioned…), the each user threads do nothing about contracting the insert buffer until the insert buffer reaches its maximum size. 1 makes the… application. You can download binaries (RPMS x86_64) and sources with patches here http://www.percona.com/mysql/5.0.75-b12/
Post: Why audit logging with triggers in MySQL is bad for replication
September 29, 2008 By 10 Comments
… own as they both allow you to quickly parse the binary log files, extracting the relevant statements. mysqlbinlog bin_log.000001 | mysqlsla -lt binary – Check out http://hackmysql.com/mysqlsla_filters for how to…, with the inserts being assigned a different Foo_History_Id. This is because the statements are written to the binary log in…
