… also updates groups with some metadata: BEGIN; INSERT INTO users_groups (user_id, group_id) VALUES (100, 1); UPDATE groups SET last_joined=NOW(), last_user_id=100 WHERE id=1; COMMIT; How often might multiple users join the same… go through, and possibly some poor user experience. batch writes Instead of updating global counters one at a time (from Example 1…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…_writtenblogpercona.commanual innodb_purge_batch_sizeblogpercona.commanual innodb_purge_….commanual interactive_timeoutblogpercona.commanual join_buffer_sizeblogpercona.commanual keep_…isolationblogpercona.commanual unique_checksblogpercona.commanual updatable_views_with_limitblogpercona.commanual …
Post: INSERT INTO ... SELECT Performance with Innodb tables.
July 12, 2006 By 22 Comments
… applies to similar tables with where clause and joins. It is important for tables which is being… general purpose. For example if you’re doing batch processing which is well indexed you might chop… thread id 1794760, query id 6994946 localhost root Updating update sample set j=0 where i=5 ——- TRX…
Post: How to debug long-running transactions in MySQL
March 8, 2011 By 6 Comments
… p.HOST FROM information_schema.innodb_lock_waits w INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id INNER JOIN information_schema.processlist p on b.trx_mysql_thread_id… of short, small transactions. Don’t design batch jobs that run in loops and update data, unless you make them commit between…
Post: Heikki Tuuri Innodb answers - Part I
October 26, 2007 By 19 Comments
…: Another possible area of optimization. I frequently see batch jobs killing server performance overtaking buffer pool. Though…(for example, if ORDER and ORDERLINE tables are joined to form a single table), it might …you to answer second portion of the questions. UPDATE: Second Part of the answers is …
