… aggregation, which should produce updates in-place (I will use INSERT .. ON DUPLICATE KEY UPDATE statements for that), so it will produce all good… –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
… ‘Insert‘ part. 1. With a standard numeric key id, set as a Primary Key, it will probably be quicker, or the same response, on… come out of the partition, not the whole table. 3. Insert on Duplicate Key (I dont do replace… ever)… if across partitions, will be…. 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… step because update statements that match no rows or insert statements that have duplicate key errors may be faster than an actual database…
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’)’ Retrieved… is found in Percona Toolkit for MySQL. Last week I gave a talk at Percona MySQL University @Toronto about GTID. It includes…
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 people… primary key, hits int unsigned not null, last_hit timestamp); insert into ipstat values(inet_aton(’192.168.0.1′),1,now()) on duplicate key…
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 SQL… 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: Edge-case behavior of INSERT...ODKU
October 27, 2012 By 6 Comments
…secondary UNIQUE KEY. The queries being run against this table were almost exclusively INSERT … ON DUPLICATE KEY UPDATE (INSERT ODKU), with the columns from the INSERT part…defined, and the PRIMARY KEY is always going to be considered as being defined first. So, MySQL checks our INSERT, sees that …
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… 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…) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uniqname` (`name`) ) ENGINE=InnoDB; Insert a value using a LEFT OUTER JOIN: insert into foo(name) select…
Post: The story of one MySQL Upgrade
October 8, 2010 By 8 Comments
… problems 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 table…
Post: Announcing Percona Server 5.5.27-28.0
August 23, 2012 By 5 Comments
… upstream fix for MySQL bug #64284. Bug fixed #1015109 (Stewart Smith). Fixed the upstream MySQL bug #66301. Concurrent INSERT … ON DUPLICATE KEY UPDATE statements on a table with an AUTO_INCREMENT column could result in spurious duplicate key errors (and…
