… distribute splitting and/or loading among multiple nodes as well. Note that in… flight. Some data hand updated.’; — – Table structure for table `ontime_fact` — CREATE TABLE `ontime_fact` ( `date_id…from 1988 to 2010′; mysql> use ontime1; Database changed mysql> show table status like ‘ontime_fact’…
Post: Sysbench with support of multi-tables workload
April 29, 2011 By 4 Comments
…just pushed to sysbench support for workload against multiple tables ( traditionally it used only single table). It is available from launchpad source …select insert delete update_index update_non_index to support different OLTP modes. Most interesting: all scripts support –oltp-tables-count=N ( …
Post: MySQL Upgrade Webinar Questions Followup
June 28, 2012 By 7 Comments
… you can get to former execution plan either by updating statistics (ANALYZE TABLE) or disabling some of the optimization/statistics related features… (including memory) available. Another thing to mention having multiple instances of different MySQL versions on the same box typically means you will…
Post: Sharing an auto_increment value across multiple MySQL tables
October 4, 2010 By 9 Comments
…); # $connection->insert_id(); Option #2: Use a table with one just row: CREATE TABLE option2 (id int not null primary key) engine… 1 # each insert does two operations to get the value: UPDATE option2 SET id=@id:=id+1; SELECT @id; So which… threads are perpetually in a state of ‘Searching rows for update‘ – which is what I suspect is better described as waiting…
Post: Avoiding auto-increment holes on InnoDB with INSERT IGNORE
November 29, 2011 By 8 Comments
… you using InnoDB tables on MySQL version 5.1.22 or newer? If so, you probably… a small cost. Queries like INSERT … ON DUPLICATE KEY UPDATE produce gaps on the auto_increment column. To avoid … 1 row affected (0.00 sec) Insert the same value multiple times. As you will see, the INSERT is ignored …
Post: Moving Subtrees in Closure Table Hierarchies
February 14, 2011 By 18 Comments
… new node. When you insert a subtree, you’re inserting multiple nodes, and you want to create new paths for each… MySQL complains: “You can’t specify target table ‘TreePaths’ for update in FROM clause.” We can’t DELETE and SELECT from the same table in a single query in MySQL…
Post: MySQL Query Cache
July 27, 2006 By 70 Comments
…”Query Cache” which is quite helpful for MySQL Performance optimization tasks but there are …are constantly being invalidated from query cache by table updates, this means number of queries in cache… caching systems can effectively use memory on multiple systems so there is no duplication. Memcached…
Post: MySQL caching methods and tips
April 4, 2011 By 5 Comments
… MySQL query cache, since the rate of invalidation can not be controlled, and multiple cache entries may be invalidated by a single table… a summary table. Ideally, the summary tables can be updated with only the information that changed since they were last populated. MySQL includes two… time (like a memcache miss) or they must be updated. Updating the summary tables efficiently is a hard problem to solve. My latest…
Post: MySQL Users Conference - Innodb
May 9, 2007 By 6 Comments
… does which can be multiple orders of magnitude faster for large tables. There is fair number …to statement level MySQL Replication which among other requirements, needs all auto-increment values in multiple value …in INSERT IGNORE and ON DUPLICATE KEY UPDATE cases. Now In MySQL 5.1+ it is also …
Post: Percona XtraDB Cluster: Multi-node writing and Unexpected deadlocks
August 17, 2012 By 17 Comments
… rows affected (0.02 sec) node2 mysql> show create table autoinc\G *************************** 1. row *************************** Table: autoinc Create Table: CREATE TABLE `autoinc` ( `i` int(10) unsigned NOT… on node1 updating row 1 in our table. Note we haven’t committed yet. Now, let’s go to node3: node3 mysql> set…, when is this really a problem? Really whenever transactions across multiple nodes can take the same write locks. Whether or not…
