… with one master and two slaves, all running MySQL 5.6 with GTIDs disabled. First try: configure only one of the servers with GTIDs Let’s stop slave #2, change configuration and restart it: mysql…; mysql> start slave; and let’s create a new table on the master: mysql> create table test.t (id int not null auto_increment…
Post: MySQL 5.6.10 Optimizer Limitations: Index Condition Pushdown
March 14, 2013 By 2 Comments
… of the nicest features of the newer MySQL optimizer: the Index Condition Pushdown Optimization, or … `role_id_note` (`role_id`,`note`) ) ENGINE=InnoDB AUTO_INCREMENT=22187769 DEFAULT CHARSET=utf8; The table had …like commands or optimizer_switch flags- we can disable ICP, but not “using index”. So, I…
Post: Is there room for more MySQL IO Optimization?
January 3, 2013 By 11 Comments
… — system files, binary log, FRM files, MySQL MyISAM system tables etc. Starting MySQL 5.5 MySQL uses asynchronous IO which should allow it… Max_data_length: 0 Index_length: 9047113728 Data_free: 6291456 Auto_increment: NULL Create_time: 2012-12-31 16:24:54 Update… available in MySQL 5.5 (different version is added back in MySQL 5.6) so I tried that as well as disabling read…
Post: Statement based replication with Stored Functions, Triggers and Events
December 16, 2011 By 4 Comments
… this case we alter every event adding DISABLE ON SLAVE. ALTER EVENT even_name DISABLE ON SLAVE. LOCAL VARIABLES Local Variables are… same auto incremental values on master and slaves the actual used auto incremental value is logged as an extra event just before the statement. MySQL…
Post: MySQL 5.5 and MySQL 5.6 default variable values differences
February 21, 2013 By 8 Comments
…INCREMENT | 8 | 64 | | MAX_CONNECT_ERRORS | 10 | 100 | | SORT_BUFFER_SIZE | 2097152 | 262144 | | LC_MESSAGES_DIR | /mnt/nfs/dist/mysql…innodb_stats_on_metadata is disabled by default in MySQL 5.6 Welcome to much… users. Considering the default innodb_auto_extend_increment is 64 starting with 64M might…
Post: Percona XtraBackup 1.6
April 12, 2011 By 15 Comments
… passed directly to the xtrabackup script and –incremental-basedir is ignored. (Vadim Tkachenko) Added option –incremental-dir to innobackupex. This option is… some compiler warnings. (Stewart Smith) Ported XtraBackup to MySQL 5.1.55, MySQL 5.5.9, Percona Server 5.1.55-12… for XtraBackup. (Aleksandr Kuzminsky) Disabled auto-creating ib_logfile* when innobackupex is called with –redo-only or with –incremental-dir. If neccessary ib…
Post: The Optimization That (Often) Isn't: Index Merge Intersection
December 14, 2012 By 6 Comments
… that had single-column indexes on them, MySQL could sometimes make use of the multiple indexes… TABLE users ( user_id INT UNSIGNED NOT NULL AUTO_INCREMENT, parent_id INT NOT NULL DEFAULT 0, … adjust the optimizer_switch configuration setting and disable index_merge_intersection. It’s a dynamically-…
Post: Percona Server 5.1.59-13.0
October 16, 2011 By Leave a Comment
…creation has been introduced for enabling or disabling this feature. #858945 (Alexey Kopytov). Bugs …KEY. #851674 (Alexey Kopytov). Backported fix for MySQL bug #53761 (Wrong estimate for RANGE … failure in InnoDB. #814404 (Yasufumi Kinoshita). Since AUTO_INCREMENT columns must be defined as keys, …
Post: Percona Server 5.5.16-22.0
October 16, 2011 By 2 Comments
…from the Percona Software Repositories). Based on MySQL 5.5.16, including all the …creation has been introduced for enabling or disabling this feature. #858945 (Alexey Kopytov). Bugs Fixed… failure in InnoDB. #814404 (Yasufumi Kinoshita). Since AUTO_INCREMENT columns must be defined as keys, omitting…
Post: Performance gotcha of MySQL memory tables
February 1, 2008 By 5 Comments
…comes from the fact it is the only MySQL storage engine which defaults to HASH index type…: CREATE TABLE `test` ( `id` int(11) NOT NULL auto_increment, `c` tinyint(4) default NULL, PRIMARY KEY (`id`), … and log flush on transaction commit was also disabled, meaning Innodb could delay writes and do all…
