…, non-correlated, derived tables, scalar subqueries) and we saw in my presentation that sometimes these perform worse and sometimes better than… because each correlation name (k1, k2, k3) is an index lookup. Look at the EXPLAIN output — it accesses these first, by looking… the order of columns in the index. Example: WHERE (last_name, first_name) = (‘Karwin’, ‘Bill’); Q: On the Dynamic Pivot, the straight join…
Post: The small improvements of MySQL 5.6: Duplicate Index Detection
May 31, 2013 By Leave a Comment
… DBAs. Duplicate Index Detection I commented about this on my last webinar, but did not have time to …way, a good practice to avoid this is always naming your keys with a consistent pattern. This new …enforce this kind of constraints? For many reasons: the first one because it would break 99% of all …
Post: More on MySQL transaction descriptors optimization
April 26, 2013 By Leave a Comment
Since my first post on MySQL transaction descriptors optimization introduced in Percona Server … QPI speed (and thus, faster inter-process/node communication). Results: First of all, we used a smaller dataset for this benchmarks… protecting the list of transactions, its applicability is quite limited. Namely, it requires the list of transactions to be either empty…
Post: Percona XtraDB Cluster for MySQL and encrypted Galera replication
May 3, 2013 By 2 Comments
…Name (full name) []: Locality Name (eg, city) [Default City]: Organization Name (eg, company) [Default Company Ltd]: Organizational Unit Name (eg, section) []: Common Name (eg, your name… cluster will fail on the first state change after the expiration… a [brief] outage. In my case, I have an existing…
Post: Open Source, the MySQL market (and TokuDB in particular)
May 10, 2013 By 13 Comments
…thought on the topic, a PlanetMySQL post caught my eye. It was penned by Mike Hogan and …providing additional value to customers of your product. Namely, if your product is Open Source, you …have a successful business. There are two examples: First, the PBXT storage engine from PrimeBase – even with…
Post: Galera Flow Control in Percona XtraDB Cluster for MySQL
May 2, 2013 By 5 Comments
… find in standard async MySQL replication. It is my belief that the lack of understanding of this … applying replication. This may sound counter-intuitive at first: how would synchronous replication get behind? As I’… Node Queue Ops Bytes Flow Conflct time name P cnf # name cmt sta Up Dn Up Dn Up …
Post: Is your MySQL buffer pool warm? Make it sweat!
April 16, 2013 By 6 Comments
… server’s caches hot. (See my Fosdem 2013 slides for information about discarded designs and benchmarks.) First, we set long_query_time….1 –mysql-username playback –mysql-password PaSSwOrd –mysql-schema schema_name –query-log-stdin –dispatcher-plugin thread-pool –thread-pool-threads… roughly 1M queries. We warmed the cold database with the first chunk and replayed the second chunk after the warmup. The…
Post: Percona Live MySQL Conference 2013 wrap-up
April 29, 2013 By Leave a Comment
… and Matt Aslett of 451 Research very insightful. My major takeaways from those talks were: Oracle is …all MySQL users, vendors, and technologies. This was the first of what I hope are many such events … Clara, and the Santa Clara Convention Center, to name just a few. Please mark your calendars now …
Post: Can MySQL temporary tables be made safe for statement-based replication?
May 26, 2008 By 8 Comments
… if your transaction accesses only InnoDB tables, it’s safe. My first thought was, you can’t do that. CREATE TABLE commits… binlog events in ‘mysql-bin.000007′\G *************************** 1. row *************************** Log_name: mysql-bin.000007 Pos: 4 Event_type: Format_desc Server… ‘%temp%’; +————————+——-+ | Variable_name | Value | +————————+——-+ | Slave_open_temp_tables | 1 | +————————+——-+ The slave is now “vulnerable,” in theory. To test my theory, I’ll…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 20 Comments
Combating “data drift” In my first post in this series, I described materialized views (MVs). An … the first argument on the command line: $ cat sales.sql create table dashboard_customer_sales AS select customer_id, customer_name, DATE…_id’,'customer_id’); CALL flexviews.add_expr(@mvid,’GROUP’,'customer_name‘,’customer_name‘); CALL flexviews.add_expr(@mvid,’GROUP’,'DATE_FORMAT(order…
