… a query automatically would be more costly than just running the query in…up the row with “=” instead of “>” and be guaranteed to find exactly one match….the sequence after making certain insert/update/delete operations. If you have …id. It was more efficient in this case to force MySQL to scan the `…
Post: MySQL and the SSB - Part 2 - MyISAM vs InnoDB low concurrency
May 22, 2013 By Leave a Comment
… found that InnoDB does more IO than MyISAM when the database…than InnoDB both cold and hot. SSB Flight #4 There is one… a larger number of rows must be joined together (Q4… basedir=/usr/local/mysql socket=/var/lib/mysql/mysql.sock user=justin …_persistent innodb_stats_auto_update=off [mysqld_safe] log-…
Post: Follow these basics when migrating to Percona XtraDB Cluster for MySQL
May 1, 2013 By 10 Comments
…MySQL is a hot thing right now and some users jump right in without enough testing. Consequently, they’re more…_slave_updates is REQUIRED You need to have log_slave_updates enabled…, that is if you have more than one PXC node. This is because… your async master can use ROW based binlog format as well …
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 replication? It’s actually much worse than… large window where rows won’t be able to be updated more than just a few… simply send all your writes to one node. This should prevent the deadlock …
Post: Is your MySQL buffer pool warm? Make it sweat!
April 16, 2013 By 6 Comments
…more about MySQL buffer pools or anything else that’s on your mind! There are numerous solutions for MySQL…update statements that match no rows or insert statements that have duplicate key errors may be faster than… up the buffer pool for the next one. As an additional experiment we tried replaying…
Comment: MySQL Partitioning - can save you or kill you
April 30, 2013 By 45 Comments
…one at a time. (Please provide specific references if you find otherwise.) I would argue that single-row queries (SELECT, INSERT, DELETE, UPDATE…rows. This is, in my experience, the main use case for PARTITIONing. I go into more details (and code) here: http://mysql…
Post: Modeling MySQL Capacity by Measuring Resource Consumptions
March 8, 2011 By 1 Comment
…look at procfs for MySQL process: root@ubuntu:/var/log/mysql# cat /proc/19018…: 0 ROWS_FETCHED: 485139 ROWS_UPDATED: 0 TABLE_ROWS_READ: 610954 SELECT_COMMANDS: 181243 UPDATE_COMMANDS…example is unlikely to help. One helpful way to use this… this query which analyzes no more than 1 row may take up to …
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 20 Comments
…rows in set (43 min 10.11 sec) As an added benefit, if you build more than one…more quickly than COUNT(*). mysql> select count(*) cnt from order_lines\G *************************** 1. row *************************** cnt: 155187034 1 row in set (32.03 sec) mysql…
Post: Shard-Query EC2 images available
May 11, 2011 By 2 Comments
…for all configurations of more than one node. This would …mysql> show table status like ‘ontime_fact’G *************************** 1. row *************************** Name: ontime_fact Engine: InnoDB Version: 10 Row_format: Compact Rows: 6697533 Avg_row…-stats innodb-recovery-update-relay-log innodb-…
Post: Scaling problems still exist in MySQL 5.5 and Percona Server 5.5
May 20, 2011 By 11 Comments
…more than 8 cores effectively (which would correspond to single CPU socket these days) Here are some more…update,btr_cur_search_to_nth_level,btr_p cur_open_with_no_init_func,row_sel_get_clust_rec_for_mysql,row…
