…two cases covered in the first post: single SELECT queries doing PRIMARY KEY lookups (aka QPS sysbench mode); same MySQL queries executed inside single…in MySQL 5.6, because all SELECT queries in…
Comment: MySQL Partitioning - can save you or kill you
April 30, 2013 By 45 Comments
…queries (within a single connection) anywhere in any ‘free’ variant of MySQL…single-row queries (SELECT, INSERT, DELETE, UPDATE) are similar in…table into 100 partitions, the BTree in each partition would be about 4 levels. A “point query” in a billion-row (non-partitioned) table…
Post: Percona XtraDB Cluster: Multi-node writing and Unexpected deadlocks
August 17, 2012 By 17 Comments
…mysql> insert into autoinc (j) values (‘node2′ ); Query OK, 1 row affected (0.00 sec) Now, on two …updating row 1 in our table. Note we haven’t committed yet. Now, let’s go to node3: node3 mysql> set autocommit=off; Query… to most complex: Only write to a single node in the cluster (at a time)– all …
Post: Implications of Metadata Locking Changes in MySQL 5.5
February 1, 2013 By 9 Comments
…table that is already in the Table Cache, any query to a table that has a DDL operation waiting, will block. Why? Because MySQL…update; +—+ | i | +—+ | 1 | +—+ 1 row in set (0.00 sec) session2 > CREATE TABLE…
Post: Data mart or data warehouse?
July 15, 2010 By 14 Comments
… There are two main …single dimension, while also maintaining the historical information about when each item was in…tables in a data mart may be updated. In…query execution, clustered tables and materialized views. These features make working with a star schema much easier than it may be on MySQL…
Post: Upgrading MySQL
January 5, 2010 By 24 Comments
…? Performing MySQL upgrade two …in MySQL 4.1 or changing JOIN evaluation in 5.0 (which could both cause different result set or make query not runnable in MySQL… in single installation – Wordpress…table-checksum to ensure data is the same. It is possible some update statements worked differently in…
Post: High-Performance Click Analysis with MySQL
December 22, 2008 By 13 Comments
…. Because I’ve built two such systems to manage …the single one doesn’t know what data should live in it…based replication in a way. When you’re updating big aggregate tables, …tables in a complex query with many joins. I have never seen this approach scale well. Use The Best Technologies You Can MySQL…
Post: Heikki Tuuri Innodb answers - Part I
October 26, 2007 By 19 Comments
…in two extents same as primary key tree or stored in…query where clause when it was not resolved by index HT: That is already implemented in… known new features in MySQL 5.1…in some denormalized database (for example, if ORDER and ORDERLINE tables are joined to form a single table…the questions. UPDATE:…
Post: Percona Toolkit Webinar followup Q&A
December 19, 2012 By 1 Comment
…table at a time, and processes each table one chunk at a time, checksumming each chunk with a single query…table-sync if the damage is contained (i.e. one or a few tables, in rows that probably won’t be updated…
Post: MySQL caching methods and tips
April 4, 2011 By 5 Comments
…single row in any table, the query cache entries for every query which accessed that table must be invalidated. If a frequently executed or expensive query… table. Ideally, the summary tables can be updated with only the information that changed since they were last populated. MySQL includes two…
