… slow query log until InnoDB again updates the statistics. But when does InnoDB perform the updates aside from the first opening of…: 1 Tmp_disk_tables: 0 Tmp_table_sizes: 0 SET timestamp=1316767697; show table status from `db1`; As you can see… seconds to update all the tables’ statistics. Luckily, this can be controlled with Percona Server with the variable innodb_stats_auto_update (from…
Post: When Does InnoDB Update Table Statistics? (And When It Can Bite)
October 6, 2011 By 5 Comments
Post: Connecting orphaned .ibd files
May 13, 2011 By 12 Comments
… AUTO_INCREMENT, first_name VARCHAR(45) NOT NULL, last_name VARCHAR(45) NOT NULL, last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP… space #15 … Skipped output… SYS_TABLES is updated successfully … Skipped output… SYS_INDEXES is updated successfully It is possible that space_id…
Comment: How to find wrong indexing with glance view
March 8, 2010 By 27 Comments
… table create table orders( order_id int unsigned not null auto_increment primary key, item_type_id tinyint unsigned not null… null, order_date datetime not null, last_updated timestamp not null default current_timestamp on update current_timestamp, foreign key(item_type_id) references item…
Comment: How to find wrong indexing with glance view
March 8, 2010 By 27 Comments
… definitions: create table orders( order_id int unsigned not null auto_increment primary key, item_type_id tinyint unsigned not null… null, order_date datetime not null, last_updated timestamp not null default current_timestamp on update current_timestamp, foreign key(item_type_id) references item…
Post: Long PRIMARY KEY for Innodb tables
October 3, 2006 By 8 Comments
… you do not end up having random primary key BTREE updates which can be very expensive. Today I would like to…(3) unsigned NOT NULL default ’0′, `ts` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, PRIMARY KEY (`url`,`thumb_width`,`thumb_height… be avoided. Long primary key – why did not I use auto_increment id in this case ? I could but this would…
Post: Joining on range? Wrong!
May 17, 2010 By 10 Comments
…) unsigned NOT NULL AUTO_INCREMENT, `itm_prd_id` int(10) unsigned NOT NULL, `itm_order_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY…_prd_id, itm_order_date); UPDATE items_ordered SET itm_order_date = DATE(itm_order_timestamp); Now the rewritten query: EXPLAIN SELECT…
Post: Using Flexviews - part two, change data capture
March 25, 2011 By 11 Comments
… usually operate in one of the following ways: Timestamps (usually more than one) to identify rows that… –table=demo success Note that you can enable auto_changelog=true in the config file to …the deletion. Though they are none shown here, updates would be represented by a deletion followed by an…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…suspicious_udfsblogpercona.commanual ansiblogpercona.commanual auto_increment_incrementblogpercona.commanual auto_increment_offsetblogpercona.commanual …tx_isolationblogpercona.commanual unique_checksblogpercona.commanual updatable_views_with_limitblogpercona.commanual Uptimeblogpercona.commanual…
Post: Percona Server 5.5.10 (Release Candidate)
April 4, 2011 By 4 Comments
…timestamp_always. (Oleg Tsarev) Old system variable slow_query_log_microseconds_timestamp was renamed to slow_query_log_timestamp… (Yasufumi Kinoshita) Old system variable innodb_auto_lru_dump was renamed to …Kinoshita) Bug Fixes Bug #724674 – Ported an updated version of the original implementation …
Post: To UUID or not to UUID ?
March 13, 2007 By 28 Comments
… buffer. The most efficient approach here is not to use auto_increment but use certain partitioned sequences, for example you can… considered best for parallel insert/updates) has pretty much table level locks when it comes to auto_increment columns, which is however… random they are – there is timestamp based part in UUID which has similar properties to auto_increment and which could be used…
