… on MySQL version 5.1.22 or newer? If so, you probably have gaps in your auto-increment columns. A simple INSERT… and assigns the new value to the column. Prior to MySQL 5.1.22 InnoDB used a method to access that… to this new value, a simple insert query like a single-row or multi-row INSERT/REPLACE uses a light-weight mutex instead…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
… amazing MySQL manual, especially the option and …commanual Com_replaceblogpercona.commanual Com_replace_selectblogpercona.commanual Com_resetblogpercona….commanual delayed_insert_limitblogpercona.commanual Delayed_insert_threadsblogpercona.commanual delayed_insert_timeoutblogpercona.commanual…
Post: MySQL on Amazon RDS part 1: insert performance
March 15, 2011 By 37 Comments
… Database Service (RDS) is a cloud-hosted MySQL solution. I’ve had some clients hitting performance… of them wanted to evaluate RDS as a replacement. It is built on the same technologies, but…would really benefit them. Finally, a single-threaded insert workload is not very revealing. To understand the…
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… to replace multiple values at the same time just as you have multiple value INSERT. As a side note: the fact REPLACE does… doing something like REPLACE INTO tbl (col1) VALUES (col1+10);. Instead of acting as update this really will insert NULL value, as…
Post: Recovering Innodb table Corruption
July 4, 2008 By 19 Comments
… I actually went ahead and manually edited test.ibd file replacing few bytes so corruption is mild. First I should note… trial and error approach: mysql> insert into test2 select * from test; ERROR 2013 (HY000): Lost connection to MySQL server during query You may… sec) mysql> insert ignore into test2 select * from test where id>250; ERROR 2013 (HY000): Lost connection to MySQL server during query mysql> insert ignore…
Post: EXPLAIN EXTENDED can tell you all kinds of interesting things
June 15, 2010 By 7 Comments
…. Because of this, MySQL can compare the constant values before completely formulating the plan. You will notice the MySQL replaces the WHERE clause… test the plan: mysql> insert into j1 values (1); insert into j2 select * from j1; insert into j3 select * from j2; mysql> explain extended select…
Post: MySQL Server Variables - SQL layer or Storage Engine specific.
June 8, 2006 By 14 Comments
…memory tables. So first MySQL versions did not …inserts (multiple value inserts). Really helps if you have really bulk inserts 100-1000+ values per single insert statement. concurrent_insert – Enables concurrent insert… Key Cache/Key Buffer replacement algorithm configuration variables. Obviously…
Post: Dynamic row format for MEMORY tables
September 6, 2011 By 3 Comments
…MEMORY storage engine (also known as HEAP) in MySQL has been one of the long-standing …application depending on MEMORY tables, they are no replacement for application-specific tests. We start by … Also we modify the sysbench non-transactional INSERT test to insert strings of random length between 0 …
Post: MySQL caching methods and tips
April 4, 2011 By 4 Comments
…. MySQL includes two statements that make this easier: CREATE TABLE .. SELECT and INSERT .. SELECT. These SQL commands can be used to either replace the contents of, or insert new data into the… can be indexed appropriately for your queries. Using INSERT .. SELECT for summary tables The INSERT .. SELECT approach works best when there is…
Post: MySQL Query Cache
July 27, 2006 By 69 Comments
… great feature called “Query Cache” which is quite helpful for MySQL Performance optimization tasks but there are number of things you… overhead is of course inserts so you can see how much of inserted queries are used: Qcache_hits/Qcache_inserts Other portion of overhead comes from modification statements which you can calculate by (Com_insert+Com_delete+Com_update+Com_replace…
