June 18, 2013

Post: MySQL 5.6 Compatible Percona Toolkit 2.2 Released

March 25, 2013 By 2 Comments

…’s still one noticeable omission: pt-table-sync. It’s still unclear …problem finding queries to run and compare. pt-query-digest is simpler pt-query-digest…bad versions. For example, there are two versions of the DBD::mysql … their run-related options a little differently. This magic is gone now: both…

Filed Under: Percona Software, Percona Toolkit Tagged With: , , , , ,

Post: Flexviews - part 3 - improving query performance using materialized views

April 4, 2011 By 20 Comments

… tables” (those tables on which the view was built) there must be a process to bring them up-to-date. Flexviews includes two different…, order_lines and customers tables directly (the tables on which dashboard_customer_sales is built), then the query would take significantly longer (40…

Filed Under: Insight for DBAs, MySQL

Post: InnoDB Full-text Search in MySQL 5.6: Part 2, The Queries!

March 4, 2013 By 7 Comments

… post) than what MyISAM gives us. Recall that we have two different sets of data, one which is the text of roughly…=latin1; Query OK, 0 rows affected (0.02 sec) mysql: SET GLOBAL innodb_ft_server_stopword_table=’test/innodb_ft_list2′; Query OK… InnoDB table, be prepared for a table rebuild. Calculation of match score is completely different between the two engines; sometimes this leads to wildly different…

Filed Under: Insight for DBAs, Insight for Developers, MySQL Tagged With: , , , , ,

Post: Identifying the load with the help of pt-query-digest and Percona Server

December 29, 2011 By 3 Comments

…two (34/22%)… 1 more # Query_time distribution # 1us # 10us # 100us ################### # 1ms ################################################################ # 10ms # # 100ms # 1s # 10s+ # Tables # SHOW TABLE…

Filed Under: Insight for DBAs, MySQL

Post: Why MySQL could be slow with large tables ?

June 9, 2006 By 141 Comments

… a lot as we’re speaking about single thread/query here. So difference is 3.000 times! It might be a bit… times difference. Here is little illustration I’ve created the table with over 30 millions of rows. “val” column in this table has… the join. Some people assume join would be close to two full table scans (as 60mil of rows need to be read…

Filed Under: Insight for DBAs Tagged With: ,

Post: Innodb Table Locks

July 31, 2012 By 4 Comments

…spotted important difference between MyISAM and Innodb when it comes to Table Level Locks. For MyISAM tables running UPDATE query on the table is …conflicts on MySQL level table locks for most queries. Summary: MySQL Table level locks and Innodb Table Level locks are two separate beings. You …

Filed Under: Insight for DBAs, MySQL

Post: Derived Tables and Views Performance

August 31, 2006 By 16 Comments

… compare in terms of performance ? Derived Tables in MySQL 5.0 seems to have different implementation from views, even though I would… in the temporary table, furthermore temporary table with no indexes (so you really do not want to join two derived tables for example). One… hope MySQL will fix in next MySQL versions – the fact queries in this example behave differently is illogical and counter intuitive.

Filed Under: Insight for Developers Tagged With:

Post: How to estimate query completion time in MySQL

April 22, 2008 By 16 Comments

… typical data-warehousing query needed optimization. It was a fact table joined to two dimension tables — a classic star schema query. The fact table was very… you an idea how you can tackle this problem in different situations.

Filed Under: Insight for DBAs Tagged With: ,

Post: Merge Tables Gotcha

May 9, 2007 By 4 Comments

… underlying base tables some of the queries may give you wrong results: mysql> create table t1(id int unsigned primary key); Query OK, 0…) In this case there are obviously two rows with value 1 in merge table however select querying by primary index returns only one…(t1,t2)” If you create table this way it structure will be in fact different from underlying tables as PRIMARY KEY on the…

Filed Under: Insight for DBAs Tagged With:

Post: Efficient Boolean value storage for Innodb Tables

April 23, 2008 By 6 Comments

… which can store one of two values – NULL or Empty String. Lets see how these 3 different table format look in Innodb (I’ve populated each with some 2M rows so difference is more visible) CREATE TABLE `tbool… NULL operator which is painful because you need to have different query based on parameter (IS ” would not work) or you can…

Filed Under: Insight for Developers Tagged With:
« Older Posts
Newer Posts »