June 20, 2013

Post: Hijacking Innodb Foreign Keys

January 2, 2012 By 2 Comments

… make it happen: CREATE TABLE `parent` ( `parent_id` int(11) NOT NULL, `v` varchar(10) DEFAULT NULL, PRIMARY KEY (`parent_id`) ) ENGINE…) mysql> show create table child \G *************************** 1. row *************************** Table: child Create Table: CREATE TABLE `child` ( `parent_id` int(11) NOT NULL, `v` varchar…

Filed Under: Insight for DBAs, MySQL

Post: Beware the Innodb Table Monitor

November 29, 2011 By 2 Comments

… itself dumps the contents of the Data dictionary to the mysql error log, which looks something like this: =========================================== 090420 12:09… COLUMNS: ID: DATA_VARCHAR DATA_ENGLISH len 0; FOR_NAME: DATA_VARCHAR DATA_ENGLISH len 0; REF_NAME: DATA_VARCHAR DATA_ENGLISH len 0; N_COLS: DATA_INT len 4; DB…

Filed Under: Insight for DBAs, MySQL

Post: High-Performance Click Analysis with MySQL

December 22, 2008 By 13 Comments

…-0019b984eacd” and is it stored in a VARCHAR(36)?  When tables get big, every …int unsigned not null, clicks int unsigned not null, impressions int unsigned not null, blue_clicks int unsigned not null, blue_impressions int… by either using MySQL 5.1′s row-based replication, or in MySQL 5.0 and …

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

Post: Recovering from a bad UPDATE statement

October 19, 2012 By 5 Comments

… table without a WHERE clause?  Did you know that in MySQL 5.5 that sometimes you can recover from a bad… ! Imagine this scenario: CREATE TABLE `t1` ( `c1` int(11) NOT NULL AUTO_INCREMENT, `c2` varchar(10) NOT NULL, PRIMARY KEY (`c1`) ) ENGINE…=ROW — MIXED doesn’t help as in my testing as MySQL chooses to binlog UPDATEs in STATEMENT format when in MIXED…

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

Post: Dynamic row format for MEMORY tables

September 6, 2011 By 5 Comments

…. In 2008 Igor Chernyshev of eBay implemented true-VARCHAR support for MEMORY tables for MySQL 5.0. Although this patch did not… be the following: CREATE TABLE sbtest (id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, c VARCHAR(4096) NOT NULL DEFAULT ”, PRIMARY KEY… row format could be used instead: for schemas containing large VARCHARs, BLOB, TEXT. Unfortunately, enabling this feature is not trivial, but…

Filed Under: MySQL, Percona Software

Post: InnoDB Full-text Search in MySQL 5.6 (part 1)

February 26, 2013 By 1 Comment

… VARCHAR(255) “title” and “details” with a TEXT “body”. CREATE TABLE `dir_test_innodb` ( `id` int(10) unsigned NOT NULL, `full_name` varchar….ibd -rw-rw—-. 1 mysql mysql 98304 Feb 20 16:09 FTS_00000000000000ad_STOPWORDS.ibd -rw-rw—-. 1 mysql mysql 98304 Feb 20 15….ibd -rw-rw—-. 1 mysql mysql 98304 Feb 20 15:54 FTS_0000000000000114_STOPWORDS.ibd -rw-rw—-. 1 mysql mysql 8618 Feb 20 16…

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

Post: Percona XtraDB Cluster: Multi-node writing and Unexpected deadlocks

August 17, 2012 By 17 Comments

… insert some data: node2 mysql> create table autoinc ( i int unsigned not null auto_increment primary key, j varchar(32) ); Query OK, 0 rows affected (0.02 sec) node2 mysql> show… TABLE `autoinc` ( `i` int(10) unsigned NOT NULL AUTO_INCREMENT, `j` varchar(32) DEFAULT NULL, PRIMARY KEY (`i`) node2 mysql> insert into autoinc…

Filed Under: MySQL, XtraDB Cluster

Post: Concatenating MyISAM files

November 19, 2012 By 5 Comments

… able to concatenate these files. Let’s see. mysql> create table test_concat(id int unsigned not null, primary key (id)) engine… added varchar and deleted rows to the mix: mysql> truncate table test_concat; Query OK, 0 rows affected (0.00 sec) mysql> truncate table test_concat_part; Query OK, 0 rows affected (0.01 sec) mysql> alter table test_concat add data varchar(10…

Filed Under: MySQL

Post: MySQL 6.0 vs 5.1 in TPC-H queries

March 25, 2008 By 17 Comments

…TPC-H benchmarks, particularly comparing MySQL 6.0.4-alpha with 5.1. MySQL 6.0 is interesting …orderkey` int(11) NOT NULL, `l_partkey` int(11) NOT NULL, `l_suppkey` int(11) NOT NULL, `l_linenumber` int(11… `l_shipinstruct` char(25) NOT NULL, `l_comment` varchar(44) NOT NULL, `l_shipmode` char(10) NOT …

Filed Under: Benchmarks Tagged With:

Post: Edge-case behavior of INSERT...ODKU

October 27, 2012 By 6 Comments

… the situation: CREATE TABLE update_test ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, username VARCHAR(20) NOT NULL, host_id TINYINT UNSIGNED… always going to be considered as being defined first. So, MySQL checks our INSERT, sees that the next auto-inc value…. Do the math. The maximum value for an auto-increment INT UNSIGNED is 4294967295. Divide that by 1500 qps and then…

Filed Under: Insight for DBAs, Insight for Developers, MySQL
« Older Posts
Newer Posts »