… your workload. How frequently do you update any given row? We call rows that are updated heavily “hotspots”. Examples of hotspots Example… a player makes an achievement, your application updates this table with a statement like this: UPDATE achievements SET count = count + 1 where… go through, and possibly some poor user experience. batch writes Instead of updating global counters one at a time (from Example 1…
Comment: Is Synchronous Replication right for your app?
May 14, 2013 By 18 Comments
… penalty for each update. Second I think as the technology evolves we should see more workarounds. Queuing and batching technologies can be…
Post: Estimating Undo Space needed for LVM Snapshot
June 9, 2008 By 5 Comments
… know the IO pattern, say most of traffic comes from batch updates updating pretty much every page in 10GB table you can account… backup you will need 10GB of undo space. Second, limit batch activities – Backups are often done at night when server activity… will cause table rebuild and use a lot of space. Batch jobs such as recounting rating summary tables recreation etc are…
Post: How to estimate time it takes Innodb to Recover ?
May 9, 2007 By 7 Comments
… 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…
Comment: MySQL Master-Master replication manager released
April 6, 2007 By 27 Comments
… you do not have to wait for long but if batch update happens to fit in between it may take too long…
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… it did not have a bug. INSERT ON DUPLICATE KEY UPDATE, REPLACE, UPDATE all should be affected, though I have not tested…
Post: Fighting MySQL Replication Lag
September 22, 2008 By 7 Comments
… – this can be running update/delete with LIMIT in the loop, controlling maximum amount of values per batch in multiple row insert statement or Fetching data you’re planning to update/delete and having multiple…. In such case slave will only need to run UPDATE Example: UPDATE posts SET spam=1 WHERE body LIKE “%cheap rolex%”; This…
Post: How many partitions can you have ?
December 5, 2009 By 19 Comments
… statement. As I tested the UPDATE path on INSERT OF DUPLICATE KEY UPDATE (adding ON DUPLICATE KEY UPDATE set c=c+1 to… 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
… write all batch jobs to use Gearman MySQL UDF interface to apply update transactions to the master (but having the batch job only use the slave connection). You only need to implement a generic Gearman Worker to take update transactions… have a dedicated master for all batch job processing and use Gearman Workers to apply updates to this dedicated master, using pacing…
Post: MyISAM mmap feature (5.1)
May 26, 2006 By 10 Comments
… all POSIX-compatible platforms. It will work faster for SELECT/UPDATE/INSERT inside file queries, and no performance gain (maybe a… the end of file and on an exclusive operation (DELETE/UPDATE/INSERT inside file), for work with non-mmaped area we… ro) UPDATE primary key (update-key) UPDATE non key column (updaye-nokey) OLTP rw queries, SELECT/INSERT/DELETE/UPDATE queries (OLTP rw) batch select queries…
