June 20, 2013

Post: The small improvements of MySQL 5.6: Duplicate Index Detection

May 31, 2013 By Leave a Comment

…mysql> SHOW CREATE TABLE test\G *************************** 1. row *************************** Table: test Create Table: CREATE TABLE `test` ( `id` int(11) NOT NULL AUTO_INCREMENT,…11) default null # `col3` varchar(200) default null # To remove this duplicate index, execute: ALTER TABLE `test`.`test`…

Filed Under: Insight for DBAs, MySQL, On Ground Training, Percona Toolkit, Training Tagged With: , , , , , , , , ,

Post: Replication in MySQL 5.6: GTIDs benefits and limitations - Part 2

May 30, 2013 By 6 Comments

…: #For s2 (the new master), we remove its configuration as a slave s1> stop …auto_position=1; s0> start slave; # For s1, s3 and s4 mysql> stop slave; mysql> change master to master_port=10002; mysql… each event across the cluster a monotonically incremental identifier for each event. Notice that …

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

Post: Hacking to make ALTER TABLE online for certain changes

October 29, 2007 By 26 Comments

…InnoDB auto-inc scalability issue with MySQL 5.0 and older versions, employing other techniques to maintain the PK auto incremental …auto_increment, `text` text NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB To remove auto_increment, we (1) create table with the same layout but without auto_increment…

Filed Under: Insight for DBAs Tagged With: , ,

Post: MySQL Users Conference - Innodb

May 9, 2007 By 6 Comments

… bunch of others due to statement level MySQL Replication which among other requirements, needs all auto-increment values in multiple value insert to… In MySQL 5.1+ it is also possible to fix this problem for cases when number of rows in auto-increment batch insert… many cases can be removed for READ-COMMITED isolation mode which will start offering more performance benefits in MySQL 5.1 It…

Filed Under: Events and Announcements Tagged With:

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

February 26, 2013 By 1 Comment

…’ll get an error: mysql> CREATE TABLE dir_test_innodb4 (fts_doc_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY); ERROR 1166… sort of sequence value for that column, whether as an auto_increment value or via some other means, but the bit that… its corresponding FTS_*_DOC_ID.ibd file will also be removed. HOWEVER, even if you drop all of the FT indexes…

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

Post: Announcing Percona Server 5.1.65-14.0

September 4, 2012 By Leave a Comment

…). Fixed the upstream MySQL bug #66301. Concurrent INSERT … ON DUPLICATE KEY UPDATE statements on a table with an AUTO_INCREMENT column could result…_STATS or INNODB_INDEX_STATS. Bug fixed #896439 (Stewart Smith). Removed the INFORMATION_SCHEMA table INNODB_PATCHES (actually XTRADB_ENHANCEMENTS) as…

Filed Under: MySQL, Percona Software

Post: DROP TABLE and stalls: Lazy Drop Table in Percona Server and the new fixes in MySQL

June 22, 2012 By 33 Comments

… Oracle MySQL >= 5.5.23 The main function that is responsible for cleaning the bufferpool is buf_LRU_flush_or_remove_pages… structure: CREATE TABLE t_x(i int(11) not null auto_increment primary key) And during the benchmark, these 30 tables were… –test=oltp –oltp-table-size=20000000 –mysql-table-engine=innodb –mysql-socket=/tmp/mysql_sandbox5524.sock –mysql-db=test –oltp-table-name=sbtest_20m…

Filed Under: Benchmarks, Insight for DBAs, MySQL

Post: Announcing Percona Server 5.5.27-28.0

August 23, 2012 By 5 Comments

…). Fixed the upstream MySQL bug #66301. Concurrent INSERT … ON DUPLICATE KEY UPDATE statements on a table with an AUTO_INCREMENT column could result… innodb_autoinc_lock_mode=1. Bug fixed #1035225 (Alexey Kopytov). Removed error log warnings that occured after enabling innodb_use_sys…

Filed Under: MySQL

Post: Dynamic row format for MEMORY tables

September 6, 2011 By 5 Comments

… MySQL versions, including the latest 5.6 development tree as of last week. There have been efforts in the community to remove this limitation. In 2008 Igor Chernyshev of eBay implemented true-VARCHAR support for MEMORY tables for MySQL 5.0… following: CREATE TABLE sbtest (id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, c VARCHAR(4096) NOT NULL DEFAULT ”, PRIMARY KEY (id)) ENGINE…

Filed Under: MySQL, Percona Software

Post: The Optimization That (Often) Isn't: Index Merge Intersection

December 14, 2012 By 6 Comments

… columns that had single-column indexes on them, MySQL could sometimes make use of the multiple indexes. … CREATE TABLE users ( user_id INT UNSIGNED NOT NULL AUTO_INCREMENT, parent_id INT NOT NULL DEFAULT 0, status … parent_id = 0, we might be tempted to remove the index on parent_id if queries for …

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