… aggregation, which should produce updates in-place (I will use INSERT .. ON DUPLICATE KEY UPDATE statements for that), so it will produce all… –test=insert_roll.lua –oltp-table-size=10000 –mysql-user=root –oltp-tables-count=32 –mysql_table_engine=tokudb –oltp_auto_inc=on –max…
Comment: MySQL Partitioning - can save you or kill you
April 30, 2013 By 45 Comments
… the same response, on a normal table as against a partitioned table. 2. The Insert should be quicker on a partitioned table, as… come out of the partition, not the whole table. 3. Insert on Duplicate Key (I dont do replace… ever)… if across partitions, will…. If you do queries on any other column, these could easily be quicker on a partitioned table, as Mysql can fire off one…
Post: Is your MySQL buffer pool warm? Make it sweat!
April 16, 2013 By 6 Comments
… about MySQL buffer pools or anything else that’s on your mind! There are numerous solutions for MySQL high availability. Many rely on MySQL’s… the option innodb_fake_changes to prevent INSERTs, UPDATEs, and DELETEs from corrupting the data on the standby server. You will need… step because update statements that match no rows or insert statements that have duplicate key errors may be faster than an actual…
Post: Repair MySQL 5.6 GTID replication by injecting empty transactions
March 26, 2013 By 1 Comment
… error: Last_SQL_Error: Error ‘Duplicate entry ’4′ for key ‘PRIMARY” on query. Default database: ‘test’. Query: ‘insert into t VALUES(NULL,’salazar… is found in Percona Toolkit for MySQL. Last week I gave a talk at Percona MySQL University @Toronto about GTID. It includes an overview of MySQL 5.6 GTID that can…
Post: INSERT ON DUPLICATE KEY UPDATE and REPLACE INTO
January 18, 2007 By 15 Comments
Jonathan Haddad writes about REPLACE INTO and INSERT ON DUPLICATE KEY UPDATE. Really, Why MySQL has both of these, especially both are non ANSI… really will insert NULL value, as col1 is undefined at this stage. INSERT ON DUPLICATE KEY UPDATE is newer feature which came in MySQL 4.1 by advice one of MySQL big users. The…
Post: INSERT ON DUPLICATE KEY UPDATE and summary counters.
May 29, 2006 By 48 Comments
INSERT … ON DUPLICATE KEY UPDATE is very powerful but often forgotten MySQL feature. It was introduced in MySQL 4.1 but I still constantly see… null, last_hit timestamp); insert into ipstat values(inet_aton(’192.168.0.1′),1,now()) on duplicate key update hits=hits+1; This example actually shows one more neat feature of MySQL – inet_aton and…
Post: Edge-case behavior of INSERT...ODKU
October 27, 2012 By 6 Comments
… against this table were almost exclusively INSERT … ON DUPLICATE KEY UPDATE (INSERT ODKU), with the columns from the INSERT part of the statement corresponding to… going to be considered as being defined first. So, MySQL checks our INSERT, sees that the next auto-inc value is available…
Post: Avoiding auto-increment holes on InnoDB with INSERT IGNORE
November 29, 2011 By 8 Comments
… tables on MySQL version 5.1.22 or newer? If so, you probably have gaps in your auto-increment columns. A simple INSERT… table lock on AUTO-INC. We have recovered the concurrency and the performance but with a small cost. Queries like INSERT … ON DUPLICATE KEY UPDATE produce gaps on the auto_increment column. To avoid this little…
Post: The case for getting rid of duplicate “sets”
May 17, 2011 By 2 Comments
…” the table in the database by removing the duplicates: mysql> create table ex2 as select val, count(*) from…time to delete. There is no index on this table. mysql> delete from data where val=16; Query …00 sec) mysql> insert into ex2 values (2,1); Query OK, 1 row affected (0.00 sec) mysql> select a….
Post: The story of one MySQL Upgrade
October 8, 2010 By 8 Comments
… in number of other environments. INSERT ON DUPLICATE KEY UPDATE had a unfair share of replication issues in MySQL 5.0. There are number… frequently this functionality is used. Happily there are only few INSERT ON DUPLICATE KEY UPDATE query instances, and only one of them into… easy enough to change the single application not to use INSERT ON DUPLICATE KEY UPDATE in this instance so it was done. So…
