… on “MySQL Query Patterns, Optimized” for Percona MySQL Webinars. If you…compare the use of subqueries/multiple joins vs. multiple queries (e.g. temp … every solution for randomly choosing rows involves some compromise, either of… sequence after making certain insert/update/delete operations. If you …
Post: Follow these basics when migrating to Percona XtraDB Cluster for MySQL
May 1, 2013 By 10 Comments
…Cluster (PXC) for MySQL is a hot thing right now and … basics. log_slave_updates is REQUIRED You need to have log_slave_updates enabled on the…be good if your async master can use ROW based binlog format as well to achieve … if you are planning to write on multiple nodes. Have PRIMARY KEYS If you still…
Post: Is Synchronous Replication right for your app?
May 14, 2013 By 18 Comments
…MySQL replication from this instance, since MySQL replication is asynchronous. What about semi-sync MySQL …How frequently do you update any given row? We call rows that are updated heavily “hotspots”. …UPDATE groups SET last_joined=NOW(), last_user_id=100 WHERE id=1; COMMIT; How often might multiple…
Comment: Is Synchronous Replication right for your app?
May 16, 2013 By 18 Comments
… a message queue, not MySQL at all. E.g. ActiveMQ, RabbitMQ, Resque, etc. All applications would send updates to the MQ in… comment, “I’m not sure how useful it is to update the same row multiple times in a single transaction.”
Post: Fighting MySQL Replication Lag
September 22, 2008 By 7 Comments
… you have transaction containing millions of simple updates instead of one large update to help MySQL replication lag it is not going to… 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 queries to delete it…
Post: How multiple disks can benefit for single client workload ?
April 26, 2008 By 4 Comments
… bound or CPU bound, while to understand if MySQL goes through more rows than it needs to requires look at the queries… update queries (from replication thread for example). When update is performed first problem is actually reading the data. If you’re updating the row… can result in multiple outstanding requests to the disk. Though this does not help dramatically for many “random” update queries. After pages…
Post: INSERT ON DUPLICATE KEY UPDATE and REPLACE INTO
January 18, 2007 By 15 Comments
… rows is – it could perform row replacement without reading old data first, and of course because you could set it to replace multiple… in MySQL. There are also number of similar cases when you want ether to insert the new row or update stats for existing row. I think both features are really great and I use them both. They are implemented really in MySQL…
Post: Edge-case behavior of INSERT...ODKU
October 27, 2012 By 6 Comments
…considered as being defined first. So, MySQL checks our INSERT, sees that …row (which failed) and 1 request to update a row, which succeeded (this explains why, when there’s a row-update, that we have 2 rows…table with multiple unique indexes such as this one would be equivalent to “UPDATE update_test…
Post: Innodb Table Locks
July 31, 2012 By 4 Comments
… sec fetching rows mysql tables in use 1, locked 1 9417 lock struct(s), heap size 915896, 696679 row lock(s) MySQL thread id… because update needs to lock the rows in exclusive mode. Similar to IS lock IX is rather lose – I can have multiple update queries running on the table each locking it in IX mode, which will not conflict unless they touch the same rows…
Post: Should we give a MySQL Query Cache a second chance ?
April 10, 2011 By 64 Comments
… multiple MySQL Query Cache instances hashed by query hash. It would help to reduce complexity dramatically. Fast Invalidation Right now when you update…, for which tracking exact rows used from query syntax may be hard. Query Cache Storage Engines MySQL Query Cache now stores queries…
