June 19, 2013

Post: High availability for MySQL on Amazon EC2 – Part 2 - Setting up the initial instances

June 29, 2010 By 9 Comments

…_KEY=pk-yves.pem yves@yves-laptop:~$ ec2-add-group hamysql -d ‘nodes for HA MySQL solution’ GROUP hamysql nodes for HA MySQL solution From now, I’ll always assume the EC2_CERT and EC2_PRIVATE_KEY…-31-38-04-E5-E4.compute-1.internal.694: UDP, length 212 We can also use the “crm” tool to query…

Filed Under: Insight for DBAs, MySQL Tagged With:

Post: The MySQL optimizer, the OS cache, and sequential versus random I/O

April 28, 2008 By 17 Comments

… Engine: MyISAM Rows: 147045493 Avg_row_length: 117 Data_length: 17217646764 Index_length: 11993816064 mysql> show table status like ‘… filesort *************************** 2. row *************************** table: fact type: ref key_len: 4 rows: 606 Extra: Using where *************************** 3…

Filed Under: Insight for Developers Tagged With: ,

Post: Data compression in InnoDB for text and blob fields

May 30, 2012 By 2 Comments

… became the default after MySQL 5.0.3 and has a more compact representation for nulls and variable-length fields than REDUNDANT. Using InnoDB’s new Barracuda file format (available since InnoDB plugin 1.1 or MySQL 5… statement. As table compression is table specific, ROW_FORMAT and KEY_BLOCK_SIZE directives are passed via CREATE TABLE or ALTER…

Filed Under: Insight for DBAs, Insight for Developers, MySQL, Percona Software

Post: Finding out largest tables on MySQL Server

February 4, 2008 By 27 Comments

…MySQL instance is no brainier in MySQL 5.0+ thanks to Information …length / ( 1024 * 1024 * 1024 ), 2), ‘G’) idx, CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 * 1024 ), 2), ‘G’) total_size, ROUND(index_length / data_length… may be there is long primary key with Innodb tables. Of course …

Filed Under: Insight for DBAs Tagged With:

Post: Getting History of Table Sizes in MySQL

December 8, 2010 By 13 Comments

…_INCREMENT` bigint(21) unsigned DEFAULT NULL, PRIMARY KEY(DAY,TABLE_SCHEMA,TABLE_NAME), KEY(TABLE_SCHEMA,TABLE_NAME) ) ENGINE=INNODB DEFAULT CHARSET…,DATA_LENGTH,INDEX_LENGTH,DATA_FREE,AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES” Though if you’re looking to keep it completely inside MySQL…

Filed Under: Insight for DBAs, MySQL Tagged With:

Post: Joining on range? Wrong!

May 17, 2010 By 10 Comments

…`,`itm_order_timestamp`) In 3rd row key_len is only 4 bytes, while the full key length is 4 bytes for `itm_prd… those not matching the date range. A very inefficient approach! MySQL was unable to optimize those simple conditions to match both… no join, however you can notice key_len is 8 bytes, so the full index length. It means both index columns will…

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

Post: ALTER TABLE: Creating Index by Sort and Buffer Pool Size

June 27, 2012 By 4 Comments

…in terms of data and index size: mysql> show table status like “sbtest” \G *************************** 1. row… Rows: 10000060 Avg_row_length: 224 Data_length: 2247098368 Max_data_length: 0 Index_length: 1460322304 Data_free…creation=1 which builds table with primary key only when creating a table and …

Filed Under: Insight for DBAs, MySQL

Post: Moving Subtrees in Closure Table Hierarchies

February 14, 2011 By 18 Comments

…) NOT NULL, descendant CHAR(1) NOT NULL, length INT NOT NULL DEFAULT 0, PRIMARY KEY (ancestor, descendant) ) ENGINE=InnoDB; Because there isn… the same table in a single query in MySQL. But we can use MySQL‘s multi-table DELETE syntax, to find any… down). INSERT INTO TreePaths (ancestor, descendant, length) SELECT supertree.ancestor, subtree.descendant, supertree.length+subtree.length+1 FROM TreePaths AS supertree JOIN TreePaths…

Filed Under: Insight for Developers, MySQL

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

March 4, 2013 By 7 Comments

…’s default ft_min_word_length. First, MyISAM, with MySQL 5.5, on the SEO data set: mysql: SELECT id, title, MATCH(title… phrases that include initials, numbers, or any other string of length less than 3 (the default), I think you’re going… escape hatch from this rabbit hole. When defining a FULLTEXT KEY, you can use the “WITH PARSER” modifier to specify the…

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

Post: High-Performance Click Analysis with MySQL

December 22, 2008 By 13 Comments

… fixed-length. One reason denormalization is important is that nested-loop joins on large data sets are very expensive.  If MySQL… and annoyances.  There are already inconvenient limitations on some key parts of partitioning, such as maintenance and repair commands, that… save that work by either using MySQL 5.1′s row-based replication, or in MySQL 5.0 and earlier, doing the…

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