… variable – if updates you’re performing are happening to some small amount of pages (for example you’re doing batch update for relatively… application was well optimized to accumulate updates in the cache and so having pretty scattered updates instead updating view counter for popular movie… the disk and undo phase – rolling back uncommitted transactions. As MySQL 5.0 these seems to be done in the background…
Post: MySQL Users Conference - Innodb
May 9, 2007 By 6 Comments
… similar problems in INSERT IGNORE and ON DUPLICATE KEY UPDATE cases. Now In MySQL 5.1+ it is also possible to fix this problem for cases when number of rows in auto-increment batch… patch out where already which may come in MySQL 5.2 or even MySQL 5.1. I surely would like to see…
Post: How expensive is MySQL Replication for the Master
January 16, 2010 By 13 Comments
…generally thought about MySQL replication as being quite low overhead on Master, depending on… was Master was having a lot of very small updates going on – over 15.000 transactions a second. Each … make replication threads to be woken up in the batches. For example we could tune the system to wake …
Post: Innodb performance gotcha w Larger queries.
August 28, 2009 By 7 Comments
… INSERT ON DUPLICATE KEY UPDATE) As I went from 1 value to 3 or 10 in the batch performance improved, especially for network tests, however going to batches of 100 and 1000 values… mysql_thd…. Scanning the statement for each update… that is a bummer ! It is great to see however it is fixed in MySQL…
Post: How many partitions can you have ?
December 5, 2009 By 19 Comments
… with large MySQL data warehouse had the table which was had data merged into it with INSERT ON DUPLICATE KEY UPDATE statements… the batch. With 100 rows per batch and with 10000 rows per batch performance was not significantly different from 1000 rows per batch so I conclude this is per row overhead. This test gives other rather interesting data point – it looks like updates…
Comment: Load management Techniques for MySQL
May 10, 2012 By 3 Comments
… to write all batch jobs to use Gearman MySQL UDF interface to apply update transactions to the master (but having the batch job only… take update transactions to apply and pace MySQL transactions to the real master db. You can configure multiple workers for quicker batch job… use Percona MySQL Cluster to have a dedicated master for all batch job processing and use Gearman Workers to apply updates to this…
Post: Looking at Redis
August 27, 2009 By 27 Comments
… applications which is very update intensive with updates being pretty much random single row updates which are hard to batch. With MySQL/Innodb I got server being able to handle some 30.000 updates/sec on… probably get 4 MySQL instances on this server and get up to 100K updates/sec with up to 40K updates/sec being able…
Comment: Edge-case behavior of INSERT...ODKU
October 28, 2012 By 6 Comments
… due to mysql‘s gap locking. The method I use is to insert into these types of tables, involves batching up a… use select for update on the user table to avoid race conditions — but then be careful of the batch insert/update sizes, because you may run into contention issues. If however, the update doesn’t depend on…
Comment: Edge-case behavior of INSERT...ODKU
October 31, 2012 By 6 Comments
… cut it. It was slow as a dog (even when batching in transactions), and was leaking autoincr keys when hitting duplicates…. I was forced to find another method, and my batch insert / update method was it. Now I dont remember exactly what the… also highlights what would be a VERY good feature in MySQL. InsODKU would be much better implemented on the server side…
Post: INSERT INTO ... SELECT Performance with Innodb tables.
July 12, 2006 By 22 Comments
… are less general purpose. For example if you’re doing batch processing which is well indexed you might chop transactions and… 14 sec, process no 29895, OS thread id 2894768 updating or deleting mysql tables in use 1, locked 1 LOCK WAIT 3… size 320, undo log entries 1 MySQL thread id 1794760, query id 6994946 localhost root Updating update sample set j=0 where i…
