… me rephrase the use case — * The entire table is too big to be cached, but * One partition is small enough to… stay in the buffer_pool.) If I recall correctly, any INSERT/DELETE/etc always opens all partitions, even before thinking about… find otherwise.) I would argue that single-row queries (SELECT, INSERT, DELETE, UPDATE) are similar in speed between PARTITIONed or non…
Post: Follow these basics when migrating to Percona XtraDB Cluster for MySQL
May 1, 2013 By 10 Comments
… internally within the cluster, this is to avoid collisions when INSERTs are happening on not only a single node. However, this… any workload – it shines with small transactions but not with big ones. If you are consistently running overnight reporting jobs and…
Comment: Measuring the amount of writes in InnoDB redo logs
October 8, 2012 By 4 Comments
… and you may wonder why a big insert statement only uses a small portion of your nice big buffer pool size. 2. Adaptive… to this, ensuring that they optimise for lifetime with nice big log files, not just making the log files small. People… handling non-regular loads like the time someone runs a big report and slows everything down, including log flushing. The OS…
Post: MySQL on Amazon RDS part 1: insert performance
March 15, 2011 By 37 Comments
…. This creates a table+index data size approximately twice as big as memory, so the workload should be somewhat IO-bound… thought it would be interesting to measure the single-threaded insert throughput as sysbench ran the “prepare” phase and filled the… that it would really benefit them. Finally, a single-threaded insert workload is not very revealing. To understand the sustained write…
Post: INSERT ON DUPLICATE KEY UPDATE and REPLACE INTO
January 18, 2007 By 15 Comments
… acting as update this really will insert NULL value, as col1 is undefined at this stage. INSERT ON DUPLICATE KEY UPDATE is newer feature which came in MySQL 4.1 by advice one of MySQL big users… also number of similar cases when you want ether to insert the new row or update stats for existing row. I…
Post: INSERT ON DUPLICATE KEY UPDATE and summary counters.
May 29, 2006 By 48 Comments
… see people unaware of it. Myself I like this feature big deal because it is designed in truly MySQL style – very… automatically updated to current timestamp on insert and update. We actually could have omitted now() in insert clause but this would require… timestamp updated. The benefit of using this feature insted of INSERT + UPDATE could be different, depending on number of new rows…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…Delayed_errorsblogpercona.commanual delayed_insert_limitblogpercona.commanual Delayed_insert_threadsblogpercona.commanual delayed_insert_timeoutblogpercona.commanual delayed_…sql_auto_is_nullblogpercona.commanual sql_big_selectsblogpercona.commanual sql_big_tablesblogpercona.commanual sql_buffer_…
Post: High-Performance Click Analysis with MySQL
December 22, 2008 By 13 Comments
… bypass the hard-to-scale database server for the initial insertion, because you can write CSV files with any programming language… server. If you do it on the master with INSERT..SELECT queries, it will propagate to the slaves and it… The nested-loop joins are not all that fast on big joins; the query optimizer can sometimes pick bad plans when…
Post: My Innodb Feature wishes
May 4, 2006 By 30 Comments
… times space compression. Insert buffer for delete. For Insert operation Innodb uses nice tecnique called Insert Buffer which speeds up inserts for non-unique… times+ slower, which is especially bad for rollback of failed inserts which may take too long to complete Index build by… to have more than one log flush thread. The other big lacking (especially as patch for it existed for years) is…
Post: Bug#12704861
November 20, 2011 By 1 Comment
…-transaction. btr_store_big_rec_extern_fields_func(): Add the parameter alloc_mtr, which is NULL (old behaviour in inserts) and the…_entry_low(): When columns are being made off-page in insert-by-update, invoke btr_mark_freed_leaves(nonfree=TRUE) and… record that contains NULL BLOB pointers must be a fresh insert. row_upd_clust_rec(): When columns are being moved off…
